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);

这种语法查询，结果为