mysql数据库行转列

问题

例：学生表 和 老师表。
原始设计时没有关联表，使用“,”隔开表示关联关系

Student

id	name	teacherIds
1	小明	1,3,4
2	小红	2,3,4

Teacher

id	name	lesson
1	张三	数学
2	李四	数学
3	王五	语文
4	赵六	英语

转换
StudentTecherRelation

id	studentId	teacherId
1	1	1
2	1	3
3	1	4
4	2	2
4	2	3
4	2	4

解决

1. 创建自增表

使用sql关联查询笛卡尔积特性，生成1-1000的自增表（如果需要10000，在添加一个表t4）

DROP TABLE IF EXISTS tmp_generator1000;
CREATE TABLE IF NOT EXISTS tmp_generator1000
( id INT ( 11 ) NOT NULL ,
PRIMARY KEY ( `id` ) USING BTREE );

INSERT INTO tmp_generator1000
SELECT @v := @v + 1 AS id 
FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t3,
(SELECT @v := 0) t0;

2. 使用函数SUBSTRING_INDEX分割teacherIds字段

注意，位置，做去空处理

INSERT INTO student_teacher_relation ( studentId, teacherId ) 
SELECT studentId, teacherId
FROM(
SELECT
student.id AS studentId,
SUBSTRING_INDEX(SUBSTRING_INDEX(student.teacherIds, ',', num.id), ',',- 1 ) AS teacherId
FROM
	( SELECT id, teacherIds FROM student ) student
	JOIN tmp_generator1000 num 
WHERE
	num.id < LENGTH(student.teacherIds) - LENGTH(REPLACE(student.teacherIds, ',', '' )) + 2) relation 
WHERE relation.teacherId <> ''; 

-- 删除 自增表
DROP TABLE IF EXISTS tmp_generator1000;