Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The article contains information about declaring variable in MySQL VTY system to store temporary data. Variables can be used to pass values from one MySQL query to another MySQL query. With the user-defined variables feature in MySQL, data can be stored in variables during a session and used in MySQL queries.
How to declare variable in mysql
SET or SELECT can be used to define variables in the MySQL VTY system.
1 | SET @variable_name:= value; |
1 | SELECT @variable_name:= value; |
The operators “: =” or “=” are used to assign values.
Variables are not case sensitive.
So @id is the same as @ID.
MySQL supports integer, float, decimal, string and NULL variable types.
MySQL variables are used only in the current user and session.
MySQL Variable Usage
Defined variables can also be used in functions within MySQL.
1 2 | SET @name:="Yusuf SEFA SEZER"; SELECT LENGTH(@name); |
Variables are used to store the value obtained as a result of a query and then reuse it.
1 | SELECT @max_value:=MAX(product_price) FROM products; |
As a result of the query, the @max_value variable will select the highest value in the product_price column of the products table.
Let’s get the information of the product with the highest price using the defined variable.
1 | SELECT * FROM products WHERE product_price=@max_value; |
Doesnt’ work for me:
SET @test=’db_name’;
SHOW TABLES FROM @test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘@test’ at line 1