内嵌数据库mysql_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)