mysql列转行几种方式

SELECT column1 AS value FROM table
UNION ALL
SELECT column2 AS value FROM table
UNION ALL
SELECT column3 AS value FROM table;

SELECT 'column1' AS column_name, column1 AS value FROM table
UNION ALL
SELECT 'column2' AS column_name, column2 AS value FROM table
UNION ALL
SELECT 'column3' AS column_name, column3 AS value FROM table;

SELECT JSON_OBJECT('column1', column1, 'column2', column2, 'column3', column3) AS json_object
FROM table;

CREATE TEMPORARY TABLE temp_table (column_name VARCHAR(255), value VARCHAR(255));

INSERT INTO temp_table (column_name, value)
SELECT 'column1', column1 FROM table
UNION ALL
SELECT 'column2', column2 FROM table
UNION ALL
SELECT 'column3', column3 FROM table;

SELECT * FROM temp_table;

SELECT * FROM table
LATERAL VIEW OUTER APPLY (
    SELECT 'column1' AS column_name, column1 AS value UNION ALL
    SELECT 'column2' AS column_name, column2 AS value UNION ALL
    SELECT 'column3' AS column_name, column3 AS value
) AS lateral_view;

SELECT CAST(column1 AS VARCHAR(255)) AS value FROM table
UNION ALL
SELECT CAST(column2 AS VARCHAR(255)) AS value FROM table;

SET GLOBAL tmp_table_size = 2147483648; -- 设置临时表空间大小为2GB