mysql 列存储_MySql 行转列 存储过程实现

同学们在使用mysql的过程中，会遇到一个行转列的问题，就是把多条数据转化成一条数据 用多列显示。

方法1. 实现方式用下面的存储过程，表名对应的修改就行。

BEGIN

declare current integer;

declare strValue tinytext ;

declare sqlValue BLOB;

declare columnCount int(4);

declare countNum INTEGER;

declare pageNumber INTEGER;

declare firstNumber integer;

set current = 0;

set countNum=0;

set columnCount=0;

set pageNumber=0;

set firstNumber=0;

drop table if exists personal_contact_temp;

CREATE TABLE `personal_contact_temp` (

`id` varchar(64) NOT NULL COMMENT '主键',

`personal_id` varchar(64) ,

`relation` int(4) ,

`name` TEXT ,

`phone` TEXT ,

`phone_status` int(4) ,

`mobile` TEXT ,

PRIMARY KEY (`id`)

) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DEFAULT CHARSET=utf8 COMMENT='客户信息联系人临时表';

set columnCount=(select max(conNum) from (select count(*) as conNum from personal_contact GROUP BY personal_id) b);

set current =(select count(*) from (select count(*) as countNum from personal_contact GROUP BY personal_id) b);

while columnCount>0 do

begin

set sqlValue='';

set sqlValue= CONCAT('alter TABLE personal_contact_temp add COLUMN relation',columnCount,'A',' INT(4),',

'add COLUMN name',columnCount,'A',' VARCHAR(255),',

'add COLUMN phone',columnCount,'A',' varchar(255),',

'add COLUMN phone_status',columnCount,'A',' int(4),',

'add COLUMN mobile',columnCount,'A',' VARCHAR(255)',';');

set @ms=sqlValue;

PREPARE stmt1 FROM @ms;

EXECUTE stmt1;

END;

set columnCount=columnCount-1;

end WHILE;

while current>0 do

set pageNumber=current-1;

set strValue = (select personal_id from personal_contact group by personal_id limit pageNumber,1);

set countNum =(select count(*) from personal_contact where personal_id =strValue);

set firstNumber=countNum;

if countNum=1 then

insert into personal_contact_temp(id,personal_id,relation,name,phone,phone_status,mobile)

(select uuid(),personal_id,relation,`name`,phone,phone_status,mobile from personal_contact where personal_id =strValue);

ELSE

while countNum>0 do

set pageNumber=countNum-1;

if(firstNumber=countNum) then

insert into personal_contact_temp(id,personal_id,relation,name,phone,phone_status,mobile)

(select uuid(),personal_id,relation,`name`,phone,phone_status,mobile from personal_contact where personal_id =strValue LIMIT pageNumber,1);

ELSE

BEGIN

set sqlValue='';

set sqlValue= CONCAT('update personal_contact_temp a left join

(select relation,`name`,phone,phone_status,mobile,personal_id from personal_contact where personal_id =',"'",strValue,"'",' limit ',pageNumber,',1) b

on a.personal_id =b.personal_id

set a.relation',countNum,'A','=b.relation,

a.name',countNum,'A','=b.`name`,

a.phone',countNum,'A','=b.phone,

a.phone_status',countNum,'A','=b.phone_status,

a.mobile',countNum,'A','=b.mobile where a.personal_id =',"'",strValue,"'",';');

set @ms=sqlValue;

PREPARE stmt1 FROM @ms;

EXECUTE stmt1;

end;

end if;

set countNum =countNum-1;

end WHILE;

end if;

set current=current-1;

end WHILE;

END;

注意 这个存储过程在运行的时候回报 列太大的问题，就是如果多行数据展示到一行，会超出 mysql 行数据的最大值。 这个可以酌情处理。

方法2. Mysql 提供了 group_concat 函数

select GROUP_CONCAT(`name`,'"',',',relation,'"',',',phone) from personal_contact GROUP BY personal_id;

通过这个函数可以将你要查询的数据放到一个列中，然后到处CSV格式的数据，将后面这一列的数据转化成CSV格式的数据，然后存成CSV导入到数据库中。

欢迎大家提出更多解决方案。