MySQL：变量

变量

在 MySQL 中，变量是一种用于存储和操作数据的占位符。变量是存储数据的重要工具，它们可以在SQL语句、存储过程、函数等多种场合中使用。

根据变量的创建方式和作用域，MySQL中的变量可以是一个用户变量，也可以是一个系统变量，或者是局部变量：

• 用户变量：在会话期间设置的变量，它的作用范围局限于当前会话，不会影响其他会话。在 SQL 语句中，用户变量是以 ‘@’ 开头的变量，例如 @myar。用户变量的类型是动态的，根据赋值操作自动确定类型。

• 系统变量：由 MySQL 系统定义和管理的全局变量，控制 MySQL 的行为和配置。在 SQL 语句中，系统变量可以以’@@'前缀表示，例如 @@max_connections。系统变量的设置通常需要超级用户权限，其值在MySQL 服务器级别是全局共享的，对所有会话生效。

• 局部变量：在 MySQL 中，局部变量是在存储过程、函数或触发器中声明和使用的变量。局部变量的作用域仅限于声明它的存储过程、函数或触发器。在 SQL 语句中，不使用 ‘@’ 开头的变量是局部变量。

  与用户变量相比，局部变量不需要提前预定义。它们主要用于存储内部计算或中间结果，提供了更临时和局部化的数据存储方式。在存储过程、语句块或函数结束时会自动销毁，无法在外部访问或引用。

系统变量用于控制 MySQL 服务器的行为，例如调整缓冲区大小、设置最大连接数等。而用户变量则用于在会话中存储和操作数据。这两种变量类型有不同的作用范围和用途，在不同的场景下会有不同的使用方式。

系统变量

系统变量由 MySQL 系统定义和维护，用于控制MySQL服务器的行为。系统变量分为全局变量和会话变量。

• 全局变量：在 MySQL 服务器启动时通过命令行参数或者配置文件（如my.cnf或my.ini）进行设置，对所有的客户端连接都有效。一旦设置，它将影响整个MySQL服务器实例，直到服务重启或该变量被重新赋值。

• 会话变量：当客户端连接到 MySQL 服务器时，MySQL 会将大部分的全局变量复制一份作为客户端的会话变量。会话变量只对当前的客户端连接有效，断开连接后该变量将被销毁。

查看系统变量

查看系统变量的语法如下：

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 'pattern' | WHERE expr];

• GLOBAL：表示查看全局变量。
• SESSION：表示查看会话变量（默认选项，也可以使用LOCAL替代）。
• LIKE：用于查找特定名字的变量。
• WHERE：用于查找满足指定条件的变量。

例如：

SHOW SESSION VARIABLES; -- 查询当前会话中的所有会话变量  
SHOW GLOBAL VARIABLES; -- 查询所有全局变量  
SHOW GLOBAL VARIABLES LIKE 'max%'; -- 使用LIKE返回所有名字以max开头的全局变量

除了使用 SHOW VARIABLES 命令，还可以通过 SELECT 语句查询系统变量的值：

SELECT @@GLOBAL.back_log; -- 查询全局变量back_log的值  
SELECT @@SESSION.sql_mode; -- 查询会话变量sql_mode的值

设置系统变量

可以使用 SET 命令动态修改某些系统变量的值。修改全局变量的值需要加上 GLOBAL 关键字或 @@GLOBAL 限定符，修改会话变量的值则使用 SESSION 关键字或 @@SESSION 限定符（默认）。

GLOBAL max_connections = 1000; -- 修改全局变量 max_connections 的值  
SET @@SESSION.sql_mode = 'STRICT_TRANS_TABLES'; -- 修改会话变量sql_mode的值

创建变量

使用 DECLARE 语句声明一个局部变量，并指定其类型。

DECLARE variable_name datatype [DEFAULT value];

• variable_name：变量名

• datatype：变量类型

• [DEFAULT value]：默认值（可选）

例如：

DECLARE myvar INT;
DECLARE var INT DEFAULT 10; -- 变量 var 的默认值为 10

赋值变量

使用 SET 语句将一个值赋给一个已声明的变量，或创建一个用户变量并赋值。

SET variable_name = value;
SET @user_variable_name = value; -- 创建用户变量并赋值

用户变量是用户自定义的变量，其作用域在当前连接会话期间有效，可以在不同的 SQL 语句之间传递数据。用户变量以 @ 符号开头，变量名由字母、数字、点号（.）、下划线（_）以及美元符号（$）组成，最大长度为64个字符。

SET myvar = 5; -- 给局部变量赋值需要对应其类型
SET @userVar = "Hello World";

还可以使用 SELECT ... INTO ... 给变量赋值，将表中的某一列的值赋给一个变量。需要确保查询的结果只有一个行和一列，否则将会引发错误。如果查询没有返回任何结果，变量的值将会被设为 NULL。

-- 赋值局部变量
SELECT column_name INTO var_name FROM table_name;

-- 赋值用户变量
SELECT column_name INTO @user_var_name FROM table_name

修改变量

使用 SET 语句来修改已经存在的变量的值。

SET variable_name = new_value;

SET var = var + 5;
SET @userVar = 10;

使用变量

可以使用 SELECT 输出变量值

SELECT var_name;
SELECT @user_var_name;

在查询、计算或条件语句中使用变量。用户变量可以在 SELECT 列表、 WHERE 子句、INSERT 或 UPDATE 等语句中作为表达式使用。

SELECT column_name FROM table_name 
WHERE column_name = variable_name;

SELECT * FROM users WHERE age = myvar;