MySQL data migration

I’ve been working with MySQL throughout my career (11 years and still going strong), and have grown to really love it and favor it over any other database out there. It’s rock solid, stable, and with MySQL 5 it also gained clustering capabilities.

As a developer, refactoring is comething I do pretty often. Renaming classes, methods, creating new ones, killing some others (Shotgun Surgery is a great refactoring standard). When it comes to the data structure in the database, things are not so simple. Usually you have yoru own development database, a testing server with its own database, and a production server, also with its own database.

Keeping those in sync can be a pain if you’re not disciplined enough to generate SQL scripts from all your actions in the mysql console or phpMyAdmin.  Mysqldiff proposes to be a solution to this pain by allowing you to diff a table in two different databases and generate the SQL needed for syncing them.

But what about data migrations? For one of my projects, we just realized that two of the entities we were using, Academic History and Professional History actually belong to a common shared model, History. They were spread in two tables, but since I’m using CakePHP for this one project, it’s easier to manage the inheritance if it’s all in a single table.

I found out that MySQL allows you to use a nested select in insert statements, thus making it really easy to migrate the data from those 2 tables into the shared one:

INSERT INTO history (profile_id,type,organization,start_year,
SELECT p.profile_id, 'Professional',, p.start_year,
p.end_year, position, description
FROM professional_histories p;

INSERT INTO history (profile_id,type,organization,start_year,
end_year,level_id, degree_id, field, area)
SELECT a.profile_id, 'Academic', a.institution, a.start_year, a.end_year,
a.level_id, a.degree_id, a.field, a.area
FROM academic_histories a;

DROP TABLE academic_histories;
DROP TABLE professional_histories;

Nifty isn’t it?

Leave a Reply

Your email address will not be published. Required fields are marked *