MySQL 如何使用MySQL SQL来拆分以逗号分隔的行

在数据库中，经常会遇到需要处理以逗号分隔的数据的情况。例如，我们可能会在一个列中保存多个值，这些值之间用逗号隔开。当需要对这些值进行操作和查询时，就需要将它们分隔开。本文将介绍如何使用MySQL SQL来拆分以逗号分隔的行。

阅读更多：MySQL 教程

用SUBSTRING_INDEX拆分单词

我们可以使用MySQL SQL中的SUBSTRING_INDEX函数来拆分单词。该函数可以将字符串拆分成两部分：左侧部分和右侧部分。 接下来，我们将使用该函数将逗号分隔的字符串拆分为单独的单词。下面是一个例子，我们将使用该函数来获取字符串中的第一个单词：

SELECT SUBSTRING_INDEX('apple,banana,orange', ',', 1);

结果为：

apple

该函数的参数如下：

• 字符串：要拆分的字符串。
• 分隔符：用于拆分字符串的分隔符。
• 索引：指定从左向右数的单词位置。

我们可以通过改变索引的值来获取不同的单词。例如，要获取第二个单词，可以将索引参数设置为2。

SELECT SUBSTRING_INDEX('apple,banana,orange', ',', 2);

结果为：

apple,banana

使用正则表达式拆分单词

当需要拆分的字符串的数量不确定时，使用SUBSTRING_INDEX就会变得不太适合，此时可以使用正则表达式来拆分字符串。下面是一个使用正则表达式来拆分字符串的例子：

SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', numbers.n), ',', -1) as fruit
FROM
 (SELECT @row := @row + 1 as n FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t, (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t2, (SELECT @row := -1) r) numbers
WHERE 
  LENGTH('apple,banana,orange') <= LENGTH(REPLACE('apple,banana,orange', ',', '')) + 1
  AND numbers.n <= LENGTH('apple,banana,orange') - LENGTH(REPLACE('apple,banana,orange', ',', '')) + 1;

结果为：

fruit
apple
banana
orange

该SQL查询使用了嵌套的子查询和正则表达式，用于将逗号分隔的字符串拆分成单独的单词。该查询中的子查询生成了一个数字表，其中包含从1到N个数字，其中N是输入字符串中的逗号数加1。该数字表充当拆分过程中单词的位置。主要逻辑是将字符串拆分成单独的单词，并将它们与数字表中的位置匹配。

拆分多行数据

在某些情况下，可能需要将多个逗号分隔的行拆分成单词，并将其作为单独的行进行插入。例如，当从CSV文件中加载数据时，可能会遇到这种情况。下面是一个使用正则表达式将多行数据拆分的例子：

DROP TABLE IF EXISTS fruit;
CREATE TEMPORARY TABLE fruit (id INT, fruits VARCHAR(255));
INSERT INTO fruit VALUES (1, 'apple,banana'), (2, 'orange,pear');

SELECT 
  id as old_id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(fruits, ',', numbers.n), ',', -1) as fruit
FROM
 (SELECT @row := @row + 1 as n FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t, (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t2, (SELECT @row := -1) r) numbers
JOIN fruit ON LENGTH(fruit) <= LENGTH(REPLACE(fruit, ',', '')) + 1 
   AND numbers.n <= LENGTH(fruit) - LENGTH(REPLACE(fruit, ',', '')) + 1
ORDER BY id, fruit;

结果为：

old_id  fruit
1       apple
1       banana
2       orange
2       pear

该SQL查询使用了与前面的例子相同的方法，只是在最后将其连接到原始表中。它使用JOIN操作，将数字表和水果表连接在一起，并使用ORDER BY对结果进行排序。

总结

MySQL SQL提供了多种方法来拆分以逗号分隔的行。我们可以使用SUBSTRING_INDEX函数来拆分单独的单词，也可以使用正则表达式来拆分多个单词。在使用正则表达式时，我们可以使用数字表来充当单词的位置。了解这些技术将有助于处理含有逗号分隔值的数据，使数据更容易查询和操作。