背景
最近在开发数据库表的可视化编辑任务，很好奇navicat中点击设计表时，拉取的元数据是从哪里来的，如何跟展示的字段是对应起来的。是先获取了表的DDL，然后将字段抠出来的呢？还是从元数据表里获取的到全部信息？
神奇的information_schema表
其实在MySQL数据库中，information_schema库提供了对数据库元数据信息的访问，如数据库、表、列、视图、触发器、索引等内容的元数据信息，都可以通过information_schema库中对应的表查询访问。
下面我们从常见的可视化编辑选项说开去，看看它们分别存在了information_schema的哪个表里面：
数据库
获取方式
数据库元数据存于information_schema.schemata表中，一行则表示一个数据库的元数据信息，查询sql：select * from information_schema.schemata where schema_name = "xxx";
字段列表
| 字段 | 类型 | 中文含义 | 备注 |
|---|---|---|---|
| CATALOG_NAME | varchar | 类型名称 | 如def |
| SCHEMA_NAME | varchar | 库名 | |
| DEFAULT_CHARACTER_SET_NAME | varchar | 默认字符集名称 | |
| DEFAULT_COLLATION_NAME | varchar | 默认排序规则名称 | |
| SQL_PATH | varchar | SQL路径 |
数据表
获取方式
数据表元数据存于information_schema.tables表中，一行则表示一张数据表的元数据信息，查询Sql：select * from information_schema.tables where table_schema = "xxx" and table_name = "xxx";
字段列表
| 字段 | 类型 | 中文含义 | 备注 |
|---|---|---|---|
| TABLE_CATALOG | varchar | 表所在目录 | 如def |
| TABLE_SCHEMA | varchar | 所属数据库 | |
| TABLE_NAME | varchar | 表名 | |
| TABLE_TYPE | varchar | 表类型 | |
| ENGINE | varchar | 存储引擎 | |
| VERSION | bigint | 版本 | |
| ROW_FORMAT | varchar | 行格式 | 如Dynamic |
| TABLE_ROWS | bigint | 表的行数 | |
| AVG_ROW_LENGTH | bigint | 平均一行的长度 | |
| DATA_LENGTH | bigint | 数据长度 | |
| MAX_DATA_LENGTH | bigint | 最大行的数据长度 | |
| INDEX_LENGTH | bigint | 索引长度 | |
| DATA_FREE | bigint | 空闲空间 | |
| AUTO_INCREMENT | bigint | 自增值 | |
| CREATE_TIME | datetime | 创建时间 | |
| UPDATE_TIME | datetime | 更新时间 | |
| CHECK_TIME | datetime | 检查时间 | |
| TABLE_COLLATION | varchar | 表的排序规则 | |
| CHECKSUM | bigint | 检查次数 | |
| CREATE_OPTIONS | varchar | 创建选项 | |
| TABLE_COMMENT | varchar | 表备注 |
数据表列
获取方式
数据表列元数据存于information_schema.columns表中，一行则表示一列的元数据信息，查询Sql：select * from information_schema.tables where table_schema = "xxx" and table_name = "xxx" and column_name = "xxx";
字段列表
| 字段 | 类型 | 中文含义 | 备注 |
|---|---|---|---|
| TABLE_CATALOG | varchar | 表所在目录 | 如def |
| TABLE_SCHEMA | varchar | 库名 | |
| TABLE_NAME | varchar | 表名 | |
| COLUMN_NAME | varchar | 列名 | |
| ORDINAL_POSITION | bigint | 该列的位置 | |
| COLUMN_DEFAULT | longtext | 列的默认值 | |
| IS_NULLABLE | varchar | 是否为NULL | |
| DATA_TYPE | varchar | 数据类型 | |
| CHARACTER_MAXIMUM_LENGTH | bigint | 字符类型数据的长度 | （单位是字符） |
| CHARACTER_OCTET_LENGTH | bigint | 字符数据的存储长度 | （单位是字节） |
| NUMERIC_PRECISION | bigint | 数字类型的长度 | |
| NUMERIC_SCALE | bigint | 小数点位数 | |
| DATETIME_PRECISION | bigint | 日期精度 | |
| CHARACTER_SET_NAME | varchar | 字符编码名称 | 如latin1 |
| COLLATION_NAME | varchar | 排序规则 | 如latin1_swedish_ci |
| COLUMN_TYPE | longtext | 列类型 | 如varchar(32) |
| COLUMN_KEY | varchar | 列的建类型 | PRI 主键 MUL非主键 |
| EXTRA | varchar | 其余信息 | 如自增 |
| PRIVILEGES | varchar | 操作权限 | select,insert,update,references |
| COLUMN_COMMENT | varchar | 列的备注 | |
| GENERATION_EXPRESSION | longtext | 生成表达式 |
索引
获取方式
索引元数据存于information_schema.statistics表中，一行则表示一个索引的元数据信息，查询Sql：select * from information_schema.statistics where table_schema = "xxx" and table_name = "xxx" and index_name="xxx";
字段列表
| 字段 | 类型 | 中文含义 | 备注 |
|---|---|---|---|
| TABLE_CATALOG | varchar | 所属表目录 | def |
| TABLE_SCHEMA | varchar | 所属表的数据库名称 | |
| TABLE_NAME | varchar | 所属表名称 | |
| NON_UNIQUE | bigint | 索引是否可重复 | 0 不可重复 1 可重复 |
| INDEX_SCHEMA | varchar | 所属数据库名称 | |
| INDEX_NAME | varchar | 索引名称 | |
| SEQ_IN_INDEX | bigint | 索引的序号 | |
| COLUMN_NAME | varchar | 列名称 | |
| COLLATION | varchar | 列在所以中的排序方式 | A 升序 D 降序 NULL 未排序 |
| CARDINALITY | bigint | 索引中唯一值的数量 | 该值不一定准确 |
| SUB_PART | bigint | 索引前缀字符数 | 如果是前缀索引，则是索引字符的数量；如果是整列索引，则为NULL |
| PACKED | varchar | 打包方式 | |
| NULLABLE | varchar | 该列值是否可为NULL | YES表示可为NULL，否则不可为NULL |
| INDEX_TYPE | varchar | 索引类型 | BTREE，FULLTEXT，HASH，RTREE |
| COMMENT | varchar | 注释 | 未在其自己的列中描述的索引信息，例如disabled是否禁用了索引 |
| INDEX_COMMENT | varchar | 索引注释 | NULL |
外键信息
获取方式
MySQL中的外键信息可以在information_schema数据库中的"REFERENTIAL_CONSTRAINTS"表中找到。
您可以使用以下命令来查询该表的信息：
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'XXX' AND TABLE_NAME = 'XXX';
字段列表
CONSTRAINT_CATALOG ：约束所属目录的名称。该值始终为def。 CONSTRAINT_SCHEMA ：约束所属schema（database）的名称 CONSTRAINT_NAME ：约束名称 UNIQUE_CONSTRAINT_CATALOG：包含约束引用的唯一约束的目录的名称。该值始终为def。 UNIQUE_CONSTRAINT_SCHEMA ：包含约束引用的唯一约束的schema（数据库）的名称。 UNIQUE_CONSTRAINT_NAME ：约束引用的唯一约束的名称。 MATCH_OPTION ：约束MATCH属性的值。此时唯一有效的值是NONE。 UPDATE_RULE ：约束ON UPDATE属性的值。可能的值是CASCADE，SET NULL，SET DEFAULT，RESTRICT，NO ACTION。 DELETE_RULE ：约束ON DELETE属性的值。可能的值是CASCADE，SET NULL，SET DEFAULT，RESTRICT，NO ACTION。 TABLE_NAME ：表的名称。此值与TABLE_CONSTRAINTS表中的值相同。 REFERENCED_TABLE_NAME ：约束引用的表的名称。
约束信息
获取方式
约束信息可以在information_schema数据库中的TABLE_CONSTRAINTS表中找到。
select * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'xxx' and TABLE_NAME = 'xxx';
字段列表
CONSTRAINT_CATALOG：约束所属目录的名称。该值始终为def。
CONSTRAINT_SCHEMA：指定约束所在的数据库名称。
CONSTRAINT_NAME：约束名称
TABLE_SCHEMA：指定表所在的数据库名称。
TABLE_NAME：表名
CONSTRAINT_TYPE：指定约束的类型，包括PRIMARY KEY（主键）、FOREIGN KEY（外键）、UNIQUE（唯一约束）、CHECK（检查约束）等。
ENFORCED：指定约束是否强制执行，通常为“YES”。
获取方式2
select * FROM information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA = 'xxx' and TABLE_NAME = 'xxx';
KEY_COLUMN_USAGE 该表中的信息包含主键、唯一索引、外键等约束信息，例如：所在的库表列名、引用的库表列名等。该表中的信息与 TABLE_CONSTRAINTS 表中记录的信息有些类似，但 TABLE_CONSTRAINTS 表中没有记录约束引用的库表列信息，而KEY_COLUMN_USAGE 表中却记录了TABLE_CONSTRAINTS 表中所没有的约束类型。该表中，主要列出了参考库，参考表，参考列，用于查询外键约束的属性。
字段列表2
CONSTRAINT_CATALOG：约束所属目录的名称。该值始终为def。
CONSTRAINT_SCHEMA：指定约束所在的数据库名称。
CONSTRAINT_NAME：指定约束的名称。
TABLE_CATALOG：表约束所属目录的名称。该值始终为def。
TABLE_SCHEMA：库名
TABLE_NAME：表名
COLUMN_NAME: 指定列的名称。
ORDINAL_POSITION: 指定列在约束中的位置。
POSITION_IN_UNIQUE_CONSTRAINT: 指定列在唯一约束中的位置，如果该约束不是唯一约束，则为NULL。
REFERENCED_TABLE_SCHEMA: 指定所参考的表所在的数据库名称。
REFERENCED_TABLE_NAME: 指定所参考的表的名称。 REFERENCED_COLUMN_NAME: 指定所参考的列的名称。
