- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 −
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)
- Select into a user-defined variable with MySQL
- MySQL ORDER BY with numeric user-defined variable?
- Create a new user with password in MySQL 8?
- Perform MySQL SELECT INTO user-defined variable
- How to create MySQL user with limited privileges?
- Set user-defined variable with table name in MySQL prepare statement?
- Set user variable from result of query in MySQL?
- Set custom messages by working with MySQL IF Statements and SELECT in a user-defined variable
- Updating a MySQL table row column by appending a value from user defined variable?
- How Can we permanently define user-defined variable for a client in MySQL?
- Using DECLARE to create variable in MySQL?
- MySQL create user if it does not exist?
- MySQL query to create user and grant permission
- In MySQL, why a client cannot use a user-defined variable defined by another client?
- How can we use SET statement to assign a SELECT result to a MySQL user variable?