MySQL 拆分列中的分号，为每个拆分形成一个新行

Question

所以我得到了带有如下数据的遗留 mysql 数据库:

   DATE     |     DATA
2015-04-27  | sample; sample2; sample3
2015-04-28  | sample1; sample4

我想创建一个新表来存储这样的日期/数据对。

   DATE     |     DATA
2015-04-27  | sample
2015-04-27  | sample2
2015-04-27  | sample3
2015-04-28  | sample1
2015-04-28  | sample4

我试过研究用户定义的拆分函数，但它们似乎都只想拆分一个字符并返回拆分的一个实例。我需要保留所有实例以放入新表中。

所以使用这个:http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

我创建了 split 函数并尝试改变返回值:

REPLACE(SUBSTRING(SUBSTRING_INDEX(list, ';'),
LENGTH(SUBSTRING_INDEX(list, ';', pos -1)) + 1),
delim, '')

但是它需要第三个参数来指定要返回哪个实例。有没有办法返回具有左列日期的新行的所有实例？

Answer 1

这是一个想法......

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
( date DATE NOT NULL
, data VARCHAR(100) NOT NULL
);

INSERT INTO my_table VALUES
('2015-04-27','sample; sample2; sample3'),
('2015-04-28','sample1; sample4');

SELECT * FROM my_table;;
+------------+--------------------------+
| date       | data                     |
+------------+--------------------------+
| 2015-04-27 | sample; sample2; sample3 |
| 2015-04-28 | sample1; sample4         |
+------------+--------------------------+

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT DISTINCT date
              , data
              , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(data,';',i+1),';',-1)) x 
           FROM ints
              , my_table
          ORDER 
             BY date,i;

+------------+--------------------------+---------+
| date       | data                     | x       |
+------------+--------------------------+---------+
| 2015-04-27 | sample; sample2; sample3 | sample  |
| 2015-04-27 | sample; sample2; sample3 | sample2 |
| 2015-04-27 | sample; sample2; sample3 | sample3 |
| 2015-04-28 | sample1; sample4         | sample1 |
| 2015-04-28 | sample1; sample4         | sample4 |
+------------+--------------------------+---------+