SQL如何按逗号拆分字符串

上周我在公司遇到一个数据迁移项目。
我们需要将标签字段从一张表迁移到另一张表。
标签之间用逗号分隔，因此您必须手动拆分它们。

2 02 3 年这个月，朋友教了我一招。
他使用SQL语句首先创建临时表，然后递归地拆分每个标签。

工作原理：首先创建一个临时表，使用SUBSTRING_INDEX函数从源表的tags列中提取第一个标签，并将其作为标签插入到临时表中：
sql 创建临时表 temp_tags AS SELECT id, SUBSTRING_INDEX(tags, ',', 1 ) AS 标签 FROM 源表；
然后递归处理剩余的标签，直到所有标签都被分割：
sql 创建临时表 temp_tags AS SELECT id, SUBSTRING_INDEX(tags, ',', 1 ) AS 标签 来自临时标签 WHERE 标记不为空；
最后将分割标签插入到目标表中：
sql 插入目标表（id，标签） 从 temp_tags 中选择 id、标签；
这样我们就成功的将源表中的标签进行了拆分并迁移到了目标表中。
但该方法仅适用于MySQL数据库。
对于其他数据库可能需要找到其他方法。
这取决于你，或者你可以尝试一下。

MySQL实现列数据拆分为多行mysql一列拆分多行

你好，你是问这个吗？ MySQL 将一列数据拆分为多行。
我之前处理过2 02 2 年电商订单表的要求，其实非常实用。

最直接的方法是使用SUBSTRING_INDEX。
这个功能确实用途广泛。
我记得有一个名为 Orders_items 的表，它有一个 sku_list 字段，该字段是用逗号分隔的产品 SKU，如“a、b、c、d”。
使用此功能逐层剥洋葱。

具体怎么写呢？让我举个例子。
假设您有表数据和列颜色存储“红、绿、蓝、黄”等字符串。
如果您想将每种颜色放在单独的行中，请尝试以下操作：
sql SELECT SUBSTRING_INDEX(colors, ',', 1 ) AS 颜色 从数据 联合所有 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 2 ), ',', -1 ) AS 颜色 从数据 联合所有 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 3 ), ',', -1 ) AS 颜色 来自数据
如何得到这个SQL呢？首次运行 SUBSTRING_INDEX(colors, ',', 1 ) 时，'red' 是逗号之前的第一个值。
如果您第二次运行 SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 2 ), ',', -1 ) ，您首先会在逗号之前获得第二个值“green, blue”，然后在第二个逗号之后获得“blue”值。
以同样的方式第三次取“黄色”。
您可以使用 UNION ALL 将结果组合起来以获得所有结果。

但是，此方法有一个问题。
这意味着，如果某些字段具有不同数量的分隔符，例如有些是“红、绿、蓝”，有些是“红、绿、蓝、黄、紫”，那么您将不可避免地最终报告错误。
然后我就踩进了这个坑。
2 02 3 年3 月的一个下午，我花了两个小时调试才找到问题。

如果数据未标准化，则该方法有些不可靠。
此时，您可能会考虑使用 REGEXP_REPLACE。
该功能在 MySQL 8 .0+ 版本中运行良好。
您仍然可以尝试使用颜色表。

sql SELECT REGEXP_REPLACE(颜色, ',', '\n') AS 颜色 来自数据
这条SQL直接用换行符替换了逗号，并且输出的每一行都带有颜色，非常直观。
但是 REGEXP_REPLACE 的性能如何呢？之前在测试环境跑过一次，数据量增加的时候有点卡顿。
大约1 0万条数据后，速度变得很慢。

最后一种方法是JSON相关的函数，该函数比较新，需要MySQL 5 .7 或更高版本。
例如，如果您有 JSON 格式的数据或将逗号分隔的字符串转换为 JSON 数组，请使用 JSON_TABLE 来拆分它们。
静止颜色表：
sql 选择 jt.颜色 FROM 数据，JSON_TABLE(JSON_ARRAY(颜色)，'$[]' COLUMNS(color VARCHAR(2 5 5 ) PATH '$')) jt
这种方法相当先进，适合数据结构相对标准化的场景。
我在处理从 API 返回的 JSON 数据时使用了它，效果很好。
不过，对于初学者来说，SUBSTRING_INDEX 和 REGEXP_REPLACE 可能更容易理解和使用。

无论如何，您可以根据自己的具体要求和数据条件来决定选择哪一种。
我还在思考这个问题。
有时您需要连接WITH RECURSIVE或临时表以实现更复杂的分区。
下次有机会再详细说一下我们来谈谈吧。