假设存在表 “学生和班级的关系”,但是由于表结构的设计的问题,学生的数据的存放是将学生的ID用“,”分隔连接起来存在在数据库中的,如下图:
班级C1中存在着10个学生,studentdIds 列中存的是这10个学生的ID,并以逗号分隔。
但是由于效率问题,要更改该表,将每个学生的所在班级的信息分开存放,class列和student列作为联合主键,如下图:
由于之前的数据已经存在,我们怎样才能将原来的数据搬到新表中呢?
答案如下:
insert into newClass(classId, StudentId) WITH
n
(
str,
ori,
pos,
classId
) AS
(
(
SELECT
concat(studentsIds,','),
1,
posstr(concat(studentsIds ,','), ','),
classId
FROM
class
)
UNION ALL
SELECT
str,
pos+1,
locate(',', str, pos+1),
classId
FROM
n
WHERE
locate(',', str, pos+1)>0
)
SELECT
classId,
substr(str,ori,case pos-ori when -1 then 32 else pos - ori end) as result
FROM
n;