MySQL语句实现字段拆分成多行

数据表`sp_raw` 结构及数据示例

其中`kw`是要拆分为多行的数据,分隔符为 " | ",管道符及前后空格共计三个字符

CREATE TABLE `sp_raw` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doctor` int(11) NOT NULL,
  `source` int(11) DEFAULT NULL,
  `kw` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `doctor_id` (`doctor`),
  KEY `source_id` (`source`)
);

辅助表`sp_idx`

事实上只是个从1到65535的一列数据,下面要用。

CREATE TABLE `sp_idx` (
  `id` smallint(5) unsigned NOT NULL,
  `x` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`)
) COMMENT='拆分辅助表';
INSERT into `sp_idx`(`id`,`x`) values(1,0);
INSERT into `sp_idx`(`id`,`x`) values(2,0);
INSERT into `sp_idx`(`id`,`x`) values(3,0);
...
INSERT into `sp_idx`(`id`,`x`) values(65535,0);

对每行记录计算拆分条数

计算每行要拆分成的行数据,即有多少条数据通过分隔符拼接在一起。即计算字符串中删除分隔符后长度的减少量,即是 分隔符的个数*分隔符长度。分隔符个数+1 即是拆分条数。

这里使用char_length()计算长度,如果分隔符没有多字节字符,使用length()也可以。

SELECT r.`id`, `doctor`, `source`, `kw`,
 (char_length(kw)-char_length(REPLACE(kw,' | ',''))) DIV char_length(' | ') + 1 as kw_count 
FROM `sp_raw` r ;

联入入辅助表

按上步计算出的kw_count,联入相应条数的序列数据,从1 到 kw_count,序列数据从辅助表取。联表关系使用 < ,而不是通常使用的等于;这是本方案的精妙之处。

SELECT r.`id`, `doctor`, `source`, `kw`
 ,x.`id`
FROM `sp_raw` r
INNER JOIN `sp_idx` x 
on x.`id` <= (char_length(kw)-char_length(REPLACE(kw,' | ',''))) DIV char_length(' | ') + 1
;

按序号取子串

结果记录集最后一列id,事实上就是原始字符的排序号。每条记录按该序号截取子串即是结果了。这里使用 SUBSTRING_INDEX() 函数,截取前N段或后N段子串。

第一步,如下,截前N段子串的结果,再截最后一段即是每行所需结果。

SELECT r.`id`, `doctor`, `source` , `kw`
 ,x.`id`, substring_index(`kw`,' | ',x.`id`) as step_1
 ,substring_index(substring_index(`kw`,' | ',x.`id`),' | ',-1) as step_2
FROM `sp_raw` r
INNER JOIN `sp_idx` x 
on x.`id` <= (char_length(kw)-char_length(REPLACE(kw,' | ',''))) DIV char_length(' | ') + 1
;

总结整个过程

SELECT r.`id`, `doctor`, `source`
,x.`id` as idx, substring_index(substring_index(`kw`,' | ',x.`id`),' | ',-1) as piece
FROM `sp_raw` r 
INNER JOIN `sp_idx` x 
ON x.`id` <= (char_length(kw)-char_length(REPLACE(kw,' | ',''))) DIV char_length(' | ') + 1 ;

语句,可以当作模板使用,其中`kw` 替换为待拆分字段,' | ' 为分隔字符串,piece为结果,idx为piece在原始`kw`中的序号。

辅助表`sp_idx`需要事先建好,事实上只要从是从1开始的整数序列即可;如果待拆分子串数超过65535,则要扩充本表的记录数。INSERT into `sp_idx`(x) SELECT `x` FROM `sp_idx`;

如果实在不方便建辅助表,可以使用`mysql`. `help_topic`表的help_topic_id字段(不推荐)。要能保证其完整性,而且待拆分字符串不大于`help_topic`的其行数。而且还要加个where条件 help_topic_id>0,否则会拆分出多余的空串行,因为该字段是从0开始编号的。

附件:原始示例数据

原始示例数据备份下载 split_field_to_lines_in_mysql.zip




coded by nessus

发表评论

电子邮件地址不会被公开。

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据