MySQL 元数据
MySQL 元数据是关于数据库和其对象（如表、列、索引等）的信息。
元数据存储在系统表中，这些表位于 MySQL 数据库的 information_schema 数据库中，通过查询这些系统表，你可以获取关于数据库结构、对象和其他相关信息的详细信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| php |
| robomaster |
| sys |
+--------------------+
6 rows in set (0.01 sec)
MySQL以下三种信息：
- 查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数
- 数据库和数据表的信息： 包含了数据库及数据表的结构信息
- MySQL 服务器信息： 包含了数据库服务器的当前状态，版本号等
在 MySQL 的命令提示符中，我们可以很容易的获取以上服务器信息
information_schema 数据库
information_schema 是 MySQL 数据库中的一个系统数据库，它包含有关数据库服务器的元数据信息，这些信息以表的形式存储在 information_schema 数据库中
SCHEMATA 表
存储有关数据库的信息，如数据库名、字符集、排序规则等
SELECT * FROM information_schema.SCHEMATA;
结果：
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| def | mysql | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO
|
| def | information_schema | utf8mb3 | utf8mb3_general_ci | NULL | NO
|
| def | performance_schema | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO
|
| def | sys | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO
|
| def | php | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO
|
| def | robomaster | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO
|
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
6 rows in set (0.00 sec)
TABLES 表
包含有关数据库中所有表的信息，如表名、数据库名、引擎、行数等
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'robomaster';
结果：
+---------------+--------------+---------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+---------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| def | robomaster | test | BASE TABLE | InnoDB | 10 | Dynamic | 0 |
0 | 16384 | 0 | 0 | 0 | NULL | 2024-04-28 16:57:28 | 2024-04-28 17:20:00 | NULL | utf8mb4_0900_ai_ci | NULL | | |
| def | robomaster | test_username | BASE TABLE | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 0 | 0 | NULL | 2024-04-28 21:19:40 | 2024-04-28 21:22:42 | NULL | utf8mb4_0900_ai_ci | NULL | | |
| def | robomaster | username | BASE TABLE | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 0 | 0 | NULL | 2024-04-28 17:57:18 | NULL
| NULL | utf8mb4_0900_ai_ci | NULL | | |
+---------------+--------------+---------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
3 rows in set (0.01 sec)
COLUMNS 表
包含有关表中列的信息，如列名、数据类型、是否允许 NULL 等
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'robomaster' AND TABLE_NAME = 'username';
结果：
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+--------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION | SRS_ID |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+--------+
| def | robomaster | username | id | 1 | NULL | YES | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int | | | select,insert,update,references | | | NULL |
| def | robomaster | username | Username | 2 | NULL | NO | varchar | 255 | 1020 | NULL | NULL | NULL | utf8mb4 | utf8mb4_0900_ai_ci | varchar(255) | PRI | | select,insert,update,references | | | NULL |
| def | robomaster | username | Password | 3 | NULL | YES | varchar | 255 | 1020 | NULL | NULL | NULL | utf8mb4 | utf8mb4_0900_ai_ci | varchar(255) | | | select,insert,update,references | | | NULL |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+--------+
3 rows in set (0.00 sec)
STATISTICS 表
提供有关表索引的统计信息，如索引名、列名、唯一性等
SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'robomaster' AND TABLE_NAME = 'username';
结果：
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | IS_VISIBLE | EXPRESSION |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
| def | robomaster | username | 0 | robomaster | PRIMARY | 1 | Username | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
1 row in set (0.00 sec)
KEY_COLUMN_USAGE 表
包含有关表中外键的信息，如外键名、列名、关联表等
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'robomaster' AND TABLE_NAME = 'username';
结果：
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | IS_VISIBLE | EXPRESSION |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
| def | robomaster | username | 0 | robomaster | PRIMARY | 1 | Username | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'robomaster' AND TABLE_NAME = 'username';
+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| def | robomaster | PRIMARY | def | robomaster | username | Username | 1 | NULL | NULL | NULL | NULL
|
+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
REFERENTIAL_CONSTRAINTS 表
存储有关外键约束的信息，如约束名、关联表等
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'robomaster' AND TABLE_NAME = 'username';
结果：
+--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME |
+--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
| def | robomaster | fk_id | def | robomaster | NULL | NONE | NO ACTION | NO ACTION | username | test |
+--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
1 row in set (0.00 sec)