MySQL用户变量与局部变量&MySQL分页方式

以下内容是来自网上资料的摘抄，原文参考地址：https://blog.csdn.net/chinafire525/article/details/91370550

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语句内部，效果一样。