Session variables and global variables in MySQL

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: \ windows \ temp \ |

| 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 |

| Version_compile_ OS | Win32 |

| Wait_timeout | 28800 |

+ --------------------------------- + ---------------------------------------------------------------- +

232 rows in set, 1 warning (0.00 Sec)

To output all global variables:

Show global variables;

Some system variable values can be dynamically changed using statements, but some system variable values are read-only.

For system variables that can be changed, we can use the set statement to change them.

To change the value of a session variable, use the following statement:

Set session varname = value;

Or set @ session. varname = value;

For example:

Mysql> set session sort_buffer_size = 40000;

Query OK, 0 rows affected (0.00 Sec)

If you want to change the value of the global variable, change the session to global:

Set global sort_buffer_size = 40000;

Set @ Global. sort_buffer_size = 40000;

However, to change the value of a global variable, you must have the super permission.

(

Note: Root is only a built-in account, not a permission,

This account has all permissions in the MySQL database. Any account, as long as it has the permission of "super,

You can change the value of the global variable, just as any user can call load_file or

Into OUTFILE, into dumpfile, and load data infile are the same.

)

Using the SELECT statement, we can query the values of a single session variable or global variable:

Select @ session. sort_buffer_size

Select @ Global. sort_buffer_size

Select @ Global. tmpdir

All sessions mentioned above can be replaced by the keyword "local.

For example:

Select @ local. sort_buffer_size

Local is the synonym of session.

When setting system variables or querying system variables, you only need to specify whether it is a global variable or a session variable.

Are processed as session variables.

For example:

Set @ sort_buffer_size = 50000;

Select @ sort_buffer_size;

Neither global nor session is specified above, so all are processed as sessions.

There is also a structured system variable. I don't know what to do, so I can't remember it.