why not accumulate changes in the development scheme in updating the script and just run the script in the next version.
There are really different tools for comparing schemas, but, in my opinion, they should be used only to check if the script update is correct, and not to create a script.
And at release, you should commit a script that generates a new schema and update the script as empty for the version control system.
Suppose your schema is:
-- schema.sql CREATE TABLE t1 ( `t_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `t_data` VARCHAR(45) NOT NULL, PRIMARY KEY (`t_id`) ) ENGINE = InnoDB COLLATE utf8_feneral_ci;
Commit 1
Now you have a lot of data in your table in production with a lot of duplicates, and you would like to do some normalization:
- puts individual data in a separate table
- use the link in table t1:
Scenarios:
-- updates.sql CREATE TABLE t2 ( `d_hash` CHAR(32) NOT NULL COLLATE ascii_general_ci, `t_data` VARCHAR(45) NOT NULL, PRIMARY KEY (`d_hash`) ) ENGINE = InnoDB COLLATE utf8_general_ci; ALTER TABLE t1 ADD COLUMN `d_hash` CHAR(32)COLLATE ascii_general_ci AFTER `t_data`; UPDATE t1 SET d_hash = MD5(UPPER(t_data)); INSERT IGNORE INTO t2 (t_data, d_hash) SELECT t_data, d_hash FROM t1; ALTER TABLE t1 DROP COLUMN `t_data`, MODIFY COLUMN `d_hash` CHAR(32) COLLATE ascii_general_ci NOT NULL, ADD CONSTRAINT `FK_d_hash` FOREIGN KEY `FK_d_hash` (`d_hash`) REFERENCES `t2` (`d_hash`) ON DELETE CASCADE ON UPDATE CASCADE;
Commit 2
Release
-- schema.sql CREATE TABLE t1 ( `t_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `d_hash` CHAR(32) COLLATE ascii_general_ci NOT NULL, PRIMARY KEY (`t_id`), KEY `FK_d_hash` (`d_hash`), CONSTRAINT `FK_d_hash` FOREIGN KEY (`d_hash`) REFERENCES `t2` (`d_hash`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB COLLATE utf8_general_ci; -- updates.sql -- Empty
Commit 3
I would like to see a comparison tool that will allow you to make this a lot easier.