Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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 −
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)
830 Views
