一旦工作,那就要努力的干,聪明的干,快速的干——用省下来的时间干自己喜欢干的事情。!

MySQL授权时使用变量出错,提示ERROR 1064 (42000): You have an error in your SQL syntax;

数据库 lampnick 5483℃ 0评论

在MySQL中使用下面的形式对变量进行赋值操作:

SET @dbname = 'test';
SET @dbaccount = 'test';

执行下列语句时会报如下错误: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 '%' at line 18

GRANT 
 SELECT,
 INSERT,
 UPDATE,
 DELETE,
 CREATE,
 DROP,
 INDEX,
 ALTER,
 CREATE TEMPORARY TABLES,
 LOCK TABLES,
 EXECUTE,
 CREATE VIEW,
 SHOW VIEW,
 CREATE ROUTINE,
 ALTER ROUTINE,
 EVENT,
 TRIGGER ON @dbname.* TO @dbaccount@'%';
FLUSH PRIVILEGES;

原因:MySQL对变量引用没能成功解析,因为里面有多个@符号。

解决方法:将上面红色的修改为:ON `@dbname`.* TO `@dbaccount`@'%'; 变量两边加上反引号即可解决。

请参考MySQL官网文档:A user variable cannot be used directly in an SQL statement as an identifier or as part of an identifier. See Section 9.4, “User-Defined Variables”, for more information and examples of workarounds.

https://dev.mysql.com/doc/refman/5.5/en/identifiers.html

及用户变量使用文档:

https://dev.mysql.com/doc/refman/5.5/en/user-variables.html

转载请注明:lampNick » MySQL授权时使用变量出错,提示ERROR 1064 (42000): You have an error in your SQL syntax;

喜欢 (0)or分享 (0)
头像
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址