1. 自定义变量

和Java 一样，MySQL也允许用户自定义变量进行使用。它是一个用来存储内容的临时容器，在连接 MySQL的整个过程中都存在。

定义方式：

  • 简单变量： SET @num := 1
  • 查询赋值： SET @min_num := (SELECT MIN(number) FROM student)
  • 日期： SET @last_day := CURRENT_DATE -INTERVAL 1 DAY

简单使用：

  • SELECT * FROM student WHERE number > @num

注意：

  • 不能在使用常量或者标识符的地方使用自定义变量（例如表名、列名等）
  • 不能用自定义变量来做连接间的通信
  • 赋值符号 := 的优先级非常低
  • 使用未定义的变量不会产生任何语法错误

案例一：

可以在给一个变量赋值的同时使用这个变量，即“左值”特性

SET @rowNum := 0;

SELECT stuId, @rowNum := @rowNum + 1 FROM student

案例二：避免重复查询刚更新的数据

用户希望更新一条数据后，立马获取到该条数据的更新时间

-- 之前的做法
UPDATE student SET updateTime = NOW() WHERE stud_id = 1;
SELECT updateTime FROM student WHERE stud_id = 1;

-- 使用变量后
UPDATE student SET updateTime = NOW() WHERE stud_id = 1 AND @now := NOW();
SELECT @now;

使用变量虽然也是需要两个SQL，但是第2个查询无需访问数据表，加快了检索时间

案例三：取值的顺序

让变量的赋值和取值发生在执行查询的同一阶段，不然可能会出现赋值后还没更新，取到原来值的问题

SET @rownum := 0;

SELECT user_name, @rownum row_num
FROM student
WHERE (@rownum := @rownum + 1) <= 1

案例四：偷懒的 UNION

假设需要查找某条数据，先在一个频繁访问的表查找热数据，找不到再去另外一个较少访问的表查找冷数据

  • 如果直接使用 UNION ALL，那无论第一个表有没有查到数据，都会去第二个表中再找一次
SELECT id FROM student WHERE id = 123
UNION ALL
SELECT id FROM student_archived WHERE id = 123;

使用变量则可以规避这个问题：

SELECT GREATEST( @found := - 1, id ) AS id FROM	student WHERE id = 123 -- 先查找热数据
UNION ALL
SELECT id FROM student_archived WHERE id = 123 AND @found IS NULL  -- 如果查不到就去查找冷数据
UNION ALL
SELECT 1 FROM DUAL WHERE ( @found := NULL ) IS NOT NULL;  -- 都查不到的情况

2. 一行与多行之间的转换

mysql 天定义 mysql定义日期变量_mysql 天定义

2.1 一行转多行

解决：借助 mysql 库的 help_topic 表

SELECT
	d.id,
	SUBSTRING_INDEX( SUBSTRING_INDEX( d.class_name, ',', ht.help_topic_id + 1 ), ',', - 1 ) splitId 
FROM
	demo d
	JOIN mysql.help_topic ht ON ht.help_topic_id < ( LENGTH( d.class_name ) - LENGTH( REPLACE ( d.class_name, ',', '' )) + 1 ) 
WHERE
	d.id = 4

2.2 多行转一行

合并行使用 GROUP_CONCAT 函数，如果出现编码问题导致查询出来的是 blob 数据类型时，使用 CONVERT(GROUP_CONCAT(需要转换的字段名) USING utf8) 解决

SELECT
	class_name,
	GROUP_CONCAT( id SEPARATOR ';' ) groupId -- SEPARATOR 指定什么字符作为分隔符
FROM
	demo 
GROUP BY
	class_name

3. IF函数

IF 函数，通过判断条件来返回特定值

IF(expr, result_true, result_false) -- 如果表达式 expr的结果为真，则返回 result_true，否则返回 result_false

案例：在 student 表中，男生用 1表示，女生用 0 表示，查询student 表，并用汉字展示性别

SELECT
	stu_name,
	IF( sex = 1, '男', '女' ) sex 
FROM
	student

tudent 表中，男生用 1表示，女生用 0 表示，查询student 表，并用汉字展示性别

SELECT
	stu_name,
	IF( sex = 1, '男', '女' ) sex 
FROM
	student