【MySQL】ERROR 1064

错误信息

这种错误一般出现在运行SQL语句的时候，往往是SQL的语法有问题，有以下几种情况

1. 执行DDL语句时，例如对数值类型的字段修改字符集。

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 'CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci' at line 1

1. 执行任意类型语句时，语句中包含系统关键字。

ERROR 1064 (42000) at line 1: 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 'Key = 'eureka.service.url'' at line 1

原因分析/错误复现

1.针对数值型的字段，执行了修改字符集时会报错。

示例来自于zabbix数据库

ALTER TABLE zabbix.items MODIFY COLUMN itemid bigint(20) unsigned CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2.通过命令行执行语句时，查询的字段Key是系统关键字。

$ mysql -hxxx -pxxx dbname -e "select * from xxx where Key = 'xxx'\G" 
ERROR 1064 (42000) at line 1: 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 'Key = 'xxx'' at line 1

解决方法

1. 数值型不能修改字符集，只能是字符型的才能修改字符集。

2. 因为命令行中包含SQL的关键字。此例子系统关键字是”Key”，需要添加 ` 进行转义。

mysql -hxxx -pxxx dbname -e "select * from tbname where \`Key\` = 'xxx'\G" 
*************************** 1. row ***************************
                       Id: 1