当前位置： 移动技术网 > IT编程>数据库>Mysql > 转MySQL遇到的语法差异及解决方案

转MySQL遇到的语法差异及解决方案

2018年09月29日  | 移动技术网IT编程  | 我要评论

最近公司项目需要从sql server转到mysql， 在转的过程中遇到两者语法之间的一些差异，在网上找了解决方案后，特记录在此。由于解决方案可能有很多种，我只记录了自己使用过的，仅作参考。

1. 拼接字符串

使用group_concat方法

  • mssql

    ( select    left(dal.deliveryarealist,
                 len(dal.deliveryarealist) - 1)
  from      ( select    ( select    cast(deliveryarea as varchar)
                                    + '|'
                          from      rel_maincommoditydeliveryarea
                          where     maincommodityid = a.maincommodityid
                                    and deliveryarea <> 0
                                    and disabled = 0
                        order by deliveryarea asc
                        for
                          xml path('')
                        ) as deliveryarealist
            ) dal
) as deliveryareaslist

  • mysql

    (select 
 group_concat(rmcda.deliveryarea order by rmcda.deliveryarea desc separator '|')
from rel_maincommoditydeliveryarea rmcda
where rmcda.maincommodityid = a.maincommodityid and rmcda.deliveryarea <> 0 and rmcda.disabled = 0) as deliveryareaslist

2. mysql中和update set select语法

mysql中update set select无from,需要使用更新多表的语法

  • mssql

    update fc
set fc.usescenarios   = (isnull(fc.inheritname, '')
                           + isnull(fmc.maincommodityname_postfix, '')
                           + '-' + isnull(cn.channelalias, '')
                           + isnull(fc.commodityname_postfix, '')),
  fc.usescenariosen   = (isnull(fc.commodityname_prefix, '')
                           + isnull(fc.inheritname, '')
                           + isnull(fmc.maincommodityname_postfix,
                                    '')
                           + isnull(fc.commodityname_postfix, '')),
  fc.[rec_modifyby]   = '{updateuser}',
  fc.[rec_modifytime] = now(3)
from fct_commodity as fc
  inner join fct_maincommodity as fmc on fc.maincommodityid = fmc.maincommodityid
  inner join dim_channel as cn on fc.channelid = cn.channelid
where fc.disabled = 0
      and fmc.disabled = 0
      and fc.inheritname is not null
      and fc.inheritname <> ''
      and fmc.[maincommoditycode] in ({codelist})

  • mysql

    update fct_commodity fc, fct_maincommodity fmc, dim_channel cn
set fc.usescenarios = (ifnull(fc.inheritname, '')
                       + ifnull(fmc.maincommodityname_postfix, '')
                       + '-' + ifnull(cn.channelalias, '')
                       + ifnull(fc.commodityname_postfix, '')),
  fc.usescenariosen = (ifnull(fc.commodityname_prefix, '')
                       + ifnull(fc.inheritname, '')
                       + ifnull(fmc.maincommodityname_postfix,
                                '')
                       + ifnull(fc.commodityname_postfix, '')),
  fc.rec_modifyby   = '{updateuser}',
  fc.rec_modifytime = now(3)
where
  fc.maincommodityid = fmc.maincommodityid
  and fc.channelid = cn.channelid
  and fc.disabled = 0
  and fmc.disabled = 0
  and fc.inheritname is not null
  and fc.inheritname <> ''
  and fmc.maincommoditycode in ({codelist})

3. mysql子查询中使用limit

mysql中子某些子查询不允许limit, 如需要使用,需要用select再包一层

  • mssql

    select unitid,unitname
from dim_unit
where unitname in (
                  select top 6 fmc.unit
                  from fct_maincommodity fmc inner join dim_unit du on fmc.unit=du.unitname
                  where fmc.disabled=0 and du.disabled=0
                  group by fmc.unit
                  order by count(fmc.unit) desc
                 )

  • mysql

    select
  unitid,
  unitname
from dim_unit
where unitname in (
  select temp.unit
  from
    (select fmc.unit
     from fct_maincommodity fmc inner join dim_unit du on fmc.unit = du.unitname
     where fmc.disabled = 0 and du.disabled = 0
     group by fmc.unit
     order by count(fmc.unit) desc
     limit 6) temp)

4. parameter '@rec_createtime' must be defined

参数化拼sql, 不要用now(3), 直接在代码里面获取当前时间

  • mssql

    public static hashtable createbycheck(hashtable htvalue,string userid)
{
    if (!htvalue.contains("rec_createtime"))
    {
        htvalue.add("rec_createtime", "now(3)");
    }
    if (!htvalue.contains("rec_createby"))
    {
        htvalue.add("rec_createby", httpcontext.current == null ? "admin" : userid);
    }
    return htvalue;
}

  • mysql

    public static hashtable createbycheck(hashtable htvalue,string userid)
{
    if (!htvalue.contains("rec_createtime"))
    {
        htvalue.add("rec_createtime", datetime.now);
    }
    if (!htvalue.contains("rec_createby"))
    {
        htvalue.add("rec_createby", httpcontext.current == null ? "admin" : userid);
    }
    return htvalue;
}

5 拼接字符串+字符集

  1. (maincommodityname + ifnull(maincommodityname_postfix, ''))拼接得不到想要的结果

  2. [hy000][1267] illegal mix of collations (utf8_bin,none) and (utf8_general_ci,coercible) for operation '=': 需要加 collate utf8_general_ci 统一字符集

  • mssql

    select maincommodityname
from fct_maincommodity
where (maincommodityname + ifnull(maincommodityname_postfix, '')) = '附件上传原料a进a出1003' and disabled = 0 and
      ifnull(isautohide, 0) != 1 and maincommodityid != '27135417-a42b-453f-a1cc-1617d6fc471e';

  • mysql

    select maincommodityname
from fct_maincommodity
where concat(maincommodityname, cast(ifnull(maincommodityname_postfix, '') as nchar(50))) collate utf8_general_ci =
      '附件上传原料a进a出1003'
      and disabled = 0 and ifnull(isautohide, 0) != 1 and maincommodityid != '27135417-a42b-453f-a1cc-1617d6fc471e';

6 sql中使用正则

mssql中like后面可以使用正则，但是mysql需要使用regexp

  • mssql
 select isnull( max(brandcode),99999)+1 as brandcode from fct_brand 
                where brandcode like '[0-9][0-9][0-9][0-9][0-9][0-9]'
  • mysql
select ifnull( max(brandcode),99999)+1 as brandcode from fct_brand 
                where brandcode regexp '[0-9][0-9][0-9][0-9][0-9][0-9]'

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网