MySQL行列转换及多选字典

关注

原创最新推荐文章于 2026-08-01 10:20:21 发布 621 阅读 AI 写同款· GEO 优化 ›

Android数据转换实战：中华新华字典导入MySQL 在本项目中，我们将中华新华字典的原始文本数据转换为可被MySQL直接导入的.sql文件。该项目的核心目标是实现从非结构化文本数据到结构化数据库表的完整迁移，适用于Android平台的数据处理流程。通过数据采集、格式解析、字段提取、结构化转换、SQL语句生成以及最终的文件写入等步骤，构建一个高效、稳定、可扩展的数据转换系统。整个项目采用Kotlin语言开发，充分利用其在Android平台上的简洁性与功能性。阅读详情

数据准备

建表


-- 学生表
-- DROP TABLE tb_stu;
CREATE TABLE tb_stu (
  id int(11) NOT NULL AUTO_INCREMENT,
  userid varchar(20) NOT NULL COMMENT '用户id',
  name varchar(20) DEFAULT NULL COMMENT '姓名',
  subjects varchar(64) DEFAULT NULL COMMENT '科目',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 成绩表
-- DROP TABLE tb_score;
CREATE TABLE tb_score (
  id int(11) NOT NULL AUTO_INCREMENT,
  userid varchar(20) NOT NULL COMMENT '用户id',
  subject varchar(20) DEFAULT NULL COMMENT '科目',
  score double DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 字典表
-- DROP TABLE tb_dic;
CREATE TABLE tb_dic (
  id int(11) NOT NULL AUTO_INCREMENT,
  code varchar(20) NOT NULL COMMENT '字典-代码',
  text varchar(20) DEFAULT NULL COMMENT '字典-文字',
  seq int(2) DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

写入数据

-- 学生表
INSERT INTO tb_stu(userid,name,subjects) VALUES ('001','小明','YW,SX,YY');
INSERT INTO tb_stu(userid,name,subjects) VALUES ('002','小锋','YW,SX,YY');
INSERT INTO tb_stu(userid,name,subjects) VALUES ('002','小军','YW,SX,YY,ZZ');

-- 成绩表
INSERT INTO tb_score(userid,subject,score) VALUES ('001','YW',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','SX',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','YY',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','YW',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','SX',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','YY',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','YW',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','SX',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','YY',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','ZZ',82);


-- 字典表
INSERT INTO tb_dic(code,text,seq) VALUES ('YW','语文',1);
INSERT INTO tb_dic(code,text,seq) VALUES ('SX','数学',2);
INSERT INTO tb_dic(code,text,seq) VALUES ('YY','英语',3);
INSERT INTO tb_dic(code,text,seq) VALUES ('ZZ','政治',4);

查询验证

第1步：逗号分割 - 列转行

SQL

SELECT
    stu.name,
    stu.subjects,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', mht.help_topic_id + 1),
            ',',
            - 1) subj
FROM
    test.tb_stu stu
        JOIN
    mysql.help_topic mht ON mht.help_topic_id < (LENGTH(stu.subjects) - LENGTH(REPLACE(stu.subjects, ',', '')) + 1)
ORDER BY stu.userid;

RESULT

userid	name	subjects	subj
001	小明	YW,SX,YY	YW
001	小明	YW,SX,YY	SX
001	小明	YW,SX,YY	YY
002	小锋	YW,SX,YY	YW
002	小锋	YW,SX,YY	SX
002	小锋	YW,SX,YY	YY
003	小军	YW,SX,YY,ZZ	YW
003	小军	YW,SX,YY,ZZ	SX
003	小军	YW,SX,YY,ZZ	YY
003	小军	YW,SX,YY,ZZ	ZZ

第2步：逗号分割 - 列转行 - 字典匹配

SQL

SELECT
    tst.userid, tst.name, tst.subjects, tst.subj, dic.text
FROM
    (SELECT
        stu.userid,
            stu.name,
            stu.subjects,
            SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', mht.help_topic_id + 1), ',', - 1) subj
    FROM
        tb_stu stu
    JOIN mysql.help_topic mht ON mht.help_topic_id < (LENGTH(stu.subjects) - LENGTH(REPLACE(stu.subjects, ',', '')) + 1)) tst
        LEFT JOIN
    tb_dic dic ON tst.subj = dic.code
ORDER BY tst.userid;

RESULT

userid	name	subjects	subj	text
001	小明	YW,SX,YY	YW	语文
001	小明	YW,SX,YY	SX	数学
001	小明	YW,SX,YY	YY	英语
002	小锋	YW,SX,YY	YW	语文
002	小锋	YW,SX,YY	SX	数学
002	小锋	YW,SX,YY	YY	英语
003	小军	YW,SX,YY,ZZ	YW	语文
003	小军	YW,SX,YY,ZZ	SX	数学
003	小军	YW,SX,YY,ZZ	YY	英语
003	小军	YW,SX,YY,ZZ	ZZ	政治

第3步：逗号分割 - 列转行 - 字典匹配 - 分组合并(行转列)

SQL

SELECT
    tst.userid,
    tst.name,
    tst.subjects,
    -- tst.subj,
    -- dic.text,
    GROUP_CONCAT(dic.text) subjects_cn
FROM
    (SELECT
        stu.userid,
            stu.name,
            stu.subjects,
            SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', mht.help_topic_id + 1), ',', - 1) subj
    FROM
        tb_stu stu
    JOIN mysql.help_topic mht ON mht.help_topic_id < (LENGTH(stu.subjects) - LENGTH(REPLACE(stu.subjects, ',', '')) + 1)) tst
        LEFT JOIN
    tb_dic dic ON tst.subj = dic.code
GROUP BY tst.userid , tst.name , tst.subjects
ORDER BY tst.userid;

SELECT
    stu.userid,
    stu.name,
    stu.subjects,
    (SELECT
            GROUP_CONCAT(dic.text) subjects_cn
        FROM
            tb_dic dic
        WHERE
            1 = 1
                AND dic.code IN (SELECT DISTINCT
                    (REGEXP_SUBSTR(T.subjects, '[^,]+', 1, mht.help_topic_id))
                FROM
                    (SELECT stu.subjects) T
                        JOIN
                    mysql.help_topic mht ON mht.help_topic_id <= (CHAR_LENGTH(T.subjects) - CHAR_LENGTH(REPLACE(T.subjects, ',', '')) + 1))) subjects_cn
FROM
    tb_stu stu
ORDER BY stu.userid

V3 [2021/11/9 13:56]

SELECT 
    stu.userid,
    stu.name,
    stu.subjects,
    (SELECT 
            GROUP_CONCAT(dic.text)
        FROM
            tb_dic dic
        WHERE
            FIND_IN_SET(dic.code, stu.subjects)) subjects_cn
FROM
    tb_stu stu
ORDER BY stu.userid

RESULT

userid	name	subjects	subjects_cn
001	小明	YW,SX,YY	语文,数学,英语
002	小锋	YW,SX,YY	语文,数学,英语
003	小军	YW,SX,YY,ZZ	语文,数学,英语,政治

第4步：逗号分割 - 列转行 - 字典匹配 - 分组合并(行转列) - 其他表

SQL

SELECT
    tst.userid,
    tst.name,
    tst.subjects,
    -- tst.subj,
    -- dic.text,
    GROUP_CONCAT(dic.text) subjects_cn,
    GROUP_CONCAT(dic.text, ':', sco.score) scores_cn
FROM
    (SELECT
        stu.userid,
            stu.name,
            stu.subjects,
            SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', mht.help_topic_id + 1), ',', - 1) subj
    FROM
        tb_stu stu
    JOIN mysql.help_topic mht ON mht.help_topic_id < (LENGTH(stu.subjects) - LENGTH(REPLACE(stu.subjects, ',', '')) + 1)) tst
        LEFT JOIN
    tb_dic dic ON tst.subj = dic.code
        LEFT JOIN
    tb_score sco ON tst.userid = sco.userid
        AND tst.subj = sco.subject
GROUP BY tst.userid , tst.name , tst.subjects
ORDER BY tst.userid;

V2 [2021/11/9 14:03]

SELECT 
    stu.userid,
    stu.name,
    stu.subjects,
    (SELECT 
            GROUP_CONCAT(dic.text, ':', sco.score)
        FROM
            tb_dic dic
        WHERE
            FIND_IN_SET(dic.code, stu.subjects)) scores_cn
FROM
    tb_stu stu
        LEFT JOIN
    tb_score sco ON stu.userid = sco.userid
ORDER BY stu.userid

RESULT

userid	name	subjects	subjects_cn	scores_cn
001	小明	YW,SX,YY	语文,数学,英语	语文:90,数学:92,英语:80
002	小锋	YW,SX,YY	语文,数学,英语	语文:88,数学:90,英语:75.5
003	小军	YW,SX,YY,ZZ	语文,数学,英语,政治	语文:70,数学:85,英语:90,政治:82

其他方式

SUBSTRING_INDEX

MySQL8下

with numbers as (
  select 1 as n
  union select 2 as n
  union select 3 as n
  union select 4 as n
  union select 5 as n
  union select 6 as n
  union select 7 as n
  union select 8 as n
  union select 9 as n
)
SELECT
    stu.userid,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stu.subjects, ',', n),
            ',',
            - 1) AS sub_value
FROM
    tb_stu stu
        JOIN
    numbers ON CHAR_LENGTH(stu.subjects) - CHAR_LENGTH(REPLACE(stu.subjects, ',', '')) >= n - 1
    order by stu.userid

REGEXP_SUBSTR

SELECT
    stu.userid,
    REGEXP_SUBSTR(stu.subjects, '[^,]+', 1, mht.help_topic_id) AS sub_value
FROM
    tb_stu stu
        JOIN
    mysql.help_topic mht ON mht.help_topic_id <= (CHAR_LENGTH(stu.subjects) - CHAR_LENGTH(REPLACE(stu.subjects, ',', '')) + 1)
ORDER BY stu.userid