[Err] 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 ''<h1 style="text-align: center;">php

[Err] 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 ''&lt;h1 style=&quot;text-align: center;&quot;&gt;php实现 字符串分割&lt;/h' at line 1

一、总结

一句话总结:我本来都是直接打开sql复制里面的内容到mysql中执行,这次是用nodepad++,所以出错。这不是错误,1064是语法错误。

二、[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL s

[Err] 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 at line 3

MySQL 5.6,Navicat For MySQL 10.0.10 中执行SQL语句时提示此错误,在CMD中执行SQL文件中的语句时提示以下错误:

 D:\Users\Aven>mysql -uroot -p < F:\Publish\Data\share_update.sql
Warning: Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the ma
nual that corresponds to your MySQL server version for the right syntax to use n
ear 'ALTER聽TABLE聽mcu聽CHANGE聽`LOCAL_ID`聽`LOCAL_ID`聽VARCHAR(50)聽'
at line 1

解决方案:

发现解决该问题的关键,是错误提示中的空格都是“聽”,这让我想到是文件的编码问题,然后做文件编码的转换。 
推荐使用Notepad++,编码转换非常方便,如图: 
[Err] 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 ''&lt;h1 style=&quot;text-align: center;&quot;&gt;php

转换为UTF-8格式,问题依旧,再转为ANSI格式试试: 
[Err] 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 ''&lt;h1 style=&quot;text-align: center;&quot;&gt;php

[Err] 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 ''&lt;h1 style=&quot;text-align: center;&quot;&gt;php

哈哈,原形毕露了吧!把?都替换为空格,保存,再执行,一切OK了。

反思:

为什么会出现这种问题呢? 
这些语句是从QQ复制到Word中,又从Word中复制到Notepad++中, 
估计是从QQ或Word中复制出来的空格有问题。

三、完美解决 ERROR 1064 (42000): You have an error in your SQL syntax ... near …

在MySQL命令行使用sql语句进行建表时,MySQL 报错,这个问题之前遇到过几次,但是总是会因为疏忽又相遇,今天把这个问题写出来,加深印象吧。

sql语句:

CREATE TABLE 'lrs_audit_rule_package'(
'id' BIGINT(20) AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
'package_code' varchar(6) NOT NULL COMMENT '规则包',
'package_type' varchar(2) NOT NULL COMMENT '规则包类型',
'package_desc' varchar(100) COMMENT '描述',
'create_time' datetime DEFAULT NULL COMMENT '创始时间',
'modified_time' datetime DEFAULT NULL COMMENT '修改时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='审核规则包';

看上去这条sql语句确实没毛病,但是运行起来就是报错
报错信息:

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 ''lrs_audit_rule_package'(
'id' BIGINT(20) AUTO_INCREMENT PRIMARY KEY COMMENT ' at line 1

其实这个问题就是语法上的错误,在MySQL中,为了区分MySQL的关键字与普通字符,MySQL引入了一个反引号。
详情见:https://dev.mysql.com/doc/refman/5.5/en/keywords.html

在上述的sql语句中,列名称使用的是单引号而不是反引号,所以会就报了这个错误出来。修改后

CREATE TABLE `lrs_audit_rule_package`(
`id` BIGINT(20) AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
`package_code` varchar(6) NOT NULL COMMENT '规则包',
`package_type` varchar(2) NOT NULL COMMENT '规则包类型',
`package_desc` varchar(100) COMMENT '描述',
`create_time` datetime DEFAULT NULL COMMENT '创始时间',
`modified_time` datetime DEFAULT NULL COMMENT '修改时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='审核规则包';

再次运行就不会报错了,但是有一点需要注意,后面列的注释不能用反引号,因为这只是一个普通字符串,不是MySQL的关键字。

在英文键盘输入环境下,按图示按钮输入反引号 `

[Err] 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 ''&lt;h1 style=&quot;text-align: center;&quot;&gt;php

 
上一篇:curl 命令详解


下一篇:Linux之curl命令详解