I hava a sql that work fine in mysql 5.7.35, but couldn't work in 8.0.22-CodePudding

Here is my sql sentence

INSERT IGNORE INTO user_predicts (id,points,rank,rid,uid,win_count)
VALUES (0,0,0,1,1016,0)

All the fields in this table is int But in 8.0.22, it just give me this bottom line:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank,rid,uid,win_count) VALUES (0,0,0,1,1016,0)' at line 1

But this sql works fine in 5.7.35

CodePudding user response：

RANK is a reserved word in MySQL v8, added since version 8.0.2. You can't use it in query unless you wrap it with backticks (`). Try this:

INSERT IGNORE INTO user_predicts (id,points,`rank`,rid,uid,win_count) VALUES 
(0,0,0,1,1016,0)

Here's MySQL documentation about reserved words. Please note that all words appended with (R) in the docs must wrap in backticks if you want to use it. Either that, or just change you column name to Ranks.

CodePudding user response：

You are using the RANK keyword as field name. In MySQL 8.0, the word is used as a window function RANK(). You must replace the field name rank with a different name.