使用变量创建MySQL用户？

Question

我想这样做：

#Variables
SET @username="jdoe", @password="secret";

# Insert a new MySQL User
CREATE USER @username@'localhost' IDENTIFIED BY @password;GRANT USAGE ON *.* TO @username@'localhost' IDENTIFIED BY @password WITH MAX_QUERIES_PER_HOUR 120 MAX_CONNECTIONS_PER_HOUR 60 MAX_UPDATES_PER_HOUR 60 MAX_USER_CONNECTIONS 2;

但是得到：

#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 '@username@'localhost' IDENTIFIED BY @password' at line 2

我如何在CREATE USER语句中使用变量？

- Nick

---

可能是使用变量创建用户名和密码时出现语法错误的重复问题。 - aleroot

@aleroot 我不确定这是否是同一个问题，因为它涉及到创建存储过程。 - Nick

Answer 1

你可以使用 动态 SQL 来实现：

SET @query1 = CONCAT('
        CREATE USER "',@username,'"@"localhost" IDENTIFIED BY "',@password,'" '
        );
PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @query1 = CONCAT('
    GRANT USAGE ON *.* TO "',@username,'"@"localhost" IDENTIFIED BY "',@password,'" WITH
          MAX_QUERIES_PER_HOUR 120 MAX_CONNECTIONS_PER_HOUR 60 MAX_UPDATES_PER_HOUR 60 
          MAX_USER_CONNECTIONS 2'
        );
PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

- Omesh

---

这似乎不起作用... 我得到`＃1064-您的SQL语法中有错误；请检查与您的MySQL服务器版本相对应的手册，以获取使用正确语法的方法，在第1行处' secret; GRANT USAGE ON . TO jdoe@'localhost' IDENTIFIED BY secret ' - Nick