如何查找所有mysql表之间的所有关系?

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.

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

Is there anyway to know this?

有必要知道这个吗?

8 个解决方案

#1

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

更好的方法是，以编程的方式，从INFORMATION_SCHEMA中收集数据。KEY_COLUMN_USAGE表如下:

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

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

有更多的列信息，比如ORDINAL_POSITION，根据您的目的可能会很有用。

More info: http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

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

#2

Try this:

试试这个:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

#3

Try

试一试

SELECT
`TABLE_NAME`,
`COLUMN_NAME`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE `CONSTRAINT_SCHEMA` = 'YOUR_DATABASE_NAME' AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL AND
`REFERENCED_TABLE_NAME` IS NOT NULL AND
`REFERENCED_COLUMN_NAME` IS NOT NULL

从“information_schema”中选择“TABLE_NAME”、“COLUMN_NAME”、“REFERENCED_TABLE_NAME”。' KEY_COLUMN_USAGE '其中' CONSTRAINT_SCHEMA ' = 'YOUR_DATABASE_NAME'和' referd_encetable_schema '不为空，' REFERENCED_TABLE_NAME '不为空，' REFERENCED_COLUMN_NAME '不为空

do not forget to replace YOUR_DATABASE_NAME with your database name!

不要忘记用您的数据库名替换您的_database_name !

#4

A quick method of visualizing relationships in MySQL is reverse engineering the database with MySQL Workbench.

在MySQL中可视化关系的一种快速方法是使用MySQL Workbench反向工程数据库。

This can be done using the reverse engineering too, which will result in an entity-relationship diagram much like the following (though you may have to organize it yourself, once it is generated):

这也可以用逆向工程来完成，这将导致实体关系图很像下面的(尽管您可能需要自己组织它，一旦它生成):

#5

you can use:

您可以使用:

SHOW CREATE TABLE table_name;

#6


SELECT 
    count(1) totalrelationships ,
    c.table_name tablename,
    CONCAT(' ',GROUP_CONCAT(c.column_name ORDER BY ordinal_position SEPARATOR ', ')) columnname,
    CONCAT(' ',GROUP_CONCAT(c.column_type ORDER BY ordinal_position SEPARATOR ', ')) columntype    
FROM
    information_schema.columns c RIGHT JOIN
    (SELECT column_name , column_type FROM information_schema.columns WHERE 
    -- column_key in ('PRI','MUL') AND  -- uncomment this line if you want to see relations only with indexes
    table_schema = DATABASE() AND table_name = 'YourTableName') AS p
    USING (column_name,column_type)
WHERE
    c.table_schema = DATABASE()
    -- AND c.table_name != 'YourTableName'
    GROUP BY tablename
    -- HAVING (locate(' YourColumnName',columnname) > 0) -- uncomment this line to search for specific column 
    ORDER BY totalrelationships desc, columnname
;

#7

1) Go into your database:
use DATABASE;

1)进入数据库:使用数据库;

2) Show all the tables:
show tables;

2)显示所有表格:显示表格;

3) Look at each column of the table to gather what it does and what it's made of:
describe TABLENAME;

3)查看表的每一列以收集它的功能和组成:describe TABLENAME;

4) Describe is nice since you can figure out exactly what your table columns do, but if you would like an even closer look at the data itself: select * from TABLENAME
If you have big tables, then each row usually has an id, in which case I like to do this to just get a few lines of data and not have the terminal overwhelmed:
select * from TABLENAME where id<5 - You can put any condition here you like.

4)描述很好因为你可以找出你的表列做什么,但如果你想一个更近距离观察数据本身:select * from表如果你有大的表,那么每一行通常有一个id,在这种情况下,我喜欢这样做只是几行数据,没有终端不知所措:select *从表id < 5 -你可以把你喜欢的任何条件。

This method give you more information than just doing select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;, and it also provides you with more bite-sized information each time.

这个方法提供了比从information_schema . table_constraint中执行select *更多的信息，而且每次都能提供更多的少量信息。

EDIT

编辑

As the comments suggested, the above WHERE id < 5 was a bad choice as a conditional placeholder. It is not a good idea to limit by ID number, especially since the id is usually not trustworthy to be sequential. Add LIMIT 5 at the end of the query instead.

正如评论所指出的，上面的id < 5是作为条件占位符的错误选择。用ID号进行限制不是一个好主意，尤其是因为ID通常不值得信任是连续的。在查询末尾添加LIMIT 5。

#8

One option is : You can do reverse engineering to understand it in diagrammatic way.

一种选择是:您可以做逆向工程，以图表的方式理解它。

When you install MySQL, you will get MySQLWorkbench. You need to open it and choose the database you want to reverse engineer. Click on Reverse Engineer option somewhere you find under the tools or Database menu. It will ask you to choose the tables. Either you select the tables you want to understand or choose the entire DB. It will generate a diagram with relationships.

安装MySQL时，将获得MySQLWorkbench。您需要打开它并选择要反向工程的数据库。在“工具”或“数据库”菜单中，单击“反向工程”选项。它会要求你选择表格。要么选择要理解的表，要么选择整个DB。它将生成一个关系图。

#1