mysql中如何使用列模式_mysql中把一字段拆分成列模式

pro_game_info表中type字段值如下：

gtype='即时制,角色扮演,模拟,网络,休闲,解谜,动作,策略'

gtype='即时制,角色扮演,模拟'

需求把gtype值按照','拆分成多列模式值，即

模拟

网络

休闲

解谜

动作

策略

动作

财务

方法如下：

CREATE DEFINER=`gtcom`@`%` PROCEDURE `pro_game_type`()

begin

DECLARE type int(6);

DECLARE i int DEFAULT 0;

-- 查询最大','的数量

select max(length(gtype)-length(replace(gtype,',',''))) into type from pro_game_info;

-- 创建辅助表

CREATE TABLE if not exists help_game_type ( id INT(11),PRIMARY KEY (`id`));

truncate table help_game_type;

while i

insert into help_game_type (id) values (i);

set i =i+1;

end while;

-- 插入足够的辅助数据

while i < type+1 do

insert into help_game_type(id) values (i);  set i=i+1;

end while;

-- 统计结果展示

TRUNCATE table help_game_tmp;

INSERT into help_game_tmp(gtype,n)

select gtype,count(*) n from

(SELECT substring_index(substring_index(t.gtype,',',b.id + 1),',',- 1) as gtype

FROMpro_game_info tJOIN help_game_type b ON   b.id''

) as t

group by gtype ORDER BY n desc;

end