Detailed explanation of mysql user variables and set statement examples

1 Introduction to user variables

User variables are variables defined by the user. We can assign values ​​to user variables and use them anywhere a scalar expression can be used normally.

Before introducing a user variable, we must use a set statement or a select statement to define it, and then assign a value to it, otherwise the variable will only have an empty value.

User variables are associated with the connection. That is, variables defined by one client cannot be seen or used by other clients. When a client exits, all variables connected by that client are automatically released.

2 User variable definition

The set statement can be used to assign values ​​to system variables or user variables. The definition of user variables is as follows:

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

You can also use the select statement to define:

SELECT @var_name := expr [, @var_name = expr] ...

User variables: start with "@" and are in the form of "@var_name" to distinguish user variables from column names. It can be any random, composite, scalar expression, as long as there are no column specifications.

A variable name can consist of alphanumeric characters from the current character set and "_", "$", and ".". The default character set is ISO-8859-1 Latin1; this can be changed with the --default-character-set option to mysqld.

For SET, you can use = or := to assign values, but for SELECT, you can only use := to assign values.

We can use a simple select statement to query the value of the defined user variable.

3 Use of user variables

3.1 Examples through set

A scalar expression used to assign a value to a variable can be a compound expression. Calculations, functions, system scalars, and other user variables are allowed, as are subqueries. Then the value of a user variable can be obtained through the select statement, and the result is a table with one row.

mysql> set @var1=1, @var2='vartest', @var3=abs(-2), @var4=(select count(*) from mysql.user);
mysql> select @var1, @var2, @var3, @var4;
+-------+---------+-------+-------+
| @var1 | @var2 | @var3 | @var4 |
+-------+---------+-------+-------+
| 1 | vartest | 2 | 25 |
+-------+---------+-------+-------+

In the expression used to assign a user variable, you can also specify other user variables. It should be noted that MySQL first determines the value of all expressions before assigning the value to the variable.

For example:

mysql> set @varA = 2;

For the following two examples, the value of varB is different.

Example 1:

mysql> set @varA = 3, @varB = @varA;
mysql> select @varB;
+-------+
| @varB |
+-------+
| 2 |
+-------+

Example 2:

mysql> set @varA = 3; 
mysql> set @varB = @varA;
mysql> select @varB;            
+-------+
| @varB |
+-------+
| 3 |
+-------+ 

3.2 Examples through select

Compared with the set statement, using select to define variables will return a tabular result.

mysql> select @var1:=1, @var2:='vartest', @var3:=abs(-2), @var4:=(select count(*) from mysql.user);
+----------+------------------+----------------+------------------------------------------+
| @var1:=1 | @var2:='vartest' | @var3:=abs(-2) | @var4:=(select count(*) from mysql.user) |
+----------+------------------+----------------+------------------------------------------+
| 1 | vartest | 2 | 25 |
+----------+------------------+----------------+------------------------------------------+
mysql> select @var1, @var2, @var3, @var4;
+-------+---------+-------+-------+
| @var1 | @var2 | @var3 | @var4 |
+-------+---------+-------+-------+
| 1 | vartest | 2 | 25 |
+-------+---------+-------+-------+
+-------+---------+-------+-------+
| 1 | vartest | 2 | 25 |
+-------+---------+-------+-------+ 

4 User variable considerations

4.1 User variables are used in where or having clauses. They must first be defined in another statement. For example, in the following example, the initial query does not return any results. Only after they are defined will there be output in subsequent queries.

mysql> select @H:='localhost' from mysql.user where host = @H;
Empty set (0.00 sec)
mysql> select @H:='localhost';
+-----------------+
| @H:='localhost' |
+-----------------+
| localhost |
+-----------------+
1 row in set (0.00 sec)
mysql> select @H:='localhost', user from mysql.user where host = @H;
+-----------------+-----------------+
| @H:='localhost' | user |
+-----------------+-----------------+
| localhost | |
| localhost | jesse |
| localhost | local |
| localhost | root |
| localhost | user_tab_update |
+-----------------+-----------------+

4.2 User variables are at the session level. When we close the client or log out, all user variables disappear. If you want to save custom variables, you need to create a table and insert the scalar into the table.

4.3 User variable names are not case sensitive.

4.4 Undefined variables are initialized to null.

5 Supplementary knowledge: mysql statement do

In the do statement, one or more scalar expressions are used, and MySQL processes them one by one, but does not display the results of the expressions. For example, we can call a function to execute something in the background without seeing its results.

For example:

mysql> do sleep(5);
Query OK, 0 rows affected (5.00 sec)

Summarize

This is the end of this article about MySQL user variables and set statements. For more relevant MySQL user variables and set statements, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!