Mysql是否显示create constraint？

Question

有没有一种简单的方法来查询表中的约束(特定的外键)，就像show create table那样，但只针对约束？

谢谢,

pvgoddijn

Answer 1

要只显示外键约束，可以检查information_schema.table_constraints中的constraint_type，并通过连接获取information_schema.key_column_usage中受影响的列

SELECT b.table_name, b.column_name, b.constraint_name,
       b.referenced_table_name, b.referenced_column_name
FROM information_schema.table_constraints a
JOIN information_schema.key_column_usage b
ON a.table_schema = b.table_schema AND a.constraint_name = b.constraint_name
WHERE a.table_schema=database() AND a.constraint_type='FOREIGN KEY'
ORDER BY b.table_name, b.constraint_name;

Answer 2

select * from 
information_schema.KEY_COLUMN_USAGE 
where table_schema = <db_name> 
and table_name = <table_name>;

Answer 3

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

外键约束列在输出的“注释”列中。