MySQL数据库中如何实现行列互转及字符串拆分？-php教程-PHP中文网

MySQL数据库中如何实现行列互转及字符串拆分？

花韻仙語

发布： 2025-03-02 15:32:00

原创

422人浏览过

mysql数据库中如何实现行列互转及字符串拆分？

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函数和自连接来拆分字符串，并生成新表。

Remove.bg

AI在线抠图软件，图片去除背景

102

查看详情

二、多列转换为多行 (多列数据转换为多行)

假设表结构如下：

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列数据类型为整数，如有不同，请根据实际情况调整数据类型。

以上就是MySQL数据库中如何实现行列互转及字符串拆分？的详细内容，更多请关注php中文网其它相关文章！