それマグで!

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

習慣に早くから配慮した者は、 おそらく人生の実りも大きい。

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

MySQLSQLiteという物を考えてやってみたログ。

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

そこで、MySQLSQLiteという物を考えてみた。

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文を出力しない方法 - ブックマクロ開発に

2:テーブル定義の取り出し。
     mysqldump -u root -p  epgrec  -d > create_table.sql

次に SQLite形式に変換する

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

3:手作業で、変換

先人の知恵に習って手作業変換をした。参考資料→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に入れてみる

4:インサート文を実行した
$>sqlite3 test.db
sqlite>./read create_table.sql

出来た。

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

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

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

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

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

BeauTV〜VOCE ビューティーヴィー #104','恋する女性必見
オークローンショッピング','テレビショッピング',3,'2011-12-03 11:30:
くらしのサプリ!集合!鈴木三姉妹〜Girls\' Adventure〜 #9','砂羽
二人の食卓〜ありがとうのレシピ〜 【ゲスト】金山一彦、浅野温子','料
UMONファミリースペシャル 第26回全国童謡歌唱コンクールグランプリ大
ききみみ','BS朝日よりミミヨリ情報をあなたに!',3,'2011-12-03 14:5
カーグラフィックTV 「20年前にタイムスリップ!\'91年のフランクフ
News Access','最新ニュース',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

  }


}