轉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]'