I have a database in MySQL created by someone. I don't have any documentation of the database.
我在MySQL中创建了一个由某人创建的数据库。我没有任何数据库文档。
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?
是否有任何查询或程序来生成报告，以便很容易找到关系？
I can look into Schema information and manually figure it out, but it would be great if I could generate a relationship report.
我可以查看Schema信息并手动找出它，但如果我能生成一个关系报告会很好。
8 个解决方案
#1
8
The better way as 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
and another one is
另一个是
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
#2
17
You can get an overview in MySql Workbench by doing the steps below:
您可以通过以下步骤在MySql Workbench中获得概述：
- Go to "Database" Menu option.
- 转到“数据库”菜单选项。
- Select the "Reverse Engineer" option.
- 选择“逆向工程”选项。
- A wizard will be opened and will generate an EER Diagram which shows up
- 将打开一个向导，并生成一个显示的EER图表
#3
10
Try out SchemaSpy (http://schemaspy.sourceforge.net/):
试用SchemaSpy（http://schemaspy.sourceforge.net/）：
SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format.
SchemaSpy是一个基于Java的工具（需要Java 5或更高版本），它分析数据库中模式的元数据，并以浏览器可显示的格式生成它的可视化表示。
Here is a screenshot of the HTML page of the sample output from http://schemaspy.sourceforge.net/sample/ :
以下是来自http://schemaspy.sourceforge.net/sample/的示例输出的HTML页面的屏幕截图：
There is also a nice GUI if you do not want to use the command line: http://schemaspygui.sourceforge.net/
如果您不想使用命令行，还有一个很好的GUI：http：//schemaspygui.sourceforge.net/
Both tools are open source and in my opinion very lightweight and easy to use. I used them several times when I was in situations that you described: To get an overview of the schema and even some details to dive deeper. (Take a look at the "Anomalies" report.)
这两个工具都是开源的，在我看来非常轻巧，易于使用。当我遇到你所描述的情况时，我曾多次使用它们：了解架构，甚至更深入地了解一些细节。 （看看“异常”报告。）
Update
Be sure to check out the upcoming version of SchemaSpy at http://schemaspy.org
请务必在http://schemaspy.org上查看即将推出的SchemaSpy版本
#4
5
Do you have the SELECTs that use the database? That may be the best source of the relationships.
你有使用数据库的SELECT吗？这可能是关系的最佳来源。
#5
5
One more valuable option may be if you just install mysql workbench.( refers to) And try "Create EER models from database" .You will surely able to see relations among tables.
一个更有价值的选择可能是你只需要安装mysql workbench。（参考）并尝试“从数据库创建EER模型”。你肯定能够看到表之间的关系。
#6
4
You may take a look at information_scheme.KEY_COLUMN_USAGE table
您可以查看information_scheme.KEY_COLUMN_USAGE表
As it is suggested there a quick way to list your FKs (Foreign Key references) using the KEY_COLUMN_USAGE view:
因为建议使用KEY_COLUMN_USAGE视图快速列出您的FK（外键引用）：
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 = (your schema name here)
AND REFERENCED_TABLE_NAME is not null
ORDER BY TABLE_NAME, COLUMN_NAME;
#7
3
If you are using phpmyadmin then:
如果您使用的是phpmyadmin，那么：
- Goto the database.
- 转到数据库。
- Select the table and goto its structure.
- 选择表并转到其结构。
- You'll find relation view at the bottom of your table structure.
- 您将在表结构的底部找到关系视图。
#8
0
Better you use Mysql workbench. There is an option for generating ER diagram. If you are using phpmyadmin select any table. There is structure tab from where you can see the table structure. Hopefully, this will help.
最好使用Mysql工作台。有一个生成ER图的选项。如果您使用phpmyadmin选择任何表。有结构选项卡，您可以从中看到表结构。希望这会有所帮助。
#1
8
The better way as 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
and another one is
另一个是
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
#2
17
You can get an overview in MySql Workbench by doing the steps below:
您可以通过以下步骤在MySql Workbench中获得概述：
- Go to "Database" Menu option.
- 转到“数据库”菜单选项。
- Select the "Reverse Engineer" option.
- 选择“逆向工程”选项。
- A wizard will be opened and will generate an EER Diagram which shows up
- 将打开一个向导，并生成一个显示的EER图表
#3
10
Try out SchemaSpy (http://schemaspy.sourceforge.net/):
试用SchemaSpy（http://schemaspy.sourceforge.net/）：
SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format.
SchemaSpy是一个基于Java的工具（需要Java 5或更高版本），它分析数据库中模式的元数据，并以浏览器可显示的格式生成它的可视化表示。
Here is a screenshot of the HTML page of the sample output from http://schemaspy.sourceforge.net/sample/ :
以下是来自http://schemaspy.sourceforge.net/sample/的示例输出的HTML页面的屏幕截图：
There is also a nice GUI if you do not want to use the command line: http://schemaspygui.sourceforge.net/
如果您不想使用命令行，还有一个很好的GUI：http：//schemaspygui.sourceforge.net/
Both tools are open source and in my opinion very lightweight and easy to use. I used them several times when I was in situations that you described: To get an overview of the schema and even some details to dive deeper. (Take a look at the "Anomalies" report.)
这两个工具都是开源的，在我看来非常轻巧，易于使用。当我遇到你所描述的情况时，我曾多次使用它们：了解架构，甚至更深入地了解一些细节。 （看看“异常”报告。）
Update
Be sure to check out the upcoming version of SchemaSpy at http://schemaspy.org
请务必在http://schemaspy.org上查看即将推出的SchemaSpy版本
#4
5
Do you have the SELECTs that use the database? That may be the best source of the relationships.
你有使用数据库的SELECT吗？这可能是关系的最佳来源。
#5
5
One more valuable option may be if you just install mysql workbench.( refers to) And try "Create EER models from database" .You will surely able to see relations among tables.
一个更有价值的选择可能是你只需要安装mysql workbench。（参考）并尝试“从数据库创建EER模型”。你肯定能够看到表之间的关系。
#6
4
You may take a look at information_scheme.KEY_COLUMN_USAGE table
您可以查看information_scheme.KEY_COLUMN_USAGE表
As it is suggested there a quick way to list your FKs (Foreign Key references) using the KEY_COLUMN_USAGE view:
因为建议使用KEY_COLUMN_USAGE视图快速列出您的FK（外键引用）：
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 = (your schema name here)
AND REFERENCED_TABLE_NAME is not null
ORDER BY TABLE_NAME, COLUMN_NAME;
#7
3
If you are using phpmyadmin then:
如果您使用的是phpmyadmin，那么：
- Goto the database.
- 转到数据库。
- Select the table and goto its structure.
- 选择表并转到其结构。
- You'll find relation view at the bottom of your table structure.
- 您将在表结构的底部找到关系视图。
#8
0
Better you use Mysql workbench. There is an option for generating ER diagram. If you are using phpmyadmin select any table. There is structure tab from where you can see the table structure. Hopefully, this will help.
最好使用Mysql工作台。有一个生成ER图的选项。如果您使用phpmyadmin选择任何表。有结构选项卡，您可以从中看到表结构。希望这会有所帮助。