MySQL表修改时间戳
我有一个使用测试数据库中的数据的测试服务器。 当我完成测试后，它会被转移到实时数据库中。
问题是，我还有其他项目依赖于现在生产中的数据，因此我必须运行一个脚本来从我需要的表中获取数据，删除测试数据库中的数据并从实时数据库中插入数据。
我一直在试图找到一种方法来改进这个模型。 问题不在于迁移，因为数据每周只更新一两次（我没有采取任何操作）。 问题是仅在需要时才进行迁移。 我希望我的迁移脚本包括对实时表和测试表的快速检查，并在需要时进行移动。 如果没有更新，脚本就会退出。
这样，我可以将更新脚本包含在其他脚本中，而不必担心数据是否同步。
我无法使用时间戳。 首先，一旦上线，我就无法控制现场的桌子，而且为了方便而增加桌子的数量似乎有点愚蠢。
我尝试执行“SHOW TABLE STATUS FROM livedb”，但是因为表都是InnoDB，所以没有“更新时间”，而且，“创建时间”似乎是今天早上，这让我相信数据库是支持的每天都会更新和重新创建。
表中是否还有其他属性可以显示这两个属性中哪一个较新？ 也许是“最新行日期”？
I have a test server that uses data from a test database. When I'm done testing, it gets moved to the live database.
The problem is, I have other projects that rely on the data now in production, so I have to run a script that grabs the data from the tables I need, deletes the data in the test DB and inserts the data from the live DB.
I have been trying to figure out a way to improve this model. The problem isn't so much in the migration, since the data only gets updated once or twice a week (without any action on my part). The problem is having the migration take place only when it needs to. I would like to have my migration script include a quick check against the live tables and the test tables and, if need be, make the move. If there haven't been updates, the script quits.
This way, I can include the update script in my other scripts and not have to worry if the data is in sync.
I can't use time stamps. For one, I have no control over the tables on the live side once it goes live, and also because it seems a bit silly to bulk up the tables more for conviencience.
I tried doing a "SHOW TABLE STATUS FROM livedb" but because the tables are all InnoDB, there is no "Update Time", plus, it appears that the "Create Time" was this morning, leading me to believe that the database is backed up and re-created daily.
Is there any other property in the table that would show which of the two is newer? A "Newest Row Date" perhaps?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
简而言之：使您的应用程序中的开发实时更新成为一流。 不要依赖数据库引擎为您提供必要的信息来让您做出决定（更新或不更新......这就是问题），只需将其作为你的申请。 否则，您将尝试将圆钉装入方孔中。
在不知道您的数据模型是什么，并且根本不了解您的同步模型是什么的情况下，您有几个选择：
长话短说：数据库同步非常困难。 实施特定于您的应用程序的解决方案。 不存在可以理想工作的“通用”解决方案。
In short: Make the development-live updating first-class in your application. Instead of depending on the database engine to supply you with the necessary information to enable you to make a decision (to update or not to update ... that is the question), just implement it as part of your application. Otherwise, you're trying to fit a round peg into a square hole.
Without knowing what your data model is, and without understanding at all what your synchronization model is, you have a few options:
Long story short: Database synchronization is very hard. Implement a solution which is specific to your application. There is no "generic" solution which will work ideally.
如果表中有自动增量，则可以比较最大自动增量值以查看它们是否不同。
但是你使用的是哪个版本的mysql？
If you have an autoincrement in your tables, you could compare the maximum autoincrement values to see if they're different.
But which version of mysql are you using?
您可以使用现有的解决方案来保持数据库同步，而不是自行推出。 我听说过有关 SQLYog 的 SJA 的好消息（请参阅此处）。 我自己从未使用过它，但他们的其他程序给我留下了深刻的印象。
Rather than rolling your own, you could use a preexisting solution for keeping databases in sync. I've heard good things about SQLYog's SJA (see here). I've never used it myself, but I've been very impressed with their other programs.