如何写“创建用户?”with MySQL prepared语句

Question

我试过了：

SET @user = 'foo@localhost';
SET @pass = 'bar';
SET @sql = 'CREATE USER ? IDENTIFIED BY ?';
PREPARE stmt FROM $sql;

我得到了错误

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 '? IDENTIFIED BY ?'

我也尝试了多种不同的变量定义方式，但仍然一无所获。

SET @user = "'foo'@'localhost'";
SET @sql = 'CREATE USER ? IDENTIFIED BY ?';

甚至

SET @user = 'foo';
SET @host = 'localhost';
SET @sql = 'CREATE USER ?@? IDENTIFIED BY ?';

类似的问题在两年多前就已经被问过了，但仍然没有有用的答案:( Create User with MySQLi Prepared Statement

Answer 1

这是我创建用户的唯一方法，不幸的是它没有为用户变量使用占位符：

SET @user := 'foo';
SET @host := 'localhost';
SET @pass := 'bar';
SET @sql := CONCAT("CREATE USER ", QUOTE(@user), "@", QUOTE(@host), " IDENTIFIED BY ", QUOTE(@pass));
PREPARE stmt FROM @sql;
EXECUTE stmt;

P.S.应从MySQL 5.1.12开始支持{CREATE | RENAME | DROP} USER语句