infomation_schema介绍

INFORMATION_SCHEMA是 MySQL 中的一个虚拟数据库，它提供了一系列只读视图，用于访问数据库元数据。通过INFORMATION_SCHEMA，用户可以查询关于数据库、表、列、索引、用户权限等的各种信息。这对于数据库管理、监控和调试非常有用。以下是对INFORMATION_SCHEMA的一些关键点的介绍：

主要特性

1. 元数据访问：

   • INFORMATION_SCHEMA提供了对数据库服务器上所有数据库对象的元数据访问。它是一个存储数据库结构信息的地方。

2. 只读视图：

   • 表中的数据是只读的，用户不能通过INFORMATION_SCHEMA修改数据库结构或数据。

3. 标准化：

   • INFORMATION_SCHEMA是 SQL 标准的一部分，许多其他数据库系统也提供类似的功能。

常见视图

以下是一些常见的INFORMATION_SCHEMA视图，以及它们提供的信息：

• SCHEMATA：包含关于所有数据库的信息。
• TABLES：提供关于每个数据库中表的信息，包括表名、表类型、表引擎等。
• COLUMNS：提供关于表中每列的信息，包括列名、数据类型、默认值等。
• STATISTICS：包含关于表索引的信息。
• USER_PRIVILEGES：显示用户权限的信息。
• TABLE_CONSTRAINTS：提供关于表约束的信息，如主键和外键。
• KEY_COLUMN_USAGE：描述哪些列被用作键。
• TRIGGERS：包含关于数据库中触发器的信息。
• ROUTINES：包括存储过程和函数的信息。

使用示例

以下是一些查询示例，展示如何使用INFORMATION_SCHEMA来获取数据库元数据：

• 查询所有数据库：

  SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
  

• 查询某个数据库的所有表：

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

• 查询某张表的所有列：

  SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
  

• 查询某个表的索引信息：

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

• 查询字段的size（转成GB，MB）：

SELECT TABLE_NAME,TABLE_ROWS,AVG_ROW_LENGTH,
              ROUND(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS data_length_gb,
              ROUND(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS index_length_gb,
              ROUND(DATA_FREE / 1024 / 1024, 2) AS data_free_mb,
              DATA_FREE,AUTO_INCREMENT
FROM information_schema.tables
WHERE table_schema = '数据库名'

应用场景

• 数据库管理：管理员可以使用INFORMATION_SCHEMA来检查数据库结构、表的详细信息和用户权限等。
• 性能调优：开发者可以通过分析索引和表结构信息来优化查询性能。
• 自动化工具：许多自动化工具和脚本使用INFORMATION_SCHEMA来生成报告或进行数据迁移。

通过INFORMATION_SCHEMA，用户可以方便地访问数据库的元数据，帮助进行数据库的管理、监控和优化。