MySQL 如何找到所有mysql表之间的所有关系？

How to find all the relations between all mysql tables?

提问by user3148861

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?

反正有知道这个吗？

回答by xudre

The better way, programmatically speaking, is gathering data from INFORMATION_SCHEMA.KEY_COLUMN_USAGEtable 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_POSITIONthat 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

回答by BaBL86

Try this:

尝试这个：

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

回答by Mostafa Lavaei

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`、
`REFERENCED_COLUMN_NAME`。`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_SCHEMA` = 'YOUR_DATABASE_NAME' AND `REFERENCED_TABLE_SCHEMA` IS NOT ` NULL AND REFERENCED_TABLE_SCHEMA` IS NOT ` NULL AND REFERENCE_NAME`NULL不为空

do not forget to replace YOUR_DATABASE_NAME with your database name!

不要忘记将 YOUR_DATABASE_NAME 替换为您的数据库名称！

回答by Fanta Sylla

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):

这也可以使用逆向工程来完成，这将导致实体关系图如下所示（尽管您可能必须自己组织它，一旦它生成）：

回答by Ricardo Sismeiro

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
;

回答by balderys

you can use:

您可以使用：

SHOW CREATE TABLE table_name;

回答by Roman

1) Go into your database:
use DATABASE;

1）进入你的数据库：
use DATABASE;

2) Show all the tables:
show tables;

2）显示所有表：
show tables;

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）Describe 很好，因为您可以准确地弄清楚表格列的作用，但是如果您想更仔细地查看数据本身： select * from TABLENAME
如果您有大表格，那么每行通常都有一个id，在这种情况下我喜欢这样做只是为了获取几行数据，而不是让终端不堪重负：
select * from TABLENAME where 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.

这种方法为您提供的信息不仅仅是做select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;，而且每次都为您提供更多的信息。

EDIT

编辑

As the comments suggested, the above WHERE id < 5was 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 5at the end of the query instead.

正如评论所暗示的那样，上述WHERE id < 5作为条件占位符是一个糟糕的选择。通过 ID 号进行限制并不是一个好主意，特别是因为 id 通常是不值得信赖的顺序。LIMIT 5改为在查询末尾添加。

回答by Lucas Basquerotto

Based on xudre'sanswer, you can execute the following to see all the relations of a schema:

根据xudre 的回答，您可以执行以下操作来查看架构的所有关系：

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`
WHERE `TABLE_SCHEMA` = 'YourSchema'
AND   `REFERENCED_TABLE_NAME` IS NOT NULL  -- Only tables with foreign keys

What I want in most cases is to know all FKs that point to a specific table. In this case I run:

在大多数情况下，我想要的是知道指向特定表的所有 FK。在这种情况下，我运行：

SELECT 
  `TABLE_SCHEMA`,                          -- Foreign key schema
  `TABLE_NAME`,                            -- Foreign key table
  `COLUMN_NAME`                            -- Foreign key column
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  
WHERE `TABLE_SCHEMA` = 'YourSchema'
AND   `REFERENCED_TABLE_NAME` = 'YourTableName'

回答by user3655708

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。您需要打开它并选择要逆向工程的数据库。在工具或数据库菜单下的某处单击反向工程选项。它会要求您选择表格。要么选择要理解的表，要么选择整个数据库。它将生成一个关系图。