mysql - 从特定数据库中检索所有表名、列名和列值

Question

我正在尝试选择所有表名、列名、列值，并按表名在一行中显示它们。这是查询。

SELECT 
    CONCAT(a.table_name,'-',GROUP_CONCAT(a.column_name,':' 
                                         ORDER BY ordinal_position ASC)) 
    as table_data
FROM
(
    SELECT * FROM information_schema.columns WHERE table_schema = 'db_xxx'
) a
GROUP BY table_name

上面的查询可以很好地检索表名和列名，但是当我试图获取它的列数据时

SELECT 
    CONCAT(a.table_name,'-',GROUP_CONCAT(a.column_name,':',
                            (SELECT a.column_name FROM a.table_name) 
                            ORDER BY ordinal_position ASC)) 
    as table_data
FROM
(
    SELECT * FROM information_schema.columns WHERE table_schema = 'db_xxx'
) a
GROUP BY table_name

无法在 (SELECT a.column_name FROM a.table_name) 中识别 a.table_name。我应该如何解决这个问题？

Answer 1

要获取表名和列名，您可以使用:

SELECT
 CONCAT(`table_name`, '-',
    GROUP_CONCAT(`column_name` ORDER BY ordinal_position SEPARATOR ':')) AS result
FROM information_schema.columns
-- WHERE `table_name` = ?
GROUP BY `table_name`;

SqlFiddleDemo

输出:

╔══════════════╗
║    result    ║
╠══════════════╣
║ tab0-id:col  ║
║ tab1-id:col2 ║
║ tab2-id:col3 ║
╚══════════════╝

---

要获取每一列的示例记录，您需要使用动态 SQL:

SET @sql = '';

SELECT @sql := CONCAT(@sql, 
                      '(SELECT ''', `table_name`, ''' AS tab_name, ''', 
                                 `column_name`, ''' AS column_name, ',
                                 `ordinal_position`, ' AS ordinal_pos, ',
                                 `column_name`, ' AS sample '
                       'FROM ', `table_name` ,         
                       ' LIMIT 1) UNION ALL ')
FROM information_schema.columns
WHERE `table_name` LIKE 'tab_';

SET @sql := SUBSTRING(@sql, 1, LENGTH(@sql) - 11);

SET @final_sql = 
'SELECT CONCAT(`tab_name`, ''-'',
    GROUP_CONCAT(CONCAT(`column_name`, '':'', sample ) ORDER BY ordinal_pos)) 
    AS result
 FROM ( <placeholder> ) AS sub
 GROUP BY tab_name';

SET @final_sql = REPLACE(@final_sql, '<placeholder>', @sql);

prepare s from @final_sql;
execute s;

deallocate prepare s;

SqlFiddleDemo2 SqlFiddleDemo3

输出:

╔════════════════════════════╗
║           result           ║
╠════════════════════════════╣
║ tab0-id:1,col:a            ║
║ tab1-id:10,col2:2          ║
║ tab2-id:20,col3:2016-01-26 ║
╚════════════════════════════╝