mysql的information_schema下的COLUMNS表详解

`information_schema.COLUMNS` 表是 MySQL 中的一个元数据表，用于存储数据库中所有表的列信息。这个表对于查询和管理数据库结构非常有用，可以帮助您了解每个表中的列定义、数据类型、约束等细节。

表结构

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

| 字段名 | 数据类型 | 描述 | |----------------------|--------------------|--------------------------------------------------------------| | TABLE_CATALOG | varchar(512) | 数据库的目录名，通常为 `def`。 | | TABLE_SCHEMA | varchar(64) | 表所属的数据库名称。 | | TABLE_NAME | varchar(64) | 表的名称。 | | COLUMN_NAME | varchar(64) | 列的名称。 | | ORDINAL_POSITION | bigint(10) unsigned| 列在表中的顺序位置，从1开始。 | | COLUMN_DEFAULT | longtext | 列的默认值。 | | IS_NULLABLE | varchar(3) | 是否允许NULL值，可以是 `YES` 或 `NO`。 | | DATA_TYPE | varchar(64) | 列的数据类型，如 `int`, `varchar`, `datetime` 等。 | | CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned| 对于字符类型，此列的最大长度。 | | CHARACTER_OCTET_LENGTH | bigint(21) unsigned| 对于字符类型，此列的最大字节长度。 | | NUMERIC_PRECISION | bigint(21) unsigned| 对于数值类型，精度（总位数）。 | | NUMERIC_SCALE | bigint(21) unsigned| 对于数值类型，小数位数。 | | DATETIME_PRECISION | tinyint(3) unsigned| 对于日期和时间类型，精度（小数点后的位数）。 | | CHARACTER_SET_NAME | varchar(32) | 字符集名称。 | | COLLATION_NAME | varchar(32) | 排序规则名称。 | | COLUMN_TYPE | longtext | 列的完整类型定义，包括类型和约束。 | | COLUMN_KEY | varchar(3) | 列的键类型，可以是 `PRI`（主键）、`MUL`（多值索引）等。 | | EXTRA | varchar(30) | 列的附加信息，如 `AUTO_INCREMENT`, `ON UPDATE CURRENT_TIMESTAMP` 等。 | | PRIVILEGES | varchar(80) | 列的权限，如 `SELECT`, `INSERT`, `UPDATE`, `REFERENCES` 等。 | | COLUMN_COMMENT | varchar(1024) | 列的注释。 |

示例

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

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

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name';

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

SELECT *
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

3. **获取具有自动递增属性的列**:

SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name' AND EXTRA LIKE '%auto_increment%';

4. **获取某个表中允许为空的列**:

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name' AND IS_NULLABLE = 'YES';

注意事项

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