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

问题描述

类似于这个问题，但对于 MySQL....p>

如何以编程方式确定 MySQL 中的外键引用(假设为 InnoDB)?我几乎可以得到它们:

显示表状态其中名称 = 'MyTableName';

...但是很可惜，似乎包含其中一些信息的评论栏被截断了，所以我不能依赖它.一定有别的办法……

我对 C API 调用、SQL 语句以及任何东西都很满意——我只需要一些始终有效的东西.

注意:我也考虑过解析SHOW CREATE TABLE MyTableName"语句的结果，但我真的希望有更简单的方法.

解决方案

您可以查询两个表来获取此信息:INFORMATION_SCHEMA.TABLE_CONSTRAINTS 和 INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

这是来自上面链接的后一页评论的查询，它演示了如何获取您寻求的信息.

SELECT CONCAT(table_name, '.', column_name, '->',referenced_table_name, '.', referenced_column_name ) AS list_of_fksFROM INFORMATION_SCHEMA.key_column_usageWHERE referenced_table_schema = '测试'AND referenced_table_name 不为空按表名、列名排序；

使用您的架构名称而不是上面的test".

Similar to this question but for MySQL....

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...

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

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

解决方案

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屋！