MySQL中列如何以逗号分隔转成多行

目录
  • MySQL列以逗号分隔转成多行
    • 场景
    • 解决方案
  • 总结

MySQL列以逗号分隔转成多行

业务场景:

在数据库中,有一张的一个字段存储方式是采用以逗号分隔存储多个值,现在需要将其进行拆分成多个独立的值,与另外一张字典表进行关联,取的最终的字典表中的 label,再以逗号拼接成显示 label 的形式展现。

场景

表中存储的值:

期待最终的展现效果:

甜品,休闲食品,饮料

解决方案

1. 将一列转成多行

select v1.id,SUBSTRING_INDEX(SUBSTRING_INDEX(v1.intention_exhibits, ',', b.help_topic_id + 1), ',', - 1) AS exhibit
                      from test v1
                               JOIN mysql.help_topic AS b ON b.help_topic_id < (length(v1.intention_exhibits) -
                                                             length(REPLACE(v1.intention_exhibits, ',', '')) + 1)
where v1.id = '63591ee4f8204212837e447b34c61fef';

说明:

mysql.help_topic 表的自增id是从0开始,所以在进行截取时要对id进行+1。【系统表,不建议使用,真正的线上环境,dba 是不允许使用系统表的,所以,我们需要自己创建一张类似的表】

创建一张自增表,来代替 mysql.help_topic 系统表,自增表的值,需要大于自己业务表中逗号拆出来的集合数:

create table add_self
(
    id int(20) null
);

INSERT INTO add_self (id) VALUES (0);
INSERT INTO add_self (id) VALUES (1);
INSERT INTO add_self (id) VALUES (2);
INSERT INTO add_self (id) VALUES (3);
INSERT INTO add_self (id) VALUES (4);
INSERT INTO add_self (id) VALUES (5);
INSERT INTO add_self (id) VALUES (6);
INSERT INTO add_self (id) VALUES (7);
INSERT INTO add_self (id) VALUES (8);
INSERT INTO add_self (id) VALUES (9);
INSERT INTO add_self (id) VALUES (10);

2. 最终 SQL

select group_concat(edn.name)
from (
select v1.id,SUBSTRING_INDEX(SUBSTRING_INDEX(v1.intention_exhibits, ',', b.id + 1), ',', - 1) AS exhibit
                      from test1 v1
                               JOIN add_self AS b ON b.id < (length(v1.intention_exhibits) -
                                                             length(REPLACE(v1.intention_exhibits, ',', '')) + 1)
    where v1.id = '63591ee4f8204212837e447b34c61fef') t
    left join test2 edn on t.exhibit = edn.local_key;

使用到的相关函数:

  • group_concat
  • substring_index
  • length

总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

(0)

相关推荐

  • mysql根据逗号将一行数据拆分成多行数据

    目录 分隔效果 命令行解释 涉及函数 分隔效果 -- 分隔前 1,2,3,4 -- 分隔后 1 2 3 4 分隔命令行 SELECT substring_index(substring_index('1,2,3,4',',', b.help_topic_id + 1), ',', -1) result FROM mysql.help_topic b where b.help_topic_id < (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ','

  • MySQL实现用逗号进行拼接、以逗号进行分割

    目录 MySQL用逗号进行拼接.以逗号进行分割 group_concat 函数 MySQL多行数据用逗号拼接返回一行 解决方案 GROUP_CONCAT简单阐述 总结 MySQL用逗号进行拼接.以逗号进行分割 MySQL中,把查询的结果拼接成一个字符串. group_concat 函数 用法:group_concat (待拼接对象) 输出:用逗号进行拼接后的字符串 例子: select group_concat(emp_no) as employees from dept_emp;   /* 结

  • MySQL中将一列以逗号分隔的值行转列的实现

    前言 有时会遇到没有遵守第一范式设计模式的业务表.即一列中存储了多个属性值.如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求(测试数据见文末) 1.得到所有的不重复的值,如 value AT BT CT DT ET SQL如下: select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)) from (select gr

  • MySQL中列如何以逗号分隔转成多行

    目录 MySQL列以逗号分隔转成多行 场景 解决方案 总结 MySQL列以逗号分隔转成多行 业务场景: 在数据库中,有一张的一个字段存储方式是采用以逗号分隔存储多个值,现在需要将其进行拆分成多个独立的值,与另外一张字典表进行关联,取的最终的字典表中的 label,再以逗号拼接成显示 label 的形式展现. 场景 表中存储的值: 期待最终的展现效果: 甜品,休闲食品,饮料 解决方案 1. 将一列转成多行 select v1.id,SUBSTRING_INDEX(SUBSTRING_INDEX(v

  • MySQL中列转行和行转列总结解决思路

    目录 引言 列转行 行转列 总结 引言 在学习sql中遇到了列转行和行转列的题目,这里总结一下如何在对应的情景下解决不同的题目: 列转行 创建一个表stu_score_01: SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for stu_score_01 -- ---------------------------- DROP TABLE IF E

  • 提高MySQL中InnoDB表BLOB列的存储效率的教程

    首先,介绍下关于InnoDB引擎存储格式的几个要点: 1.InnoDB可以选择使用共享表空间或者是独立表空间方式,建议使用独立表空间,便于管理.维护.启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效: 2.InnoDB的data page默认16KB,5.6版本以后,新增选项 innodb_page_size

  • MySQL中show命令方法得到表列及整个库的详细信息(精品珍藏)

    show databases;show tables from db_name; show columns from table_name from db_name;show index from talbe_name [from db_name]; show status;show variables; show [full] processlist;show table status [from db_name]; show grants for user; 除了status,process

  • Mysql中 unique列插入重复值该怎么解决呢

    当unique列在一个UNIQUE键上插入包含重复值的记录时,我们可以控制MySQL如何处理这种情况:使用IGNORE关键字或者ON DUPLICATE KEY UPDATE子句跳过INSERT.中断操作或者更新旧记录为新值. mysql> create table menus(id tinyint(4) not null auto_increment, -> label varchar(10) null,url varchar(20) null,unique key(id)); Query

  • MySQL中的隐藏列的具体查看

    目录 1.存在主键 2.无主键,存在唯一索引 3.存在联合主键或联合唯一索引 4.存在多个唯一索引 5.同时存在主键与唯一索引 6.无符合条件的主键与唯一索引 在介绍mysql的多版本并发控制mvcc的过程中,我们提到过mysql中存在一些隐藏列,例如行标识.事务ID.回滚指针等,不知道大家是否和我一样好奇过,要怎样才能实际地看到这些隐藏列的值呢? 本文我们就来重点讨论一下诸多隐藏列中的行标识DB_ROW_ID,实际上,将行标识称为隐藏列并不准确,因为它并不是一个真实存在的列,DB_ROW_ID

  • MySQL中一条SQL查询语句是如何执行的

    目录 前言 1. 处理连接 1.1 客户端和服务端的通信方式 1.1.1 TCP/IP协议 1.1.2 UNIX域套接字 1.1.3 命名管道和共享内存 1.2 权限验证 1.3 查看MySQL连接 2. 解析与优化 2.1 查询缓存 2.2 解析器 & 预处理器(Parser & Preprocessor) 2.2.1 词法解析 2.2.2 语法分析 2.2.3 预处理器 2.3 查询优化器(Optimizer)与查询执行计划 2.3.1 什么是查询优化器? 2.3.2 优化器究竟做了什

  • mysql中的find_in_set字符串查找函数解析

    目录 需求 方案 函数 1. 介绍 2. 语法:FIND_IN_SET(needle,haystack) 3. 实战 插曲 总结 需求 系统中,不论是订单还是退货单,有的平台使用需要卖家审核,有的则不需要,所以在系统中可以设置订单或退货单的流程节点. 而对于每个流程节点,客户需求是每个节点都要根据系统下的员工的角色去做分发,从而订单指定到个人,而不是整个公司的员工都可以处理. 方案 对于上述场景,我实现的方案是在订单表和退货单表都增加了对应节点待处理人字段,多个人可处理的话,则用,分隔,保存对应

  • MySQL中基本的多表连接查询教程

    一.多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用','  如: 由于其返回的结果为被连接的两个数据表的乘积,因此当有WHERE, ON或USING条件的时候一般不建议使用,因为当数据表项目太多的时候,会非常慢.一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN 2.   内连接INNER JOIN 在MySQL中把I SELECT * FROM table1 CROSS JOIN tabl

随机推荐