Mysql中使用自定义变量生成连续数字或日期

MySQL自定义变量

此blog中MySQL版本为：5.7.21

 

mysql使用@来定义自定义变量。

1、把常量赋值给自定义变量

SELECT @num:=1 AS num;

2、把函数赋值给自定义变量

SELECT @DATE:=NOW();

3、把查询结果赋值给自定义变量。注意：查询结果只能返回一个结果，返回多个会报错：[Err] 1242 - Subquery returns more than 1 row

SELECT @id:= (SELECT id FROM table WHERE id =100);

4、自定义变量可以运算

SELECT @num:=@num+1 AS num FROM (SELECT @num:=0) t ;

5、可以使用表达式的地方使用自定义变量

SELECT ... WHERE col <= @num;

MySQL自定义变量的属性和限制

• 使用自定义变量的查询，无法使用查询缓存
• 不能再使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中。
• 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。
• 如果使用连接池或者持久化连接，自定义变量可能让看起来毫无关系的代码发生交互。
• 自定义变量的类型是一个动态类型。
• MySQL优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想的方式运行。
• 赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定。
• 赋值符号 :=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号。
• 使用未定义变量不会产生任何语法错误，如果没有意识到这一点，非常容易犯错。

MySQL中使用自定义变量生成连续数字

eg:

SELECT @xi:=@xi+1 as xc from 
            (SELECT 1 UNION SELECT 2 ) xc1, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 ) xc2,  
            (SELECT @xi:=0) xc0;

结果：

利用笛卡尔积生成数据行，然后使用自定义变量的运算功能生成连续数字。

本例子中两个（SELECT 1 UNION SELECT 2 ）笛卡尔积生成6行数据 ，然后自定义变量就根据生成的行数进行运算，生成了1-6的连续数字。

MySQL中使用自定义变量生成了连续日期

利用上面生成的连续数字，就可以使用MySQL内置函数生成连续日期或者时间了。

eg:

select DATE_FORMAT(SUBDATE(NOW(), interval (numlist.xc*30) MINUTE),'%Y-%m-%d %H:%i:%s') as 'datetime' from 
 (SELECT @xi:=@xi+1 as xc from 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 ) xc1, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,  
            (SELECT @xi:=0) xc0) as numlist;

结果：

本例子中利用生成的连续数字，生成当前时间前的连续半小时。

 

以上是学习MySQL的一点心得体会，欢迎大神拍砖。

参考：

Mysql自定义变量的使用：https://www.cnblogs.com/guaidaodark/p/6037040.html

mysql生成连续数字或日期：https://blog.youkuaiyun.com/qiuli_liu/article/details/81707562