Recently, the company's project needs to be transferred from SQL Server to MySQL. In the process of transferring, some differences between the two grammars are encountered. After finding a solution on the Internet, it is recorded here. Since there may be many solutions, I only record what I have used for reference.

1. Splicing string

• 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 and update set select syntax

• 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. Use limit in MySQL subquery

• 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

• 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 concatenated string + character set

• MSSQL

  select MainCommodityName
  from Fct_MainCommodity
  where (MainCommodityName + ifnull(MainCommodityName_Postfix, '')) = 'Attachment upload raw materials A enter A Out of 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 =
        'Attachment upload raw materials A enter A Out of 1003'
        and Disabled = 0 and ifnull(IsAutoHide, 0) != 1 and MainCommodityId != '27135417-a42b-453f-a1cc-1617d6fc471e';

6 use regular in SQL

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