MySQL模式到模式通过触发器同步？
快速说明：我有 19 天的时间来解决客户的问题。
背景： 客户雇用了一名承包商，他吹嘘自己可以在 3 个月内推出新的应用程序。两个月又几天后，我被带进来，而那个人被解雇了；没有完整的代码，没有对模式进行思考，并且对 UI 感到厌恶。
我有两个应用程序：一个是已生产且成熟的应用程序，另一个需要一些关爱。一个拥有我需要的所有数据，而另一个则没有。我正在编写 TDD 风格的新代码，目标是建立一个部分临时操纵的 SOA 基础设施，涵盖除数据本身之外的所有问题。如果我有更多时间，我可以使用 liquibase 将架构重构为令人厌恶的碎片（发挥你的想象力），但我不......所以计划 B 如下：
应用程序 A（插入|更新|删除）更新 AppASchema 的实体 Foo .FooTable 通过后触发器更新 AppBSchema.FooLikeTable，反之亦然。
我知道这是一个疯狂的想法，但这是我所得到的最糟糕的想法中最不重要的一个，我担心的是
- 可能会创建一个无限循环（AppA触发器更新AppB，后者更新AppA）
- 负载不高，但这基本上使操作次数增加了一倍到 n*2 因此，如果我达到 MySQL 服务器容量的一半，那么对于更新索引等基本内容来说，似乎会有效地达到或接近满容量。
- 喜忧参半的是，最初的模式设计者将所有表都做成了 InnoDB 引擎……这对性能来说很糟糕，但这种设置能否确保更高的保持完整性的机会。
我实施触发器的时间预算是 12 小时，否则就完蛋了。
Quick note: I have 19 days to figure my client's problems out.
Background:
Client hired a contractor who boasted he could get a new App out the door in 3 months. Two months and some days later I'm brought in and the individual was let go; there is no complete code, no thought put into the schema, and an abomination for a UI.
I have two applications: one production and mature and the other that needs some love. One has all the data I need and the other that doesn't. I'm writing new code TDD style and aiming for a partially jury rigged SOA infrastructure that covers all issues but the data itself. If I had more time I could use liquibase to refactor the schemas into abomination shards ( use your imagination ) but I don't...so plan B is as follows:
App A (inserts|updates|deletes) entity Foo which updates AppASchema.FooTable which via a post trigger updates the AppBSchema.FooLikeTable and vice versa.
I know this is an insane idea but its the least of the worst ideas I've got, my concerns are
- Possible to create an endless loop (AppA trigger updates AppB which updates AppA )
- There isn't high load, but this basically doubles ops to n*2 so if I hit half the capacity of the MySQL server, it seems like would be effectively at or near full capacity for basic stuff like updating indexes and such.
- As a mixed blessing, the original schema designers made all tables InnoDB engine... thats horrible for performance but could this setup ensure a higher chance of keeping integrity.
My time budget to implement the triggers is 12 hours or bust.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
AppASchema.FooTable 和 AppBSchema.FooLikeTable 是否足够相似，您可以将其中一个重新实现为 可更新视图？您可能需要创建一些额外的表来保存应用程序架构之一所特有的列。这比一堆触发器更易于维护。
如果没有，并且您必须使用触发器来实现它，那么您必须非常小心以确保不存在递归触发器依赖性，这是对的。如果有几张表并且它们非常相似，那么这不会太困难。如果表格很多或者相似之处很少，那就需要时间。
Are AppASchema.FooTable and AppBSchema.FooLikeTable similar enough that you could reimplement one of them as an updatable view? You might have to create a few extra tables to hold columns that are unique to one of the app schemata. This would be much more maintainable than a bunch of triggers.
If not, and you have to implement it using triggers, you are right that you will have to be very careful to be sure there are no recursive trigger dependencies. If there are a handful of tables and they are fairly similar, this won't be too difficult. If there are a lot of tables or the similarities are few, it's going to take time.