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

最近公司项目需要从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]'