Mysql 分割字符串，一行变多行，@rownum，mysql.help_topic

1 前言

? ? 朋友最近遇到一个比较棘手的 sql 问题，让我帮忙看看：

? ? ? ? 他有两张表 testa 和 testb ，一个表存的日期，另一个表存字符串例如 2023-11-01,2023-11-02，如何将这两张表关联起来，只查 testa 表的数据（关联的时候，其中只要在 testb 表中的字符串有这个日期，就要将 testa 表是这个日期的记录的所有字段都查出来）。

? ? 我当时看的时候，觉得这个还挺简单的，想着用 in 或者 like 就行了，后发现都不是正解，于是便有了这篇文章产生。

2 建表和插入测试数据

## testa 表 （每个日期一条数据，日期的格式含秒）
CREATE TABLE `testa` (
  `operator_id` int NOT NULL,
  `stat_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

## testb 表（多个日期一条数据，日期以英文字符分隔，日期是 YYYY-MM-DD 形式）
CREATE TABLE `testb` (
  `operator_id` int NOT NULL,
  `rectify_date` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `testa` (`operator_id`, `stat_date`) VALUES (11, '2024-01-03 00:00:00');
INSERT INTO `testa` (`operator_id`, `stat_date`) VALUES (11, '2024-01-04 00:00:00');
INSERT INTO `testa` (`operator_id`, `stat_date`) VALUES (11, '2024-01-02 00:00:00');
INSERT INTO  testb  (`operator_id`, `rectify_date`) VALUES (11, '2024-01-02,2024-01-04');

3 查询语句

## rectify_date为需要拆分的字段
select a.operator_id, a.stat_date from testa a , 
      ( SELECT @rownum := @rownum + 1 as rownum ,
                       t2.operator_id as operator_id,
	                   SUBSTRING_INDEX(SUBSTRING_INDEX(t2.rectify_date, ',', t3.help_topic_id + 1), ',', - 1) as rectify_date
        FROM (SELECT @rownum := 0) t1 , testb t2 
		JOIN mysql.help_topic t3 ON t3.help_topic_id < (LENGTH(t2.rectify_date) - LENGTH(REPLACE(t2.rectify_date, ',', '')) + 1) 
		 ) b 
where SUBSTR(a.stat_date,1,10) = b.rectify_date

4 涉及知识点

4.1 @rownum

自定义变量，通过赋值语句 @rownum:=@rownum + 1 来累加达到递增行号的需求。
（ @rownum 是自定义变量而不是 Mysql 的函数，所以名字是可以随便取的比如 @rowNo @aaa @bbb 皆可。）

4.2 help_topic

系统自带的辅助表，mysql.help_topic 表的 id 特点是从 0 开始递增，最大为 681
详见《mysql.help_topic 数量/最大值上限查询》

4.3 SUBSTRING

字符串截取，SUBSTRING(str,pos,len)

• str 要截取的字符串
• pos 开始截取字符串的下标位置
• len 需要截多长

4.4 SUBSTRING_INDEX

切割字符串，SUBSTRING_INDEX(str,delim,count)

• str 表示要进行分割的字符串
• delim 表示分割符
• count表示要返回的子串的个数

5 总结

• 总体上来说，还是挺简单的，主要是依靠一张辅助表，将一行有规律的数据拆分成多行。然后再配合一些常用函数来做切割；
• 也可以考虑放在 java 代码中的 for 循环处理实现，不过数据量较大的情况下，会很吃 java 内存。java 和 数据库两个层面处理，各有利弊，具体看哪块的资源比较多，自行参考即可；