MySQL 比较两个数据库的结构？

Compare structures of two databases?

提问by streetparade

I wanted to ask whether it is possible to compare the complete database structure of two huge databases. We have two databases, the one is a development database, the other a production database. I've sometimes forgotten to make changes in to the production database, before we released some parts of our code, which results that the production database doesn't have the same structure, so if we release something we got some errors. Is there a way to compare the two, or synchronize?

我想问一下是否可以比较两个庞大的数据库的完整数据库结构。我们有两个数据库，一个是开发数据库，​​另一个是生产数据库。在我们发布代码的某些部分之前，我有时忘记对生产数据库进行更改，这导致生产数据库没有相同的结构，因此如果我们发布某些内容，我们就会出现一些错误。有没有办法比较两者或同步？

采纳答案by Anders Abel

What about http://www.mysqldiff.org/which is freeware?

免费软件http://www.mysqldiff.org/怎么样？

回答by Dmitry

For MySQL database you can compare view and tables (column name and column type) using this query:

对于 MySQL 数据库，您可以使用以下查询比较视图和表（列名和列类型）：

SET @firstDatabaseName = '[first database name]';
SET @secondDatabaseName = '[second database name]';

SELECT * FROM  
  (SELECT
      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType
    FROM information_schema.columns cl,  information_schema.TABLES ss
    WHERE
      cl.TABLE_NAME = ss.TABLE_NAME AND
      cl.TABLE_SCHEMA = @firstDatabaseName AND
      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')
    ORDER BY
      cl.table_name ) AS t1
LEFT JOIN                     
  (SELECT
      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType
    FROM information_schema.columns cl,  information_schema.TABLES ss
    WHERE
      cl.TABLE_NAME = ss.TABLE_NAME AND
      cl.TABLE_SCHEMA = @secondDatabaseName AND
      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')
    ORDER BY
      cl.table_name ) AS t2 ON t1.tableRowType = t2.tableRowType
WHERE 
  t2.tableRowType IS NULL        
UNION 
SELECT * FROM  
  (SELECT
      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType
    FROM information_schema.columns cl,  information_schema.TABLES ss
    WHERE
      cl.TABLE_NAME = ss.TABLE_NAME AND
      cl.TABLE_SCHEMA = @firstDatabaseName AND
      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')
    ORDER BY
      cl.table_name ) AS t1
RIGHT JOIN                     
  (SELECT
      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType
    FROM information_schema.columns cl,  information_schema.TABLES ss
    WHERE
      cl.TABLE_NAME = ss.TABLE_NAME AND
      cl.TABLE_SCHEMA = @secondDatabaseName AND
      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')
    ORDER BY
      cl.table_name ) AS t2 ON t1.tableRowType = t2.tableRowType
WHERE 
  t1.tableRowType IS NULL;

If you prefer using tool with UI you can also use this script https://github.com/dlevsha/compalexwhich can compare tables, views, keys etc.

如果您更喜欢使用带有 UI 的工具，您还可以使用此脚本 https://github.com/dlevsha/compalex，它可以比较表、视图、键等。

Compalex is a lightweight script to compare two database schemas. It supports MySQL, MS SQL Server and PostgreSQL.

Compalex 是一个轻量级脚本，用于比较两个数据库模式。它支持 MySQL、MS SQL Server 和 PostgreSQL。

Screenshot (compare tables)

屏幕截图（比较表格）

回答by Gere

You can use the command line:

您可以使用命令行：

mysqldump --skip-comments --skip-extended-insert -d --no-data -u root -p dbName1>file1.sql
mysqldump --skip-comments --skip-extended-insert -d --no-data -u root -p dbName2>file2.sql
diff file1.sql file2.sql

回答by Randy Minder

Red-Gate SQL Compare is a great tool that will do this for you. I have used this for years with great success. It has saved me thousands of hours of work.

Red-Gate SQL Compare 是一个很好的工具，可以为您做到这一点。我多年来一直使用它并取得了巨大的成功。它为我节省了数千小时的工作时间。

They also have a tool that will compare data as well. The product I mentioned above compares schema.

他们还有一个工具可以比较数据。我上面提到的产品比较了模式。

www-red-gate.com

www-red-gate.com

回答by MarkR

You can just dump them with --no-data and compare the files.

您可以使用 --no-data 转储它们并比较文件。

Remember to use the --lock-tables=0 option on your production database to avoid the big nasty global lock.

请记住在生产数据库上使用 --lock-tables=0 选项以避免大的讨厌的全局锁。

If you use the same mysqldump version (your dev and production systems should have the same software, right?) then you'll expect to get more-or-less identical files out. The tables will be in alpha order so a simple diff will show discrepancies up easily.

如果您使用相同的 mysqldump 版本（您的开发和生产系统应该具有相同的软件，对吗？）那么您将期望获得或多或少相同的文件。表格将按 alpha 顺序排列，因此简单的差异将很容易显示差异。

回答by Avidos

To answer this kind of question currently, I've made a script that uses information_schemacontent to compare column, datatype, and table

目前为了回答此类问题，我制作了一个脚本，该脚本使用information_schema内容来比较列、数据类型和表

SET @database_current = '<production>';
SET @database_dev = '<development>';
-- column and datatype comparison
SELECT a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH,
    b.COLUMN_NAME, b.DATA_TYPE, b.CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS a
    LEFT JOIN information_schema.COLUMNS b ON b.COLUMN_NAME = a.COLUMN_NAME
        AND b.TABLE_NAME = a.TABLE_NAME
        AND b.TABLE_SCHEMA = @database_current
WHERE a.TABLE_SCHEMA = @database_dev
    AND (
        b.COLUMN_NAME IS NULL
        OR b.COLUMN_NAME != a.COLUMN_NAME
        OR b.DATA_TYPE != a.DATA_TYPE
        OR b.CHARACTER_MAXIMUM_LENGTH != a.CHARACTER_MAXIMUM_LENGTH
    );
-- table comparison    
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, b.TABLE_NAME
FROM information_schema.TABLES a
    LEFT JOIN information_schema.TABLES b ON b.TABLE_NAME = a.TABLE_NAME
        AND b.TABLE_SCHEMA = @database_current
WHERE a.TABLE_SCHEMA = @database_dev
    AND (
        b.TABLE_NAME IS NULL
        OR b.TABLE_NAME != a.TABLE_NAME
    );

Hope this script can also help people that looks for a non-application solution, but the usage of script. Cheers

希望这个脚本也可以帮助那些寻找非应用程序解决方案的人，但脚本的用法。干杯

回答by user945389

For mysql on Linux, it is possible via phpmyadminto export the databases without 'data' and only structure.

对于Linux 上的 mysql，可以通过 phpmyadmin导出没有“数据”和只有结构的数据库。

Scrolling through the export options for the entire database, just deselect 'data' and set the output to text. Export both databases you wish to compare.

滚动整个数据库的导出选项，只需取消选择“数据”并将输出设置为文本即可。导出要比较的两个数据库。

Then in file compare in your preferred program / site, compare the two text file outputs of the databases. Synchronization is still manual in this solution, but this is effective for comparing and finding the structural differences.

然后在您首选程序/站点的文件比较中，比较数据库的两个文本文件输出。在此解决方案中，同步仍然是手动的，但这对于比较和查找结构差异是有效的。

回答by JoSSte

I tried mysqldiff without success, so I would like to enrich the future readers by drawing attention to mysqlworkbench's compare function. http://dev.mysql.com/doc/workbench/en/wb-database-diff-report.html#c13030

我尝试了 mysqldiff 没有成功，所以我想通过引起对 mysqlworkbench 的比较功能的关注来丰富未来的读者。http://dev.mysql.com/doc/workbench/en/wb-database-diff-report.html#c13030

if you open a model tab, and select the databases menu, you get a compare schemas option, which you can use to compare two different schemas on two different servers, or two schemas on the same server, or a schema and a model, or a lot of other options i haven't tried yet.

如果您打开模型选项卡并选择数据库菜单，您将获得一个比较模式选项，您可以使用该选项比较两台不同服务器上的两种不同模式，或同一服务器上的两种模式，或模式和模型，或很多其他选项我还没有尝试过。

回答by Shaun

Check out Gemini Delta - SQL Difference Manager for .NET. A free beta version is available for download, but the full version is only a few days away from public release.

查看 Gemini Delta - 适用于 .NET 的 SQL 差异管理器。免费的测试版可供下载，但完整版距离公开发布只有几天的时间。

It doesn't compare row-level data differences, but it compares tables, functions, sprocs, etc... and it is lightning fast. (The new version, 1.4, loads and compares 1k Sprocs in under 4 seconds, compared with other tools I've tested which took over 10 seconds.)

它不比较行级数据差异，但它比较表、函数、sprocs 等……而且速度快如闪电。（新版本 1.4 在 4 秒内加载和比较 1k Sproc，而我测试过的其他工具则需要 10 秒以上。）

Everyone is right though, RedGate does make great tools.

不过，每个人都是对的，RedGate 确实制作了很棒的工具。

回答by codenheim

Depending on your database, the tools available vary.

根据您的数据库，可用的工具会有所不同。

I use Embarcadero's ER/Studio for this. It has a Compare and Merge feature.

为此，我使用了 Embarcadero 的 ER/Studio。它具有比较和合并功能。

There are plenty others, such as Toad for MySQL, that also have compare. Also agree on the Red-Gate suggestion, but never used it for MySQL.

还有很多其他的，例如 Toad for MySQL，也有比较。也同意 Red-Gate 的建议，但从未将其用于 MySQL。