MYSQLのデータを、SQLITEに移動して使いたい

MySQL→SQLiteという物を考えてやってみたログ。

MySQLのデータをちょっと操作したり、実験したりするのに、MySQLをコピーしたり、もう一つサーバー作ったりしてた。MacOSXにMySQL入れてもよかったけど、ちょっとクエリ実験操作をアレコレしたり元に戻したり、SQLを手早く使うには不便だった。

そこで、MySQL→SQLiteという物を考えてみた。

SQLiteにおいておけば？

オフラインでも使えるようなるんじゃない？じゃぁMySQLデータをSQLiteに移動したい。

SQLiteへ持って行く手順

今回の手順は次のように考えた。

MySQLからテーブル定義を出力
手作業で変換
SQLiteでテーブル定義を持ち込み
MySQLからデータをインサート文として取り出し
SQLiteにインサート文を実行して持ち込み

の方法でやってみます。

準備；データベースとテーブルを全部SQL出力する

1:一行ずつのインサート文にして出力。

mysqldump -u root -p  epgrec --no-create-info -c --order-by-primary --skip-extended-insert > data_dump.sql

一行ずつにしておくとどこでエラーが出たかわかりやすいので、今回のようなDB間移動には超おすすめ。トランザクションも任意の地点に挟めるしね。→参考資料mysqldumpでcreate文を出力しない方法 - ブックマクロ開発に

２：テーブル定義の取り出し。

     mysqldump -u root -p  epgrec  -d > create_table.sql

次に SQLite形式に変換する

変換スクリプト探したけど見当たらなかった。

３：手作業で、変換

先人の知恵に習って手作業変換をした。参考資料→57AAIOO: MySQLからsqliteへのインポート

変換の項目

create tableをcompatibleにする。
AutoIncrementなどSQLiteは自動付与なので不要だったり。
非対応カラムを対応データ型にする。

SQLiteに対応していない、Create Table構文を消す。

* ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 →消す
* AUTO_INCREMENT消す
* LOCK行消す
* UNLOCK行消す。
* KEY `xxxx_idx` (`xxxxx_disc`)のインデックス指定を消す。
* その他非対応カラムは対応カラムに変える。

このように変換できないテーブル定義とカラムのデータ型は手作業で置換。

置換といっても、テーブルが100個もあるわけじゃないので、手作業でも10分ほど。

最後に、SQLite3に入れてみる

４：インサート文を実行した

$>sqlite3 test.db
sqlite>./read create_table.sql

出来た。

create tableにこのほかのエラーが残ってるなら表示されるので、あとは個別対応よろしく。僕は適当な置換すぎてカンマが残ってたりしてた。

SQLのインサート文で取り込む。

$>sqlte3 test.db
sqlite> ./read table_data_insert.sql

エラー：エスケープ文字で

エラーになった項目をピックアップした

ＢｅａｕＴＶ〜ＶＯＣＥ　ビューティーヴィー　＃104','恋する女性必見
オークローンショッピング','テレビショッピング',3,'2011-12-03 11:30:
くらしのサプリ！集合！鈴木三姉妹〜Girls\' Adventure〜　＃９','砂羽
二人の食卓〜ありがとうのレシピ〜　【ゲスト】金山一彦、浅野温子','料
UMONファミリースペシャル　第26回全国童謡歌唱コンクールグランプリ大
ききみみ','ＢＳ朝日よりミミヨリ情報をあなたに！',3,'2011-12-03 14:5
カーグラフィックＴＶ　「20年前にタイムスリップ！\'91年のフランクフ
Ｎｅｗｓ　Ａｃｃｅｓｓ','最新ニュース',1,'2011-12-03 15:30:00','201

どうやら、クォートのエスケープがちょっと違うようです。
とりあえず実験用に入ればいいので、余計な文字は全部消した。

これでいけた。

もしかして？

この辺はそのうちしらべたい。

ActiveRecordのデータエクスポート＆インポートを使った方が楽かも？

→2011-12-17調べた。YAMLでデータを取り出して YAMLをロードして save すると出来る。

MySQLがコンパチブル（互換）でテーブル定義出せたはず。

→2011-12-17調べた。MySQLはPostgpress互換、Oracle互換などでDump出来るがSqlite互換はない。

スクリプト作った。

ある程度まで、置換できるスクリプト作った。これは不完全なので書きなおしてます（末尾に書いた）

 #!/usr/bin/env ruby
 sql = STDIN.read
 sql = sql.gsub /PRIMARY KEY.+$/, ""
 sql = sql.gsub /ENGINE=[^\s]+/, ""
 sql = sql.gsub /AUTO_INCREMENT=[^\s]+/, ""
 sql = sql.gsub /CHARSET=[^;]+/, ""
 sql = sql.gsub /AUTO_INCREMENT/ , ""
 sql = sql.gsub /KEY.+/ , ""
 sql = sql.gsub /\)\s+DEFAULT / , ")"
 sql = sql.gsub /\/\*!.+$/ , ""
 
 puts sql

こうやってみるとシンプルだな。「Excelで置換する」とか未熟なプログラマだよ。マジで

2012-02-09追記

更に書きなおした。

#!/usr/bin/env ruby
require 'rubygems'


if ARGV.size < 1 or not ARGV.all?{|e|  File.exist? e} then
   puts "usage: #{__FILE__} mysql_create_table.sql
          MySQL の show create table などで出力したファイルを引数に指定する"
           exit 2
end

open(ARGV[0],"r"){|f|
  f.each_line{|line|
    line = line.gsub /engine[^\s]+/, ""
    line = line.gsub /character\s+set\s+'utf8'/, ""
    line = line.gsub /drop\s+table\s+if\s+.+$/, ""
    line = line.gsub /use.+$/, ""
    line = line.gsub /lock.+$/, ""
    line = line.gsub /unlock.+$/, ""
    line = line.gsub /key\s+.+$/, ""
    line = line.gsub /auto_increment\s/, ""
    line = line.gsub /bigint\s/, "int "
    line = line.gsub /unsigned\s/, ""
    line = line.gsub /tinyint\s/, "boolean "       #tinyint は booleanへ
    line = line.gsub /default\s+.+,/, ","          #tinyint にdefault 1 が設定されてるとまずいので消す
    line = line.gsub /#.*$/, ""                   # コメントは消す
    puts line

  }


}

それマグで！

知識はカップより、マグでゆっくり頂きます。　takuya_1stのブログ