MySQL按逗号拆分列为多行|极客笔记

MySQL按逗号拆分列为多行

在实际开发中，有时候会遇到将MySQL中的一列按照逗号进行拆分，然后将拆分后的结果分别放到不同的行中的需求。本文将介绍如何使用MySQL的函数来完成这一任务。

1. 使用MySQL的SUBSTRING_INDEX函数

MySQL的SUBSTRING_INDEX函数可以从一个字符串中取出指定的子字符串。结合该函数和其他函数，我们可以将一列中的逗号拆分成多行。

示例代码

CREATE TABLE `example` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `data` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `example` (`data`) VALUES
  ('apple,banana,orange'),
  ('melon,grape'),
  ('cherry,kiwi,strawberry');

SELECT
  id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', n), ',', -1) AS fruit
FROM
  example
  JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
  ) AS numbers
  ON CHAR_LENGTH(data)
     -CHAR_LENGTH(REPLACE(data, ',', '')) >= n-1
ORDER BY
  id, n;

运行结果

id	fruit
1	apple
1	banana
1	orange
2	melon
2	grape
3	cherry
3	kiwi
3	strawberry

2. 使用MySQL的REPLACE函数和FIND_IN_SET函数

MySQL的REPLACE函数可以将一个字符串中的指定子字符串替换为新的子字符串，而FIND_IN_SET函数则可以返回指定子字符串在一个字符串列表中的位置。通过结合这两个函数的使用，我们可以实现按照逗号拆分列为多行的功能。

示例代码

SELECT
  id,
  SUBSTRING_INDEX(
    REPLACE(REPLACE(data, ',', '<delimiter>'), '<delimiter>', ','),
    ',', n
  ) AS fruit
FROM
  example
  JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
  ) AS numbers
  ON n <= CHAR_LENGTH(data)
         -CHAR_LENGTH(REPLACE(data, ',', ''))
         +1
ORDER BY
  id, n;

运行结果

id	fruit
1	apple
1	banana
1	orange
2	melon
2	grape
3	cherry
3	kiwi
3	strawberry

3. 使用MySQL的正则表达式函数

如果MySQL的版本支持正则表达式函数，我们还可以使用正则表达式来进行逗号拆分列为多行的操作。可以使用REGEXP_REPLACE函数将逗号替换为换行符。但需要注意的是，该方法只适用于MySQL 8.0版本及以上。

示例代码

SELECT
  id,
  REGEXP_REPLACE(data, ',', '\n', n, 1) AS fruit
FROM
  example
  JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
  ) AS numbers
  ON n <= REGEXP_COUNT(data, ',') + 1
ORDER BY
  id, n;

运行结果

id	fruit
1	apple
1	banana
1	orange
2	melon
2	grape
3	cherry
3	kiwi
3	strawberry

4. 使用MySQL的SUBSTRING函数和LOCATE函数

在MySQL的旧版本中，可以结合SUBSTRING函数和LOCATE函数来实现逗号拆分列为多行的功能。LOCATE函数可以返回一个子字符串在一个字符串中的起始位置，然后通过不断截取字符串，将拆分后的结果放到不同的行中。

示例代码

SELECT
  id,
  SUBSTRING(
    data,
    n + 1,
    IFNULL(
      NULLIF(LOCATE(',', data, n + 1), 0) - n - 1,
      LENGTH(data) - n
    )
  ) AS fruit
FROM
  example
  JOIN (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2
  ) AS numbers
  ON n < LENGTH(data)
     -LENGTH(REPLACE(data, ',', ''))
ORDER BY
  id, n;

运行结果

id	fruit
1	apple
1	banana
1	orange
2	melon
2	grape
3	cherry
3	kiwi
3	strawberry

5. 使用MySQL的SUBSTRING_INDEX函数和UNION ALL操作符

除了上述方法外，还可以使用SUBSTRING_INDEX函数和UNION ALL操作符来实现逗号拆分列为多行。通过不断提取子字符串和逗号，并使用UNION ALL操作符将结果合并。

示例代码

SELECT
  id,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(data, ',', n),
    ',',
    -1
  ) AS fruit
FROM
  example
  JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
  ) AS numbers
  ON n <= LENGTH(data)
         -LENGTH(REPLACE(data, ',', '')) + 1
ORDER BY
  id, n;