KEY_COLUMN_USAGE表描述哪些键列具有约束。
KEY_COLUMN_USAGE表有以下列：
CONSTRAINT_CATALOG ：约束所属目录的名称。 该值始终为def。CONSTRAINT_SCHEMA ：约束所属schema（database）名称CONSTRAINT_NAME ：约束名称TABLE_CATALOG ：表所属目录的名称。 该值始终为def。TABLE_SCHEMA ：表所属schema（database）名称TABLE_NAME ：具有约束的表的名称COLUMN_NAME ：具有约束的列的名称。 如果约束是外键，则这是外键的列，而不是外键引用的列。ORDINAL_POSITION ：列在约束内的位置，而不是列在表中的位置。列位置从1开始编号。POSITION_IN_UNIQUE_CONSTRAINT：NULL对于唯一和主键约束。对于外键约束，此列是正在引用的表的键中的序号位置。REFERENCED_TABLE_SCHEMA ：约束引用的schema（数据库）的名称。REFERENCED_TABLE_NAME ：约束引用的表的名称。REFERENCED_COLUMN_NAME ：约束引用的列的名称。假设有两个表名t1， t3并且具有以下定义：
CREATE TABLE t1 ( s1 INT, s2 INT, s3 INT, PRIMARY KEY(s3) ) ENGINE=InnoDB; CREATE TABLE t3 ( s1 INT, s2 INT, s3 INT, KEY(s1), CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3) ) ENGINE=InnoDB;对于以上两张表， KEY_COLUMN_USAGE表有以下记录：
mysql> select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA='test'; +--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | 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 | test | PRIMARY | def | test | t1 | s3 | 1 | NULL | NULL | NULL | NULL | | def | test | CO | def | test | t3 | s2 | 1 | 1 | test | t1 | s3 | +--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ 2 rows in set (0.02 sec)也可以这样查询表上的约束
mysql> SELECT table_name, referenced_table_schema,referenced_table_name, CONSTRAINT_NAME, GROUP_CONCAT(column_name ORDER BY ORDINAL_POSITION) AS 'Columns' FROM information_schema.KEY_COLUMN_USAGE WHERE constraint_schema = 'test' AND REFERENCED_TABLE_NAME is not null group by table_name, referenced_table_schema,referenced_table_name, CONSTRAINT_NAME ORDER BY TABLE_NAME; +------------+-------------------------+-----------------------+-----------------+---------+ | table_name | referenced_table_schema | referenced_table_name | CONSTRAINT_NAME | Columns | +------------+-------------------------+-----------------------+-----------------+---------+ | t3 | test | t1 | CO | s2 | +------------+-------------------------+-----------------------+-----------------+---------+ 1 row in set (0.01 sec)转载于:https://www.cnblogs.com/wanbin/p/9514638.html
