SQLite2からMySQLへデータベースの移植を行う

プログラミング

【最終更新: 2012年2月23日】
SQLite2のデータベースの中身を、MySQLに移植したい場合。


具体的には、CakePHPのローカルテスト用に、複数のPCでDropboxを介してファイルとして同期できるSQLite2をデータベースに採用していたのに、CakePHP2.0ではSQLite2は使えなくなり、さりとて『CakePHP2.0 + Ubuntu』の組み合わせではSQLite3が正常に動いてくれないので仕方なくテスト環境でもMySQLを使おうとしている私のような場合 …(^ ^;)


【実行環境】
  • Lubuntu 10.10
  • XAMPP for Linux 1.7.7
その他、『/opt/lampp/bin』にパスを通して、『mysql』や
『sqlite』と入力するだけでデータベースに接続できるように
しています。
【参考】パス(PATH)の確認と設定方法は？ - Pocketstudio.jp Linux Wiki
http://bit.ly/zFahR2


MySQLの文字コードをUTF-8に

まず、XAMPPをインストールした段階では『latin1』になっている
MySQLの文字コードを『UTF-8』に変更します。
設定ファイル『my.conf』に追加します。


【/opt/lampp/etc/my.conf】

[client]

default-character-set=utf8
[mysqld]

character-set-server = utf8

skip-character-set-client-handshake
その後XAMPPを再起動し、下記のようにして確認します。


【コンソール】

mysql> show variables like "char%";
+--------------------------+----------------------------------+

| Variable_name            | Value                            |

+--------------------------+----------------------------------+

| character_set_client     | utf8                             |

| character_set_connection | utf8                             |

| character_set_database   | utf8                             |

| character_set_filesystem | binary                           |

| character_set_results    | utf8                             |

| character_set_server     | utf8                             |

| character_set_system     | utf8                             |

| character_sets_dir       | /opt/lampp/share/mysql/charsets/ |

+--------------------------+----------------------------------+


SQLiteのデータベースをファイルに出力・修正



【コンソール】

(出力先を、画面ではなくファイルに変更)

sqlite> .output /hoge/fuga/piyo.sql
(パラメータを与えずに、データベース全体を出力)

sqlite> .dump

その後、MySQLで正しくインポートするために、かなりの修正を加えなければなりません。


  • 文頭の『BEGIN TRANSACTION;』、文末の『COMMIT;』を消す。
  • 『INTEGER』を『INT』に置換。

  • 『PRIMARY KEY』の後には『AUTO_INCREMENT』を追加。

    (SQLite2では、AUTOINCREMENT制約を記述できない。エラーとなる)

    (MySQLではAUTO_INCREMENTがなければ連番機能が働かない)
  • 『CHAR』を『VARCHAR(文字数)』に置換。

  • 『DATETIME』型の『DEFAULT』を消す。

    その後に続く『'2008-11-15 00:00:00'』のような初期値も消す。

  • CREATE文中の『'(シングルクォート)』を『`』に変換

    ※INSERT文中のシングルクォートはそのまま!
  • 『COLLATE NOCASE』を消す。

  • 『INSERT INTO "users"』など、INSERT文中にあるテーブル名を囲んでいるダブルクォートを消す。


MySQLにインポート

受け入れるデータベースを作成し、接続します。


【コンソール】

$ mysql -u root

mysql> USE (データベース名)

mysql> source /hoge/fuga/piyo.sql;
これでOKです。


【補足】
ちなみに、MySQLのsqlファイルからSQLiteへ移植するのは
無理だと思います。
INSERT文をカンマでつなげて複数の行を挿入するということが
SQLiteではできませんから…orz