mysql查询某张表的所有外键_[MySQL]MySQL数据库中如何使用SQL语句查看表或者列的所有外键？...

[MySQL]MySQL数据库中如何使用SQL语句查看表或者列的所有外键？

问题描述

如题，[MySQL]MySQL数据库中如何使用SQL语句查看表或者列的所有外键？

方案一

查看一张表的所有外键：

SELECT

TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME

FROM

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE

REFERENCED_TABLE_SCHEMA = '' AND

REFERENCED_TABLE_NAME = '

查看一个列的所有外键：

SELECT

TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME

FROM

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE

REFERENCED_TABLE_SCHEMA = '' AND

REFERENCED_COLUMN_NAME = '';

方案二

如果使用InnoDB引擎并且定义了外键：

SELECT * FROM information_schema.TABLE_CONSTRAINTS

WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'

AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema'

AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable';

方案三

查看一张表的所有外键：

USE '';

SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME

FROM information_schema.TABLE_CONSTRAINTS i

LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME

WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'

AND i.TABLE_SCHEMA = DATABASE()

AND i.TABLE_NAME = '';

查看框架(schema)中所有表的所有外键：

USE '';

SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME

FROM information_schema.TABLE_CONSTRAINTS i

LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME

WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'

AND i.TABLE_SCHEMA = DATABASE();

查看数据库中所有外键：

SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME

FROM information_schema.TABLE_CONSTRAINTS i

LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME

WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY';

方案四

查看数据所有关系及约束等集合

select

concat(table_name, '.', column_name) as 'foreign key',

concat(referenced_table_name, '.', referenced_column_name) as 'references',

constraint_name as 'constraint name'

from

information_schema.key_column_usage

where

referenced_table_name is not null;

如果想要查看指定的数据库和数据表，则：

select

concat(table_name, '.', column_name) as 'foreign key',

concat(referenced_table_name, '.', referenced_column_name) as 'references',

constraint_name as 'constraint name'

from

information_schema.key_column_usage

where

referenced_table_name is not null

and table_schema = 'database_name';