MySQL的：如何以编程方式确定外键关系？ [英] MySQL: How to determine foreign key relationships programmatically?

问题描述

类似这个问题但对MySQL ....

Similar to this question but for MySQL....

如何编程方式确定MySQL的外键​​引用（假设InnoDB的）？我几乎可以用得到它们：

How can I programmatically determine foreign key references in MySQL (assuming InnoDB)? I can almost get them with:

SHOW TABLE STATUS WHERE Name = 'MyTableName';

...但很可惜，这似乎含有一些这种信息的注释列被截断，所以我不能依赖它。必须有一些其他的方式...

...but alas, the comment column which seems to contain some of this info gets truncated so I can't rely on it. There must be some other way...

我很乐意与C API调用，一条SQL语句，任何东西 - 我只需要的东西，始终如一

I'd be happy with a C API call, a SQL statement, anything--I just need something that consistently works.

请注意：我也认为解析SHOW CREATE TABLE MyTableName语句的结果，但我真的希望有一个更简单的东西。

Note: I've also considered parsing the results of a "SHOW CREATE TABLE MyTableName" statement, but I'm really hoping there's something simpler.

推荐答案

有可以查询得到这个信息的两个表：<一href=\"http://dev.mysql.com/doc/refman/5.1/en/table-constraints-table.html\"><$c$c>INFORMATION_SCHEMA.TABLE_CONSTRAINTS和<一个href=\"http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html\"><$c$c>INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

There are two tables you can query to get this information: INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

下面是从后者网页上面的联系，它演示了如何获得你所寻求的信息上的评论的查询。

Here's a query from the comments on the latter page linked above, which demonstrates how to get the info you seek.

SELECT CONCAT( table_name, '.', column_name, ' -> ', 
  referenced_table_name, '.', referenced_column_name ) AS list_of_fks 
FROM INFORMATION_SCHEMA.key_column_usage 
WHERE referenced_table_schema = 'test' 
  AND referenced_table_name IS NOT NULL 
ORDER BY table_name, column_name;

使用您的架构名称，而不是测试'之上。

Use your schema name instead of 'test' above.

这篇关于MySQL的：如何以编程方式确定外键关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！