MySQL的事务DDL工作流 [英] Transactional DDL workflow for MySQL

问题描述

我有点惊讶地发现，DDL语句（ alter table ， create index 等） MySQL中的当前事务。来自MS SQL Server，在本地执行事务中的数据库更改（然后回滚）的能力是我的工作流的一个重要部分。对于持续集成，如果迁移因任何原因而暂停，则使用回滚，因此至少我们没有将数据库保留在半迁移状态。

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database alterations in a transaction locally (that was then rolled back) was an important part of my workflow. For continuous integration, the rollback was used if the migration hiccuped for any reason, so that at least we did not leave the database in a half-migrated state.

如何人们在使用MySQL迁移和持续集成时解决了这两个问题？

How do people solve these two problems when using MySQL with migrations and continuous integration?

推荐答案

一种可能性是在非破坏性-manner，其中包括：

One possibility is doing DDL changes in a non-destructive-manner, which would include:

• 在DDL / DCL中拆分逻辑（+1反转所有）和DML

$ b


• on


• 成功，应用DML更改，


• 失败，应用反向DDL / DCL脚本删除您想在第二步添加的东西（显然有一些错误不存在取决于如何far step 1 got）


• split logic in DDL/DCL (+1 to reverse all) and DML
• run only the DDL/DCL script adding columns, new tables, ..
• depending on result:
  • on success, apply the DML changes,
  • on fail, apply reverse DDL/DCL script removing the stuff you wanted to add in second step (obviously with some errors "does not exist" depending on how far step 1 got)

  这篇关于MySQL的事务DDL工作流的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！