MySQL CREATE USER with a variable?

You can use a dynamic query for this. First set the variable name for username and variable name for a password. The syntax is as follows −

SET @anyVariableName=’yourUserName’; SET @anyVariableName1=’yourpassword’;

Now you can use the CONCAT() function from MySQL. The syntax is as follows −

SET @yourQueryName = CONCAT ('    CREATE USER "',@anyVariableName,'"@"localhost" IDENTIFIED BY "',@anyVariableName1,'" ' );

Let us use the prepared statement PREPARE. The syntax is as follows −

PREPARE yourStatementVariableName FROM @yourQueryName;

Now you can execute the statement. The syntax is as follows −

EXECUTE yourStatementVariableName;

Deallocate the above using the DEALLOCATE PREPARE. The syntax is as follows −

DEALLOCATE PREPARE yourStatementVariableName;

To understand the above syntax, let us follows all the steps −

Step 1 − First create two variables, one for username and second for a password using SET command.

the query is as follows to create a username −

mysql> set @UserName:='John Doe'; Query OK, 0 rows affected (0.00 sec)

The query to create a password.

mysql> set @Password:='John Doe 123456'; Query OK, 0 rows affected (0.00 sec)

Step 2 − Now use the CONCAT() function to create a user. The query is as follows −

mysql> SET @CreationOfUser = CONCAT('    '> CREATE USER "',@UserName,'"@"localhost" IDENTIFIED BY "',@Password,'" '    -> ); Query OK, 0 rows affected (0.02 sec)

In the above query, we have used @UserName variable name and @Password variable name to create a user with name and password.

Step 3 − Now you need to prepare the statement using the above user-defined variable @CreationOfUser. The query is as follows −

mysql> PREPARE st FROM @CreationOfUser; Query OK, 0 rows affected (0.00 sec) Statement prepared

Step 4 − Execute the above-prepared statement. The query is as follows −

mysql> EXECUTE st; Query OK, 0 rows affected (0.37 sec)

Step 5 − Check the user “John Doe” has been created in the MySQL.user table −

mysql> select user,host from MySQL.user;

The following is the output −

+------------------+-----------+ | user             | host      | +------------------+-----------+ | Manish           | %         | | User2            | % | | mysql.infoschema | % | | mysql.session | % | | mysql.sys | % | | root | % | | @UserName@ | localhost | | Adam Smith | localhost | | John | localhost | | John Doe | localhost | | User1 | localhost | | am | localhost | | hbstudent | localhost | +------------------+-----------+ 13 rows in set (0.00 sec)

Yes, we have a username with John Doe.

Step 6 − Now, DEALLOCATE the prepared statement. The query is as follows −

mysql> DEALLOCATE PREPARE st; Query OK, 0 rows affected (0.00 sec)