MySQL数据库高效数据转换：行列互转与字符串拆分
本文介绍如何利用SQL语句直接在MySQL数据库中进行数据转换，避免繁琐的导出导入操作。我们将针对两种常见场景提供解决方案：单列字符串拆分和多列转换为多行。
一、单列字符串拆分 (逗号分隔字符串转换为多行)
假设有一张表，type列包含逗号分隔的数值：
| id | type |
|---|---|
| 1 | 1,2,3,4 |
| 2 | 1 |
| 3 | 3 |
目标是将其转换为一对多关联表：
| id | foreign_id | type |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 2 | 1 |
| 6 | 3 | 3 |
可以使用以下SQL语句实现：
-- 创建临时表存储拆分结果
CREATE TEMPORARY TABLE temptable AS
SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(type, ',', n.n), ',', -1) AS type_item
FROM your_table
INNER JOIN (SELECT a.n + b.n * 10 + c.n * 100 + d.n * 1000 AS n
FROM (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
,(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
,(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d
) n ON LENGTH(REPLACE(type, ',', '')) <= n.n;
-- 将结果插入到目标表 (请替换your_target_table为你的目标表名)
INSERT INTO your_target_table (id, foreign_id, type)
SELECT @row_number := @row_number + 1, id, type_item FROM temptable, (SELECT @row_number := 0) AS rn;
-- 清理临时表
DROP TEMPORARY TABLE temptable;
请将your_table替换为你的实际表名。此SQL语句利用SUBSTRING_INDEX函数和自连接来拆分字符串，并生成新表。
二、多列转换为多行 (多列数据转换为多行)
假设表结构如下：
| id | type1 | type2 | type3 |
|---|---|---|---|
| 1 | 10 | 11 | 12 |
| 2 | 13 | 14 | 15 |
目标是将其转换为：
| id | foreign_id | type |
|---|---|---|
| 1 | 1 | 10 |
| 2 | 1 | 11 |
| 3 | 1 | 12 |
| 4 | 2 | 13 |
| 5 | 2 | 14 |
| 6 | 2 | 15 |
可以使用UNION ALL语句实现：
CREATE TABLE new_table (id INT, foreign_id INT, type INT); INSERT INTO new_table (id, foreign_id, type) SELECT @row_number := @row_number + 1, id, type1 FROM your_table, (SELECT @row_number := 0) AS rn UNION ALL SELECT @row_number := @row_number + 1, id, type2 FROM your_table, (SELECT @row_number := 0) AS rn UNION ALL SELECT @row_number := @row_number + 1, id, type3 FROM your_table, (SELECT @row_number := 0) AS rn;
同样，请将your_table替换为你的实际表名。此SQL语句将三列数据分别插入新表，并使用@row_number变量生成连续的id。
这些SQL语句可以直接在MySQL中执行，实现数据转换，无需其他工具。请注意，以上代码假设type列数据类型为整数，如有不同，请根据实际情况调整数据类型。
