MySQL allows you to pass parameters in your SQL query to dynamically query databases. You can do this using user variables, which are user-defined variables that can be used during MySQL session. In this article, we will learn how to pass parameter in MySQL query.
How to pass parameter in MySQL query
Here are the steps to pass parameter in MySQL query. Let us look at a simple example first. Log into MySQL prompt before you execute any of the following statement.
First, use SET command to define a variable and assign value to it. It has the following syntax.
SET @anyVariableName − = ’yourValue’;
We will set a variable id with a simple value.
mysql>set @id=1;
Now pass this variable in your MySQL query as shown in bold. Here is how you can use your user variable in SELECT and UPDATE statements.
mysql> select * from users where id=@id; mysql> update users set name='John' where id=@id;
You can also use these user variables in prepared statements. Here is an example where have created a user variable @query that uses another user variable @tblname.
mysql> SET @tblName = 'Users'; mysql> SET @query = CONCAT('Select * FROM ', @tblName, ' LIMIT 10'); mysql> PREPARE stmt FROM @query; mysql> EXECUTE stmt; mysql> DEALLOCATE PREPARE stmt;
You can also save these statements in query.sql script.
SET @tblName = 'Users'; SET @query = CONCAT('Select * FROM ', @tblName, ' LIMIT 10'); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Then you can always invoke your script as shown below, to run your queries.
mysql> \. query.sql
That’s it. In this article, we have learnt how to pass variable in MySQL query.
Also read:
How to Combine Querysets in Django
How to Get Field Value in Django Queryset
How to Convert Markdown to HTML in Python
How to Configure DNS Server on CentOS/RHEL
Python Delete Dictionary Key While Iterating
Related posts:
How to Fix Invalid Use of Group Function Error
How to Show Indexes on Table or Database in MySQL
MySQL Row Number Function & Its Uses
Python Script to Load Data in MySQL
How to Find Tables with Column Name in MySQL
How to Convert String to Date in MySQL
How to Show User Permissions in MySQL
How to Replace Part of String in Update Query in MySQL

Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.