{"id":4933,"date":"2013-01-13T07:59:24","date_gmt":"2013-01-13T14:59:24","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4933"},"modified":"2024-02-06T18:47:22","modified_gmt":"2024-02-07T01:47:22","slug":"mysql-variables","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-variables\/","title":{"rendered":"MySQL Variables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL variables in SQL statements.<\/p>\n\n\n\n

Introduction to MySQL user-defined variables<\/h2>\n\n\n\n

Sometimes, you want to pass a value from an SQL statement to other SQL statements within the same session. <\/p>\n\n\n\n

To do this, you store the value in a user-defined variable in the first statement and use it in the subsequent statements.<\/p>\n\n\n\n

To create a user-defined variable, you use the following syntax:<\/p>\n\n\n

@variable_name<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, @variable_name<\/code> is a user-defined variable. It is preceded by the @<\/code> symbol. In MySQL, user-defined variables are case-insensitive<\/em>, meaning that @id<\/code> and @ID<\/code> are the same variables.<\/p>\n\n\n\n
Note that user-defined variables are the MySQL-specific extension to SQL standard. They may not be available in other database systems.<\/p>\n\n\n\n
MySQL variable assignment<\/h2>\n\n\n\n
MySQL offers two ways to assign a value to a user-defined variable.<\/p>\n\n\n\n
1) Using the SET statement<\/h3>\n\n\n\n
To assign a value to a variable, you can use the SET statement as follows:<\/p>\n\n\n
SET<\/span> @variable_name = value<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement assigns the value to the @variable_name.<\/p>\n\n\n\n
Besides using the assign operator =, you can use the := operator:<\/p>\n\n\n
SET<\/span> @variable_name := value<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using the SELECT statement<\/h3>\n\n\n\n
The following SELECT<\/code> statement assigns a value<\/code> to the user-defined variable @variable_name<\/code>:<\/p>\n\n\n
SELECT<\/span> value<\/span> INTO<\/span> @variable_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL variable examples<\/h2>\n\n\n\n
We’ll use the products<\/code> table from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Basic user-defined variable example<\/h3>\n\n\n\n
The following statement retrieves the most expensive product in the products<\/code> table and assigns the price to the user-defined variable @msrp<\/code>:<\/p>\n\n\n
SELECT<\/span> \n  MAX<\/span>(msrp) INTO<\/span> @msrp\nFROM<\/span> \n  products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To select the value of the @msrp, you use the following statement:<\/p>\n\n\n
SELECT<\/span> @msrp<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------------+\n| @msrp:=MAX(msrp) |\n+------------------+\n|           214.30<\/span> |\n+------------------+\n1<\/span> row in<\/span> set<\/span>, 1 warning (0.03 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement uses the @msrp<\/code> variable to query the information of the most expensive product.<\/p>\n\n\n
SELECT<\/span> \n  productCode, \n  productName, \n  productLine, \n  msrp \nFROM<\/span> \n  products \nWHERE<\/span> \n  msrp = @msrp;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-------------+--------------------------+--------------+--------+\n| productCode | productName              | productLine  | msrp   |\n+-------------+--------------------------+--------------+--------+\n| S10_1949    | 1952<\/span> Alpine Renault 1300<\/span> | Classic Cars | 214.30<\/span> |\n+-------------+--------------------------+--------------+--------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using a user-defined variable in a query that returns multiple values<\/h3>\n\n\n\n
A user-defined variable can hold a single value. If the SELECT<\/code> statement returns multiple values, MySQL will issue an error and the variable will take the value of the first row in the result set:<\/p>\n\n\n
SELECT<\/span> \n  buyPrice INTO<\/span> @buy_price\nFROM<\/span> \n  products \nWHERE<\/span> \n  buyPrice > 95<\/span> \nORDER<\/span> BY<\/span> \n  buyPrice;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
ERROR 1172 (42000): Result consisted of more than one row<\/code><\/span><\/pre>\n\n\n
In this example, the @buy_price<\/code> will store the first value in the result set:<\/p>\n\n\n
SELECT<\/span> @buy_price;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------+\r\n| @buy_price |\r\n+------------+\r\n|      95.34<\/span> |\r\n+------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL user-defined variables in the SQL statements to pass data between statements within a session.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t