> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL INFORMATION_SCHEMA

> 如何使用 MySQL 的 INFORMATION_SCHEMA 查询表元数据、以编程方式查找列定义、审计数据库结构、调试 schema 问题，以及用可过滤的 SQL 查询替代 SHOW 命令。

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

`INFORMATION_SCHEMA` 是一个虚拟数据库，它通过标准 SQL 查询暴露 MySQL 服务器的元数据。`SHOW TABLES`、`SHOW COLUMNS` 和 `DESCRIBE` 能告诉您的一切，`INFORMATION_SCHEMA` 也能——并且拥有 `WHERE`、`JOIN` 和聚合的全部能力。

## 表和列

列出数据库中的所有表：

```sql theme={null}
SELECT table_name, table_type, engine, table_rows, create_time
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY table_name;
```

对于 InnoDB 表，`table_rows` 是一个估计值（来自索引统计信息），而不是精确计数。需要精确值时请使用 `SELECT COUNT(*)`。

列出某个表的所有列：

```sql theme={null}
SELECT column_name, column_type, is_nullable, column_default, extra
FROM information_schema.columns
WHERE table_schema = 'myapp'
  AND table_name = 'users'
ORDER BY ordinal_position;
```

在所有表中查找特定类型的所有列：

```sql theme={null}
SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'myapp'
  AND data_type = 'json';
```

## 索引

列出某个表上的所有索引：

```sql theme={null}
SELECT index_name, non_unique, seq_in_index, column_name, index_type
FROM information_schema.statistics
WHERE table_schema = 'myapp'
  AND table_name = 'orders'
ORDER BY index_name, seq_in_index;
```

查找没有主键的表：

```sql theme={null}
SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_schema = 'myapp'
  AND t.table_type = 'BASE TABLE'
  AND NOT EXISTS (
    SELECT 1
    FROM information_schema.table_constraints tc
    WHERE tc.table_schema = t.table_schema
      AND tc.table_name = t.table_name
      AND tc.constraint_type = 'PRIMARY KEY'
  );
```

## 约束

列出所有约束（PRIMARY KEY、UNIQUE、FOREIGN KEY、CHECK）：

```sql theme={null}
SELECT constraint_name, constraint_type, table_name
FROM information_schema.table_constraints
WHERE table_schema = 'myapp'
ORDER BY table_name, constraint_type;
```

列出所有外键及其引用的对象：

```sql theme={null}
SELECT
    kcu.table_name,
    kcu.column_name,
    kcu.constraint_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.key_column_usage kcu
JOIN information_schema.referential_constraints rc
    ON rc.constraint_schema = kcu.constraint_schema
    AND rc.constraint_name = kcu.constraint_name
WHERE kcu.table_schema = 'myapp'
  AND kcu.referenced_table_name IS NOT NULL
ORDER BY kcu.table_name;
```

列出 CHECK 约束：

```sql theme={null}
SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'myapp'
ORDER BY constraint_name;
```

## 存储例程

列出所有存储过程和函数：

```sql theme={null}
SELECT routine_name, routine_type, created, last_altered
FROM information_schema.routines
WHERE routine_schema = 'myapp'
ORDER BY routine_type, routine_name;
```

查看某个例程的定义：

```sql theme={null}
SELECT routine_definition
FROM information_schema.routines
WHERE routine_schema = 'myapp'
  AND routine_name = 'get_active_users'
  AND routine_type = 'PROCEDURE'\G
```

## 视图和触发器

列出所有视图：

```sql theme={null}
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'myapp'\G
```

列出所有触发器：

```sql theme={null}
SELECT trigger_name, event_manipulation, event_object_table,
       action_timing, action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'myapp'
ORDER BY event_object_table, action_timing;
```

## 权限

查看当前用户的有效权限：

```sql theme={null}
SELECT * FROM information_schema.user_privileges;
SELECT * FROM information_schema.schema_privileges;
SELECT * FROM information_schema.table_privileges;
```

## 关键表参考

| 表 | 内容 |
| :- | :- |
| `TABLES` | 所有表和视图，包括引擎、行数估计、大小 |
| `COLUMNS` | 所有列，包括类型、默认值、可空性 |
| `STATISTICS` | 索引定义和基数 |
| `TABLE_CONSTRAINTS` | 所有约束（PK、UK、FK、CHECK） |
| `KEY_COLUMN_USAGE` | 约束中的列，包括 FK 目标详情 |
| `REFERENTIAL_CONSTRAINTS` | FK 的级联/限制规则 |
| `CHECK_CONSTRAINTS` | CHECK 约束表达式 |
| `ROUTINES` | 存储过程和函数 |
| `TRIGGERS` | 触发器定义 |
| `VIEWS` | 视图定义 |
| `CHARACTER_SETS` | 可用的字符集 |
| `COLLATIONS` | 可用的排序规则 |
| `SCHEMATA` | 当前用户可见的所有数据库 |

## INFORMATION\_SCHEMA 与 SHOW 命令

`SHOW` 命令是简写形式，通常有对应的 `INFORMATION_SCHEMA` 等价查询：

| SHOW 命令 | INFORMATION\_SCHEMA 等价查询 |
| :- | :- |
| `SHOW TABLES` | `SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()` |
| `SHOW COLUMNS FROM t` | `SELECT * FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 't'` |
| `SHOW INDEX FROM t` | `SELECT * FROM information_schema.statistics WHERE table_name = 't'` |
| `SHOW CREATE TABLE t` | 没有直接的等价查询——`SHOW CREATE TABLE` 仍然是最简单的方式 |
| `SHOW PROCEDURE STATUS` | `SELECT * FROM information_schema.routines WHERE routine_type = 'PROCEDURE'` |

当您需要过滤、联接或聚合时，`INFORMATION_SCHEMA` 查询更好。`SHOW` 命令适合交互式查看。

## 常见问题

#### INFORMATION\_SCHEMA 查询慢吗？

对于 InnoDB 表，某些查询（尤其是针对 `TABLES` 和 `STATISTICS` 的查询）会触发索引统计信息更新，在大型 schema 上可能很慢。如果您频繁查询元数据，请使用 `mysql.innodb_table_stats` 和 `mysql.innodb_index_stats` 更快地访问缓存的统计信息。

#### 为什么 table\_rows 的值看起来不对？

`information_schema.tables.table_rows` 是来自 InnoDB 索引统计信息的估计值，不是实时计数。对于大表，它可能偏差 40-50%。运行 `ANALYZE TABLE tablename` 刷新统计信息，或者使用 `SELECT COUNT(*)` 获得精确计数。

## 故障排除

| 问题 | 解决方案 |
| :- | :- |
| 对已知的表查询不返回任何行 | 当前用户可能缺少该表的 `SELECT` 权限——`INFORMATION_SCHEMA` 只返回用户可见的表 |
| `table_rows` 与 COUNT(\*) 差别很大 | InnoDB 行数是估计值——运行 `ANALYZE TABLE` 刷新索引统计信息 |
| 无法按名称找到约束 | 约束名称的作用域是数据库——在 WHERE 子句中检查 `constraint_schema` |
| 针对 `INFORMATION_SCHEMA.TABLES` 的查询很慢 | InnoDB 在访问时刷新统计信息；使用 `information_schema_stats_expiry`（默认 86400 秒）让结果缓存更久 |

## 另请参阅

* [MySQL 用户管理](https://villagesql.com/docs/zh/guides/user-management.md)——用 INFORMATION\_SCHEMA.USER\_PRIVILEGES 审计授权
* [MySQL 中的 Schema 迁移](https://villagesql.com/docs/zh/guides/schema-migrations.md)——在迁移前查询 INFORMATION\_SCHEMA 检查 schema
* [MySQL 索引的工作原理](https://villagesql.com/docs/zh/guides/mysql-indexes.md)——根据您找到的索引元数据采取行动


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.