如何找到所有mysql表之间的所有关系？ [英] How to find all the relations between all mysql tables?

问题描述

如何找到所有MySQL表之间的所有关系？例如，我想知道具有大约100个表的数据库中表的关系。

How to find all the relations between all MySQL tables? If for example, I want to know the relation of tables in a database of having around 100 tables.

有什么办法知道这个吗？

Is that got any ways to know this?

谢谢..

推荐答案

程序化地说， INFORMATION_SCHEMA.KEY_COLUMN_USAGE 表如下：

The better way, programmatically speaking, is gathering data from INFORMATION_SCHEMA.KEY_COLUMN_USAGE table as follows:

SELECT 
  `TABLE_SCHEMA`,                          -- Foreign key schema
  `TABLE_NAME`,                            -- Foreign key table
  `COLUMN_NAME`,                           -- Foreign key column
  `REFERENCED_TABLE_SCHEMA`,               -- Origin key schema
  `REFERENCED_TABLE_NAME`,                 -- Origin key table
  `REFERENCED_COLUMN_NAME`                 -- Origin key column
FROM
  `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  -- Will fail if user don't have privilege
WHERE
  `TABLE_SCHEMA` = SCHEMA()                -- Detect current schema in USE 
  AND `REFERENCED_TABLE_NAME` IS NOT NULL; -- Only tables with foreign keys

还有更多列信息，如 ORDINAL_POSITION 根据你的目的可能是有用的。

There are more columns info like ORDINAL_POSITION that could be useful depending your purpose.

更多信息： http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

这篇关于如何找到所有mysql表之间的所有关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！