MySQL编程进阶之：如何利用`INFORMATION_SCHEMA`来查询和管理数据库元数据。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

大家好，欢迎来到今天的MySQL编程进阶小课堂！今天我们要聊的是一个MySQL自带的“八卦中心”——INFORMATION_SCHEMA。别怕，不是让你去打听明星隐私，而是教你如何利用它来窥探（咳咳，是查询和管理）数据库的元数据。简单来说，INFORMATION_SCHEMA就是一个数据库，它存储着关于你的MySQL服务器、数据库、表、列、索引等等的信息。想象一下，它就像一个MySQL的内部百科全书，你想知道什么，都可以来这里查一查。

为什么要用INFORMATION_SCHEMA？

直接修改数据库的系统表来获取元数据的方式是非常不推荐的，因为这样做风险很高，可能会破坏数据库的完整性。而INFORMATION_SCHEMA提供了一种安全、标准的方式来访问这些信息。它可以让你：

动态地发现数据库结构： 比如，你想知道某个数据库里有哪些表，或者某个表有哪些列，不用再手动去一个个看，直接查INFORMATION_SCHEMA就行了。
编写更通用的代码： 你的代码可以根据数据库的结构动态地调整行为，而不是写死某些表名或列名。
自动化数据库管理任务： 比如，你可以写一个脚本来自动备份所有超过一定大小的表。
进行数据库审计和性能分析： 你可以监控数据库的使用情况，找出潜在的问题。

INFORMATION_SCHEMA里都有什么？

INFORMATION_SCHEMA 包含很多表(也就是视图)，每个表都存储着不同类型的元数据。一些常用的表包括：

表名	描述
`SCHEMATA`	存储数据库的信息
`TABLES`	存储表的信息
`COLUMNS`	存储列的信息
`STATISTICS`	存储索引的信息
`TABLE_CONSTRAINTS`	存储表约束的信息，例如主键、外键、唯一约束等
`REFERENTIAL_CONSTRAINTS`	存储外键约束的详细信息
`ROUTINES`	存储存储过程和函数的信息
`TRIGGERS`	存储触发器的信息
`EVENTS`	存储事件调度器的信息
`USER_PRIVILEGES`	存储用户权限信息

这只是冰山一角，更多的表可以参考MySQL官方文档。

如何使用INFORMATION_SCHEMA？

使用INFORMATION_SCHEMA非常简单，就像查询普通的数据库表一样，使用SELECT语句即可。但是要注意，你需要加上INFORMATION_SCHEMA.前缀来指定要查询的表。

下面我们通过一些例子来演示如何使用INFORMATION_SCHEMA。

1. 查询所有数据库的名称：

SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA;

这个查询会返回MySQL服务器上所有数据库的名称，包括系统数据库和用户自定义数据库。

2. 查询某个数据库中的所有表：

假设我们要查询名为mydatabase的数据库中的所有表，可以这样写：

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydatabase';

注意，TABLE_SCHEMA列存储的是数据库的名称，TABLE_NAME列存储的是表的名称。

3. 查询某个表的所有列：

假设我们要查询mydatabase数据库中的mytable表的所有列，可以这样写：

SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydatabase'
  AND TABLE_NAME = 'mytable';

这个查询会返回mytable表的所有列的名称、数据类型、是否允许为空、默认值、是否为主键或索引等等信息。

COLUMN_NAME: 列名
DATA_TYPE: 数据类型 (例如 INT, VARCHAR, DATE)
COLUMN_TYPE: 更详细的数据类型，包括长度和属性 (例如 VARCHAR(255), INT UNSIGNED)
IS_NULLABLE: 是否允许 NULL 值 (YES 或 NO)
COLUMN_DEFAULT: 列的默认值
COLUMN_KEY: 指示列是否是主键 (PRI), 唯一键 (UNI), 或索引 (MUL)
EXTRA: 包含额外的属性，例如 auto_increment

4. 查询某个表的主键：

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydatabase'
  AND TABLE_NAME = 'mytable'
  AND COLUMN_KEY = 'PRI';

这个查询会返回mytable表的主键列的名称。

5. 查询某个表的外键：

这个稍微复杂一些，需要同时查询TABLE_CONSTRAINTS和REFERENTIAL_CONSTRAINTS表：

SELECT
    tc.CONSTRAINT_NAME,
    tc.TABLE_NAME,
    kcu.COLUMN_NAME,
    rc.REFERENCED_TABLE_NAME,
    rc.REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
      ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
      ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE
    tc.TABLE_SCHEMA = 'mydatabase'
    AND tc.TABLE_NAME = 'mytable'
    AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

这个查询会返回mytable表的所有外键的名称、外键列、引用的表和引用的列。

CONSTRAINT_NAME: 外键约束的名称
TABLE_NAME: 包含外键的表名
COLUMN_NAME: 外键列的名称
REFERENCED_TABLE_NAME: 外键引用的表名
REFERENCED_COLUMN_NAME: 外键引用的列名

6. 找出所有没有索引的表:

这个查询需要稍微动点脑筋，使用NOT EXISTS子查询：

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND TABLE_TYPE = 'BASE TABLE'
  AND NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = TABLES.TABLE_SCHEMA
      AND TABLE_NAME = TABLES.TABLE_NAME
  );

这个查询会返回所有没有索引的表，排除了系统数据库。记住，没有索引的表可能会影响查询性能。

7. 找出重复的索引:

MySQL允许在同一组列上创建多个索引，但这通常是不必要的，并且会浪费空间。我们可以使用以下查询来找出重复的索引：

SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE INDEX_NAME <> 'PRIMARY'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX
HAVING COUNT(*) > 1;

这个查询会返回所有在同一张表上多次定义的索引。

8. 查找大型表（按大小排序）:

虽然INFORMATION_SCHEMA本身不直接存储表的大小，但我们可以使用它来构建查询，结合其他函数来估计表的大小。这个查询依赖于 TABLES 表的 DATA_LENGTH 和 INDEX_LENGTH 列，它们分别表示数据和索引占用的空间。

SELECT TABLE_SCHEMA, TABLE_NAME,
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS "Size in MB"
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

这个查询会返回所有用户表的名称和大小（以MB为单位），并按大小降序排列。

9. 检查表是否使用了特定的存储引擎：

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'InnoDB'
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

这个查询会返回所有使用InnoDB存储引擎的表。你可以根据需要更改ENGINE的值来查找使用其他存储引擎的表。

10. 找出没有注释的列:

给数据库表和列添加注释是一个良好的习惯，可以提高数据库的可维护性。可以使用以下查询来找出没有注释的列：

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_COMMENT = ''
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

这个查询会返回所有没有注释的列。

高级用法：动态生成SQL语句

INFORMATION_SCHEMA 的一个强大的用途是动态生成SQL语句。例如，你可以根据表的结构自动生成创建表的语句，或者生成批量插入数据的语句。

下面是一个例子，演示如何根据INFORMATION_SCHEMA中的信息动态生成创建表的语句：

SELECT
    CONCAT(
        'CREATE TABLE `',
        TABLE_NAME,
        '` (',
        GROUP_CONCAT(
            CONCAT(
                '`',
                COLUMN_NAME,
                '` ',
                COLUMN_TYPE,
                ' ',
                CASE
                    WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL '
                    ELSE ''
                END,
                CASE
                    WHEN COLUMN_DEFAULT IS NOT NULL THEN CONCAT('DEFAULT '', COLUMN_DEFAULT, '' ')
                    ELSE ''
                END,
                CASE
                    WHEN EXTRA LIKE '%auto_increment%' THEN 'AUTO_INCREMENT '
                    ELSE ''
                END,
                CASE
                    WHEN COLUMN_COMMENT <> '' THEN CONCAT('COMMENT '', COLUMN_COMMENT, '' ')
                    ELSE ''
                END
            )
            ORDER BY ORDINAL_POSITION
            SEPARATOR ', '
        ),
        ', PRIMARY KEY (`',
        (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = t.TABLE_NAME AND COLUMN_KEY = 'PRI'),
        '`)',
        ') ENGINE=',
        ENGINE,
        ' DEFAULT CHARSET=',
        TABLE_COLLATION,
        ' COMMENT='',
        TABLE_COMMENT,
        '';'
    ) AS create_statement
FROM
    INFORMATION_SCHEMA.TABLES t
    JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE
    t.TABLE_SCHEMA = 'your_database'
GROUP BY
    TABLE_NAME;

将your_database替换成你要操作的数据库名称，然后运行这个查询，它会返回一个CREATE TABLE语句，你可以直接执行它来创建一张结构相同的表。

注意事项：

INFORMATION_SCHEMA 中的数据是只读的，你不能直接修改它。
查询 INFORMATION_SCHEMA 可能会影响数据库的性能，特别是对于大型数据库。尽量避免在生产环境中频繁地查询它。
不同的MySQL版本，INFORMATION_SCHEMA 中的表结构可能会有所不同。在编写代码时，最好参考对应版本的官方文档。
某些用户可能没有访问 INFORMATION_SCHEMA 的权限。确保你的用户有足够的权限来查询它。

总结

INFORMATION_SCHEMA 是一个非常强大的工具，可以帮助你更好地了解和管理你的MySQL数据库。掌握它，你就可以编写更灵活、更通用的代码，自动化数据库管理任务，并进行数据库审计和性能分析。希望今天的讲解对你有所帮助！记住，熟练使用 INFORMATION_SCHEMA 是成为MySQL专家的必经之路。感谢大家的参与，下次再见！

发表回复 取消回复

发表回复取消回复