如何知道表之间的关系 [英] How to know relations between tables

问题描述

我有一个人在MySQL中创建的数据库.我没有数据库的任何文档.

I have a database in MySQL created by someone. I don't have any documentation of the database.

我如何知道表之间的关系?

How can I know the relationship between the tables?

是否有任何查询或生成报告的过程，以便于查找关系?

Is there any query or a procedure to generate a report so that it's easy to find the relations?

我可以查看Schema信息并手动找出来，但是如果我可以生成关系报告，那将是很好的选择.

I can look into Schema information and manually figure it out, but it would be great if I could generate a relationship report.

推荐答案

以编程方式来讲，更好的方法是从INFORMATION_SCHEMA.KEY_COLUMN_USAGE表中收集数据，如下所示:

The better way as 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

另一个是

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

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