In Mysql conditionally apply ALTER statements

I have a mysql database that is under somewhat active development. It wou开发者_开发问答ld be nice to have an alter.sql file with a list of changes made since it was created. Is there a way to conditionally apply ALTER statements?

One idea I have would be to use the reverse engineer feature of MySQL Workbench. That option is found on the Database menu. You could use it to generate a script for your DB at some point in time. Then later, you could run it again to generate another script. You could diff these 2 scripts to see what has changed.

One tool I really like for diffing files is DiffMerge.

But I don't know of a way to automatically generate such scripts to give you the changes, I think you'll have to use some combination of tools and fiddle a bit.

Note, you can read more about reverse engineering MySQL DB's here.

EDIT The other thing you might be able to do is write a stored proc which runs dynamic SQL to alter the table and add the required columns, and just ignores any errors if column already exists. I'm not sure if this is possible in MySQL since I don't use it that often.

In general though, you usually need to keep up with scripts that modify the DB. The pattern we always follow in my shop is that we have Dev, QA, UAT and Prod environments. When promoting to QA or UAT, you are expected to have the required DB scripts which modify the tables, etc. to bring the DB to the current level to support whatever code changes you are making.

If you're talking about adding columns, you can execute the statement and allow it to fail if the column already exists. For anything more complicated, you'd probably have to roll your own script. If you're looking at this database as part of a web app, a common approach is to use migrations which store database changes along with code.

You start with the full schema at some version of the app, and when code changes require a database change, any SQL commands required to change the database into it's new state are stored along with the code (often along with an additional statement which will revert the changes if necessary).

Have a look at Liquibase.

It's a really good solution to automate database changes. Briefly, it figures out what updates (can any sql) that have been run against any database and will apply only those changes it hasn't yet applied. You can check the scripts in to your code repo and it's a superb way to migrate db changes from test through to production. It also caters for pre-conditions and rollbacks if required.

I have used it an highly recommend it. The alternative is having dbadmins manually sweating over over alter statement, and inevitably making mistakes.