MySQL 你如何版本你的数据库架构？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/175451/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you version your database schema?
提问by Eran Galperin
How do you prepare your SQL deltas? do you manually save each schema-changing SQL to a delta folder, or do you have some kind of an automated diffing process?
您如何准备 SQL 增量？您是否手动将每个架构更改 SQL 保存到一个增量文件夹，或者您是否有某种自动比较过程？
I am interested in conventions for versioning database schema along with the source code. Perhaps a pre-commit hook that diffs the schema?
我对版本控制数据库模式和源代码的约定很感兴趣。也许是一个区分架构的预提交钩子？
Also, what options for diffing deltas exist aside from DbDeploy?
此外，除了DbDeploy之外，还有哪些差异增量的选项？
EDIT:seeing the answers I would like to clarify that I am familiar with the standard scheme for running a database migration using deltas. My question is about creating the deltas themselves, preferably automatically.
编辑：看到答案我想澄清一下，我熟悉使用增量运行数据库迁移的标准方案。我的问题是关于自己创建增量，最好是自动创建。
Also, the versioning is for PHP and MySQL if it makes a difference. (No Ruby solutions please).
此外，版本控制适用于 PHP 和 MySQL，如果它有所不同的话。（请不要提供 Ruby 解决方案）。
采纳答案by harpo
See
看
Is there a version control system for database structure changes?
How do I version my MS SQL database in SVN?
如何在 SVN 中对我的 MS SQL 数据库进行版本控制？
and Jeff's article
和杰夫的文章
Get Your Database Under Version Control
I feel your pain, and I wish there were a better answer. This might be closer to what you were looking for.
我感受到你的痛苦，我希望有一个更好的答案。这可能更接近您要查找的内容。
Mechanisms for tracking DB schema changes
Generally, I feel there is no adequate, accepted solution to this, and I roll my own in this area.
一般来说，我觉得没有足够的、可接受的解决方案，我在这方面推出自己的解决方案。
回答by Jason Hymanson
You might take a look at another, similar thread: How do I version my MS SQL database in SVN?.
您可能会查看另一个类似的线程：如何在 SVN 中对我的 MS SQL 数据库进行版本控制？.
回答by Christophe Fondacci
If you are still looking for options : have a look at neXtep designer. It is a free GPL database development environment based on the concepts of version control. In the environment you always work with versioned entities and can focus on the data model development. Once a release is done, the SQL generation engine plugged on the version control system can generate any delta you need between 2 versions, and will offer you some delivery mechanism if you need.
如果您仍在寻找选项：看看 neXtep 设计器。它是一个基于版本控制概念的免费 GPL 数据库开发环境。在环境中，您始终使用版本化实体，并且可以专注于数据模型开发。发布完成后，插入版本控制系统的 SQL 生成引擎可以生成您需要的 2 个版本之间的任何增量，并在您需要时为您提供一些交付机制。
Among other things, you can synchronize and reverse synchronize your database during developments, create data model diagrams, query your database using integrated SQL clients, etc.
除此之外，您可以在开发过程中同步和反向同步您的数据库、创建数据模型图、使用集成的 SQL 客户端查询您的数据库等。
Have a look at the wiki for more information : http://www.nextep-softwares.com/wiki
查看 wiki 了解更多信息：http: //www.nextep-softwares.com/wiki
It currently supports Oracle, MySql and PostgreSql and is in java so the product runs on windows, linux and mac.
它目前支持 Oracle、MySql 和 PostgreSql，并且是在 java 中，因此该产品可以在 windows、linux 和 mac 上运行。
回答by Calmarius
I make sure that schema changes are always additive. So I don't drop columns and tables, because that would zap the data and cannot be rolled back later. This way the code that uses the database can be rolled back without losing data or functionality.
我确保架构更改始终是附加的。所以我不删除列和表，因为这会破坏数据并且以后无法回滚。通过这种方式，可以回滚使用数据库的代码而不会丢失数据或功能。
I have a migration script that contains statements that creates tables and columns if they don't exist yet and fills them with data.
我有一个迁移脚本，其中包含创建表和列（如果尚不存在）并用数据填充它们的语句。
The migration script runs whenever the production code is updated and after new installs.
每当更新生产代码和新安装后，迁移脚本就会运行。
When I would like to drop something, I do it by removing them from the database install script and the migration script so these obsolete schema elements will be gradually phased out in new installs. With the disadvantage that new installs cannot downgrade to an older version before the install.
当我想删除某些东西时，我会通过从数据库安装脚本和迁移脚本中删除它们来实现，这样这些过时的架构元素将在新安装中逐渐淘汰。缺点是新安装无法在安装前降级到旧版本。
And of course I execute DDLs via these scripts and never directly on the database to keep things in sync.
当然，我通过这些脚本执行 DDL，从不直接在数据库上执行以保持同步。
回答by user381751
http://bitbucket.org/idler/mmp- schema versioning tool for mysql, writed in PHP
http://bitbucket.org/idler/mmp- mysql 的模式版本控制工具，用 PHP 编写
回答by Darrel Miller
I don't manage deltas. I make changes to a master database and have a tool that creates an XML based build script based on the master database.
我不管理增量。我对主数据库进行了更改，并有一个工具可以根据主数据库创建基于 XML 的构建脚本。
When it comes time to upgrade an existing database I have a program that uses the XML based build script to create a new database and the bare tables. I then copy the data over from the old database using INSERT INTO x SELECT FROM y and then apply all indexes, constraints and triggers.
当需要升级现有数据库时，我有一个程序使用基于 XML 的构建脚本来创建新数据库和裸表。然后我使用 INSERT INTO x SELECT FROM y 从旧数据库复制数据，然后应用所有索引、约束和触发器。
New tables, new columns, deleted columns all get handled automatically and with a few little tricks to adjust the copy routine I can handle column renames, column type changes and other basic refactorings.
新表、新列、删除的列都会自动处理，通过一些小技巧来调整复制例程，我可以处理列重命名、列类型更改和其他基本重构。
I wouldn't recommend this solution on a database with a huge amount of data but I regularly update a database that is over 1GB with 400 tables.
我不会在具有大量数据的数据库上推荐此解决方案，但我会定期更新一个超过 1GB 的数据库，其中包含 400 个表。
回答by jalbert
You didn't mention which RDBMS you're using, but if it's MS SQL Server, Red-Gate's SQL Comparehas been indispensable to us in creating deltas between object creation scripts.
您没有提到您使用的是哪个 RDBMS，但如果是 MS SQL Server，Red-Gate 的SQL 比较对于我们在对象创建脚本之间创建增量是必不可少的。
回答by Joakim Bodin
I'm not one to toot my own horn, but I've developed an internal web app to track changes to database schemas and create versioned update scripts.
我不是一个吹嘘自己的人，但我开发了一个内部网络应用程序来跟踪对数据库架构的更改并创建版本化更新脚本。
This tool is called Braziland is now open source under a MIT license. Brazil is ruby / ruby on rails based and supports change deployment to any database that Ruby DBIsupports (MySQL, ODBC, Oracle, Postgres, SQLite).
这个工具被称为巴西，现在在 MIT 许可下是开源的。巴西基于 ruby / ruby on rails，并支持更改部署到Ruby DBI支持的任何数据库（MySQL、ODBC、Oracle、Postgres、SQLite）。
Support for putting the update scripts in version control is planned.
计划支持将更新脚本置于版本控制中。
回答by Joakim Bodin
I also developed a set of PHP scripts where developers can submit their deltasql scripts to a central repository.
我还开发了一组 PHP 脚本，开发人员可以在其中将他们的 deltasql 脚本提交到中央存储库。
In one of the database tables (called TBSYNCHRONIZE), I store the version number of the latest executed script, so I can upgrade any database easily by using the web interface or a client developed on purpose for Eclipse.
在其中一个数据库表（称为 TBSYNCHRONIZE）中，我存储了最新执行的脚本的版本号，因此我可以使用 Web 界面或专门为 Eclipse 开发的客户端轻松升级任何数据库。
The web interface allows to manage several projects. It supports also database "branches".
Web 界面允许管理多个项目。它还支持数据库“分支”。
You can test the application at http://www.gpu-grid.net/deltasql(if you login as admin with password testdbsync). The application is open source and can be downloaded here: http://sourceforge.net/projects/deltasql
您可以在http://www.gpu-grid.net/deltasql 上测试该应用程序（如果您以管理员身份登录并使用密码 testdbsync）。该应用程序是开源的，可以在这里下载：http: //sourceforge.net/projects/deltasql
deltasql is used productively in Switzerland and India, and is popular in Japan.
deltasql 在瑞士和印度被高效使用，在日本很流行。
回答by Shachar
We're exporting the data to a portable format (using our toolchain), then importing it to a new schema. no need for delta SQL. Highly recommended.
我们将数据导出为可移植格式（使用我们的工具链），然后将其导入新模式。不需要增量 SQL。强烈推荐。
