Variable _ MYSQL in MySQL

MYSQL variable bitsCN.com

By Ninty
I only remember the basic knowledge, and the details are too troublesome and I cannot use them.

Variables are divided into user variables and system variables.

User variable:

User variables are related to database connections. variables declared in this connection will disappear when the connection is disconnected.
Variables declared in this connection cannot be used in another connection.

The user variable name is in the form of @ varname.
The name must start.
When declaring a variable, you must use the set statement. for example, the following statement declares a variable named @.

Set @ a = 1;


Declare a variable named @ a and assign it to 1. mysql does not strictly limit the data type, its data type changes at any time based on the value you grant it.
(SQL SERVER uses the declare statement to declare variables and strictly limit data types .)

We can also use the select statement to assign values to variables.
For example:

Set @ name =;
Select @ name: = password from user limit 0, 1;

(Note that there is a colon before the equal sign, and the limit is used to limit the returned results, which is equivalent to the top 1 in SQL SERVER)

If you write directly:

Select @ name: = password from user;

If multiple values are returned for this query, the value of the @ name variable is the value of the password field in the last record.

System variables:

System variables are classified into global variables and session variables.
Global variables are automatically initialized to default values by the server when MYSQL is started. these default values can be changed by changing the my. ini file.

The session variable is initialized by MYSQL every time a new connection is established. MYSQL copies the values of all global variables. As a session variable.
(That is, if you have not manually changed the values of the session variables and global variables after the session is established, the values of all these variables are the same .)

The difference between global variables and session variables is that modifications to global variables affect the entire server, but modifications to session variables only affect the current session
(That is, the current database connection ).

We can use

Show session variables;

Statement to output all session variables:
(It can be abbreviated as show variables. if the global variables or session variables are not specified, the session variables are output by default .)

Mysql> show global variables;
+ --------------------------------- + ---------------------------------------------------------------- +
| Variable_name | Value |
+ --------------------------------- + ---------------------------------------------------------------- +
| Auto_increment_increment | 1 |
| Auto_increment_offset | 1 |
| Automatic_sp_privileges | ON |

.................................. Omitting ..............................

| Tmpdir | C: WINDOWSTEMP |
| Transaction_alloc_block_size | 8192 |
| Transaction_preallic_size | 4096 |
| Tx_isolation | REPEATABLE-READ |
| Updatable_views_with_limit | YES |
| Version | 5.0.67-community-nt |
| Version_comment | MySQL Community Edition (GPL) |
| Version_compile_machine | ia32 & nbitsCN.com