blocks|key|3151145|text|看见|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3151146|Is+there+a+version+control+system+for+database+structure+changes?|offset|length|3151147|How+do+I+version+my+MS+SQL+database+in+SVN?|3151148|杰夫的文章|3151149|Get+Your+Database+Under+Version+Control|3151150|我能感受到你的痛苦，我希望有更好的答案。这可能更接近你要找的东西。|3151151|Mechanisms+for+tracking+DB+schema+changes|3151152|一般来说，我觉得没有足够的、被接受的解决方案来解决这个问题，我在这方面提出了自己的解决方案。|3151153|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/308/is-there-a-version-control-system-for-database-structure-changes|1|https://stackoverflow.com/questions/173/how-do-i-version-my-ms-sql-database-in-svn|2|http://blog.codinghorror.com/get-your-database-under-version-control/|3|https://stackoverflow.com/questions/1607/mechanisms-for-tracking-db-schema-changes#22535^0|0|0|1T|0|0|0|17|1|0|0|0|13|2|0|0|0|15|3|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|16|8|@]|9|@$D|17|E|18|1|19]]|A|$]]|$1|F|3|G|5|6|7|1A|8|@]|9|@$D|1B|E|1C|1|1D]]|A|$]]|$1|H|3|I|5|6|7|1E|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@$D|1G|E|1H|1|1I]]|A|$]]|$1|L|3|M|5|6|7|1J|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1K|8|@]|9|@$D|1L|E|1M|1|1N]]|A|$]]|$1|P|3|Q|5|6|7|1O|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y]]|Z|$5|U|V|W|A|$X|10]]|11|$5|U|V|W|A|$X|12]]|13|$5|U|V|W|A|$X|14]]]]

See

<a href="https://stackoverflow.com/questions/308/is-there-a-version-control-system-for-database-structure-changes">Is there a version control system for database structure changes?</a>

<a href="https://stackoverflow.com/questions/173/how-do-i-version-my-ms-sql-database-in-svn">How do I version my MS SQL database in SVN?</a>

and Jeff's article

<a href="http://blog.codinghorror.com/get-your-database-under-version-control/" rel="noreferrer">Get Your Database Under Version Control</a>

I feel your pain, and I wish there were a better answer. This might be closer to what you were looking for.

<a href="https://stackoverflow.com/questions/1607/mechanisms-for-tracking-db-schema-changes#22535">Mechanisms for tracking DB schema changes</a>

Generally, I feel there is no adequate, accepted solution to this, and I roll my own in this area.

blocks|key|3151166|text|我们将数据导出为可移植的格式(使用我们的工具链)，然后将其导入新的模式。不需要增量SQL。强烈推荐。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3151167|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|3151177|text|您可以看看另一个类似的主题：How+do+I+version+my+MS+SQL+database+in+SVN?。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3151178|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/173/how-do-i-version-my-ms-sql-database-in-svn^0|E|17|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You might take a look at another, similar thread: <a href="https://stackoverflow.com/questions/173/how-do-i-version-my-ms-sql-database-in-svn">How do I version my MS SQL database in SVN?</a>.

blocks|key|364446|text|我不管理deltas。我对主数据库进行了更改，并且有一个工具可以在主数据库的基础上创建基于XML的构建脚本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|364447|当需要升级现有数据库时，我有一个使用基于XML的构建脚本来创建新数据库和裸表的程序。然后我使用INSERT+INTO+x+SELECT+from+Y从旧数据库复制数据，然后应用所有索引、约束和触发器。|364448|新的表，新的列，删除的列都是自动处理的，通过一些小技巧来调整复制例程，我就可以处理列重命名，列类型更改和其他基本重构。|364449|我不建议在数据量很大的数据库上使用这种解决方案，但我会定期更新一个超过1+1GB的数据库，其中包含400个表。|364450|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

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.

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.

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.

blocks|key|3151296|text|您没有提到您使用的是哪种关系数据库管理系统，但如果是MS+SQL+Server，那么在创建对象创建脚本之间的增量时，Red-Gate的SQL+Compare是不可或缺的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3151297|entityMap|0|LINK|mutability|MUTABLE|url|http://www.red-gate.com/products/SQL_Compare/index.htm^0|1V|B|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You didn't mention which RDBMS you're using, but if it's MS SQL Server, Red-Gate's <a href="http://www.red-gate.com/products/SQL_Compare/index.htm" rel="nofollow noreferrer">SQL Compare</a> has been indispensable to us in creating deltas between object creation scripts.

blocks|key|3151299|text|我对这个话题也很感兴趣。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3151300|这里有some+discussions+on+this+topic+in+the+Django+wiki。|offset|length|3151301|有趣的是，它看起来像只使用cake+schema+generate命令的CakePHP+has+schema+versioning+built-in。|style|CODE|3151302|entityMap|0|LINK|mutability|MUTABLE|url|http://code.djangoproject.com/wiki/SchemaEvolution|1|http://book.cakephp.org/view/735/Generating-and-using-Schema-files^0|0|3|1D|0|0|D|K|10|12|1|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@$D|Z|E|10|H|I]]|9|@$D|11|E|12|1|13]]|A|$]]|$1|J|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]|R|$5|M|N|O|A|$P|S]]]]

I am interested in this topic too.

There are <a href="http://code.djangoproject.com/wiki/SchemaEvolution" rel="nofollow noreferrer">some discussions on this topic in the Django wiki</a>.

Interestingly, it looks like <a href="http://book.cakephp.org/view/735/Generating-and-using-Schema-files" rel="nofollow noreferrer">CakePHP has schema versioning built-in</a> using just <code>cake schema generate</code> command.

blocks|key|3151419|text|我使用Firebird数据库进行大多数开发，并使用FlameRobin管理工具进行开发。它有一个很好的选项来记录所有的更改。它可以将所有内容记录到一个大文件中，也可以记录每个数据库更改的一个文件。我使用第二个选项，然后将每个脚本存储在版本控制软件中-以前我使用Subversion，现在我使用Git。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3151420|我假设你能找到一些像FlameRobin为火鸟提供的日志记录功能一样的MySQL工具。|3151421|在其中一个数据库表中，我存储了数据库结构的版本号，因此我可以轻松地升级任何数据库。我还编写了一个简单的PHP脚本，在任何目标数据库上逐个执行这些SQL脚本(在命令行中提供了数据库路径和用户名/密码)。|3151422|还有一个选项可以记录所有的DML+(insert、update、delete)语句，我在修改每个数据库包含的一些“默认”数据时激活了这个选项。|3151423|我写了一份很好的白皮书，详细介绍了我是如何做到这一切的。您可以从here下载.pdf格式的论文和演示PHP脚本。|3151424|entityMap|0|LINK|mutability|MUTABLE|url|http://www.firebirdsql.org/|1|http://www.flamerobin.org/|2|http://sourceforge.net/project/showfiles.php?group_id=58913&package_id=212064^0|3|8|0|P|A|1|0|0|0|0|W|4|2|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@$A|Y|B|Z|1|10]|$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|15|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|16|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|17|8|@]|9|@$A|18|B|19|1|1A]]|C|$]]|$1|L|3|-4|5|6|7|1B|8|@]|9|@]|C|$]]]|M|$N|$5|O|P|Q|C|$R|S]]|T|$5|O|P|Q|C|$R|U]]|V|$5|O|P|Q|C|$R|W]]]]

I use <a href="http://www.firebirdsql.org" rel="nofollow noreferrer">Firebird</a> database for most development and I use <a href="http://www.flamerobin.org" rel="nofollow noreferrer">FlameRobin</a> administration tool for it. It has a nice option to log all changes. It can log everything to a one big file, or one file per database change. I use this second option, and then I store each script in version control software - earlier I used Subversion, now I use Git. 

I assume you can find some MySQL tool that has the same logging feature like FlameRobin does for Firebird.

In one of database tables, I store the version number of the database structure, so I can upgrade any database easily. I also wrote a simple PHP script that executes those SQL scripts one by one on any target database (database path and username/password are supplied on the command line).

There's also an option to log all DML (insert, update delete) statements, and I activate this while modifying some 'default' data that each database contains.

I wrote a nice white paper on how I do all this in detail. You can download the paper in .pdf format along with demo PHP scripts from <a href="http://sourceforge.net/project/showfiles.php?group_id=58913&amp;package_id=212064" rel="nofollow noreferrer">here</a>.

blocks|key|3151439|text|我不是一个自命不凡的人，但我已经开发了一个内部web应用程序来跟踪对数据库模式的更改并创建版本化的更新脚本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3151440|这个工具叫做Brazil，现在在麻省理工学院的许可下是开源的。巴西基于ruby+/+ruby+on+rails，支持对Ruby+DBI支持的任何数据库(MySQL、ODBC、Oracle、Postgres、SQLite)进行更改部署。|offset|length|3151441|计划支持将更新脚本放入版本控制中。|3151442|entityMap|0|LINK|mutability|MUTABLE|url|http://github.com/jbripley/brazil/tree/master|1|http://ruby-dbi.rubyforge.org/^0|0|6|6|0|1N|8|1|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]|$D|W|E|X|1|Y]]|A|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]|P|$5|K|L|M|A|$N|Q]]]]

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 <a href="http://github.com/jbripley/brazil/tree/master" rel="nofollow noreferrer">Brazil</a> and is now open source under a MIT license. Brazil is ruby / ruby on rails based and supports change deployment to any database that <a href="http://ruby-dbi.rubyforge.org/" rel="nofollow noreferrer">Ruby DBI</a> supports (MySQL, ODBC, Oracle, Postgres, SQLite).

Support for putting the update scripts in version control is planned.

blocks|key|3364379|text|我还开发了一组PHP脚本，开发人员可以将他们的deltasql脚本提交到中央存储库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3364380|在其中一个数据库表(称为TBSYNCHRONIZE)中，我存储了最近执行的脚本的版本号，因此我可以使用web界面或专门为Eclipse开发的客户端轻松地升级任何数据库。|3364381|web界面允许管理多个项目。它还支持数据库“分支”。|3364382|您可以在http://www.gpu-grid.net/deltasql上测试应用程序(如果您以管理员身份登录，密码为testdbsync)。该应用程序是开源的，可在此处下载：http://sourceforge.net/projects/deltasql|offset|length|3364383|deltasql在瑞士和印度的使用效率很高，在日本也很流行。|3364384|entityMap|0|LINK|mutability|MUTABLE|url|http://www.gpu-grid.net/deltasql|1|http://sourceforge.net/projects/deltasql^0|0|0|0|4|W|0|2G|14|1|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@$H|Z|I|10|1|11]|$H|12|I|13|1|14]]|A|$]]|$1|J|3|K|5|6|7|15|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]|T|$5|O|P|Q|A|$R|U]]]]

I also developed a set of PHP scripts where developers can submit their deltasql scripts to a central repository.

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. 

The web interface allows to manage several projects. It supports also database "branches".

You can test the application at <a href="http://www.gpu-grid.net/deltasql" rel="nofollow noreferrer">http://www.gpu-grid.net/deltasql</a> (if you login as admin with password testdbsync).
The application is open source and can be downloaded here:
<a href="http://sourceforge.net/projects/deltasql" rel="nofollow noreferrer">http://sourceforge.net/projects/deltasql</a>

deltasql is used productively in Switzerland and India, and is popular in Japan.

blocks|key|3151490|text|http://bitbucket.org/idler/mmp+-用于mysql的模式版本控制工具|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3151491|entityMap|0|LINK|mutability|MUTABLE|url|http://bitbucket.org/idler/mmp^0|0|U|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

<a href="http://bitbucket.org/idler/mmp" rel="nofollow noreferrer">http://bitbucket.org/idler/mmp</a> - schema versioning tool for mysql, writed in PHP

blocks|key|361251|text|几个月前，我搜索了一个版本控制MySQL模式的工具。我发现了很多有用的工具，比如Doctrine迁移，RoR迁移，还有一些用Java和Python编写的工具。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|361252|但他们中没有一个人能满足我的要求。|361253|我的要求：|361254|361255|无要求，排除PHP和MySQL+|ordered-list-item|361256|无架构配置文件，如Doctrine|361257|中的schema.yml，能够从连接中读取当前架构并创建新的迁移脚本，而不是在其他应用程序安装中表示相同的架构。|361258|361259|我开始编写我的迁移工具，今天我有了测试版。|361260|如果你对这个话题感兴趣，请尝试一下。请向我发送未来的请求和bugreports。|361261|源码:+bitbucket.org/idler/mmp/src英文概述:bitbucket.org/idler/mmp/wiki/主页俄语概述:+antonoff.info/development/mysql-migration-with-php-project|361262|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|10|8|@]|9|@]|A|$]]|$1|J|3|K|5|I|7|11|8|@]|9|@]|A|$]]|$1|L|3|M|5|I|7|12|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|V|$]]

Some months ago I searched tool for versioning MySQL schema. I found many useful tools, like Doctrine migration, RoR migration, some tools writen in Java and Python.

But no one of them was satisfied my requirements.

My requirements:

<ol>
<li>No requirements , exclude PHP and MySQL </li>
<li>No schema configuration files, like schema.yml in Doctrine</li>
<li>Able to read current schema from connection and create new migration script, than represent identical schema in other installations of application.</li>
</ol>

I started to write my migration tool, and today I have beta version.

Please, try it, if you have an interest in this topic.
Please send me future requests and bugreports.

Source code: bitbucket.org/idler/mmp/src
Overview in English: bitbucket.org/idler/mmp/wiki/Home
Overview in Russian: antonoff.info/development/mysql-migration-with-php-project

blocks|key|362003|text|如果你还在寻找选择:看看neXtep+designer。它是一个基于版本控制概念的免费GPL数据库开发环境。在该环境中，您始终使用版本化的实体，并且可以专注于数据模型开发。一旦发布完成，插在版本控制系统上的SQL生成引擎可以在两个版本之间生成您需要的任何增量，并将在您需要时为您提供一些交付机制。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|362004|其中，您可以在开发期间同步和反向同步您的数据库，创建数据模型图，使用集成的SQL客户端查询您的数据库等。|362005|有关更多信息，请查看维基：http://www.nextep-softwares.com/wiki|offset|length|362006|它目前支持Oracle、MySql和PostgreSql，而且是java版本，因此该产品可以在windows、linux和mac上运行。|362007|entityMap|0|LINK|mutability|MUTABLE|url|http://www.nextep-softwares.com/wiki^0|0|0|D|10|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$F|U|G|V|1|W]]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

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.

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.

Have a look at the wiki for more information : 
<a href="http://www.nextep-softwares.com/wiki" rel="nofollow">http://www.nextep-softwares.com/wiki</a>

It currently supports Oracle, MySql and PostgreSql and is in java so the product runs on windows, linux and mac.

blocks|key|364568|text|我使用http://code.google.com/p/oracle-ddl2svn/|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|364569|entityMap|0|LINK|mutability|MUTABLE|url|http://code.google.com/p/oracle-ddl2svn/^0|3|14|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

I use <a href="http://code.google.com/p/oracle-ddl2svn/" rel="nofollow">http://code.google.com/p/oracle-ddl2svn/</a>

blocks|key|364593|text|我使用了严格的数据库模式版本控制(在一个单独的表中跟踪)。脚本存储在版本控制中，但在进行任何更改之前，它们都会验证当前的架构版本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|364594|下面是SQL+Server的完整实现(如果需要，也可以为MySQL开发相同的解决方案)：How+to+Maintain+SQL+Server+Database+Schema+Version|offset|length|364595|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codinghelmet.com/?path=howto/sql-schema-versioning^0|0|18|1E|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

I am using strict versioning of the database schema (tracked in a separate table). Scripts are stored in version control, but they all verify current schema version before making any change.

Here is the full implementation for SQL Server (the same solution could be developed for MySQL if needed): <a href="http://www.codinghelmet.com/?path=howto/sql-schema-versioning" rel="nofollow">How to Maintain SQL Server Database Schema Version</a>

blocks|key|364606|text|我确保模式更改始终是累加的。因此，我不会删除列和表，因为这会使数据大量涌现，并且不能在以后回滚。这样，使用数据库的代码可以回滚，而不会丢失数据或功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|364607|我有一个迁移脚本，其中包含一些语句，如果表和列还不存在，则创建它们并用数据填充它们。|364608|每当更新生产代码并在新安装之后，迁移脚本都会运行。|364609|当我想要删除某些内容时，我会将它们从数据库安装脚本和迁移脚本中删除，以便这些过时的模式元素在新的安装中逐渐被淘汰。缺点是新安装程序不能在安装之前降级到较旧的版本。|364610|当然，我通过这些脚本执行DDL，而不是直接在数据库上执行，以保持同步。|364611|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

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.

blocks|key|3151724|text|对于MySQL|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3151725|当我登陆一个新的数据库时：|3151726|首先，我检查结构：|3151727|3151728|+mysqldump+--无数据--跳过注释--跳过-扩展-插入-h+__DB_HOSTNAME__+-u+__DB_USERNAME__+-p+__DB1_NAME__+%7C+sed+'s/+AUTO_INCREMENT=0-9*//g‘>+FILENAME_1.sql+mysqldump+--无数据--跳过注释--跳过-扩展-插入-h+__DB_HOSTNAME__+-u+__DB_USERNAME__+-p+__DB2_NAME__+%7C+sedS/+AUTO_INCREMENT=0-9*//g‘>+FILENAME_2.sql+diff+FILENAME_1.sql+FILENAME_2.sql+>+DIFF_FILENAME.txt+cat+DIFF_FILENAME.txt+%7C+less|blockquote|3151729|3151730|感谢stackoverflow用户，我可以编写这个快速脚本来查找结构差异。|3151731|源码：和|3151732|3151733|在第二步中，我使用mysqldiff逐表检查数据。它有点过时，但基于information_schema数据的php循环使工作变得可靠|offset|length|style|CODE|3151734|对于版本控制，我使用相同的方法，但我使用不同的结果格式化SQL更新脚本(用于升级或回滚)，并使用版本号约定(经过几次修改，版本号看起来像ip地址)。|3151735|initial+version+:+1.0.0
++++++++++++++++++%5E+%5E+%5E
++++++++++++++++++%7C+%7C+%7C
structure+change:+-+%7C+%7C
datas+added:+--------+%7C
datas+updated:+--------|code-block|syntax|javascript|3151736|entityMap^0|0|0|0|0|0|0|0|0|0|9|9|Y|I|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|17|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|18|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1A|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1B|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1D|8|@$R|1E|S|1F|T|U]|$R|1G|S|1H|T|U]]|9|@]|A|$]]|$1|V|3|W|5|6|7|1I|8|@]|9|@]|A|$]]|$1|X|3|Y|5|Z|7|1J|8|@]|9|@]|A|$10|11]]|$1|12|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|13|$]]

<h2>For MySQL</h2>

When I land on a new DB:

Firstly, I check structure:

<blockquote>
<pre><code>mysqldump --no-data --skip-comments --skip-extended-insert -h __DB_HOSTNAME__ -u __DB_USERNAME__ -p __DB1_NAME__ | sed 's/ AUTO_INCREMENT=[0-9]*//g' &gt; FILENAME_1.sql
mysqldump --no-data --skip-comments --skip-extended-insert -h __DB_HOSTNAME__ -u __DB_USERNAME__ -p __DB2_NAME__ | sed 's/ AUTO_INCREMENT=[0-9]*//g' &gt; FILENAME_2.sql
diff FILENAME_1.sql FILENAME_2.sql &gt; DIFF_FILENAME.txt
cat DIFF_FILENAME.txt | less
</code></pre>
 
 Thanks to stackoverflow users I could write this quick script to find structure differences.
 
 src : <a href="https://stackoverflow.com/a/8718572/4457531">https://stackoverflow.com/a/8718572/4457531</a> &amp; <a href="https://stackoverflow.com/a/26328331/4457531">https://stackoverflow.com/a/26328331/4457531</a>
</blockquote>

In a second step, I check datas, table by table with <code>mysqldiff</code>. It's a bit archaic but a php loop based on <code>information_schema</code> datas make job surely

For versioning, I use the same way but I format a SQL update script (to upgrade or rollback) with diff results and I use version number convention (with several modifications the version number look like an ip address).

<pre><code>initial version : 1.0.0
 ^ ^ ^
 | | |
structure change: - | |
datas added: -------- |
datas updated: --------
</code></pre>

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?

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 <a href="http://dbdeploy.com/" rel="noreferrer">DbDeploy</a>?

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

How do you version your database schema?

数据库管理系统

版本控制系统

数据库管理

SQL Server

数据库

Eclipse

Python

SQLite

MySQL

Java

如何准备SQL增量？您是手动将每个更改模式的SQL保存到增量文件夹中，还是使用某种自动的差异过程？我对版本控制数据库模式的约定以及源代码很感兴趣。也许是一个不同于模式的预提交钩子？此外，除了之外，还有哪些用于区分增量的选项EDIT:看到答案后，我想澄清一下，我熟悉使用增量运行数据库迁移的标准方案。我的问题是关于创建增量...

问您的数据库架构是如何版本的？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问您的数据库架构是如何版本的？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问您的数据库架构是如何版本的？
EN