MYSQL内置数据库之information_schema
information_schema是MYSQL中内置的一个数据库，可用show databases;即可看到。
初步了解
通过指令查到的结果如下，足足有61张表。后面将介绍几张比较基本的表。
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_FT_CONFIG |
| INNODB_SYS_VIRTUAL |
| INNODB_CMP |
| INNODB_FT_BEING_DELETED |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCK_WAITS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_CMPMEM |
| INNODB_BUFFER_POOL_STATS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
+---------------------------------------+
61 rows in set (0.00 sec)
实验环境
- MYSQL 5.7.30
- Ubuntu 16.04
- 自定义数据库
user - 自定义数据表
agent、seller
TABLE_CONSTRAINTS 表约束
表约束就是主键、外键等这些施加在某一列上面的约束。
现在给agent表添加一个外键sellerid指向seller表的sellerid列：
alter table agent add constraint sellerid foreign key(sellerid) references seller(sellerid);
下面看看表agent和seller的约束，可以看出仅仅对agent表有约束，而seller表是没任何约束的。
CONSTRAINT_SCHEMA 数据库名
TABLE_SCHEMA 数据库名
TABLE_NAME 表名
CONSTRAINT_TYPE 约束类型
CONSTRAINT_NAME 约束名
mysql> SELECT * FROM TABLE_CONSTRAINTS WHERE TABLE_NAME = 'agent';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def | user | PRIMARY | user | agent | PRIMARY KEY |
| def | user | sellerid | user | agent | FOREIGN KEY |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from TABLE_CONSTRAINTS where table_name = 'seller';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def | user | PRIMARY | user | seller | PRIMARY KEY |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
1 row in set (0.00 sec)
