记-MySQL存储过程字符串切割Insert

CREATE TABLE teacher(
	tea_id varchar(50) NOT NULL,
	tea_name VARCHAR(50) NOT NULL,
	PRIMARY KEY(tea_id)
)

CREATE TABLE student (
  stu_id varchar(50) NOT NULL,
  tea_id varchar(50) NOT NULL
)

需求:教师表内容已有,新增学生信息,一个学生对应多个授课教师,一次性添加完成.

输入数据形如:("1001","2001!2002!2003!2004!2005!")前为学生id,后为教师id字符串'!'分割并结尾-->用mysql函数SUBSTRING_INDEX(字符串, 截取标记, 标记出现次数)可对字符串进行截取-->SUBSTRING_INDEX("1002!", "!", 1 ) = SUBSTRING_INDEX("1002!", "!", 2 )  而 SUBSTRING_INDEX("2001!2002!", "!", 1 ) != SUBSTRING_INDEX("2001!2002!", "!", 2 ) 则可以此为循环条件,再利用RIGHT(),LENGTH(),让该字符串截取到值插入后删除该部分.

存储过程建立:

DELIMITER $$
CREATE PROCEDURE insert_stu_tea(
IN stu_id VARCHAR(255),#("1001")
IN tea_string VARCHAR(255)#("2001!2002!2003!2004!2005!")
)
BEGIN
DECLARE tea_id VARCHAR(20);
SET tea_id = SUBSTRING_INDEX(tea_string,'!',1);#获得2001
WHILE tea_id != SUBSTRING_INDEX(tea_string,'!',2)#2001 != 2001!2002 以此类推
DO 
INSERT INTO student VALUES (stu_id, tea_id);#插入('1001', '2001') 以此类推
SET tea_string = RIGHT(tea_string,LENGTH(tea_string)-LENGTH(tea_id)-1);#tea_string = 2002!2003!2004!2005! 以此类推
SET tea_id = SUBSTRING_INDEX(tea_string,'!',1);#tea_id = 2002 以此类推
END WHILE;
END$$
DELIMITER

教师表数据准备:

INSERT INTO teacher VALUES( '2001', '张老师');
INSERT INTO teacher VALUES( '2002', '李老师');
INSERT INTO teacher VALUES( '2003', '王老师');
INSERT INTO teacher VALUES( '2004', '黄老师');
INSERT INTO teacher VALUES( '2005', '杨老师');

执行存储过程:

CALL insert_stu_tea('1001', '2001!2002!2003!2004!2005!');

查结果:

SELECT stu_id, tea_name FROM student s LEFT JOIN teacher t ON s.tea_id = t.tea_id

致此结束,方法很多,各有优缺!