mssql2mysql

mssql 部分语句转 mysql

https://blog.csdn.net/weixin_47779182/article/details/121349393

id()

MS SQL newid() vs MySQL uuid()

时间转换 如 2022-01-01

mysql

DATE_FORMAT(rwol.CreateDate, "%Y %m %d %k:%i:%s")
DATE_FORMAT(rwol.CreateDate, "%Y %m %d")

SELECT name, data
FROM   (SELECT di.DefectItemName AS name, SUM(rwol.Qty) AS data
          FROM    Production_ReportWorkOrderList AS rwol LEFT OUTER JOIN
                     Base_DefectItem AS di ON di.DefectItem_Id = rwol.DefectItem
          GROUP BY di.DefectItemName,DATE_FORMAT(rwol.CreateDate, "%Y %m %d")) AS a
WHERE (data <> 0)

mssql

CONVERT(VARCHAR(10), rwol.CreateDate, 120)

SELECT name, data
FROM   (SELECT di.DefectItemName AS name, SUM(rwol.Qty) AS data
          FROM    dbo.Production_ReportWorkOrderList AS rwol LEFT OUTER JOIN
                     dbo.Base_DefectItem AS di ON di.DefectItem_Id = rwol.DefectItem
          GROUP BY di.DefectItemName, CONVERT(VARCHAR(10), rwol.CreateDate, 120)) AS a
WHERE (data <> 0)

一、 top —— limit

mssql:

select top 10 * from table

mysql:

select * from table limit 10 ***注意：limit 是在 最后！最后！最后！比order by还后！***

二、 替换DECLARE （只是本人在项目中遇到的，可以忽略）

mssql：

DECLARE @ff VARCHAR(MAX),@str VARCHAR(MAX) 
 SELECT @ff=(''''+REPLACE(rm.MenuID,',',''',''')+'''') FROM H rm 
 LEFT JOIN U u ON rm.RoleID=u.User_LevelID 
 WHERE u.UserID='001' 
 SET @str='select *  from M where MenuID IN ('+@ff+') ORDER BY CAST(Mark AS INT)' 
 exec (@str)

mysql：

 SELECT * FROM M
 WHERE FIND_IN_SET( MenuID ,(SELECT rm.MenuID FROM M rm 
 LEFT JOIN HR_User u ON rm.RoleID=u.User_LevelID 
 WHERE u.UserID='admin') ) 
 ORDER BY CAST(Mark AS SIGNED);

FIND_IN_SET():

list是常量，则可以直接用IN， 否则要用find_in_set()函数。

也就是这两个sql是查询的效果是相同的：

SELECT id,name,list from tb_test WHERE FIND_IN_SET('daodao',list);

SELECT * from C_PURCHASINGMASTERDATA where FIND_IN_SET(EKGRP,'C54,C02,C14,C60,C06,C61,C53,C51,C12,C08,C03,C07')

SELECT * from C_PURCHASINGMASTERDATA where EKGRP in ('C54','C02','C14','C60','C06','C61','C53','C51','C12','C08','C03','C07')

三、 in 子查询用法

mssql: select * from S where ID not in (select id from S where) （在mssql中这样使用没问题但是到mysql中就需要包起来）

mysql: SELECT * FROM S WHERE ID NOT IN(SELECT S.ID FROM (SELECT * FROM S WHERE 1=1 ) AS S )

四、 临时表创建的不同

mssql:

     SELECT * INTO #temp FROM S WHERE 1=1 
     SELECT * FROM #temp
     DROP TABLE #temp

mysql:

CREATE TEMPORARY TABLE temp(
     SELECT * FROM S WHERE 1=1
);

SELECT * FROM temp;
DROP TABLE temp;

注意：mysql 三句一起执行的话一定要 加分号！加分号！加分号！这样就会前一句成功了才会执行下一句，否则。。。自己试一下^ . ^

五、 LEN —— LENGTH

**mssql:**  LEN(Note//字段)>10//长度

**mysql:** LENGTH(Note//字段)>10//长度

六、 Cast（）与 CONVERT（）

mssql mysql

int    SIGNED
varchar    CHAR(N)

Cast（）与 CONVERT（） 用法不同 值类型一样 CAST(xxx AS 类型), CONVERT(xxx,类型)

七、 IDENTITY()—— max(id)

 **mssql:** select IDENTITY(INT,1,1) INTO #temp1 FROM  table 
 **mysql:** CREATE TEMPORARY TABLE temp1(SELECT MAX(id) from table )

注意： mysql 中的max(id)只限在创建临时表中或者同时只改单个表的时候用，同时多表插入请使用LAST_INSERT_ID()

八、 UNION ALL

mssql:

SELECT * INTO #temp FROM Q
UNION ALL 
SELECT * FROM QX
UNION ALL
SELECT * FROM QF 
SELECT * FROM #temp";
DROP TABLE #temp

mysql:

CREATE TEMPORARY TABLE temp AS(
SELECT * FROM
 ((SELECT * FROM Q)
  UNION ALL 
  (SELECT * FROM QX )
  UNION ALL 
  (SELECT * FROM QF))
  AS temp
);

 SELECT * ROM temp;
 DROP TABLE temp;

mysql中在 CREATE TEMPORARY TABLE 是不能直接用 UNION ALL 的，需要包含到子查询中，否则。。自己可以尝试一下 ^ . ^

九、 ISNULL —— IFNULL

**mssql：** ISNULL(e , 0) 
**mysql：** IFNULL(e , 0) 

e代表要替换的值，当e为null时就会被0代替，否则还是其本身

mysql扩展 ： 1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0
2.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1

十、 WITH AS 和 OVER (ORDER BY ) 转

mssql:

WITH questions AS
(
SELECT tx.ID,tx.QID, ROW_NUMBER() OVER (ORDER BY tx.ID) AS RowNumber
FROM QuestionTX tx
) 
SELECT * FROM questions ;

mysql:

CREATE TEMPORARY TABLE questions AS
(
    SELECT * FROM (
    SELECT ID, QID,RowNumber 
    FROM (
        SELECT Q.ID, Q.QID @rownum := @rownum + 1,
                IF (
                @pjan = Q.ID AND @grade = Q.QID,
                @rank :=@rank + 1 ,@rank := @rank + 1 //（我这里为了满足项目需求才写成这样的正常是@rank :=@rank + 1 ,@rank := 1）
                ) AS RowNumber,
                # 定义了两个个参数 Q.ID 和 Q.QID，通过if函数判断，
                # 当这两个参数与查询出来的数据一样的时候，我们自增，不然那就从1开始重新计数
                @pjan := Q.ID,
                @grade := Q.QID
            FROM
            (
                SELECT tx.ID, tx.QID
                FROM QuestionTX tx
                ORDER BY QID,ID DESC
            ) Q,
            (
            SELECT @rownum := 0, @grade := NULL ,@pjan := NULL ,@rank := 0
            ) a
        ) result
    ) AS questions
);
SELECT * FROM questions ;
DROP TABLE questions;

十一

Fatal error encountered during command execution.
我中途遇到了这个Fatal error encountered during command execution.
查了以后发现是：因为在sql语句中使用了自定义参数：类似这样 @rank :=@rank + 1
所以……
需要在连接的URL内加上

Allow User Variables=True

，完美解决

<add key="HRconnectionstring" value="Server= .;DataBase=.;Persist Security Info=True;UID=s;PWD=123;port=3366;Allow User Variables=True"/>

十二、 CONCAT 字符拼接

例子： CONCAT('T' ,id)

十三、 GETDATE()——NOW()

两个都是获取时间的，mssql的GETDATE（）可以直接在mysql中用NOW（）获得

十四、 FOR XML PATH(‘’) ——GROUP_CONCAT（）
mssql:

SELECT  ''''+QID+''''+',' FROM Q 
ORDER BY 排序字段 FOR XML PATH('')

mysql:

SELECT  CONCAT(CONCAT('''',GROUP_CONCAT(DISTINCT QID SEPARATOR ''',''')),'''')   FROM Q
ORDER BY 排序字段

FOR XML PATH 是将查询结果集以XML形式展现
GROUP_CONCAT把多条记录的某个字段拼成一条记录的字段值