MySQL Variables

 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 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, a 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;

You can use either:= or = as the assignment operator in the SET statement. For example, the statement assigns the number 100 to the variable @counter.

SET @counter := 100;

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;

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;

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;

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();

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;

SELECT @buyprice;

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.