mysqlからsqlite3に移行してみた際の恥ずかしい備忘録

 はるか昔(最古のデータの作成日時が1999年...)に使用していた関係で惰性で使い続けてきたmysqlでしたが、もう長いこと読み出しこそ頻繁にあるもののデータの更新はもはや月に数件というほど減少し、ちょっと大げさになっちゃったね、お役御免かな、と思っていました。

最近、CentOS7からUbuntu24に移行した際にPCから.net用のコネクタでリモート接続していたプログラムが動かなくなって、再ビルドしようとしたらvisual studio2022用では.net用のコネクタが使えなくて、見切りを付けました。

その後、最近時間ができたので、どうせならmysqlから一気にsqlite3に移行させてみることにしました。

その際の恥ずかしい作業録です。

・CREATE TABLEの UNIQUE KEY 句は KEY がいらない

KEYという文字列を削除するだけでした。

・CREATE TABLEでインデックスを作成するKEY句がない

前項とややこしいですが、mysqlのKEY句はテーブル作成時に同時にインデックスを張れます。
で、sqliteではCREATE TABLE時にはインデックスのカラムを指定できないので、テーブル作成後にCREATE INDEX文で作成する必要がありました。

・NOW()関数がない

CURRENT_TIMESTAMPに置き換えました。

そしたら・・・次の項目の問題がありました。

・sqlite3の日付関連はすべてUTC

問答無用でUTCなので潔いです。
そういえば十年以上前にある仕事で使ったときになんかあったような気が・・・。ちっとも覚えていませんでした。

既にあるデータをUTCにするのも手ですが、手抜きでCREATE TABLEでカラムのDEFAULT値に DATETIME( 'now', 'localtime' )とかやってみたら叱られました。

早速Google検索経由で先達にお伺いしたところ、仕様だアキラメロというページが最上位に出てくるのですが、さらにお伺いを立てると、かっこでくくってあげて、(DATETIME( 'now', 'localtime' ))とするとオッケーだとのありがたいお言葉で無事解決できました。ありがとうございます!

・mysqlではテキストデータではCRは\r,LFは\nと格納されるが、sqlite3ではもろそのままの文字コードで格納される。

なるほど～。って感じですね。

対策は、ちょっと長くなってしまったので、別の記事にしました。

概要としては、これまでに作成されたmysql形式に合わせてphp側(手始めに移行しているテーブルは自作のやっつけphpアプリで利用するためのテーブルなので)でキャリッジリターン(0x0a)とラインフィード(0x0d)をそれぞれ文字列としての\r, \nに相互に変換することにしました。

・sqlite3 には INSERT INTO ... SET 構文がない

これはむしろmysql方言ですね。なくても仕方がありません。

トリガーでのINSERT文でSET構文を使っていたのですが、むやみにUPSERTとか方言だらけの機能は使わないでおきたいので、標準的なSQLに書き換えるだけにしておきました。

・sqlite3にはVACUUMが必要

postgresqlを思い出すバキューム。カーがつくと途端に香しくなる不思議な言葉です。

でも、更新が月に数回なデータベースにVACUUMなんかいるわけがありません。どうしても必要なら手動でやればいいので、無視することにしました。

・複数クライアントからの同時更新ができない

最近はできるらしいのですが、特にテストをしてみようとは思いません。それが重要ならmysqlから移行する必要がありません。

万が一バッティングしたら「モウイチドヤレヨ」光線を発射して運用でカバーする予定です。

・RAND()関数がない

RANDOM()関数ならありました。

とりあえず、この程度でテスト運用を開始して、当分様子を見る予定です。

何か恥ずかしいことが起きたら、喜んで記事にしたいので起きてくれることを祈りつつ、こんな記事をお読みいただきまして、心からお詫びを申し上げる次第です。