2つのデータベースの差分からDDL(CREATE/DROP/ALTER TABLE文)を作成する

「テーブルの追加と修正をしたから各自開発環境でこのCREATE/ALTER文を実行して！」

データベースのマイグレーションツールを使っていない場合は、こういった作業をしばらくサボって、あとで困ってしまうことがよくあると思います。

そんなときは、
SQL::Translator (SQLFairy)
を使ってさくっとテーブル構造を同期させましょう。

準備

SQL::Translatorのインストール

$ sudo yum install perl-SQL-Translator.noarch -y

テーブル構造の同期

1. 2つのデータベースのスキーマをバックアップ

$ mysqldump -h[NEW_DB] -uUSER DB_NAME -d -p | sed -e 's/AUTO_INCREMENT=[0-9]\+//' > NEW_DB_schema.sql
$ mysqldump -h[OLD_DB] -uUSER DB_NAME -d -p | sed -e 's/AUTO_INCREMENT=[0-9]\+//' > OLD_DB_schema.sql

[OLD_DB] = テーブル構造の古いデータベース
[NEW_DB] = テーブル構造の新しいデータベース

※AUTO_INCREMENTは邪魔になるのでsedで削除しています。

2. 2つのバックアップファイルから差分DDLを作成

$ sqlt-diff OLD_DB_schema.sql=MySQL NEW_DB_schema.sql=MySQL > diff.sql

sql-diff 古いDBのバックアップファイル=MySQL 新しいDBのバックアップファイル=MySQL
のようにコマンド引数を指定します。
コマンド引数のMySQLの部分はSQLのパーサとなります。使えるパーサの一覧はqlit-diff -lで確認が出来ます。

3. DDLを実行し、OLD_DBをNEW_DBのテーブル構造に合わせる

$ mysql -hOLD_DB -uUSER DB_NAME -p < diff.sql

以上の作業で2つのデータベースのテーブルの構造は一致します。

---

実験してみる

2つのデータベースにusersテーブルを作成

-- TEST1 新しい構造のテーブル
CREATE DATABASE `TEST1` DEFAULT CHARACTER SET = `utf8`;
USE `TEST1`;
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(10) NOT NULL DEFAULT '',
  `last_name` varchar(10) NOT NULL DEFAULT '',
  `address` varchar(200) NOT NULL DEFAULT '',
  `note` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- TEST2 古い構造のテーブル
CREATE DATABASE `TEST2` DEFAULT CHARACTER SET = `utf8`;
USE `TEST2`;
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `address` varchar(100) NOT NULL DEFAULT '',
  `note` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2つのデータベースのスキーマをバックアップ

$ mysqldump -hHOST -uUSER TEST1 -d -p > TEST1_schema.sql
$ mysqldump -hHOST -uUSER TEST2 -d -p > TEST2_schema.sql

差分DDLを作成

$ sqlt-diff TEST2_schema.sql=MySQL TEST1_schema.sql=MySQL > diff.sql

diff.sqlの中身を確認

diff.sql

$ more diff.sql 
-- Convert schema 'TEST2_schema.sql' to 'TEST1_schema.sql':;

BEGIN;

ALTER TABLE users DROP COLUMN name,
                  ADD COLUMN first_name varchar(10) NOT NULL DEFAULT '',
                  ADD COLUMN last_name varchar(10) NOT NULL DEFAULT '',
                  CHANGE COLUMN address address varchar(200) NOT NULL DEFAULT '',
                  CHANGE COLUMN note note varchar(100) NULL DEFAULT '';


COMMIT;

良い感じ。

2つのデータベースのテーブル構造を一致させる（TEST2 ← TEST1)

$ mysql -hHOST -uUSER TEST2 -p < diff.sql

テーブル構造が一致しているか確認

$ mysqldump -hHOST -uUSER TEST2 -d -p > TEST2_new_schema.sql
$ diff TEST1_schema.sql TEST2_new_schema.sql
3c3
< -- Host: XXXXX    Database: TEST1
---
> -- Host: XXXXX    Database: TEST2
27,28d26
<   `first_name` varchar(10) NOT NULL DEFAULT '',
<   `last_name` varchar(10) NOT NULL DEFAULT '',
30a29,30
>   `first_name` varchar(10) NOT NULL DEFAULT '',
>   `last_name` varchar(10) NOT NULL DEFAULT '',
44c44
< -- Dump completed on 2016-02-25 XX:XX:XX
---
> -- Dump completed on 2016-02-25 XX:XX:XX

あれ？

どうやら、カラムの並び順までは考慮されていないようです。

$ more TEST2_new_schema.sql 
~~(省略)~~
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `address` varchar(200) NOT NULL DEFAULT '',
  `note` varchar(100) DEFAULT '',
  `first_name` varchar(10) NOT NULL DEFAULT '',
  `last_name` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
~~(省略)~~

気になる場合は、ALTER TABLEでカラム並び順を変更しましょう。（手作業でALTER TABLE文を作成）

USE `TEST2`;
ALTER TABLE `users` MODIFY COLUMN `first_name` VARCHAR(10) NOT NULL DEFAULT '' AFTER `id`;
ALTER TABLE `users` MODIFY COLUMN `last_name` VARCHAR(10) NOT NULL DEFAULT '' AFTER `first_name`;

再度確認

$ mysqldump -hHOST -uUSER TEST2 -d -p > TEST2_new_schema.sql

$ more TEST2_new_schema.sql 
~~(省略)~~
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(10) NOT NULL DEFAULT '',
  `last_name` varchar(10) NOT NULL DEFAULT '',
  `address` varchar(200) NOT NULL DEFAULT '',
  `note` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
~~(省略)~~

$ diff TEST1_schema.sql TEST2_new_schema.sql
3c3
< -- Host: XXXXX    Database: TEST1
---
> -- Host: XXXXX    Database: TEST2
44c44
< -- Dump completed on 2016-02-25 XX:XX:XX
---
> -- Dump completed on 2016-02-25 XX:XX:XX

テーブル構造が完全に一致しました。

以上です。