MySQL数据库变量

文章目录

• MySQL 变量分类
• 系统变量
• • 查看系统变量
  • 设置系统变量
  • • 如何通过配置文件来设置变量值
  • 设置动态的全局性的系统变量
  • 设置静态的全局性的系统变量
  • 设置会话级的系统变量
• 用户自定义变量
• • 设置用户变量的值
  • • 使用命令 set 赋值
    • 使用表达式给变量赋值
  • 查看用户变量的值

MySQL 变量分类

MySQL 通过变量来定义当前服务器的特性，保存状态信息等。我们可以通过手动更改变量的值来配置MySQL，也可以通过变量获得MySQL的当前状态信息。

MySQL 的变量类型可以从多个维度来划分，如果从声明主体划分，可以分为系统变量和用户变量；如果从变量的修改方式来划分，可以分为动态变量和静态变量；如果从生效范围来划分可以分为全局变量（GLOBAL VARIABLES）和会话变量（SESSION VARIABLES）。

系统变量以 @@ 开头，是系统预设的变量；用户变量以 @ 开头，是用户自己定义的变量；全局变量影响 MySQL 服务的整体运行方式和状态的变量；会话变量是影响具体客户端会话（一个数据库连接产生一个会话）的操作方式及状态的变量；动态变量是指在 MySQL 运行过程中，可以通过命令 set 随时调整变量值的变量；静态变量是指不能通过命令 set 随时调整变量值，必须通过配置文件设置其值的变量。

按照上述的划分维度，在系统变量中又分为全局变量和会话变量，全局变量又分为动态全局变量和静态全局变量，而所有的会话变量和部分的全局系统变量就可以通过命令 set 直接赋值，这些变量就可以称为动态变量；而必须在配置文件中修改，然后重启服务后才能生效的系统变量称为静态变量，所以静态变量肯定是全局性的系统变量，不可能是会话变量，更不可能是用户自定义变量。

有些全局的系统变量也是会话变量，换句话说有些系统变量既是全局变量也是会话变量，是会话变量的系统变量绝对也是全局变量，但是绝对不存在只是会话变量的系统变量。

用户变量中又分为会话变量和局部变量，其中局部变量。。。
用户变量不存在全局的变量，换句话说用户无法自定义全局性的变量。

系统变量

服务器维护着两种系统变量，即全局变量和会话变量。每一个客户端成功连接服务器后，都会产生与之对应的会话。会话期间，MySQL 服务实例会在服务器内存中生成与该会话对应的会话变量，这些会话变量的初始值是全局变量值的拷贝。

MySQL 中的系统变量以两个“@”开头：

1.@@global 仅仅用于标记全局变量；
2.@@session 仅仅用于标记会话变量；
3.@@ 首先标记会话变量，如果会话变量不存在，则标记全局变量。

查看系统变量

无论是在设置系统变量还是查询系统变量值的时候，只要没有指定到底是全局变量还是会话变量，都当做会话变量来处理。

在 shell 中使用命令 mysqladmin 查看所有的会话系统变量：

[root@htlwk0001host ~]# mysqladmin -uroot -p variables;

或者连接数据库后，通过命令 show 查看所有的会话系统变量：

mysql> show variables;

可以使用以下命令查看 MySQL 中所有的全局变量信息：

SHOW GLOBAL VARIABLES; 

可以使用以下命令查看与当前会话相关的所有会话变量：

SHOW SESSION VARIABLES;

查看指定的系统变量：

SHOW GLOBAL VARIABLES LIKE 'innodb_data_file_path'; # 查看指定名称的全局性的系统变量
SHOW SESSION VARIABLES LIKE 'innodb_data_file_path'; # 查看指定名称的会话级别的系统变量
SHOW VARIABLES LIKE 'innodb_data_file_path'; # 不指定关键词，默认查询的是会话级别的系统变量

查看指定的系统变量，也可以在变量表中查询：

mysql> SELECt * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERe VARIABLE_NAME='VARIABLE_NAME'; # 查询全局的系统变量
mysql> SELECt * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERe VARIABLE_NAME='VARIABLE_NAME'; # 查询会话级别的系统变量

查看指定的系统变量，也可以这样查询：

mysql> select @@sql_mode; # 不指定关键词，默认查询的是会话级别的系统变量，该变量不是会话变量，会报错

设置系统变量

当服务器启动时，会将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。

可以通过以下方法设置系统变量：

修改 MySQL 源代码，然后对 MySQL 源代码重新编译（该方法适用于 MySQL 高级用户，这里不做阐述）。
在 MySQL 配置文件（mysql.ini 或 mysql.cnf 或 my.cnf）中修改 MySQL 系统变量的值（需要重启 MySQL 服务才会生效）。
在 MySQL 服务运行期间，使用 SET 命令重新设置系统变量的值。

如何通过配置文件来设置变量值

以变量 local_infile 的设置为例，在命令终端使用 vim 打开配置文件进行编辑：

[root@htlwk0001host ~]# vim /etc/my.cnf

在其中的 [mysqld] 下面添加如下内容：

local-infile=ON # 能不能写成 local_infile=ON，我不知道，没有试过

说明：
1.表示开启，其值可以设置为 ON 或 TRUE 或 1 都可以；表示关闭其值可以设置为 OFF 或 FALSE 或 0 都可以
2.通过配置文件设置的参数值必须重启数据库服务才能生效，并且是永久生效

设置动态的全局性的系统变量

更改全局变量，必须具有 SUPER 权限。设置全局变量的值的方法如下：

SET @@global.innodb_file_per_table=default;
SET @@global.innodb_file_per_table=ON;
SET global innodb_file_per_table=ON;

注：
1.给全局性的系统变量赋值，必须写关键词 global，不写默认是给会话级的系统变量赋值
2.表示某种功能特性开启还是关闭的变量，其值设置为 ON 或 TRUE 或 1 都可以表示开启；设置为 OFF 或 FALSE 或 0 都可以表示关闭
3.更改全局变量只影响更改后连接客户端的相应会话变量，而不会影响目前已经连接的客户端的会话变量（即使客户端执行 SET GLOBAL 语句也不影响）。也就是说，对于修改全局变量之前连接的客户端只有在客户端重新连接后，才会影响到客户端。简而言之，全局变量修改后，客户端必须重新连接才会生效
4.使用 SET 设置全局变量成功后，如果 MySQL 服务重启，数据库的配置会重新初始化，一切按照配置文件进行初始化，因此数据库服务重启后全局变量的配置会失效

设置静态的全局性的系统变量

MySQL 中还有一些特殊的全局变量，如 log_bin、tmpdir、version、datadir，在 MySQL 服务实例运行期间它们的值不能动态修改，也就是不能使用 SET 命令进行重新设置，这种变量称为静态变量。数据库管理员可以使用前面提到的修改源代码或更改配置文件来重新设置静态变量的值。

设置会话级的系统变量

服务器还为每个连接的客户端维护一系列会话变量。在连接时使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。设置会话变量的值的方法如下：

SET @@session.pseudo_thread_id=5; # 如果不存在此会话级的系统变量会报错
SET session pseudo_thread_id=5; # 如果不存在此会话级的系统变量会报错
SET @@pseudo_thread_id=5; # 默认设置的是会话系统变量，如果pseudo_thread_id是全局变量，不是会话级的变量，这样赋值会报错，当然如果根本不存在此变量，也会报错
SET pseudo_thread_id = 5; # 默认设置的是会话系统变量，如果pseudo_thread_id是全局变量，不是会话级的变量，这样赋值会报错，当然如果根本不存在此变量，也会报错

注：
1.会话变量的配置在当前会话退出后就失效了，会话断开即失效。
2.不指定 SESSION 或 GLOBAL 默认是 SESSION
3.LOCAL 是 SESSION 的同义词，所以 SESSION 可以替换成 LOCAL

用户自定义变量

用户自定义变量简称用户变量，用户变量与连接会话有关，也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。所以说用户变量是针对当前登录数据库的会话的变量，用户变量属于会话变量的范畴。

用户变量的作用：
可以先在用户变量中保存值然后在后面的命令语句中引用它。这样可以将值从一个语句传递到另一个语句。

变量的表示形式：
系统变量都是以 @@ 开头，用户变量以 @ 开头。
用户变量的形式为 @var_name，其中变量名可以由当前字符集的英文、数字、.、_ 和 $ 组成。 默认字符集是 cp1252 (Latin1)。可以用mysqld 的 --default-character-set 选项更改字符集。用户变量名对大小写不敏感。

设置用户变量的值

未分配的用户变量有一个默认值 NULL，类型为字符串。

使用命令 set 赋值

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

set 赋值语句中，可以使用 =或 := 作为分配符。分配给每个变量的值可以为整数、实数、字符串、NULL值。

mysql> SET @t1=0, @t2=0, @t3=0;

使用表达式给变量赋值

还可以直接使用表达式给用户变量赋值，但是此时分配符必须为 := 而不能用 =，因为在非 set 语句中 = 被视为一个比较操作符。

mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

查看用户变量的值

mysql> select @var_name;

注：show 命令只能查看系统变量的值，不能查看用户变量的值。