变量、服务器配置和状态

变量概述

  • 变量分为系统变量、状态变量、 用户自定义变量，并且都分为全局变量和会话变量两种，全局变量表示对所有会话有效，会话变量表示只在当前会话生效

    • 状态变量用于保存mysqld运行中的统计数据的变量，不可更改

  • 参考文档：https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html

    • 主要关注以下字段：
      • System Var 系统变量
      • Status Var 状态变量
      • Var Scope 变量生效范围，global为全局变量，both表示既是session又是global
      • Dynamic 是否可以动态修改

列出变量

#查看所有变量(包括global和session)
SHOW [SESSION] VARIABLES;
SHOW GLOBAL VARIABLES #只查看global级变量

#查看所有的服务器状态变量
SHOW [SESSION] STATUS;
SHOW GLOBAL STATUS; #只查看global级变量

#查看指定的变量
SHOW VARIABLES LIKE 'VAR_NAME';
#或
SELECT @@VAR_NAME; #查看指定的系统变量
SELECT @VAR_NAME; #查看指定的自定义变量

#shell端执行
mysqladmin variables #查看选项和部分变量
#或
mysql -e "show variables"

系统变量

修改全局变量

  • 更多用法：help SET

  • **注意：**仅对修改后新创建的会话有效；对已经建立的会话无效

SET GLOBAL system_var_name=value; #常用
#或
SET @@global.system_var_name=value;

修改会话变量

  • 更多用法：help SET
SET [SESSION] system_var_name=value; #常用
#或
SET @@[session.]system_var_name=value;

常用的系统变量

  • **注意：**有些系统变量既可以作为系统变量来查看 又可以作为服务器选项来对配置文件进行修改从而实现持久修改
#服务器系统变量
max_connections
sql_mode #对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，详情参阅下面的说明

#服务器状态变量，只读，用于保存mysqld运行中的统计数据的变量，不可更改
...

sql_mode

参考文档：

常见MODE：

  • NO_AUTO_CREATE_USER
    • 禁止GRANT创建密码为空的用户
  • NO_ZERO_DATE
    • 在严格模式不允许使用‘0000-00-00’的时间
  • ONLY_FULL_GROUP_BY
    • 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
  • NO_BACKSLASH_ESCAPES
    • 反斜杠“\”作为普通字符而非转义字符
  • PIPES_AS_CONCAT
    • 将"||“视为连接操作符而非“或”运算符

自定义变量

定义普通变量

  • 只在当前会话中有效，退出终端或在其他窗口无效
#赋值
MySQL [mysql]> set @var1='hello';
Query OK, 0 rows affected (0.00 sec)
#调用
MySQL [mysql]> select @var1;
+-------+
| @var1 |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)

#通过查询结果进行赋值（结果要为单个值）
MySQL [hellodb]> select max(age) from students into @age;
Query OK, 1 row affected (0.01 sec)
#调用
MySQL [hellodb]> select @age;
+------+
| @age |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

#对赋值的变量进行更改
MySQL [hellodb]> set @age=66;
Query OK, 0 rows affected (0.00 sec)

MySQL [hellodb]> select @age;
+------+
| @age |
+------+
|   66 |
+------+

自定义局部变量

  • 在函数或存储过程内才有效，需要用DECLARE 声明，之后直接用 var_name引用

自定义函数中定义局部变量

#自定义函数中定义局部变量语法
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]

#说明：局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义

#范例
DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED) 
RETURNS SMALLINT
BEGIN
 DECLARE a, b SMALLINT UNSIGNED;
 SET a = x, b = y;
 RETURN a+b;
END//
DELIMITER ;

调用变量

#语法
select @@varname; #调用系统变量
select @varname; #调用自定义变量

#范例
MySQL [mysql]> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

MySQL [mysql]> select @@hostname;
+------------+
| @@hostname |
+------------+
| 8          |
+------------+
1 row in set (0.00 sec)