MySQL Variables
Summary: in this tutorial, you will learn how to use MySQL user-defined variables in SQL statements.
Introduction to MySQL user-defined variables
Sometimes, you want to pass a value from an SQL statement to another SQL statement. To do this, you store the value in a MySQL user-defined variable in the first statement and refer to it in the subsequent statements.
To create a user-defined variable, you use the format @variable_name, where the variable_name consists of alphanumeric characters. The maximum length of the user-defined variable is 64 characters as of MySQL 5.7.5
The user-defined variables are not case-sensitive. It means that the @id and @ID are the same.
You can assign the user-defined variable to a certain data types such as integer, floating point, decimal, string or NULL.
A user-defined variable defined by one client is not visible by other clients. In other words, an user-defined variable is session-specific.
Note that the user-defined variables are the MySQL-specific extension to SQL standard. They may not be available in other database systems.
MySQL variable assignment
There are two ways to assign a value to a user-defined variable.
The first way is to use the SET statement as follows:
SET @variable_name := value;Code language: SQL (Structured Query Language) (sql)
You can use either := or = as the assignment operator in the SET statement. For example, the statement assigns number 100 to the variable @counter.
SET @counter := 100;Code language: SQL (Structured Query Language) (sql)
The second way to assign a value to a variable is to use the SELECT statement. In this case, you must use the := assignment operator because, within the SELECT statement, MySQL treats the = operator as the equal operator.
SELECT @variable_name := value;Code language: SQL (Structured Query Language) (sql)
After the assignment, you can use the variable in the subsequent statement where an expression is permitted e.g., in WHERE clause, INSERT or UPDATE statement.
MySQL variable examples
The following statement gets the most expensive product in the products table and assigns the price to the user-defined variable @msrp:
SELECT
@msrp:=MAX(msrp)
FROM
products;
Code language: SQL (Structured Query Language) (sql)
The following statement uses the @msrp variable to query the information of the most expensive product.
SELECT
productCode, productName, productLine, msrp
FROM
products
WHERE
msrp = @msrp;Code language: SQL (Structured Query Language) (sql)
Sometimes, you want to insert a row into a table, get the last insert id, and use it for inserting data into another table. In this case, you can use the user-defined variable to store the most recent id generated by an AUTO_INCREMENT column as follows.
SELECT @id:=LAST_INSERT_ID();Code language: SQL (Structured Query Language) (sql)
A user-defined variable can hold a single value only. If the SELECT statement returns multiple values, the variable will take the value of the last row in the result.
SELECT
@buyPrice:=buyprice
FROM
products
WHERE
buyprice > 95
ORDER BY buyprice;Code language: SQL (Structured Query Language) (sql)
SELECT @buyprice;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, we have shown you how to use the MySQL variables in the SQL statements to pass data between statements within a session.