mysql中information_schema库下的KEY_COLUMN_USAGE表详解

`information_schema.KEY_COLUMN_USAGE` 表是 MySQL 中的一个元数据表，用于存储关于表的键列信息，包括主键、唯一键以及外键的详细信息。这个表包含了关于哪些列参与了哪些类型的键约束的信息。下面是这个表的详细解释：

表结构

下面是 `information_schema.KEY_COLUMN_USAGE` 表的主要字段及其含义：

| 字段名 | 数据类型 | 描述 |
|-----------------------------|--------------------|--------------------------------------------------------------|
| CONSTRAINT_CATALOG | varchar(64) | 约束所在数据库的目录名，通常为 `def`。 |
| CONSTRAINT_SCHEMA | varchar(64) | 约束所在数据库的名称。 |
| CONSTRAINT_NAME | varchar(64) | 约束的名称。 |
| TABLE_CATALOG | varchar(64) | 表所在的数据库目录名，通常为 `def`。 |
| TABLE_SCHEMA | varchar(64) | 表所在的数据库名称。 |
| TABLE_NAME | varchar(64) | 表的名称。 |
| COLUMN_NAME | varchar(64) | 列的名称。 |
| ORDINAL_POSITION | int UNSIGNED | 列在键中的位置，从1开始计数。 |
| POSITION_IN_UNIQUE_CONSTRAINT| int UNSIGNED | 列在唯一约束中的位置，对于非唯一约束为 `NULL`。 |
| REFERENCED_TABLE_SCHEMA | varchar(64) | 引用表所在的数据库名称，对于非外键约束为 `NULL`。 |
| REFERENCED_TABLE_NAME | varchar(64) | 引用表的名称，对于非外键约束为 `NULL`。 |
| REFERENCED_COLUMN_NAME | varchar(64) | 引用表中的列名称，对于非外键约束为 `NULL`。 |

字段说明

- **`CONSTRAINT_CATALOG`**: 约束所在数据库的目录名，通常为 `def`。
- **`CONSTRAINT_SCHEMA`**: 约束所在数据库的名称。
- **`CONSTRAINT_NAME`**: 约束的名称。
- **`TABLE_CATALOG`**: 表所在的数据库目录名，通常为 `def`。
- **`TABLE_SCHEMA`**: 表所在的数据库名称。
- **`TABLE_NAME`**: 表的名称。
- **`COLUMN_NAME`**: 列的名称。
- **`ORDINAL_POSITION`**: 列在键中的位置，从1开始计数。
- **`POSITION_IN_UNIQUE_CONSTRAINT`**: 列在唯一约束中的位置，对于非唯一约束为 `NULL`。
- **`REFERENCED_TABLE_SCHEMA`**: 引用表所在的数据库名称，对于非外键约束为 `NULL`。
- **`REFERENCED_TABLE_NAME`**: 引用表的名称，对于非外键约束为 `NULL`。
- **`REFERENCED_COLUMN_NAME`**: 引用表中的列名称，对于非外键约束为 `NULL`。

示例

以下是一些使用 `information_schema.KEY_COLUMN_USAGE` 表的示例查询：

1. **获取特定数据库中所有表的键列信息**:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name';

2. **获取特定表的所有键列信息**:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

 

3. **获取特定表的外键信息**:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name' AND REFERENCED_TABLE_NAME IS NOT NULL;

 

注意事项

- `information_schema` 是一个只读模式的数据库，不能对其进行修改。
- 查询 `information_schema` 通常不需要锁定表，因此性能影响较小。
- 在查询 `information_schema` 时，确保替换 `'your_database_name'` 和 `'your_table_name'` 为您具体的数据库和表名。

 总结

`information_schema.KEY_COLUMN_USAGE` 表提供了关于表的键列信息，包括主键、唯一键以及外键的详细信息。这些信息对于理解和管理数据库中的约束关系非常有用。