mysql常用系统变量_MySQL 系统变量(system variables)

MySQL系统变量(system variables)实际上是一些系统参数，用于初始化或设定数据库对系统资源的占用，文件存放位置等等。这些个系统变量可以分为全局以及会话级别层面来修改，有些也可以进行动态修改。本文主要介绍了系统变量的一些概念以及如何设置查看这些系统变量。

1、什么是系统变量   系统变量实际上用于控制数据库的一些行为和方式的参数。比如我们启动数据库的时候设定多大的内存，使用什么样的隔离级别，日志文件的大小，存放位置等等一系列的东东。当然我们数据库系统启动后，有些系统变量(参数)也可以通过动态修改来及时调整数据库。这个系统变量在Oracle里边是通过pfile或者spfile来控制，称之为参数，是一个意思。

系统变量取值：都有默认值，可以在启动时及启动后修改。

设置范围：全局与回话级别，全局级别需要super权限，会话级别只影响自身会话。

设置方法：启动前可以通过配置文件以及启动选项来修改，启动后通过SET子句来设置。

生效周期：全局变量全局可见，但只影响在更改后连接的从该全局变量初始化相应会话变量的客户端。当前会话及已登录的会话不受影响。

对于有关涉及到size的设置值，可以使用后缀K、M或G分别表示千字节、兆字节或gigabytes，不区分大小写。2、set设置系统变量的用法--当前的版本

mysql> show variables like ‘version%‘;

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

| Variable_name | Value |

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

| version | 5.5.37 |

| version_comment | MySQL Community Server (GPL) |

| version_compile_machine | x86_64 |

| version_compile_os | Linux |

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

--获取有关set的帮助

mysql> help set

Name: ‘SET‘

Description:

Syntax:

SET variable_assignment [, variable_assignment] ...

variable_assignment:

user_var_name = expr

| [GLOBAL | SESSION] system_var_name = expr

| [@@global. | @@session. | @@]system_var_name = expr

--查看全部系统变量

root@localhost[tempdb]> show variables; --该命令会输出当前系统全部系统变量

--查看sort_buffer

mysql> show variables like ‘sort_buffer%‘;

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

| Variable_name | Value |

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

| sort_buffer_size | 2097152 |

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

--在省略global与session关键字的情形下为session级别

mysql> set sort_buffer_size=1024*1024*4; --设置为4M

mysql> show variables like ‘sort_buffer%‘;

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

| Variable_name | Value |

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

| sort_buffer_size | 4194304 |

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

--恢复到缺省值

mysql> set sort_buffer_size=default;

mysql> show variables like ‘sort_buffer%‘;

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

| Variable_name | Value |

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

| sort_buffer_size | 2097152 |

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

3、全局与会话级别设置示例--如何设置隔离级别

mysql> help isolation

Name: ‘ISOLATION‘

Description:

Syntax:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{

REPEATABLE READ

| READ COMMITTED

| READ UNCOMMITTED

| SERIALIZABLE

}

--下面我们通过演示隔离级别来设置全局与session级别变量

--查看当前session级别的隔离方式

root@localhost[(none)]> show variables like ‘%isolation%‘;

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

| Variable_name | Value |

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

| tx_isolation | REPEATABLE-READ |

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

--修改当前session级别的隔离方式为READ-COMMITTED

root@localhost[(none)]> set session transaction isolation level read committed;

root@localhost[(none)]> show variables like ‘%isolation%‘;

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

| Variable_name | Value |

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

| tx_isolation | READ-COMMITTED |

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

--另外的一个session ， 登录用户为fred

--当前sessioin级别继承全局隔离级别为REPEATABLE-READ

fred@localhost[(none)]> show variables like ‘%isolation%‘;

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

| Variable_name | Value |

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

| tx_isolation | REPEATABLE-READ |

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

--在root会话中设置全局隔离级别为serializable

root@localhost[(none)]> set global transaction isolation level serializable;

--注意，在root会话中 session级别还是为READ-COMMITTED

root@localhost[(none)]> show variables like ‘%isolation%‘;

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

| Variable_name | Value |

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

| tx_isolation | READ-COMMITTED |

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

--在root会话中我可以看到全局的值已经变为SERIALIZABLE

root@localhost[(none)]> show global variables like ‘%isolation%‘;

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

| Variable_name | Value |

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

| tx_isolation | SERIALIZABLE |

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

--在fred中全局的也变成了SERIALIZABLE

fred@localhost[(none)]> show global variables like ‘%isolation%‘;

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

| Variable_name | Value |

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

| tx_isolation | SERIALIZABLE |

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

--从上面的演示来说，无论global级别如何设置，不会影响到当前session级别的设置

--下面我们使用一个新用户登录来看看全局设置是否影响新会话

robin@SZDB:~> mysql -urobin

--如下查询新会话的隔离级别等于全局的隔离级别

robin@localhost[(none)]> show variables like ‘%isolation%‘;

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

| Variable_name | Value |

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

| tx_isolation | SERIALIZABLE |

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

4、如何获取变量值除了通过前面演示的使用show global|session variables like ‘vari_name‘方式之外，我们可以通过查询

information_schema数据中特定的表来获得这些变量的值。

通过查询数据information_schema的表global_variables

root@localhost[information_schema]> select variable_value from global_variables where

-> variable_name=‘tx_isolation‘;

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

| variable_value |

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

| SERIALIZABLE |

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

--Author: Leshami

--Blog : http://blog.csdn.net/leshami

root@localhost[information_schema]> select @@global.tx_isolation;

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

| @@global.tx_isolation |

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

| SERIALIZABLE |

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

root@localhost[information_schema]> select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| READ-COMMITTED |

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

--下面查询session_variables结果与查询global_variables获得的值相同，究其原因还在进一步研究中

root@localhost[information_schema]> select * from session_variables where variable_name=‘tx_isolation‘;

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

| VARIABLE_NAME | VARIABLE_VALUE |

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

| TX_ISOLATION | SERIALIZABLE |

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

5、总结a、设置方法

要想设置一个GLOBAL变量的值，使用下面的语法：

mysql> SET GLOBAL sort_buffer_size=value;

mysql> SET @@global.sort_buffer_size=value;

要想设置一个SESSION变量的值，使用下面的语法：

mysql> SET SESSION sort_buffer_size=value;

mysql> SET @@session.sort_buffer_size=value;

mysql> SET sort_buffer_size=value;

LOCAL是SESSION的同义词。

如果设置变量时不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION。

b、检索设置

要想检索一个GLOBAL变量的值，使用下面的语法：

mysql> SELECT @@global.sort_buffer_size;

mysql> SHOW GLOBAL VARIABLES like ‘sort_buffer_size‘;

要想检索一个SESSION变量的值，使用下面的语法：

mysql> SELECT @@sort_buffer_size;

mysql> SELECT @@session.sort_buffer_size;

mysql> SHOW SESSION VARIABLES like ‘sort_buffer_size‘;

这里，LOCAL也是SESSION的同义词。

c、其他注意事项

当你用SELECT @@var_name搜索一个变量时(也就是说，不指定global.、session.或者local.)，

MySQL返回SESSION值(如果存在)，否则返回GLOBAL值。

对于SHOW VARIABLES，如果不指定GLOBAL、SESSION或者LOCAL，MySQL返回SESSION值。