mysql的FIND_IN_SET函数
表结构与数据
-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`id` int(11) NOT NULL,
`class_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '一班');
INSERT INTO `class` VALUES ('2', '二班');
INSERT INTO `class` VALUES ('3', '三班');
INSERT INTO `class` VALUES ('4', '四班');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(11) NOT NULL,
`teacher_name` varchar(255) NOT NULL,
`class_ids` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '王老师', '1,2');
INSERT INTO `teacher` VALUES ('2', '李老师', '3,4');
teacher表数据
class表数据
不管这个表结构设计的好不好,此时想要查询每个班级的老师名称,就会很麻烦,因为teacher表里存的是以class_ids,以逗号分隔的一个字段,此时可以使用
SELECT
t.teacher_name,
c.*
FROM
`teacher` t
LEFT JOIN class c ON FIND_IN_SET(c.id, t.class_ids);
这种语法查询,结果为