这个解决方案不仅会显示所有的关系，还会显示约束的名称，这在某些情况下是必需的(例如drop constraint):

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';

同样，对于特定的列名，添加

table_name = 'table_name

在查询的末尾。

受到这篇文章的启发

查找包含特定外键(如employee_id)的所有表

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('employee_id')
AND TABLE_SCHEMA='table_name';

在旧的答案上添加一些有用的信息。

我遇到了类似的问题，但我还想查看CONSTRAINT_TYPE以及引用的表和列名。所以,

To see all FKs in your table: USE '<yourschema>'; 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 = '<yourtable>'; To see all the tables and FKs in your schema: USE '<yourschema>'; 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(); To see all the FKs in your 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';

记住!

这里使用的是InnoDB存储引擎。如果在添加外键后，似乎无法显示任何外键，这可能是因为您的表正在使用MyISAM。

检查:

SELECT * TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<yourschema>';

要修复，使用这个:

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

使用REFERENCED_TABLE_NAME并不总是有效，它可以是一个NULL值。下面的查询可以代替:

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '<table>';

如果你还想获取外键列的名称:

SELECT i.TABLE_SCHEMA, i.TABLE_NAME, 
       i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, 
       k.COLUMN_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.TABLE_SCHEMA = '<TABLE_NAME>' AND i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
 ORDER BY i.TABLE_NAME;

我需要鸟瞰表之间的关系(在ORM中使用)。使用该页面的建议，经过试验，我整理了以下查询:

SELECT
    KCU.CONSTRAINT_NAME,
    KCU.TABLE_NAME,
    KCU.COLUMN_NAME,
    KCU.REFERENCED_TABLE_NAME,
    KCU.REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    JOIN INFORMATION_SCHEMA.COLUMNS AS COLS
        ON
                COLS.TABLE_SCHEMA = KCU.TABLE_SCHEMA
            AND COLS.TABLE_NAME   = KCU.TABLE_NAME
            AND COLS.COLUMN_NAME  = KCU.COLUMN_NAME
WHERE
        KCU.CONSTRAINT_SCHEMA = {YOUR_SCHEMA_NAME}
    AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
    KCU.TABLE_NAME,
    COLS.ORDINAL_POSITION

它只返回我需要的东西，而且是按照我想要的顺序。

我还对结果做了一些处理(将其转换为某种字典)，以便它可以用于创建聚合。