mysql中的系统变量、自定义变量

一、系统变量：

1.说明

系统变量是mysql系统提供的，非用户定义的变量，它可以分为全局变量和会话变量。

• 全局变量：用global关键字标识，在所有会话（连接）中均有效
• 会话变量：用session关键字标识，只对当前会话（连接）有效

2.查看系统变量

• 查看所有全局变量：SHOW GLOBAL VARIABLES;
• 查看所有会话变量：SHOW SESSION VARIABLES; session关键字可省略，如SHOW VARIABLES; 缺省session默认查询当前会话变量。
  下文中将以 | 符号表示或者，以 [] 表示省略，则查看系统变量公式为：

 	SHOW GLOBAL|[SESSION] VARIABLES;

• 查看指定的系统变量：

	# %表示匹配任意字符，可选择性使用。
	SHOW GLOBAL|[SESSION] VARIABLES LIKE '%系统变量名%';   

例如：查看常用的全局系统变量，字符集：SHOW GLOBAL VARIABLES LIKE '%char%'; 事务自动提交：SHOW GLOBAL VARIABLES LIKE 'autocommit'; 事务隔离级别：SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; （如果查询当前会话变量值，将关键字GLOBAL省略或替换成SESSION）。

• 查看系统变量值：

	SELECT @@GLOBAL|[SESSION].系统变量名;

例如：查询mysql全局默认存储引擎：SELECT @@GLOBAL.default_storage_engine; 查询mysql当前会话默认存储引擎：SELECT @@SESSION.default_storage_engine;(SESSION关键字可省略)。

3.修改系统变量

• 方式一：

	SET GLOBAL|[SESSION] 系统变量名= 值;

• 方式二：

	SET @@GLOBAL|[SESSION].系统变量名= 值;

例如：关闭事务自动提交：SET SESSION autocommit = 0; 或者SET @@SESSION.autocommit = 0; (1表示开启ON，0表示关闭OFF)。

特别注意： 修改全局系统变量值可以跨连接，不能跨重启 ，也就是说修改全局变量后，对之后的每个会话（连接）都有效，而mysql服务器重启后，值将失效，系统变量将重新使用默认值。（想修改后永久生效，可以直接改配置文件，这个不在本文阐述）

小tips：一般我们修改当前会话系统变量，均习惯省略关键字SESSION，但在某些情况下（如使用cmd命令行修改事务隔离级别），缺省关键字修改变量，值不会马上更新。所以一般修改时建议带上关键字。

二、自定义变量

1.说明

自定义变量为非系统提供的，用户自定义的变量。它可以分为用户变量和局部变量

• 用户变量：可以定义在任何位置（begin end里面或外面都可以），在当前会话（连接）中有效，在使用中必须使用@，定义变量时不需要类型。
• 局部变量：仅仅在定义它的begin end(用于存储过程或函数)中有效，一般不需要使用@，定义变量必须要加类型。

2.自定义变量的使用

用户变量：

（1）声明及初始化：

	SET @用户变量名= 变量值;
	SET @用户变量名 := 变量值'
	SELECT @用户变量名 := 变量值;

赋值符号有=和:=两种，SET可以与任意一种搭配，SELECT只能搭配:=。定义变量不需要指定具体类型（类似js中 var 变量 = 值），例如：

	# 字符型
	SET @name = 'xy'; 
	# 数值型
	SET @name := 123;
	# 浮点型
	SELECT @name :=12.3;

均可以。

（2）赋值（更新值）：

• 方式一：使用（1）中声明及初始化的语句

	SET @用户变量名= 变量值;
	SET @用户变量名 := 变量值'
	SELECT @用户变量名 := 变量值;

• 方式二：使用SELECT INTO

 	SELECT
		字段1,字段2...  INTO @用户变量名1,@用户变量名2....
	FROM
		表;

select into可以一次性赋值多个用户变量，以英文逗号,隔开即可，注意每个字段查询结果只能为一个值（一行一列），无法将多个值赋给一个用户变量。当然，可以使用查询的任意合法值进行赋值，如count(*)、max(fd_age)/min(fd_age)、常量等。
例如：将basic_user表的统计结果赋值给变量@result

	SELECT
		count(*) INTO @result
	FROM
		basic_user;

（3）使用用户变量：

	SELECT @用户变量名称;

你也可以使用变量做逻辑运算、逻辑比较等
例如：仍然使用上面的@result变量，我们可以

# 判断是否变量值是否等于1，结果将返回1（true）或者0(false)，注意mysql的等于只用一个=符号
SELECT @result=1;

# 返回两倍变量值
SELECT @result*2;

局部变量：

（1）声明：

	DECLARE 变量名 类型;
	DECLARE 变量名 类型 DEFAULT 值;

（2）赋值（更新值）：
局部变量赋值与用户变量赋值方式一致

• 方式一：使用（1）中声明及初始化的语句

	SET 局部变量名= 变量值;
	SET 局部变量名 := 变量值'
	SELECT @局部变量名 := 变量值;  # 使用select赋值需要带上@

• 方式二：使用SELECT INTO

 	SELECT
		字段1,字段2...  INTO 局部变量名1,局部变量名2....
	FROM
		表;

（3）使用局部变量：

	SELECT 局部变量名称;

局部变量只能在begin…end使用，例如：创建存储过程，判断用户表basic_user中是否有某个用户。
使用管理员方式打开cmd，连接数据库 mysql -uroot -proot123,使用对应数据库 use xytest; 创建如下存储过程

	DELIMITER $ # 将语句结束符号分号;改为$
	CREATE PROCEDURE myp1(IN username VARCHAR(20)) 
	BEGIN
			DECLARE result INT DEFAULT 0;  # 定义局部变量result
	
			SELECT
				count(*) INTO result  # 给局部变量赋值
			FROM
				basic_user
			WHERE
				fd_name = username;
	
			SELECT IF(result>0,'存在','不存在');   # 使用局部变量的值进行判断
	END $

调用存储过程：

	CALL myp1('xy')$    # 使用存储过程

输出结果：

文中如有不正确观点，敬请指正！