MySQL/MariaDB如何优化垂直返回查询结果的SQL语句？

我通过以下UNION查询可将结果垂直展示为多行形式：

SELECT "Username"  AS "Attribute", username  AS "Value" FROM users WHERE id = 123
UNION 
SELECT "Email"     AS "Attribute", email     AS "Value" FROM users WHERE id = 123
UNION 
SELECT "Name"      AS "Attribute", name      AS "Value" FROM users WHERE id = 123
UNION 
SELECT "Activated" AS "Attribute", activated AS "Value" FROM users WHERE id = 123;

该查询会生成如下垂直表格：

此方法可行，但当属性达10个以上时，需编写大量UNION SELECT语句，且重复出现FROM users WHERE id = 123，导致查询冗长且可能低效。我想了解MySQL/MariaDB中是否有其他方法可优化或简化该查询？

补充说明：我有一个PHP脚本用于将查询结果以HTML表格形式展示，即按查询返回的行列呈现。部分场景需垂直展示结果，因此希望通过SQL语句直接得到上述格式的结果，而非修改PHP脚本进行表格转置。

优化方案 #

1. 用CROSS JOIN关联虚拟属性列表 #

构造一个包含所有目标属性名称的虚拟表，通过CROSS JOIN仅查询一次users表，避免重复的FROM和WHERE子句：

SELECT attr.attribute,
       CASE attr.attribute
           WHEN 'Username' THEN u.username
           WHEN 'Email' THEN u.email
           WHEN 'Name' THEN u.name
           WHEN 'Activated' THEN u.activated
       END AS value
FROM users u
CROSS JOIN (
    SELECT 'Username' AS attribute UNION ALL
    SELECT 'Email' AS attribute UNION ALL
    SELECT 'Name' AS attribute UNION ALL
    SELECT 'Activated' AS attribute
) attr
WHERE u.id = 123;

新增属性时，只需在虚拟表中添加一行SELECT '属性名' AS attribute即可，查询逻辑更简洁，性能也更优。

2. 替换UNION为UNION ALL（原查询快速优化） #

原查询中UNION会自动执行去重和排序，但同一用户的属性不会重复，完全可以用UNION ALL替代——它会跳过去重排序步骤，直接合并结果，减少不必要的性能开销：

SELECT "Username"  AS "Attribute", username  AS "Value" FROM users WHERE id = 123
UNION ALL
SELECT "Email"     AS "Attribute", email     AS "Value" FROM users WHERE id = 123
UNION ALL
SELECT "Name"      AS "Attribute", name      AS "Value" FROM users WHERE id = 123
UNION ALL
SELECT "Activated" AS "Attribute", activated AS "Value" FROM users WHERE id = 123;

3. MariaDB 10.5+专属：使用UNPIVOT原生语法 #

MariaDB 10.5及以上版本支持UNPIVOT操作，这是专门为列转行场景设计的原生语法，写法最简洁：

SELECT attribute, value
FROM users
UNPIVOT (
    value FOR attribute IN (
        username AS 'Username',
        email AS 'Email',
        name AS 'Name',
        activated AS 'Activated'
    )
) AS unpivoted
WHERE id = 123;

内容的提问来源于stack exchange，提问作者Gowire