MySQL按顿号拆分列为多行

小咪咪 2023-05-29 11:47 21阅读 0赞

`图一`  
![在这里插入图片描述][2019110522234211.png]  
`图二`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70]

# 按顿号拆分列为多行 #

把 `图一` 的展示效果转换成 `图二` 的展示效果  
1、创建用户表（sys\_user）

CREATE TABLE sys_user (
    id BIGINT NOT NULL auto_increment COMMENT '主键',
    login_name VARCHAR ( 64 ) NOT NULL COMMENT '登陆名',
    PASSWORD VARCHAR ( 64 ) NOT NULL COMMENT '密码',
    STATUS INT NOT NULL COMMENT '状态',
    NAME VARCHAR ( 64 ) COMMENT '真实姓名',
    role_id VARCHAR ( 255 ) COMMENT '角色id',
    create_time datetime NOT NULL COMMENT '创建时间',
    create_name VARCHAR ( 20 ) NOT NULL COMMENT '创建人',
    modify_time datetime COMMENT '修改时间',
    modify_name VARCHAR ( 20 ) COMMENT '修改人',
    PRIMARY KEY ( id ),
    UNIQUE KEY UNQ_SYS_USER_LOGIN_NAME ( login_name ) 
    );

2、新增测试数据

TRUNCATE TABLE sys_user;
    INSERT INTO sys_user ( login_name, PASSWORD, STATUS, NAME, role_id, create_time, create_name )
    VALUES
    	( 'admin', '123456', 1, '超级管理员', '1、2、3', now( ), 1 );

3、拆分列为多行SQL

SELECT
    	a.login_name,
    	a.NAME,
    	SUBSTRING_INDEX( SUBSTRING_INDEX( a.role_id, '、', h.help_topic_id + 1 ), '、',- 1 ) AS role_id 
    FROM
    	sys_user a
    	JOIN mysql.help_topic h ON h.help_topic_id < (
    	( length( a.role_id ) - length( REPLACE ( a.role_id, '、', '' ) ) + 1 ) / 3 
    	)

[2019110522234211.png]: https://img-blog.csdnimg.cn/2019110522234211.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191105222414867.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA==,size_16,color_FFFFFF,t_70