MySQL字段内容拆分及合并

点击上面的蓝字关注我

1. 创建测试表和数据

--创建一张tb_stu表， CREATE TABLE tb_user( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) COMMENT '人名', c_no VARCHAR(64) COMMENT '持剑ID，逗号分隔 ); INSERT INTO tb_user(NAME,c_no) VALUES一、三、五、七； INSERT INTO tb_user(NAME,c_no) VALUES一、二、四、八、五； INSERT INTO tb_user(NAME,c_no) VALUES二，九； INSERT INTO tb_user(NAME,c_no) VALUES一、二； INSERT INTO tb_user(NAME,c_no) VALUES一、二、六、七、三、5'); INSERT INTO tb_user(NAME,c_no) VALUES(柳下惠，2，4，3，5)； INSERT INTO tb_user(NAME,c_no) VALUES百里奚，1，9； INSERT INTO tb_user(NAME,c_no) VALUES一、六、七； INSERT INTO tb_user(NAME,c_no) VALUES霍去病，1,8,5； INSERT INTO tb_user(NAME,c_no) VALUES慕容白曜7'); INSERT INTO tb_user(NAME,c_no) VALUES七、八、九、鱼幼薇； INSERT INTO tb_user(NAME,c_no) VALUES宋玉，六，五；

--创建一张剑名 create table tb_sword( id int primary key AUTO_INCREMENT, c_name varchar(4)) comment '剑名';   insert into  tb_sword(c_name)values(」； insert into  tb_sword(c_name)values(湛卢)； insert into  tb_sword(c_name)values(赤霄)； insert into  tb_sword(c_name)values(太阿')； insert into  tb_sword(c_name)values(七星龙渊)； insert into  tb_sword(c_name)values(干将； insert into  tb_sword(c_name)values(莫邪)； insert into  tb_sword(c_name)values(鱼肠)； insert into  tb_sword(c_name)values(纯钧')；

两张表如下：

tb_user

tb_sword

2.数据拆分合并

需求：使用一条SQL获得tb_user表中每个人持有的剑名（剑名用“|分隔)，即得到以下结果

拆解需求：

1） 先将tb_user表中的c_no按逗号拆分

2）将拆分后c_no中的各个id与tb_sword中的id关联，获得剑名

3) 最后，每一个user相应的剑名合并成一个字段

分段SQL如下：

步骤1：

每一个user的c_no按逗号分为对应的c_id，这种方法需要借助mysql.help_topic表

SELECT a.id,a.name,a.c_no, SUBSTRING_INDEX( SUBSTRING_INDEX( a.c_no, ',', b.help_topic_id   1 ), ',',- 1 ) c_id FROM tb_user a JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.c_no ) - LENGTH( REPLACE ( a.c_no, ',', '' ) )   1 ) ORDER BY a.id

结果如下：

第二步:关联获取每一个id对应的剑名

SELECT a2.id,a2.name,a2.c_no,a2.c_id,b2.c_name  FROM (SELECT a.id,a.name,a.c_no, SUBSTRING_INDEX( SUBSTRING_INDEX( a.c_no, ',', b.help_topic_id   1 ), ',',- 1 ) c_id FROM tb_user a JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.c_no ) - LENGTH( REPLACE ( a.c_no, ',', '' ) )   1 ) ORDERBYa.id)a2,--a2表是步骤1中拆分的结果 tb_sword b2 WHERE a2.c_id =b2.id  -- 关联，相当于inner join（或者join）

结果如下

步骤3：

将每个人的剑名合并为一个字段，并使用"|" 符合合并

SELECT a2.id,a2.name,a2.c_no,   GROUP_CONCAT(b2.c_nameSEPARATOR'|')sword_name --SEPARATOR指定分隔富，默认不加逗号分隔 FROM (SELECT a.id,a.name,a.c_no, SUBSTRING_INDEX( SUBSTRING_INDEX( a.c_no, ',', b.help_topic_id   1 ), ',',- 1 ) c_id FROM tb_user a JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.c_no ) - LENGTH( REPLACE ( a.c_no, ',', '' ) )   1 ) ORDER BY a.id) a2,tb_sword b2 WHERE a2.c_id =b2.id GROUP BY a2.id

结果如下：

实现需求

过去的精彩回顾

1.MySQL高可用之MHA集群部署

2.mysql8.新用户和修改加密规则的事情

3. 比hive大数据查询利器快10倍-- presto

4. 监控利器出鞘：Prometheus Grafana监控MySQL、Redis数据库

5. PostgreSQL主要从复制-物理复制

6.MySQL传统点复制在线转向传统点复制GTID模式复制

7.MySQL加密和解密敏感数据

8.&bsp; MySQL数据备份及还原（一）

9.  MySQL数据备份及还原（二）

扫码关注