Deconstruct MySQL tables
P粉549986089
P粉549986089 2023-08-29 18:24:12
0
1
512
<p>I was wondering if I could change this output to: </p> <pre class="brush:php;toolbar:false;">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</pre> <p>It becomes like this:</p> <pre class="brush:php;toolbar:false;">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</pre> <p>The query statement I used is: </p> <pre class="brush:php;toolbar:false;">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;</pre> <p>Any help would be greatly appreciated. Thank you in advance! </p>
P粉549986089
P粉549986089

reply all(1)
P粉481815897

This is an example of "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;

Sorry, there is no way to automatically define pivot or unpivot queries for columns per schema based on the data found by the query. Columns must be set in the query before the query starts reading data.

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template