数据库列转行实例—Mysql列转行-序列表方法

场景

场景：一行多条数据转为多行。（多用于数据清洗）

原数据

转换成下图数据

建表数据

• 创建表

create TABLE user1(
id int(11),
user_name varchar(3),
overss varchar(5),
mobile varchar(100)
);

• 插入数据

INSERT into user1 VALUES(1,'唐僧','功德佛','121123456,141123456,161123456');
INSERT into user1 VALUES(2,'猪八诫','净坛使者','12144643321,14144643321');
INSERT into user1 VALUES(3,'孙悟空','斗战胜佛','12166666666,14166666666,16166666666,18166666666');
INSERT into user1 VALUES(4,'沙僧','金身罗汉','12198765432,14198765432');

• 查询数据

select * from user1;

• 创建序列表(后续作用讲解)

CREATE table tb_sequence(id int auto_increment not null ,primary key(id));

• 插入自增长数据（注：此时的场景插入四行，个人理解为有多少逗号就插入逗号数+1行）

insert into tb_sequence VALUES(),(),(),();

• 查询【tb_sequence】表

select * from tb_sequence;

• 完整sql查询语句

select user_name,REPLACE(substring(SUBSTRING_INDEX(mobile,',',a.id),
CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') as mobile 
from (
SELECT user_name,CONCAT(mobile,',') as mobile,
LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size
from user1 
) b join tb_sequence a on a.id<=b.size

可以看到数据转换完成，下面分割步骤。

步骤and思路

先来看最里面一层

SELECT user_name,CONCAT(mobile,',') as mobile,
LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size,
REPLACE(mobile,',','') // 这一列REPLACE(mobile,',','')结果语句里是没有的，为了便于大家理解
from user1

注：文章结尾有函数讲解

• REPLACE()函数用于字符串替换。
• CONCAT()函数用于字符串拼接。
• LENGTH()函数用于查询字符串长度。

此时可以看到【mobile】列的数据后面都添加了一个逗号，
【size】列是【mobile】原数据总长度，减去去掉逗号的原数据，加1的长度。
【REPLACE(mobile,’,’,’’)】列是【mobile】列原数据把逗号替换为空的数据。
注意：如果你的数据不是逗号，这里记得更换。

• 结果集
  
• 连接序列化表【tb_sequence】，此时为了直观一些，先不做处理。

select * from (
SELECT user_name,CONCAT(mobile,',') as mobile,
LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size
from user1 
) b join tb_sequence a on a.id<=b.size

重点在于【on a.id<=b.size】，意思是只要【tb_sequence】表【id】列的序号只要小于等于【size】列的长度就笛卡尔积一次，也就是【size】的长度是多少，就显示为多少行数据。
注意：此时也说明了【tb_sequence】表为什么插入4行序列号。

• 结果集
  
• 拆解步骤，每个字段可以对着结果集分别理解Why，后面有注释。
  注：文章结尾有函数讲解

select user_name,
SUBSTRING_INDEX(mobile,',',a.id-1) one, // 截取第a.id-1个逗号之前的数据

CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1 tow,// 查询上面的数据的长度+1

SUBSTRING_INDEX(mobile,',',a.id) three, // 截取第a.id个逗号之前的数据

substring(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1) four,// 从three列截取tow列位置之后的数据

REPLACE(substring(SUBSTRING_INDEX(mobile,',',a.id),
CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') as mobile // 替换掉four列的逗号

from (
SELECT user_name,CONCAT(mobile,',') as mobile,
LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size
from user1 
) b join tb_sequence a on a.id<=b.size

• 结果集
  

结果

• 完整sql查询语句

select user_name,REPLACE(substring(SUBSTRING_INDEX(mobile,',',a.id),
CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') as mobile 
from (
SELECT user_name,CONCAT(mobile,',') as mobile,
LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size
from user1 
) b join tb_sequence a on a.id<=b.size

完成后的结果集

函数

• CONCAT()函数

语法：CONCAT(str1,str2,…)  
说明：返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
例子：
SELECT CONCAT('12','ab','!@')      //结果为：12ab!@
SELECT CONCAT('12','ab','!@',null)      //结果为：null

• LENGTH()函数

语法：LENGTH(str) 和CHAR_LENGTH(str)
说明：都是返回字符串的长度（区别就不说了）。
例子：
select LENGTH('156abc')    //结果为6
select CHAR_LENGTH('156abcdg')   //结果为8

• SUBSTRING()函数

语法：SUBSTRING（str, pos）  
说明：SUBSTRING（被截取字段，从第几位开始截取） 
例子：SELECT SUBSTRING('abcdefg',3)    //结果为 cdefg 

语法：SUBSTRING（str, pos, length）
说明：SUBSTRING（被截取字段，从第几位开始截取，截取长度） 
例子：SELECT SUBSTRING('abcdefg',3,2) //结果为 cd

• SUBSTRING_INDEX()函数

语法：SUBSTRING_INDEX（str,delim,count） 
说明：SUBSTRING_INDEX（被截取字段，分隔符，关键字出现的次数）
例子：
SELECT SUBSTRING_INDEX('15,151,152,16',',',1); //结果是15
//以第一个逗号为分割截取
SELECT SUBSTRING_INDEX('15,151,152,16',',',2); //结果是15,151
//以第二个逗号为分割截取
SELECT SUBSTRING_INDEX('15,151,152,16',',',-1); //结果是16
//从后面开始算第一个逗号

• REPLACE()函数

语法：REPLACE(str , from_str , to_str) 
说明：把str 中出现from_str 的全部替换为to_str
例子：SELECT REPLACE('www.jb51.net' , 'w' , 'Ww') //结果是：WwWwWw.jb51.net