MySQL DDL Trigger, Diff table schema for column rename

ALTER TABLE table CHANGE COLUMN old_col_name new_col_name column_definition;

This changes the column name without losing the data in the column (unless you change the data type as well and the new data type is smaller than the old data type, e.g. changing BIGINT to SMALLINT).

This command unfortunately requires that you repeat the current definition of the column, e.g. data type, NOT NULL, and other options. There is no command in MySQL to simply rename a column.

The CHANGE COLUMN clause is a MySQL extension to standard SQL, which provides no syntax to rename a column in place.

Another solution if you want to stick to standard SQL is to add a new column, copy the data from the old column to the new column, and then drop the old column:

ALTER TABLE table ADD COLUMN new_col_name column_definition;UPDATE table SET new_col_name = old_col_name;ALTER TABLE table DROP COLUMN old_col_name;

The biggest issue here is that if more than 1 column name (of the same type) was changed for the same table there is NO way to tell which of the columns was changed to which. (There could still be the option of actually comparing the data and hope that some data is the same in both tables, that way be able to deduce which column what changed to what, but it would be completely un-reliable)

Than there's also the issue of 1 (or more) field changed and 1 (or more) added, you would be running on the same exact issue as above again.

MySQLDiff mentioned above would be able to tell you which fields are new and which are old (and more like new tables etc), but it won't be able to tell you which column name got changed to what and it doesn't even try to. (No software can reliably do that, especially if your data is not the same between db's)You can see what it does in: mysql-1.5.0/library/database.lib.php and mysql-1.5.0/library/generator.lib.php

One thing I would highly recommend though, is introducing a very simple process in your development team. - Every time a change to db structure is made (or any records that are needed for the system to work) should be recorded into a eg. db_changes.sql file which is under version control. For every new revision, if such changes to the db are needed then the db_changes.sql file is wiped, changes get recorded and saved as a new revision. That way changes to the database structure are revised properly and applying them is just a matter of diff-ing 2 revision points.(MysqlWorkbench is one of the clients that if you change tables it will show you the query it will internally run, if your devs need to use a client at all)

I don't think you should be synchronizing the schema this way. You should be checking migrations scripts to source code every time you change anything, then have a table in your database that logs which migration was applied. This way you can make sure that all the migrations were applied on all databases. for example `

CREATE TABLE `ddl_version` (version` varchar(32)  NOT NULL,`applied_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY `version` (`version`))`