MySQL中实现用户权限查询结果Unpivoting逆透视转换的方法

MySQL 权限表行列转换实现方案 #

你需要的效果可以通过**先逆透视（把权限列拆成多行）、再透视（把数据库名转成多列）**的逻辑实现，不同MySQL版本可用的语句如下：

方案1：MySQL 8.0及以上版本（推荐，语法简洁）

WITH unpivot_data AS (
    -- 第一步：逆透视，将多列权限转为多行
    SELECT 
        USER AS Users,
        db AS DatabaseName,
        PrivilegeName,
        PrivilegeValue
    FROM mysql.db
    JOIN JSON_TABLE(
        JSON_ARRAY(
            JSON_OBJECT('name','Select','value',IF(Select_priv='Y','1','0')),
            JSON_OBJECT('name','Insert','value',IF(Insert_priv='Y','1','0')),
            JSON_OBJECT('name','Update','value',IF(Update_priv='Y','1','0')),
            JSON_OBJECT('name','Delete','value',IF(Delete_priv='Y','1','0')),
            JSON_OBJECT('name','Create','value',IF(Create_priv='Y','1','0')),
            JSON_OBJECT('name','References','value',IF(References_priv='Y','1','0')),
            JSON_OBJECT('name','Alter','value',IF(Alter_priv='Y','1','0')),
            JSON_OBJECT('name','Drop','value',IF(Drop_priv='Y','1','0'))
        ),
        '$[*]' COLUMNS (
            PrivilegeName VARCHAR(20) PATH '$.name',
            PrivilegeValue VARCHAR(2) PATH '$.value'
        )
    ) AS priv_list
)
-- 第二步：透视，将数据库名转为列
SELECT 
    Users,
    PrivilegeName AS Privileges,
    MAX(CASE WHEN DatabaseName = 'performance_schema' THEN PrivilegeValue ELSE '' END) AS `performance_schema`,
    MAX(CASE WHEN DatabaseName = 'sys' THEN PrivilegeValue ELSE '' END) AS `sys`
FROM unpivot_data
GROUP BY Users, PrivilegeName
-- 保证权限排序和你示例的一致
ORDER BY Users, FIELD(PrivilegeName,'Select','Insert','Update','Delete','Create','References','Alter','Drop');

方案2：MySQL 5.x兼容版本（用UNION ALL实现逆透视）

SELECT 
    Users,
    Privileges,
    MAX(CASE WHEN DatabaseName = 'performance_schema' THEN PrivilegeValue ELSE '' END) AS `performance_schema`,
    MAX(CASE WHEN DatabaseName = 'sys' THEN PrivilegeValue ELSE '' END) AS `sys`
FROM (
    SELECT USER AS Users, db AS DatabaseName, 'Select' AS Privileges, IF(Select_priv='Y','1','0') AS PrivilegeValue FROM mysql.db
    UNION ALL
    SELECT USER AS Users, db AS DatabaseName, 'Insert' AS Privileges, IF(Insert_priv='Y','1','0') AS PrivilegeValue FROM mysql.db
    UNION ALL
    SELECT USER AS Users, db AS DatabaseName, 'Update' AS Privileges, IF(Update_priv='Y','1','0') AS PrivilegeValue FROM mysql.db
    UNION ALL
    SELECT USER AS Users, db AS DatabaseName, 'Delete' AS Privileges, IF(Delete_priv='Y','1','0') AS PrivilegeValue FROM mysql.db
    UNION ALL
    SELECT USER AS Users, db AS DatabaseName, 'Create' AS Privileges, IF(Create_priv='Y','1','0') AS PrivilegeValue FROM mysql.db
    UNION ALL
    SELECT USER AS Users, db AS DatabaseName, 'References' AS Privileges, IF(References_priv='Y','1','0') AS PrivilegeValue FROM mysql.db
    UNION ALL
    SELECT USER AS Users, db AS DatabaseName, 'Alter' AS Privileges, IF(Alter_priv='Y','1','0') AS PrivilegeValue FROM mysql.db
    UNION ALL
    SELECT USER AS Users, db AS DatabaseName, 'Drop' AS Privileges, IF(Drop_priv='Y','1','0') AS PrivilegeValue FROM mysql.db
) AS unpivot_data
GROUP BY Users, Privileges
ORDER BY Users, FIELD(Privileges,'Select','Insert','Update','Delete','Create','References','Alter','Drop');

如果后续需要新增其他数据库的展示列，只要在SELECT部分新增对应的CASE WHEN 数据库名 = 'xxx' THEN PrivilegeValue ELSE '' END分支即可。

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