只有mysqldump模式，模式更新没有丢失 [英] mysqldump schema only, schema update without drop

问题描述

我正在寻找使用git pre-commit hook在提交更改之前导出MySQL数据库模式，以便其他开发人员可以使用git repo中的SQL脚本更新自己的数据库。

默认情况下，mysqldump（我使用--no-data）会在重建之前删除现有的表，这不是我所追求的。我想知道是否有人知道如何使用mysqldump或类似的方法来描述db模式与SQL来更新表，如果它们存在，而不是丢弃和重建。我意识到这可能是一个很长的一步，但如果任何人都可以指向正确的方向，这将是很好的。

MySQL知道要更新什么？它无法知道别人的数据库将处于什么状态，因此无法知道要应用哪些更新。此外，更新通常不仅仅需要更改架构;他们可能需要对已存在的数据进行更改，或者可能需要将数据从旧模式移动到新模式。你永远不会得到能自动检测所有内容的东西。

正确的解决方案是编写迁移。每次更改数据库时，都不要只是自己更改开发副本的模式，而是编写一个脚本，以便从前一个模式更新为新模式（并且通常是一个脚本以降级回先前的模式，因此您可以执行如果你需要回滚更新）。这个脚本可以做任何你需要改变模式，移动数据等等。

大多数现代web框架，像 Ruby on Rails 支持迁移，以便更轻松地跟踪您已经运行的迁移。如果您没有使用支持迁移的框架，那么编写您自己的脚本来应用迁移并不算太难。只需为每个迁移编号，或者在其中添加一个日期，并在数据库中保留一张仅存储当前版本的架构的表。当您运行迁移脚本时，如果有任何迁移比当前版本更新，请按顺序应用这些脚本，然后更新数据库中的数字以说明您的版本。

I'm looking at using the git pre-commit hook to export a MySQL db schema prior to commiting changes so that other developers can update their own databases with a SQL script from the git repo.

By default a mysqldump (I'm using --no-data) will drop existing tables before rebuilding them which isn't what I'm after. I'm wondering if anyone knows of a way to do a mysqldump or similar to describe the db schemas with SQL to update tables if they exists instead of a drop and rebuild. I realize this might be a long shot but if anyone could point me in the right direction it would be great.

解决方案

How would MySQL know what to update? It can't know what state someone else's database will be in, so it can't know what updates to apply. Also, updates frequently require more than just changing the schema; they might require changes to data that already exists, or might need data to be moved from the old schema to the new one. You will never get something which will automatically detect all of that.

The right solution is to write migrations. Every time you change the database, instead of just changing the schema of your development copy yourself, you write a script to update from the previous schema to the new one (and usually a script to downgrade back to the previous schema, so you can do that if you need to roll back an update). That script does anything you need to change the schema, move data around, and the like.

Most modern web frameworks, like Ruby on Rails, have support for migrations to make it easier to keep track of what migrations you've already run. If you're not using a framework that supports migrations, it wouldn't be too hart to write your own scripts for applying migrations. Just number each migration, or put a date in it, and keep a table in your database storing only the current version of the schema that you're on. When you run your migrate script, if there are any migrations newer than that current version, apply those scripts in order, and then update the number in the database that says what version you're on.

这篇关于只有mysqldump模式，模式更新没有丢失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！