主页 / dba / 问题 / 93919
Accepted
tachomi
Asked: 2015-02-27 08:59:06 +0800 CST

MySQL：用表列出数据库

  • 772

有没有办法在一个命令中列出所有带有表的数据库？

期待：

mysql> SHOW DATABASES, TABLES;

    DB1 table1
        table2
    DB2 table1
        table2
        table3
        table4
        table5
    DB3 table1
        table2
mysql

3 个回答

  1. Best Answer

    使用存储在中的信息INFORMATION_SCHEMA：

    SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
ORDER BY table_schema, table_name;

    table_schema是数据库名称。

    要显示没有任何表的数据库：

    SELECT s.schema_name, t.table_name 
FROM INFORMATION_SCHEMA.schemata AS s 
  LEFT JOIN INFORMATION_SCHEMA.tables AS t 
    ON t.table_schema = s.schema_name 
-- optional, to hide system databases and tables
-- WHERE s.schema_name NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY schema_name, table_name ;

    文档链接：INFORMATION_SCHEMA.tables, INFORMATION_SCHEMA.schemata

    • 10

  2. QUERY #1（单独的列）

    SET @db = '';
SELECT db,tb FROM
(SELECT @tb := IF(@db=table_schema,table_name,'') tb,
IF(@db=table_schema,'',table_schema) db,
(@db := table_schema) unused FROM information_schema.tables) A;

    QUERY #2（一列）

    SET @db = '';
SELECT CONCAT(db,'  ',tb) DBTB FROM
(SELECT @tb := IF(@db=table_schema,table_name,'') tb,
IF(@db=table_schema,'',table_schema) db,
(@db := table_schema) unused FROM information_schema.tables) A;

    QUERY #3（第一个表上的数据库，单独的列）

    SET @db = '';
SELECT db,tb FROM
(SELECT IF(@db=table_schema,'',table_schema) db,
table_schema tb,(@db := table_schema) unused FROM information_schema.tables) A;

    QUERY #4（第一个表上的数据库，一列）

    SET @db = '';
SELECT CONCAT(RPAD(db,20,' '),tb) dbtb FROM
(SELECT IF(@db=table_schema,'',table_schema) db,
table_schema tb,(@db := table_schema) unused FROM information_schema.tables) A;
    • 2

  3. 如果要排除 MySQL 内部数据库，

    SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql' );

    无需添加“ORDER BY”

    • -1

相关问题