SQL 是否有用于数据库结构更改的版本控制系统？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/308/
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
Is there a version control system for database structure changes?
提问by EndangeredMassa
I often run into the following problem.
我经常遇到以下问题。
I work on some changes to a project that require new tables or columns in the database. I make the database modifications and continue my work. Usually, I remember to write down the changes so that they can be replicated on the live system. However, I don't always remember what I've changed and I don't always remember to write it down.
我正在对需要数据库中的新表或列的项目进行一些更改。我修改了数据库并继续我的工作。通常，我记得写下更改，以便它们可以在实时系统上复制。然而，我并不总是记得我改变了什么，我也不总是记得把它写下来。
So, I make a push to the live system and get a big, obvious error that there is no NewColumnX, ugh.
所以，我推动了实时系统并得到一个很大的明显错误，即没有NewColumnX，呃。
Regardless of the fact that this may not be the best practice for this situation, is there a version control system for databases? I don't care about the specific database technology. I just want to know if one exists. If it happens to work with MS SQL Server, then great.
尽管这可能不是这种情况的最佳实践，但是否有数据库版本控制系统？我不在乎具体的数据库技术。我只是想知道是否存在。如果它恰好适用于 MS SQL Server，那就太好了。
采纳答案by Kalid
In Ruby on Rails, there's a concept of a migration-- a quick script to change the database.
在 Ruby on Rails 中，有一个迁移的概念——一个用于更改数据库的快速脚本。
You generate a migration file, which has rules to increase the db version (such as adding a column) and rules to downgrade the version (such as removing a column). Each migration is numbered, and a table keeps track of your current db version.
您生成一个迁移文件，其中包含增加 db 版本的规则（例如添加列）和降级版本的规则（例如删除列）。每个迁移都有编号，并且有一个表跟踪您当前的数据库版本。
To migrate up, you run a command called "db:migrate" which looks at your version and applies the needed scripts. You can migrate down in a similar way.
要向上迁移，您需要运行一个名为“db:migrate”的命令，该命令查看您的版本并应用所需的脚本。您可以以类似的方式向下迁移。
The migration scripts themselves are kept in a version control system -- whenever you change the database you check in a new script, and any developer can apply it to bring their local db to the latest version.
迁移脚本本身保存在版本控制系统中——每当您更改数据库时，您都会签入新脚本，任何开发人员都可以应用它来将他们的本地数据库更新到最新版本。
回答by dar7yl
I'm a bit old-school, in that I use source files for creating the database. There are actually 2 files - project-database.sql and project-updates.sql - the first for the schema and persistant data, and the second for modifications. Of course, both are under source control.
我有点老派，因为我使用源文件来创建数据库。实际上有 2 个文件 - project-database.sql 和 project-updates.sql - 第一个用于模式和持久数据，第二个用于修改。当然，两者都在源代码控制之下。
When the database changes, I first update the main schema in project-database.sql, then copy the relevant info to the project-updates.sql, for instance ALTER TABLE statements. I can then apply the updates to the development database, test, iterate until done well. Then, check in files, test again, and apply to production.
当数据库发生变化时，我首先更新 project-database.sql 中的主架构，然后将相关信息复制到 project-updates.sql，例如 ALTER TABLE 语句。然后我可以将更新应用到开发数据库、测试、迭代直到完成。然后，签入文件，再次测试，并应用于生产。
Also, I usually have a table in the db - Config - such as:
另外，我通常在 db - Config 中有一个表 - 例如：
SQL
SQL
CREATE TABLE Config
(
cfg_tag VARCHAR(50),
cfg_value VARCHAR(100)
);
INSERT INTO Config(cfg_tag, cfg_value) VALUES
( 'db_version', '$Revision: $'),
( 'db_revision', '$Revision: $');
Then, I add the following to the update section:
然后，我将以下内容添加到更新部分：
UPDATE Config SET cfg_value='$Revision: $' WHERE cfg_tag='db_revision';
The db_versiononly gets changed when the database is recreated, and the db_revisiongives me an indication how far the db is off the baseline.
该db_version只得到当数据库被重新改变，并且db_revision给我的指示DB多远是关闭的基线。
I could keep the updates in their own separate files, but I chose to mash them all together and use cut&paste to extract relevant sections. A bit more housekeeping is in order, i.e., remove ':' from $Revision 1.1 $ to freeze them.
我可以将更新保存在它们自己的单独文件中，但我选择将它们混合在一起并使用剪切和粘贴来提取相关部分。更多的内务工作是为了，即，从 $Revision 1.1 $ 中删除 ':' 以冻结它们。
回答by Chadwick
MyBatis(formerly iBatis) has a schema migration, tool for use on the command line. It is written in java though can be used with any project.
MyBatis（以前的 iBatis）有一个schema 迁移，在命令行上使用的工具。它是用 java 编写的，但可以用于任何项目。
To achieve a good database change management practice, we need to identify a few key goals. Thus, the MyBatis Schema Migration System (or MyBatis Migrations for short) seeks to:
为了实现良好的数据库变更管理实践，我们需要确定几个关键目标。因此，MyBatis Schema Migration System（或简称 MyBatis Migrations）旨在：
- Work with any database, new or existing
- Leverage the source control system (e.g. Subversion)
- Enable concurrent developers or teams to work independently
- Allow conflicts very visible and easily manageable
- Allow for forward and backward migration (evolve, devolve respectively)
- Make the current status of the database easily accessible and comprehensible
- Enable migrations despite access privileges or bureaucracy
- Work with any methodology
- Encourages good, consistent practices
- 使用任何数据库，新的或现有的
- 利用源代码控制系统（例如 Subversion）
- 使并发开发人员或团队能够独立工作
- 允许冲突非常明显且易于管理
- 允许向前和向后迁移（分别进化、下放）
- 使数据库的当前状态易于访问和理解
- 尽管有访问权限或官僚主义，但仍可进行迁移
- 使用任何方法
- 鼓励良好、一致的做法
回答by EndangeredMassa
Redgate has a product called SQL Source Control. It integrates with TFS, SVN, SourceGear Vault, Vault Pro, Mercurial, Perforce, and Git.
Redgate 有一个名为SQL Source Control的产品。它与 TFS、SVN、SourceGear Vault、Vault Pro、Mercurial、Perforce 和 Git 集成。
回答by Karussell
I wonder that no one mentioned the open source tool liquibasewhich is Java based and should work for nearly every database which supports jdbc. Compared to rails it uses xml instead ruby to perform the schema changes. Although I dislike xml for domain specific languages the very cool advantage of xml is that liquibase knows how to roll back certain operations like
我想知道没有人提到基于 Java 的开源工具liquibase，它应该适用于几乎所有支持 jdbc 的数据库。与 Rails 相比，它使用 xml 而不是 ruby 来执行架构更改。虽然我不喜欢域特定语言的 xml，但 xml 的非常酷的优势是 liquibase 知道如何回滚某些操作，例如
<createTable tableName="USER">
<column name="firstname" type="varchar(255)"/>
</createTable>
So you don't need to handle this of your own
所以你不需要自己处理这个
Pure sql statements or data imports are also supported.
也支持纯 sql 语句或数据导入。
回答by Alex
I highly recommend SQL delta. I just use it to generate the diff scripts when i'm done coding my feature and check those scripts into my source control tool (Mercurial :))
我强烈推荐SQL delta。当我完成我的功能编码并将这些脚本检查到我的源代码控制工具（Mercurial :)）时，我只是使用它来生成差异脚本
They have both an SQL server & Oracle version.
他们有 SQL 服务器和 Oracle 版本。
回答by Ryan Fox
Most database engines should support dumping your database into a file. I know MySQL does, anyway. This will just be a text file, so you could submit that to Subversion, or whatever you use. It'd be easy to run a diff on the files too.
大多数数据库引擎应该支持将数据库转储到文件中。无论如何，我知道 MySQL 确实如此。这将只是一个文本文件，因此您可以将其提交给 Subversion 或任何您使用的文件。对文件运行差异也很容易。
回答by Perry Tribolet
If you're using SQL Server it would be hard to beat Data Dude (aka the Database Edition of Visual Studio). Once you get the hang of it, doing a schema compare between your source controlled version of the database and the version in production is a breeze. And with a click you can generate your diff DDL.
如果您使用的是 SQL Server，则很难击败 Data Dude（又名 Visual Studio 的数据库版）。一旦掌握了窍门，就可以轻而易举地在数据库的源代码控制版本和生产版本之间进行模式比较。只需单击一下，您就可以生成差异 DDL。
There's an instructional videoon MSDN that's very helpful.
MSDN 上有一个非常有帮助的教学视频。
I know about DBMS_METADATA and Toad, but if someone could come up with a Data Dude for Oracle then life would be really sweet.
我知道 DBMS_METADATA 和 Toad，但如果有人可以为 Oracle 设计一个 Data Dude，那么生活会非常甜蜜。
回答by Mark Harrison
For Oracle, I use Toad, which can dump a schema to a number of discrete files (e.g., one file per table). I have some scripts that manage this collection in Perforce, but I think it should be easily doable in just about any revision control system.
对于 Oracle，我使用Toad，它可以将模式转储到多个离散文件（例如，每个表一个文件）。我有一些脚本可以在 Perforce 中管理这个集合，但我认为它应该可以在任何版本控制系统中轻松实现。
回答by Matthew Watson
Have your initial create table statements in version controller, then add alter table statements, but never edit files, just more alter files ideally named sequentially, or even as a "change set", so you can find all the changes for a particular deployment.
在版本控制器中拥有初始的 create table 语句，然后添加 alter table 语句，但永远不要编辑文件，只需按顺序命名更多的 alter 文件，甚至作为“更改集”，这样您就可以找到特定部署的所有更改。
The hardiest part that I can see, is tracking dependencies, eg, for a particular deployment table B might need to be updated before table A.
我能看到的最难的部分是跟踪依赖关系，例如，对于特定的部署表 B 可能需要在表 A 之前更新。
