mysql当一个字段以逗号隔开存多个名字，用sql取这个名字对应的id并修改

当前有两个表，class班级表和student学生表

 

 

需求：我们需要把class班级表的student_ids中的name,改成student的id

这里我们可以用“find_in_set”函数

-- 注意s.name要在前面
select c.id,c.CLASS_NAME,GROUP_CONCAT(s.id) as count,c.STUDENT_NAMES
from class c left join student s
on FIND_IN_SET(s.`name`,c.STUDENT_NAMES)
group by c.id

 

下一步我们需要把group_coucat(s.id)更新到class表的student_ids

我们用 update 表1 inner join 表2 on 表1.字段 = 表2.字段 set 表1.字段 = 表2.字段 语句
// 意思就是把根据条件把表2的数据更新到表1中

update class c inner join (
select c.id,c.CLASS_NAME,GROUP_CONCAT(s.id) as count,c.STUDENT_NAMES
from class c left join student s
on FIND_IN_SET(s.`name`,c.STUDENT_NAMES)
group by c.id
) b on c.id = b.id set c.STUDENT_IDS = b.count;

 

 

————————————————
版权声明：本文为CSDN博主「qq_42281649」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_42281649/article/details/126537734

附件
-- 建表班级表CLASS
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for CLASS
-- ----------------------------
DROP TABLE IF EXISTS `CLASS`;
CREATE TABLE `CLASS` (
`ID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '班级ID',
`CLASS_NAME` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级名称',
`STUDENT_IDS` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人员ID',
`STUDENT_NAMES` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人员名字'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '班级人员' ROW_FORMAT = Compact;

-- ----------------------------
-- Records of CLASS
-- ----------------------------
INSERT INTO `CLASS` (`ID`,`CLASS_NAME`,`STUDENT_IDS`,`STUDENT_NAMES`) VALUES ('1', '一班', '', '张三,李四,王五,小刘');
INSERT INTO `CLASS` (`ID`,`CLASS_NAME`,`STUDENT_IDS`,`STUDENT_NAMES`) VALUES ('2', '二班', '', '小李');
INSERT INTO `CLASS` (`ID`,`CLASS_NAME`,`STUDENT_IDS`,`STUDENT_NAMES`) VALUES ('3', '三班', '', '赵权,苏武');

-- 建表student
create table student(
`id` varchar(20),
`name` varchar (20)
)