MySQL变量一共分为两大类,用户自定义变量和系统变量。如下:
- 用户自定义变量
- 局部变量
- 会话变量
- 系统变量
- 会话变量
- 全局变量
用户变量:
以”@”开始,形式为”@变量名”。用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效
全局变量:
定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global.变量名,对所有客户端生效。只有具有super权限才可以设置全局变量
会话变量:
只对连接的客户端有效。
局部变量:
作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义局部变量。set语句是设置不同类型的变量,包括会话变量和全局变量
用户定义的变量就叫用户变量。这样理解的话,会话变量和全局变量都可以是用户定义的变量。只是他们是对当前客户端生效还是对所有客户端生效的区别了。所以,用户变量包括了会话变量和全局变量。
局部变量与用户变量的区分在于两点:1.用户变量是以”@”开头的。局部变量没有这个符号。2.定义变量不同。用户变量使用set语句,局部变量使用declare语句定义 3.作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后,局部变量就消失了。
所以,最后它们之间的层次关系是:变量包括局部变量和用户变量。用户变量包括会话变量和全局变量
declare定义变量
注意:declare定义的变量名不能带‘@’符号
示例一:存储过程
1. 定义存储过程
DELIMITER $$
CREATE PROCEDURE test_while_001(IN in_count INT) # 创建存储过程 学习while循环的用法
BEGIN
DECLARE COUNT INT DEFAULT 0;
DECLARE SUM INT DEFAULT 0;
WHILE COUNT < in_count DO
SET SUM = SUM + COUNT;
SET COUNT = COUNT + 1;
END WHILE;
SELECT SUM;
END $$
DELIMITER ;
2.调用存储过程
CALL test_while_001(10); # 45 输出
3.删除存储过程
DROP PROCEDURE test_while_001;
例二: 函数
1.创建函数
DELIMITER $$
CREATE FUNCTION my_test_function(quantity INT(10)) RETURNS INT(10) # 创建函数 学习while循环的用法
BEGIN
WHILE quantity MOD 12 > 0 DO
SET quantity = quantity + 1;
END WHILE;
RETURN quantity;
END $$
DELIMITER ;
注意:创建函数之前必须更改系统设置 否则无法创建函数
SET GLOBAL log_bin_trust_function_creators=TRUE;
2.调用函数
SELECT my_test_function(10);
SELECT my_test_function(24);
SELECT my_test_function(222);
3.删除函数
DROP FUNCTION my_test_function;
mysql SET定义变量
mysql set也可以用来定于变量,定义变量的形式是以”@”开始,如:”@变量名”。
mysql SET定义变量实例:
SET @t1=0, @t2=1, @t3=2;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
mysql declare和set定义变量的区别
mysql declare和set定义变量,除了一个不加@和一个加@这个区别之外
- declare用来定义局部变量 ,@用来定义会话变量
@”符号,叫做会话变量,代表整个会话过程他都是有作用的,这个有点类似于全局变量一样。这种变量用途比较广,因为只要在一个会话内(就是某个应用的一个连接过程中),这个变量可以在被调用的存储过程或者代码之间共享数据
上面介绍了用户变量,下面来介绍MySQL分页的实现。这里实现分页不使用Limit关键字
本文内容摘抄自: https://www.jianshu.com/p/32e8c40372b3
oracle等数据库中可以方便的使用row_number函数,实现分组取组内特定数据的功能。但是MySQL中并没有引入类似的函数。为了实现这一功能,需要一些特别的处理。下面是row_number函数在MySQL中的实现。
row_number函数
函数是对分组之后的数据进行组内编号,效果如下:
由于新增了一列num,结合组内的排序,可以很方便的选取组内特定的数据。
MySQL中用到的知识
- case 语句
- 用户变量
实现步骤
1. 实现给每一行添加一个序号
SET @row_number = 0;
SELECT
(@row_number:=@row_number + 1) AS num, s.id, s.name, s.age
FROM
student s;
这里利用用户变量实现数据自增
2. 分组编号
SET @row_number=0, @customer_no=0;
SELECT
@row_number:=CASE
WHEN @customer_no = s.age THEN @row_number + 1
ELSE 1
END AS num,
@customer_no:=s.age AS stu_age,
s.id,
s.name
FROM
student s
ORDER BY s.age;
说明:
customer_no是一个临时变量,每次查询都被赋值为age。而case中判断条件在customer_no赋值之前,其实就是判断当前行age值是否与上一行age值相同。当不相同时重新编号(输出1),从而实现了分组顺序编号的功能。效果与oracle中的row_number函数相同。
3. 用户变量赋值的一种技巧
可以使用另一种方式替代用户变量的赋值
SELECT
@row_number:=CASE
WHEN @customer_no = s.age THEN @row_number + 1
ELSE 1
END AS num,
@customer_no:=s.age AS stu_age,
s.id,
s.name
FROM
student s, (SELECT @row_number:=0, @customer_no:=0) AS t
ORDER BY
s.age;
这里将赋值放入select语句内部,效果一样。