Mysql8 版本兼容问题

先看一下查询 SELECT rownum – 1 AS ROW FROM (SELECT *, (@i :=@i + 1) AS rownum FROM xms_document, (SELECT @i := 0) AS it WHERE states=1 and catid=13 ORDER BY sortid desc,createtime desc) AS t1 WHERE id = 181

这个查询在Mysql5.7里面能正常,跑到Mysql8里面报错了。且不吐槽为啥开发这样写吧,单拆这个问题去Debug。拿到里面的子查询 SELECT *, (@i :=@i + 1) AS rownum FROM xms_document, (SELECT @i := 0) AS it WHERE states=1 and catid=13 ORDER BY sortid desc,createtime desc 发现是正常的。

最后结果发现是SELECT rownum – 1 AS ROW中ROW为Mysql关键词,最后解决加了“,修正为先看一下查询 SELECT rownum – 1 AS `ROW` FROM (SELECT *, (@i :=@i + 1) AS rownum FROM xms_document, (SELECT @i := 0) AS it WHERE states=1 and catid=13 ORDER BY sortid desc,createtime desc) AS t1 WHERE id = 181