MySQL一行拆分成多行|极客笔记

MySQL一行拆分成多行

在MySQL中，有时我们会遇到一行数据包含多个信息的情况，需要将其拆分成多行，以便更好地进行数据处理和分析。本文将详细介绍如何使用MySQL语句实现一行数据拆分成多行的方法。

1. 使用SUBSTRING_INDEX()函数

SUBSTRING_INDEX()函数可以根据指定的分隔符将字符串拆分成多个部分。下面是使用SUBSTRING_INDEX()函数实现一行数据拆分成多行的示例：

假设我们有一个表users，其中有一列names存储了多个名字，以逗号分隔。现在我们想将每个名字拆分成一行。

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  names VARCHAR(100)
);

INSERT INTO users (names) VALUES ('John,Doe,Mary,Smith');

SELECT 
  id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(names, ',', n.digit+1), ',', -1) name
FROM
  users
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
  ON CHAR_LENGTH(names) - CHAR_LENGTH(REPLACE(names, ',', '')) >= n.digit
ORDER BY
  id, n.digit;

上述代码中，我们使用了一个内连接的子查询SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3，通过这个子查询可以拆分出4行数据。我们也可以根据实际情况来调整这个子查询的结果集数量。

运行结果如下：

+----+------+
| id | name |
+----+------+
|  1 | John |
|  1 | Doe  |
|  1 | Mary |
|  1 | Smith|
+----+------+

通过SUBSTRING_INDEX()函数的嵌套调用，我们可以实现将一行数据拆分成多行的目的。

2. 使用字符串函数实现拆分

除了使用SUBSTRING_INDEX()函数外，MySQL还提供了其他字符串函数可以实现一行数据拆分成多行的功能，如使用SUBSTRING()函数。

下面是使用SUBSTRING()函数实现一行数据拆分成多行的示例：

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  names VARCHAR(100)
);

INSERT INTO users (names) VALUES ('John,Doe,Mary,Smith');

SELECT 
  id,
  SUBSTRING_INDEX(SUBSTRING(names, pos), ',', 1) name
FROM
  users
  CROSS JOIN
    (SELECT 1 pos UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
WHERE
  pos <= LENGTH(names) - LENGTH(REPLACE(names, ',', '')) + 1
ORDER BY
  id, pos;

上述代码中，我们使用了一个CROSS JOIN的子查询SELECT 1 pos UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4，通过这个子查询可以拆分出4行数据。同样地，我们也可以根据实际情况来调整这个子查询的结果集数量。

运行结果与上述示例一致。

3. 使用正则表达式实现拆分

如果MySQL版本较高（5.7及以上），我们可以使用正则表达式来实现一行数据拆分成多行。MySQL提供了REGEXP_REPLACE()函数用于正则表达式替换操作。

下面是使用正则表达式实现一行数据拆分成多行的示例：

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  names VARCHAR(100)
);

INSERT INTO users (names) VALUES ('John,Doe,Mary,Smith');

SELECT 
  id,
  REGEXP_REPLACE(names, '[^,]+', SUBSTRING_INDEX(SUBSTRING_INDEX(names, ',', n.digit+1), ',', -1)) name
FROM
  users
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
  ON CHAR_LENGTH(names) - CHAR_LENGTH(REPLACE(names, ',', '')) >= n.digit
ORDER BY
  id, n.digit;

运行结果与前述示例一致。

值得注意的是，正则表达式的用法较为复杂，同时也比较消耗计算资源，因此在处理大数据量时需要权衡使用。