SQLite2でテーブルに新たなカラムを追加する

Read More

SQLite2ではALTER文は使えない。
このためこれに代わる別の方法を取る必要がある。


下記のようなSQLを発行すると通常のALTER文を使ってカラムを増やすのと同様の結果を得ることができる。


■SQLite2で既に稼動しているテーブルに対し新たなカラムを追加する
BEGIN TRANSACTION;
 
-- hogeテーブルとまったく同じ仮テーブルのhoge_tempテーブルを作る
CREATE TEMPORARY TABLE hoge_temp(a, b);

-- hoge_tempテーブルにhogeの中身を丸ごとコピー
INSERT INTO hoge_temp SELECT * FROM hoge;
 
-- 古いhogeテーブルをここで削除
DROP TABLE hoge;
 
-- 新たなカラムを追加した新しいhogeテーブルを作る
CREATE TABLE hoge (a, b, c);

-- hoge_tempテーブルにnullデータを加えたものをhogeテーブルに入れる
INSERT INTO hoge SELECT *,NULL FROM hoge_temp;
 
-- hoge_tempテーブルを削除する
DROP TABLE hoge_temp;
 
COMMIT;

■参考サイト
http://www.rainorshine.asia/2009/05/23/post977.html


このやり方を知るまではプログラムでCSVデータをCSVファイルに吐き出して…とか自前でやってたのでもっと早く知りたかった。


つかcreate temporary table なんて今回初めて知った。
■create temporary table
"CREATE" と "TABLE" の間に "TEMP" もしくは "TEMPORARY" (一時的)というキーワードがある場合、生成されるテーブルはそのデータベースを開いたプロセスのみからしか見えなくなり、データベースが閉じられると自動的に削除されます。
†SQLite が認識できる SQL より引用




--
ってことでこれを使ってブログシステムのテーブルカラムを増やしてみた。
特に何も詰まることもなくすんなりできました。
ありがとサンクス元ネタの人。



ちなみにtemporaryを使わずに、通常テーブルを作ってデータコピーして消して、とかやってるとなぜかdbファイルのデータ量が膨らんで約2倍になった!
テーブルを削除しても内部的にはフラグ立ててるだけで残骸が残るのだろう。
まあテーブル削除を頻繁にしないように気をつけましょ。



ん??
ひょっとしてSQLite2の最適化ってのはこのためにあるのか?



-- 2013/02/20追記 --
本記事の配置と文章を修正。