MySQL字符串拆成多条数据|极客笔记

MySQL字符串拆成多条数据

在实际的数据处理中，我们有时会遇到需要将一个字符串拆分成多条数据的情况。比如我们有一个字符串，里面包含了多个值，我们需要将这些值分割出来并放入数据库中。在MySQL中，我们可以通过一些函数来实现这个功能。

使用`SUBSTRING_INDEX`函数

SUBSTRING_INDEX函数可以帮助我们截取字符串中指定分隔符之前或之后的部分。我们可以借助这个函数来将字符串拆分成多条数据。

示例：

假设我们有一个字符串 apple,orange,banana，我们需要将这个字符串拆分成多条数据并存入数据库中。

CREATE TABLE fruits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO fruits (name)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('apple,orange,banana', ',', n), ',', -1) AS name
FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) numbers
WHERE n <= CHAR_LENGTH('apple,orange,banana') - CHAR_LENGTH(REPLACE('apple,orange,banana', ',', '')) + 1;

在这个示例中，我们首先创建了一个名为fruits的表，其中包含id和name两个字段。然后我们使用INSERT INTO SELECT语句来将拆分后的数据插入到这个表中。在这个语句中，我们利用了SUBSTRING_INDEX函数和一个子查询来实现字符串的拆分和插入操作。最终，fruits表中将包含三条数据：apple、orange 和 banana。

使用`REGEXP_SUBSTR`函数

在MySQL 8.0版本后，新增了REGEXP_SUBSTR函数，可以通过正则表达式来提取字符串中匹配的部分，从而实现字符串的拆分。

示例：

假设我们有一个字符串 1,2,3,4,5，我们需要将这个字符串拆分成多条数据并存入数据库中。

CREATE TABLE numbers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value INT
);

INSERT INTO numbers (value)
SELECT CAST(REGEXP_SUBSTR('1,2,3,4,5', '[^,]+', 1, numbers.n) AS UNSIGNED) AS value
FROM (SELECT 1 + n AS n FROM numbers_generator) numbers
HAVING value IS NOT NULL;

在这个示例中，我们首先创建了一个名为numbers的表，其中只包含一个字段value。然后，我们使用REGEXP_SUBSTR函数和一个子查询来将字符串拆分成多条数据并插入到numbers表中。最终，numbers表中将包含五条数据：1、2、3、4 和 5。