如何为 PHP/MySQL 应用程序自动迁移（架构和数据）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/825787/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to automate migration (schema and data) for PHP/MySQL application
提问by Sabya
I have an application in PHP/MySQL. I am searching for an automated way upgrading database behind the application. I don't need to have the compatibility with older versions once it is upgraded.
我在 PHP/MySQL 中有一个应用程序。我正在寻找一种自动方式升级应用程序背后的数据库。升级后，我不需要与旧版本兼容。
I have read jeff'sand K. Scott Allen'sarticles on this.
我已经阅读了jeff和K. Scott Allen 的文章。
I am still not sure how to implement this for a PHP/MySQL application.
我仍然不确定如何为 PHP/MySQL 应用程序实现这一点。
Is there any simple and good process for this?
有什么简单而好的过程吗？
采纳答案by gnarf
I have a "Schema" object that I use - but you could do the same without classes..
我有一个我使用的“架构”对象 - 但你可以在没有类的情况下做同样的事情..
What you want to do is create a 'db_schema_versions' table:
您要做的是创建一个“ db_schema_versions”表：
CREATE TABLE db_schema_versions (
`table` varchar(255) NOT NULL PRIMARY KEY,
`version` INT NOT NULL
)
After your database can track what version # it is on - it can do SQL upgrades automatically.
在您的数据库可以跟踪它所在的版本后，它可以自动进行 SQL 升级。
You should lock your schema table while upgrading schema. This way you wont have two requests at the same moment trying to upgrade your schema.
您应该在升级架构时锁定架构表。这样您就不会同时有两个请求尝试升级您的架构。
So - keep track of the version you are upgrading from - build a big switch - something like this:
所以 - 跟踪你要升级的版本 - 构建一个大开关 - 像这样：
class SNTrack_Db_Schema extends MW_Db_Schema_Abstract {
protected $table = "sntrack_db_schema";
protected $version = 5;
protected function upgrade($fromVersion) {
// don't break
switch($fromVersion) {
case 0:
$this->db->query('CREATE TABLE sntrack_inbound_shipment (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`from` VARCHAR(255) NOT NULL,
`date` DATE NOT NULL,
`invoice` VARCHAR(255) NOT NULL,
`notes` TEXT
)');
$this->setVersion(1);
case 1:
$this->db->query('ALTER TABLE sntrack_details ADD `shipment_id` INT');
$this->db->query('ALTER TABLE sntrack_product ADD `inventory` INT NOT NULL DEFAULT 0');
$this->db->query('CREATE TABLE sntrack_inventory_shipment (
`shipment_id` INT NOT NULL,
`product_id` INT NOT NULL,
`qty` INT NOT NULL,
PRIMARY KEY (`shipment_id`, `product_id`)
)');
$this->setVersion(2);
...etc
回答by James C
Similar to gnarf's suggestion I would run with the following:
与 gnarf 的建议类似，我将使用以下命令：
- For every change to the schema create an SQL file that when run will take you from the old version to the new version (this could be one file per major version or lots of smaller changes).
- Create a separate file listing each of the SQL file names in the order they must be applied in (oldest at the top, newest at the bottom)
- Create a simple "versioning" table (all it needs is a single VARCHAR column) in your database
- 对于架构的每次更改，创建一个 SQL 文件，该文件在运行时会将您从旧版本带到新版本（这可能是每个主要版本一个文件或许多较小的更改）。
- 创建一个单独的文件，按照必须应用的顺序列出每个 SQL 文件名（最旧的在顶部，最新的在底部）
- 在您的数据库中创建一个简单的“版本控制”表（它只需要一个 VARCHAR 列）
Now you need to write a simple script that works in the following way:
现在您需要编写一个简单的脚本，其工作方式如下：
- Query the versioning table for the name of the last SQL update file applied
- If there are newer SQL alteration files to be run execute them in sequence
- record the name of the newest SQL file applied
- 在版本控制表中查询最后应用的 SQL 更新文件的名称
- 如果有更新的 SQL 更改文件要运行，请按顺序执行它们
- 记录最新应用的 SQL 文件的名称
I hope that makes sense
我希望这是有道理的
回答by Maxim Antonov
Try to use this tool for schema migration: https://github.com/idler/MMP/
尝试使用此工具进行模式迁移：https: //github.com/idler/MMP/
回答by Alex Kennberg
I created a tiny migration script for MySQL in PHP. It's good for early-stage projects and those not needing (yet) the more complex migration scripts. https://github.com/kennberg/php-mysql-migrate
我在 PHP 中为 MySQL 创建了一个很小的迁移脚本。它适用于早期项目和那些不需要（还）更复杂的迁移脚本的项目。https://github.com/kennberg/php-mysql-migrate
回答by user1122069
You could also use a free API SqlQuerySync
您还可以使用免费的 API SqlQuerySync
or create for yourself a database table managing CREATE / ALTER / DELETE queries.
或为自己创建一个管理 CREATE / ALTER / DELETE 查询的数据库表。
回答by Alexandre Adolfo
Use migratedb https://github.com/malukenho/MigrateDBfrom @malukenho
使用 migratedb https://github.com/malukenho/MigrateDB来自 @malukenho
回答by Rolf
MySQL bench software (could be found on the mysql website) does that. It involves repetitive clicky steps though.
MySQL bench 软件（可以在 mysql 网站上找到）做到这一点。不过，它涉及重复的点击步骤。
回答by Ousmane
I got the same goal : migrating a big database (more than a million lines in some tables). I am considering to use https://phinx.orgwich seems good to deal with the schema migration, in addition it comes with rollback option for safety.
我有同样的目标：迁移一个大数据库（在某些表中超过一百万行）。我正在考虑使用https://phinx.org ，这似乎可以很好地处理架构迁移，此外它还带有回滚选项以确保安全。
回答by Joe Green
You could try this library out: mysql-version-control.
你可以试试这个库：mysql-version-control。
I like this one because it differentiates between schema, core data and test data. But does it in a way that's still really easy to use.
我喜欢这个，因为它区分了模式、核心数据和测试数据。但是它的方式仍然非常易于使用。
回答by Artjom Kurapov
You can't. You either
你不能。你要么
Write update files with all sql's that were executed on source enviroment and then execute them (mentioned above), SVN-like. Need php script for execution and manual work for sql writing
Post-analyze both enviroments and suggest to user which updates should be migrated. Basically same thing as the first one, except that you have one big step for migration, not lots of small chunks. SQLyog can analyze diffrences for both schema and data.
使用在源环境中执行的所有 sql 编写更新文件，然后执行它们（如上所述），类似于 SVN。需要 php 脚本来执行和手动工作来编写 sql
对两个环境进行事后分析，并向用户建议应迁移哪些更新。与第一个基本相同，除了你有一个很大的迁移步骤，而不是很多小块。SQLyog 可以分析模式和数据的差异。
