How can we store a value in user-defined variable?

We can store a value in a user-defined variable in a statement and then refer to it afterward in other statements. Followings are the ways to store a value in user-defined variable −

With SET statement

we can store a user-defined variable by issuing a SET statement as follows −

Syntax

SET @var_name = expr[, @var_name = expr]…

In this @var_name is the variable name which consists of alphanumeric characters from current character set. We can use either = or := assignment operator with SET statement.

For example following queries can store the user variables with SET statement −

mysql> SET @value = 500;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @value := 500;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @value = 500, @value1=550;
Query OK, 0 rows affected (0.00 sec)

Without SET statement

Without the SET statement, we can also assign a value to a user variable in statements as follows −

mysql> select @value,@value1, @value2 := @value+@value1;

+--------+---------+---------------------------+
| @value | @value1 | @value2 := @value+@value1 |
+--------+---------+---------------------------+
| 500    | 550     | 1050                      |
+--------+---------+---------------------------+
1 row in set (0.00 sec)

In this case, we must have to use := assignment operator.

Nitya Raut

Updated on 20-Jun-2020 09:09:22

• Related Questions & Answers
• How Can we permanently define user-defined variable for a client in MySQL?
• How can we assign a bit value as a number to a user variable?
• Select into a user-defined variable with MySQL
• Perform MySQL SELECT INTO user-defined variable
• Updating a MySQL table row column by appending a value from user defined variable?
• In MySQL, why a client cannot use a user-defined variable defined by another client?
• MySQL ORDER BY with numeric user-defined variable?
• Increment multiple Timestamp values by setting the incremented value in a user-defined variable in SQL
• MongoDB query to set user defined variable into query?
• Get the maximum exam date using a user-defined variable in SQL
• Add user defined value to a column in a MySQL query?
• How can I check whether a variable is defined in JavaScript?
• How can I return a record from a table nearest to a user-defined variables value in MySQL?
• When should we create a user-defined exception class in Java?
• Swap two Strings without using third user defined variable in Java