单表一对一改一对多处理，查询方案

基于mysql处理

类似业务，学生老师，开始一对一教学，随后改为一对多教学。精确查找某个学生的老师

测试表及数据

create table test_teacher_student
(
    id      int auto_increment
        primary key,
    teacher varchar(64) null comment '老师',
    student varchar(64) null comment '学生'
)
    comment '学生老师';

INSERT INTO task_manager.test_teacher_student (id, teacher, student) VALUES (1, '王老师', '夏洛特');
INSERT INTO task_manager.test_teacher_student (id, teacher, student) VALUES (2, '赵老师', '张三');
INSERT INTO task_manager.test_teacher_student (id, teacher, student) VALUES (3, '钱老师', '李四,王五');
INSERT INTO task_manager.test_teacher_student (id, teacher, student) VALUES (4, '李老师', '李四六,周七');

方案一

使用函数 FIND_IN_SET，检查单个字符串是否存在字段中。兼容性较低在mysql中使用

SELECT * FROM test_teacher_student WHERE FIND_IN_SET('李四', student) > 0

方案二

使用中间表关联查询，修改结构，相对复杂，稳定性高可移植性好。
中间表及数据

create table test_teacher_student_one
(
    id         int auto_increment
        primary key,
    teacher_id int         null comment '老师主键',
    student    varchar(64) null comment '学生'
);

INSERT INTO task_manager.test_teacher_student_one (id, teacher_id, student) VALUES (1, 1, '夏洛特');
INSERT INTO task_manager.test_teacher_student_one (id, teacher_id, student) VALUES (2, 2, '张三');
INSERT INTO task_manager.test_teacher_student_one (id, teacher_id, student) VALUES (3, 3, '李四');
INSERT INTO task_manager.test_teacher_student_one (id, teacher_id, student) VALUES (4, 3, '王五');
INSERT INTO task_manager.test_teacher_student_one (id, teacher_id, student) VALUES (5, 4, '周七');
INSERT INTO task_manager.test_teacher_student_one (id, teacher_id, student) VALUES (6, 4, '李四六');

查询sql

select t.*
from test_teacher_student_one s
         left join test_teacher_student t on s.teacher_id = t.id
where s.student = '李四'