MYSQL之KEY_COLUMN_USAGE

KEY_COLUMN_USAGE
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)
复制