代码改变世界

MySQL保留字 ERROR 1064 (42000)

2018-08-01 16:12  WWJD_DBA  阅读(463)  评论(0)  编辑  收藏  举报

在MySQL（5.7.18）数据库中建表

 1 CREATE TABLE SA_ACT_ITEM
 2 (
 3    ITEMID               BIGINT(14) NOT NULL,
 4    REGION               INT(5),
 5    ACTIONID             VARCHAR(32),
 6    ITEMNAME             VARCHAR(64),
 7    MINVALUE             BIGINT(16),
 8    MAXVALUE             BIGINT(16),
 9    USESCORE             BIGINT(16),
10    USETIMES             INT(5),
11    STARTDATE            DATETIME,
12    ENDDATE              DATETIME,
13    NOTES                VARCHAR(256),
14    CREATEDATE           DATETIME,
15    STATUS               INT(1),
16    STATUSDATE           DATETIME,
17    REASON               VARCHAR(64)
18 );

出现ERROR 1064 (42000)的错误，如下

 1 root@localhost:mysql3306.sock [(none)]>use zxl;
 2 Database changed
 3 root@localhost:mysql3306.sock [zxl]>CREATE TABLE SA_ACT_ITEM
 4     -> (
 5     ->    ITEMID               BIGINT(14) NOT NULL,
 6     ->    REGION               INT(5),
 7     ->    ACTIONID             VARCHAR(32),
 8     ->    ITEMNAME             VARCHAR(64),
 9     ->    MINVALUE             BIGINT(16),
10     ->    MAXVALUE             BIGINT(16),
11     ->    USESCORE             BIGINT(16),
12     ->    USETIMES             INT(5),
13     ->    STARTDATE            DATETIME,
14     ->    ENDDATE              DATETIME,
15     ->    NOTES                VARCHAR(256),
16     ->    CREATEDATE           DATETIME,
17     ->    STATUS               INT(1),
18     ->    STATUSDATE           DATETIME,
19     ->    REASON               VARCHAR(64)
20     -> );
21 ERROR 1064 (42000): 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 'MAXVALUE             BIGINT(16),
22    USESCORE             BIGINT(16),
23    USETIMES' at line 8
24 root@localhost:mysql3306.sock [zxl]>

建议不要在创建表的过程中使用mysql保留字，往这里看https://dev.mysql.com/doc/refman/5.5/en/keywords.html

上述连接中看到MAXVALUE是mysql的保留字，当使用了mysql保留字，需要用反引号将其引起来，也就是键盘数字1旁边的键（` `）

下面将该字段使用反引号引起来，再次执行即可，如下：

 1 root@localhost:mysql3306.sock [zxl]>CREATE TABLE SA_ACT_ITEM
 2     -> (
 3     ->    ITEMID               BIGINT(14) NOT NULL,
 4     ->    REGION               INT(5),
 5     ->    ACTIONID             VARCHAR(32),
 6     ->    ITEMNAME             VARCHAR(64),
 7     ->    MINVALUE             BIGINT(16),
 8     ->    `MAXVALUE`             BIGINT(16),
 9     ->    USESCORE             BIGINT(16),
10     ->    USETIMES             INT(5),
11     ->    STARTDATE            DATETIME,
12     ->    ENDDATE              DATETIME,
13     ->    NOTES                VARCHAR(256),
14     ->    CREATEDATE           DATETIME,
15     ->    STATUS               INT(1),
16     ->    STATUSDATE           DATETIME,
17     ->    REASON               VARCHAR(64)
18     -> );
19 
20 Query OK, 0 rows affected (1.20 sec)
21 
22 root@localhost:mysql3306.sock [zxl]>