对MySQL表进行解构操作

Question

我想知道是否可以将这个输出更改为：

User           Database            Select  Insert  Update  Delete  Create  References  Alter   Drop    
-------------  ------------------  ------  ------  ------  ------  ------  ----------  ------  --------
mysql.session  performance_schema  1       0       0       0       0       0           0       0       
mysql.sys      sys                 0       0       0       0       0       0           0       0

变成这样：

Users               Privileges      performance_schema      sys
-----               ----------      ------------------      ---
mysql.session       Select                  1       
mysql.session       Insert                  0       
mysql.session       Update                  0       
mysql.session       Delete                  0       
mysql.session       Create                  0       
mysql.session       References              0       
mysql.session       Alter                   0       
mysql.session       Drop                    0       
mysql.sys           Select                                  0
mysql.sys           Insert                                  0
mysql.sys           Update                                  0
mysql.sys           Delete                                  0
mysql.sys           Create                                  0
mysql.sys           References                              0
mysql.sys           Alter                                   0
mysql.sys           Drop                                    0

我使用的查询语句是：

SELECT 
  DISTINCT
  USER "User",
  db "Database",
  IF(Select_priv = 'Y', '1 ', '0') AS "Select",
  IF(Insert_priv = 'Y', '1 ', '0') AS "Insert",
  IF(Update_priv = 'Y', '1', '0') AS "Update",
  IF(Delete_priv = 'Y', '1', '0') AS "Delete",
  IF(Create_priv = 'Y', '1', '0') AS "Create",
  IF(References_priv = 'Y', '1', '0') AS "References",
  IF(Alter_priv = 'Y', '1', '0') AS "Alter",
  IF(Drop_priv = 'Y', '1', '0') AS "Drop"
 FROM
    mysql.db
 ORDER BY
    USER, Db;

任何帮助将不胜感激。提前谢谢！

Answer 1

这是一个“unpivot”的示例：

(SELECT User AS Users, 'Select' AS Privileges, 1 AS PrivOrder, MAX(CASE db WHEN 'performance_schema' THEN Select_priv='Y' END) AS `performance_schema`, MAX(CASE db WHEN 'sys' THEN Select_priv='Y' END) AS `sys` FROM db GROUP BY User)
UNION
(SELECT User, 'Insert', 2, MAX(CASE db WHEN 'performance_schema' THEN Insert_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Insert_priv='Y' END) FROM db GROUP BY User)
UNION
(SELECT User, 'Update', 3, MAX(CASE db WHEN 'performance_schema' THEN Update_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Update_priv='Y' END) FROM db GROUP BY User)
UNION
(SELECT User, 'Delete', 4, MAX(CASE db WHEN 'performance_schema' THEN Delete_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Delete_priv='Y' END) FROM db GROUP BY User)
UNION
(SELECT User, 'Create', 5, MAX(CASE db WHEN 'performance_schema' THEN Create_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Create_priv='Y' END) FROM db GROUP BY User)
...
ORDER BY Users, PrivOrder;

抱歉，无法自动根据查询找到的数据为每个模式定义列的透视或反透视查询。在查询开始读取数据之前，必须在查询中设置列。