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

在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.

及用户变量使用文档：

喜欢 (9)or分享 (0)