mysql使用FIND_IN_SET函数解决两张表没有主键id强关联，而是A表中的某个字段值，存在B表中的集合字段中

1. 前言

1. 起因：因小组的项目中前期开发中未考虑到课程信息会存在多个类别，设计表时，没有加中间关联表，而是有一个类别id直接标记到课程表上。 到后续客户给过来的课程数据，才发现课程存在多个类别的情况。
2. 需求：根据类别 关联课程的数量进行排序。

2.解决方案

• 表数据

/*
 Navicat Premium Data Transfer

 Source Server         : dbtest
 Source Server Type    : MySQL
 Source Server Version : 50717 (5.7.17-log)
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50717 (5.7.17-log)
 File Encoding         : 65001

 Date: 11/10/2023 11:18:08
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`  (
  `s_id` int(11) NOT NULL AUTO_INCREMENT,
  `subject_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `c_ids` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES (1, '政治与决策', '1,3');
INSERT INTO `subject` VALUES (2, '地理', '2,1');
INSERT INTO `subject` VALUES (3, '历史', '4,5');
INSERT INTO `subject` VALUES (4, '语文', '1,4');

-- ----------------------------
-- Table structure for subject_category
-- ----------------------------
DROP TABLE IF EXISTS `subject_category`;
CREATE TABLE `subject_category`  (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of subject_category
-- ----------------------------
INSERT INTO `subject_category` VALUES (1, '金融');
INSERT INTO `subject_category` VALUES (2, '理财');
INSERT INTO `subject_category` VALUES (3, '政治');
INSERT INTO `subject_category` VALUES (4, '新闻');
INSERT INTO `subject_category` VALUES (5, '人文');

SET FOREIGN_KEY_CHECKS = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

• 使用 FIND_IN_SET 函数来解决查询统计

-- 如果存在字符串 会返回在字符串 的下标
-- 如果不存在会返回0
SELECT FIND_IN_SET('0','a,b,c,d');




SELECT a.c_id,a.c_name,count(b.s_id)  from subject_category a LEFT JOIN `subject` b on FIND_IN_SET(a.c_id,b.c_ids) >0

GROUP BY a.c_id;
1
2
3
4
5
6
7
8
9
10

3.注意事项