如何转储 MySQL 数据库的结构并将其导入以使用现有数据更改表？
首先提供一些背景信息，我的用例是我和我的合作伙伴正处于 Web 应用程序的早期阶段。数据库已经设计好了，但我们最终仍然要更改结构以满足我们的需求。因此，我们需要一种将结构更改来回传递到数据库的方法。我一直在尝试使用 git 来存储 mysqldump，以便我们可以来回传递所有数据（以及恢复错误），但由于我们的帖子主键冲突，我们遇到了重大冲突！如果有任何办法解决这个问题，我的其余问题将基本上没有意义。假设没有解决方案， 我需要某种方法来转储可以运行的结构，而无需从数据库表中删除所有现有数据。
我已经学会了如何使用 mysqdump -d 仅转储 MySQL 数据库的结构。但是，这包括 DROP 和 CREATE 命令。因此，运行此 SQL 脚本会导致我的数据库清空（因为每个表在创建之前都被删除）。我正在寻找一种方法来转储我的数据库结构，以便我可以导入它并且不会丢失我的数据。简而言之，我希望能够转储数据库并输出 ALTER 命令，或者达到类似效果的东西。
现在我已经了解了这个问题，似乎不太可能使用 ALTER 命令转储数据库。无论如何，有没有一种方法可以转储 MySQL 数据，以便可以在不删除现有数据的情况下更改表的结构？
多谢！
模范
To first give a little bit of context, my use-case is that my partner and I are in the early stages of a web application. The database has been designed, but we still end up making changes to the structure to suit our needs. We therefore need a way of passing back and forth structural changes to the database. I have been attempting to use git to store mysqldumps so that we can pass back and forth all data (as well as revert mistakes), but we run into major conflicts due to our posts having conflicting primary keys! If there is any way around this, the rest of my question will be mostly moot. Assuming there is no solution,
I need some way to dump structure that can be run without removing all of the existing data from the DB tables.
I have learned how to dump only the structure of a MySQL database using the mysqdump -d. However, this includes DROP and CREATE commands. Therefore, running this SQL script causes my database to empty (as each table is dropped before creation). I'm looking for a way to dump my database structure such that I can import it and NOT lose my data. In short, I want to be able to dump the database and output ALTER commands, or something to a similar effect.
Now that I've got this far in the question, it seems unlikely that it would be possible to dump a DB with ALTER commands. Regardless, is there a way to dump MySQL data such that it can change the structure of tables without removing the existing data?
Thanks a lot!
Paragon
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您已经有了答案——编写改变数据库结构的脚本，在 git 中对它们进行版本控制，并提出运行它们的流程。无论是谁更改结构，都应该在 sql 脚本中形式化这些更改。通常只是创建表或更改表 DDL。与任何其他类型的代码一样，应该测试这些更改。然后，对于你们中的任何一个人来说，从 git 获取最新所需的 update.sql 脚本，并通过命令行 mysql 或在工具中运行它就足够简单了。
有时仅仅更改表是不够的——您还需要执行转换数据的查询。我还将锁定相关表，以便在需要时可以针对生产环境运行这些查询。只需将脚本命名为 name.sql，并就如何命名它们以及将它们存储在树中的位置建立约定。它可以像 update_1.sql 一样简单，如果您已经对 DDL 进行了版本控制，则可以将它们粘贴到该目录中。
另一件事......大多数更改表更改不会影响数据库中的数据。在极少数情况下可能会出现问题，但您始终可以在脚本中编写解决方法，例如使用临时列或表，以及一些在转换过程中来回移动数据的 sql 语句。
有一些工具可以为您完成此类事情，但我不明白为什么在控制结构变化时需要它们。 PHP ORM Doctrine2 具有其中一些功能，并且我见过其他提供相同类型功能的产品...比较 2 个 db 并确定同步结构所需的结构更改和 DDL。当你们都试图独立工作时，这对你们没有帮助。
我的建议简单有效，并且已在我参与的许多项目中使用。
You already have your answer -- write the scripts that alter the database structure, version them in git, and come up with a process for running them. Whoever is changing the structure should formalize these changes in a sql script. Typically it's just going to be create table or alter table DDL. Like any other type of code these changes should be tested. Then it's simple enough for either one of you to get the latest required update.sql script from git, and run it via command line mysql or in a tool.
Sometimes just altering the table is not enough -- you also need to do queries that convert the data. I also will lock relevant tables so that these queries can be run against production if needed. Just name the scripts name.sql, and establish a convention for how you'll name them and where you'll store them in your tree. It could be as simple as update_1.sql If you already have been versioning DDL you can stick them in that directory.
One other thing ... most alter table changes do not effect the data in the database. On rare occasions there might be a problem, but you can always code up a workaround in the script like using a temporary column or table, with some sql statements that move the data back and forth during the translation.
There are tools out there that will do this type of thing for you, but I don't see why you would need them when you are controlling the structural changes. The PHP ORM Doctrine2 has some of these capabilities and I've seen other products that offer the same type of capability ... comparing 2 db's and determining the structural changes and DDL required to sync the structures. That won't be helpful to you when you are both trying to work independently.
What I suggested is simple and effective, and has been used on numerous projects I've been involved with.