无法在discordjs中找到mysql语法错误

Question

我试图使用mysql包在discordjs中进行sql查询。查询(如果在sql server中使用)完全按照预期工作，但我得到一个PARSE ERROR试图在js中使用相同的查询:

SET @ROW_NUMBER = 0; SELECT s.level,s.exp,s.number 
FROM ( SELECT userID,level,exp,(@ROW_NUMBER:=@ROW_NUMBER + 1 ) AS number FROM levels ORDER BY level DESC) AS s 
WHERE userID = "${userID}";

discordjs中的代码:

//some code...
con.query(`SET @ROW_NUMBER = 0; SELECT s.level,s.exp,s.number 
FROM ( SELECT userID,level,exp,(@ROW_NUMBER:=@ROW_NUMBER + 1 ) AS number 
FROM levels ORDER BY level DESC) AS s 
WHERE userID ="${userID}";`,(err,rows) =>{
if(err)return console.log(err); 
//other code

我得到的错误:

code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: "You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'SELECT s.level,s.exp,s.number FROM ( SELECT userID ,level ,exp ,(@ROW_NUMBER:...' at line 1",
sqlState: '42000',
index: 0,
sql: 'SET @ROW_NUMBER = 0; SELECT s.level,s.exp,s.number FROM ( SELECT
userID ,level ,exp ,(@ROW_NUMBER:=@ROW_NUMBER + 1 ) AS number FROM levels
ORDER BY level DESC) AS s WHERE userID = "258217948819357697";'

Answer 1

错误消息显示您的连接器不允许多查询(两个查询作为一个批量发送)。

将变量初始化移到FROM子句中

SELECT s.level,s.exp,s.number 
FROM ( SELECT userID, level, exp, ( @ROW_NUMBER:=@ROW_NUMBER + 1 ) AS number 
FROM levels, (SELECT @ROW_NUMBER := 0) init_variable
ORDER BY level DESC ) AS s 
WHERE userID = "${userID}";

p。如果您的MariaDB版本是10.2.1或更高版本，则在CTE中使用ROW_NUMBER()窗口函数而不是变量。