mysql 字段分割成多条数据|极客笔记

mysql 字段分割成多条数据

在实际的数据库操作中，有时候会遇到将一个字段中的数据以某种规则分割成多条数据的需求。比如某个字段存储了多个标签，我们希望将这些标签拆分成各自的单独记录。这时候就需要使用MySQL中的一些函数来实现这个操作。本文将详细介绍如何使用MySQL来实现字段分割成多条数据的功能。

准备数据

首先我们需要创建一个示例表，用来存储包含需要分割数据的字段。假设我们有一个表叫tags_table，包含一个字段tags存储了多个标签，数据如下所示：

id	tags
1	tag1,tag2,tag3
2	tag4,tag5

使用`SUBSTRING_INDEX`函数分割数据

MySQL提供了SUBSTRING_INDEX函数用来获取字符串中指定分割符号前后的内容。我们可以利用这个函数来实现将一个字段中的数据分割成多条数据。

下面是一个示例查询语句，将tags字段按照逗号分割成多条数据并展示出来：

SELECT
  id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n), ',', -1) AS tag
FROM
  tags_table
JOIN (
  SELECT 1 AS n UNION ALL
  SELECT 2 UNION ALL
  SELECT 3
) AS numbers
ON
  CHAR_LENGTH(tags) - CHAR_LENGTH(REPLACE(tags, ',', '')) >= n - 1

在这个查询中，我们首先使用SUBSTRING_INDEX(tags, ',', n)获取标签字段中第n个逗号之前的所有内容，然后再使用SUBSTRING_INDEX(..., ',', -1)获取这段内容中最后一个逗号之后的内容，即得到单独的一个标签数据。通过将numbers表联接到tags_table表来循环进行分割操作，最终实现将一个字段中的数据按照分隔符分割成多条数据。

运行以上查询语句后，将得到以下结果：

id	tag
1	tag1
1	tag2
1	tag3
2	tag4
2	tag5

使用存储过程批量处理数据

如果需要对整个表的数据进行批量处理，可以使用存储过程来实现。下面是一个示例存储过程，实现了将一个表中的字段按照逗号分割成多条数据的功能：

DELIMITER //
CREATE PROCEDURE split_tags_data()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE id_val INT;
  DECLARE tags_val TEXT;
  DECLARE cur CURSOR FOR SELECT id, tags FROM tags_table;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO id_val, tags_val;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @n := 1;
    WHILE @n <= (SELECT CHAR_LENGTH(tags_val) - CHAR_LENGTH(REPLACE(tags_val, ',', '')) + 1) DO
      INSERT INTO new_tags_table (id, tag)
      SELECT id_val, SUBSTRING_INDEX(SUBSTRING_INDEX(tags_val, ',', @n), ',', -1) AS tag;

      SET @n := @n + 1;
    END WHILE;
  END LOOP;

  CLOSE cur;
END //
DELIMITER ;

在这个存储过程中，我们首先声明了一些变量，包括done用来标识是否读取完成，id_val和tags_val用来存储读取的每一行数据。然后创建了一个游标cur来遍历tags_table表中的数据。在每次循环中，根据上面提到的方式将标签字段分割成多条数据，并插入到新的表new_tags_table中。最后关闭游标并结束存储过程。