mysql 列转行函数

### MySQL 中实现列转行功能 在 MySQL 中，可以使用 `UNION ALL` 或者 `GROUP_CONCAT()` 函数来模拟列转行的效果。对于更复杂的场景，通常会结合存储过程或临时表来完成转换。 #### 使用 UNION ALL 实现简单列转行 当数据量较小且结构固定时，可以直接通过 `UNION ALL` 来拼接多条记录： ```sql SELECT 'ColumnA' AS source_column, ColumnA AS value FROM table_name UNION ALL SELECT 'ColumnB', ColumnB FROM table_name; ``` 这种方法适用于少量列的情况，并能保持较好的性能[^1]。 #### 利用 GROUP_CONCAT 和 SUBSTRING_INDEX 进行复杂处理 如果需要处理更多动态变化的数据，则可以通过组合多个聚合函数达到目的: ```sql SET SESSION group_concat_max_len = 1000000; SELECT id, SUBSTRING_INDEX(GROUP_CONCAT(column_values SEPARATOR ','), ',', n.n) AS single_value FROM ( SELECT t.*, CONCAT_WS(',', col1, col2, col3...) AS column_values FROM your_table t ) combined_data JOIN numbers n ON CHAR_LENGTH(combined_data.column_values) -CHAR_LENGTH(REPLACE(combined_data.column_values,',','')) >= n.n-1 GROUP BY id, n.n; ``` 此查询假设存在一个名为 `numbers` 的辅助表用于生成序列号 `n`，可以根据实际需求调整逻辑[^2]。 #### 数据分割方法 针对特定字段内容的拆分操作，在 SQL 查询语句内部可借助字符串处理函数如 `SUBSTRING_INDEX()` 完成基本切割工作；而对于更加灵活的需求则推荐采用编程语言编写外部脚本配合数据库交互的方式解决。 例如要按照逗号分隔符切分某列中的值并将其作为独立行返回，可以用如下方式： ```sql WITH RECURSIVE cte AS( SELECT id, TRIM(SUBSTRING_INDEX(value_list, ',', 1)) item, CASE WHEN LOCATE(',', value_list)>0 THEN INSERT(value_list, 1, LOCATE(',', value_list)+1,'') ELSE '' END rest FROM mytable WHERE value_list IS NOT NULL AND LENGTH(TRIM(value_list))>0 UNION ALL SELECT id,TRIM(SUBSTRING_INDEX(rest, ',', 1)), CASE WHEN LOCATE(',',rest)>0 THEN INSERT(rest , 1,LOCATE(',',rest )+1,'') ELSE '' END FROM cte WHERE rest !='' ) SELECT DISTINCT id,item FROM cte ORDER BY id; ``` 上述代码片段展示了如何利用递归公用表达式（CTE）迭代解析包含列表形式数据的单个单元格，最终得到每项单独占据一行的结果集[^3]。

在 MySQL 中，可以使用 GROUP_CONCAT 函数将多行数据合并为一行，然后再使用 SUBSTRING_INDEX 函数拆分为多列。这样就可以实现列转行的效果。 示例代码如下： 假设有如下表结构和数据： ``` CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `value` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO `test` (`name`, `value`) VALUES ('a', '1,2,3'); INSERT INTO `test` (`name`, `value`) VALUES ('b', '4,5,6'); INSERT INTO `test` (`name`, `value`) VALUES ('c', '7,8,9'); ``` 要将 value 列转成多行，可以使用以下 SQL 语句： ``` SELECT id, name, SUBSTRING_INDEX(SUBSTRING_INDEX(value, ',', n), ',', -1) AS value FROM test INNER JOIN ( SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 ) numbers ON CHAR_LENGTH(value) - CHAR_LENGTH(REPLACE(value, ',', '')) >= n - 1 ORDER BY id, n; ``` 这里的关键是 numbers 表，它包含了一个从 1 到 3 的数字序列。这个表会与 test 表进行内连接，产生三倍于 test 表的行数。然后使用 SUBSTRING_INDEX 函数将 value 列拆分成多个部分，并按照 n 的值进行排序。最后得到的结果就是列转行后的数据。 注意，这种方法有一个缺点，就是无法处理变长的 value 列。如果 value 列中包含的元素个数不确定，那么这种方法就无法使用了。