What do you mean MySQL user variables and how can we assign values to them?

MySQL user variables are written as @variable and it may be set to an integer, real, string or NULL value. With the help of SET statement, we can assign a value to the user variable. We can use either = or := as the assignment operator while assigning the values to user variables.

Example

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

mysql> Set @B = 'MySQL';
Query OK, 0 rows affected (0.00 sec)

The above queries have assigned the values 100 to user variable A and ‘MySQL’ to user variable B respectively.

We can check for their values with the help of SELECT statements as follows −

mysql> Select @A, @B;
+------+-------+
| @A   | @B    |
+------+-------+
| 100  | MySQL |
+------+-------+
1 row in set (0.05 sec)

We can also perform multiple variable assignments, separated by commas. It is illustrated in the following example −

mysql> SET @X = 'Ram',@Y = 'Shyam',@Z = 'Students', @S = 5000;
Query OK, 0 rows affected (0.00 sec)

mysql> Select @X, @Y, @Z, @S;
+------+-------+----------+------+
| @X   | @Y    | @Z       | @S   |
+------+-------+----------+------+
| Ram  | Shyam | Students | 5000 |
+------+-------+----------+------+
1 row in set (0.00 sec)

We can also assign values to them with SELECT statement but for that, we must have to use only := assignment operator and not =. It is illustrated in the example below −

mysql> SELECT @C := 'Tutorialspoint';
+------------------------+
| @C := 'Tutorialspoint' |
+------------------------+
| Tutorialspoint         |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT @C;
+----------------+
| @C             |
+----------------+
| Tutorialspoint |
+----------------+
1 row in set (0.00 sec)

Giri Raju

Published on 20-Feb-2018 10:46:08

• Related Questions & Answers
• How do we assign values to variables in Python?
• What do you mean by default MySQL database for the user?
• What do you mean by PRIMARY KEY and how can we use it in MySQL table?
• What do you mean by FOREIGN KEY and how can we use it in MySQL table?
• What do you mean by Scope of variables inside MySQL stored procedure?
• What do you mean by a dynamic initialization of variables?
• What do you mean by database view and how do MySQL views work?
• How do we assign values to variables in a list using a loop in Python?
• What are MySQL stored functions and how can we create them?
• How do we assign a value to several variables simultaneously in Python?
• How to assign values to variables in C#?
• How to assign values to variables in Python
• What are MySQL Temporary Tables? How can we create them?
• How can we use SET statement to assign a SELECT result to a MySQL user variable?
• Can we assign values to final arrays in Java?