Ninja Press

ざっくりSQLiteまとめ

Category:
SQLite
Tags:
データベースMySQLSQLite

最近、小規模な開発のいくつかでデータベース SQLite を導入しています。（普段のメインは Amazon RDS + MariaDB (MySQL) ）

SQLite の長所は何と言っても機能が絞られている代わりに高速に動作することだと思いますが、同時書き込みはサポートしていないこと、扱いに気をつけないとファイルが壊れてしまうこと、複雑なことがあまりできないことなど難しいところがあり、受託開発で使える場面はまだまだ限定的かなというのが実感です。私自身はデータの更新がローカルのみでサーバー側では参照しかしないようなデータでかつ速度が求められる場合に使用しています。

ちなみにアプリ開発において端末内でリレーショナルDBを使いたい場合もSQLiteを利用することになります。

備忘録も兼ねて、SQLiteの主な内容をまとめておきます。

# ファイル型DBとは何か

SQLiteはファイル型DBです。
大半のリレーショナルデータベースはdaemonとして動作しているデータベースに対して接続を行いますが、SQLiteは一つのファイルが一つのデータベースとなります。

コマンドの場合、下記のように接続を行います。（ファイルが存在しない場合、新しいファイルが自動的に作成されます）

sqlite3 test.db
1

# バックアップ、リストアについて

基本的にはファイルなのでデータのコピーやバックアップなどは対象のファイルをコピーするだけで済みますが、コピー中にデータの書き換えが発生するとコピーしたファイルが壊れていることもあります。

バックアップやリストアは下記のように.dumpコマンドを用いて行うのが安全です。

# backup.sql にダンプを保存
sqlite3 test.db .dump > backup.sql

# backup.sql からダンプしたデータをリストア
sqlite3 test.db < backup.sql
1
2
3
4
5

なお、この方法だと後述するROWIDが維持されない場合があるので注意が必要です。
ROWIDのところでも触れていますが、私自身はROWIDが変わっても問題ないようなテーブル設計をすべきだと考えています。


# データの型について

データ型は他のリレーショナルDBと比べてSQLiteのほうがデータ型が限定的です。

型 説明
NULL NULLです。
INTEGER 符号付整数。内部的には1,2,3,4,6,8バイト
REAL 浮動小数点数。8バイト
TEXT テキスト。UTF-8、UTF-16BE、UTF-16-LE
BLOB バイナリデータ

上記のほかにCREATE TABLEやALTER文で指定できる型は下記のようになります。

型 説明
NUMERIC INTEGERまたはREAL

# 存在しない型名を指定した場合

CREATE TABLEやALTER文では適当な型名を指定してもテーブルを生成できてしまいます。

CREATE TABLE IF NOT EXISTS test (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	a HOGE, 
	b FUGA 
);
1
2
3
4
5

この場合、testテーブルにINSERTするとHOGE型やFUGA型は型変換されずにそのまま格納されるようです。

INSERT INTO test (a,b) VALUES("a",1);
SELECT id, a, typeof(a), b, typeof(b) FROM test;
1
2
id          a           typeof(a)   b           typeof(b) 
----------  ----------  ----------  ----------  ----------
1           a           text        1           integer
1
2
3

# 異なる型のデータをINSERTした場合

CREATE TABLE IF NOT EXISTS test (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	a INTEGER, 
	b REAL, 
	c TEXT
);
INSERT INTO test (a,b,c) VALUES(1, 1.0, "a");
INSERT INTO test (a,b,c) VALUES(3, 3, 3);
INSERT INTO test (a,b,c) VALUES(3.0, 3.0, 3.0);
INSERT INTO test (a,b,c) VALUES(3.5, 3.5, 3.5);
INSERT INTO test (a,b,c) VALUES("3", "3", "3");
INSERT INTO test (a,b,c) VALUES("3.0", "3.0", "3.0");
INSERT INTO test (a,b,c) VALUES("3.5", "3.5", "3.5");
INSERT INTO test (a,b,c) VALUES("c", "c", "c");
INSERT INTO test (a,b,c) VALUES("09", "09", "09");
INSERT INTO test (a,b,c) VALUES("0xFF", "0xFF", "0xFF");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SQLiteでは上記のように異なる型のINSERTもできてしまうようです。
上記のようなSQLを発行した後にデータの型を調べると下記のようになります。

SELECT id, a, typeof(a), b, typeof(b), c, typeof(c) FROM test;
1
id          a           typeof(a)   b           typeof(b)   c           typeof(c) 
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           1           integer     1.0         real        a           text      
2           3           integer     3.0         real        3           text      
3           3           integer     3.0         real        3.0         text      
4           3.5         real        3.5         real        3.5         text      
5           3           integer     3.0         real        3           text      
6           3           integer     3.0         real        3.0         text      
7           3.5         real        3.5         real        3.5         text      
8           c           text        c           text        c           text      
9           9           integer     9.0         real        09          text      
10          0xFF        text        0xFF        text        0xFF        text
1
2
3
4
5
6
7
8
9
10
11
12

ここから次のようなことがわかります。

  • INTEGER型にREAL型、またはTEXT型を挿入するとINTEGER、REAL、TEXTの順でキャストしても値が変わらない型にキャストされる。
  • REAL型にINTEGER型を挿入するとREAL型にキャストされる。
  • REAL型にTEXT型を挿入すると、キャストしても値が変わらない場合はREAL型、そうでない場合はTEXT型となる。
  • TEXT型は常にTEXT型にキャストされる。
  • TEXT型からREAL、INTEGERへキャストされる場合は8進数、16進数表記は考慮されず、先頭の0は無視される。

基本的にはINTEGER -> REAL -> TEXTの順でできるだけキャストされるが、キャストされない場合はそのままの型で保持される、と考えればよいかと思います。


# ROWIDについて

SQLiteでは各レコードに対して、自動的に ROWID と呼ばれる値が設定されます。
ROWIDは明示的に参照された場合のみ表示されます。

INSERT INTO test (a,b) VALUES("a",1);
INSERT INTO test (a,b) VALUES("b",2);
INSERT INTO test (a,b) VALUES("c",3);
1
2
3
SELECT * FROM test;
1
id          a           b         
----------  ----------  ----------
1           a           1         
2           b           2         
3           c           3
1
2
3
4
5
SELECT rowid,* FROM test;
1
id          id          a           b         
----------  ----------  ----------  ----------
1           1           a           1         
2           2           b           2         
3           3           c           3
1
2
3
4
5

SQLiteは他のリレーショナルDBと同じように AUTOINCREMENT が存在します。ROWIDにも同じように連番が割り振られるのですが、ダンプ、リストアなどとrowidの割り当てが変わってしまう場合があるため、私自身はROWIDが変わっても問題ないようなテーブル設計をしています。

# LAST_INSERT_ROWID()

SQLiteには最後にINSERTしたレコードのrowidを参照するLAST_INSERT_ROWID関数が存在します。

INSERT INTO test (a,b) VALUES("d",4);
SELECT LAST_INSERT_ROWID();
1
2
LAST_INSERT_ROWID()
-------------------
4
1
2
3

SQLiteの場合、AUTOINCREMENT されたPRIMARY KEYについては同様の関数が存在しないため、MySQLのLAST_INSERT_IDのように最後にINSERTされたPRIMARY KEYを取得したい場合は下記のようにする必要があります。

SELECT id FROM test WHERE rowid = LAST_INSERT_ROWID();
1
id        
----------
4
1
2
3

# CREATE TABLE

すでに少し触れましたが、CREATE TABLE 文は MySQLや他のリレーショナルデータベースとほぼ同じです。

CREATE TABLE test (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	c INTEGER NOT NULL DEFAULT 0,
	a INTEGER NOT NULL DEFAULT 0, 
	b TEXT NOT NULL DEFAULT ''
);
1
2
3
4
5
6

強いて言えば、下記のような細かい違いがあります。

  • AUTOINCREMENT を指定する場合は PRIMARY KEYをカラム名の横に書く必要がある。（CREATE分の中にPRIMARY KEY(カラム名)と書くとエラーになる）
  • NOT NULLを指定している場合、DEFAULTの設定がない状態で未指定にするとNULLを入れようとしてエラーになる。（MySQLの場合、INTEGER NOT NULL でDEFAULT指定しないと自動的にDEFAULT 0になる）
  • CREATE文の後ろにはWITHOUT ROWIDという指定をつけることができるが、それ以外は基本的に何もつけない。

WITHOUT ROWID はその名の通り、ROWIDなしでテーブルを作成するオプションです。こちらを設定することで最適化ができますが長くなってしまうため、また別の記事でまとめます。

# ALTER TABLE

ALTER TABLE 文は他のSQLに比べて、いくつか対応していないシンタックスがあるので注意が必要です。

  • DROP (カラムの削除)には未対応
  • CHANGE (カラムの定義変更)には未対応（ただし、カラム名の変更はRENAMEで可能）
  • ADD (カラムの追加)は末尾のみ可能（MySQLのようにAFTERでカラムを入れる場所を指定することは不可）
  • RENAMEでテーブル名の変更が可能

下記のようなことが可能です。

/* カラムの追加は末尾のみ */
ALTER TABLE test ADD c INTEGER NOT NULL DEFAULT 0;

/* カラム名の変更（定義の変更は不可能） */
ALTER TABLE test RENAME c TO d;

/* テーブル名の変更 */
ALTER TABLE test RENAME TO testb;
1
2
3
4
5
6
7
8

# フィールドの変更

任意の場所にフィールドを追加したり、フィールドの定義を変更したい場合は新しくテーブルを作り直す必要があります。

下記のようなテーブルがあるものとします。

.schema test
1
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT, 
a HOGE, 
b FUGA 
);
1
2
3
4
5
select * from test;
1
id          a           b         
----------  ----------  ----------
1           a           1         
2           b           2         
3           c           3         
4           d           4
1
2
3
4
5
6

このテーブルのidの後ろにcというカラムを追加してみます。
合わせて HOGE、FUGAなどのカラム定義をちゃんとしたものに修正します。

/* テーブル名を一旦、変更 */
ALTER TABLE test RENAME TO test_tmp;

/* 新しい定義でテーブルを生成 */
CREATE TABLE test (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	c INTEGER NOT NULL DEFAULT 0,
	a INTEGER NOT NULL DEFAULT 0, 
	b TEXT NOT NULL DEFAULT ''
);

/* test_tmpからtestテーブルへレコードをコピー */
INSERT INTO test (id,c,a,b) SELECT id,0,a,b FROM test_tmp;
INSERT INTO test (rowid,id,c,a,b) SELECT rowid,id,0,a,b FROM test_tmp;

/* 古いテーブルを削除 */
DROP TABLE test_tmp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

これで定義が変更されました。

.schema test
1
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT, 
c INTEGER NOT NULL DEFAULT 0,
a INTEGER NOT NULL DEFAULT 0, 
b TEXT NOT NULL DEFAULT ''
);
1
2
3
4
5
6
select * from test;
1
id          c           a           b         
----------  ----------  ----------  ----------
1           0           a           1         
2           0           b           2         
3           0           c           3         
4           0           d           4
1
2
3
4
5
6

ちなみにrowidを飛び飛びにしてこの操作を試してみたところ、（個人的には意外でしたが）ROWIDは正常に引き継がれておりました。

INSERT 〜 SELECT などの操作では気を利かせてROWIDを引き継ぐようになっているのかも知れませんが、既存のレコードがすでに存在していてROWIDが重複している場合はやはり別のROWIDが割り振られてしまいましたし、この挙動もバージョンによるかも知れないので、やはりROWIDは割り当てし直されてしまうかも知れない、ということは設計の段階で念頭に入れておいたほうがよい気がします。

なお、対象のテーブルに対して、後述のCREATE INDEX文でインデックスを生成していた場合、新しくCREATE TABLEしたテーブルに対してもCREATE INDEX文によるインデックスの生成が必要になります。


# DROP TABLE

DROP TABLE 文は他のリレーショナルデータベースとほぼ同じです。

DROP TABLE test;
1

# CREATE INDEX

PRIMARY KEY以外のインデックスは基本的にCREATE TABLE文に含めることはできないので、CREATE INDEX文として別に定義します。

CREATE INDEX test_a ON test(a);

/* 複合インデックス */
CREATE INDEX test_ab ON test(a, b);

/* すでに同じインデックスが存在する場合、インデックスを作成しない */
CREATE INDEX IF NOT EXISTS test_ab ON test(a, b);

/* ユニークインデックス */
CREATE UNIQUE INDEX test_ac ON test(a, c);

/* 条件付きインデックス */
CREATE INDEX test_b ON test(b) WHERE b % 2 = 0;
1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT文に対して、インデックスがどのように使われるかはSELECTの前に EXPLAIN QUERY PLAN を追加することで調べることが可能です。

# 単一カラムの指定では単一カラムのインデックスを使用する
EXPLAIN QUERY PLAN SELECT id FROM test WHERE a = 'a';
1
QUERY PLAN
`--SEARCH TABLE test USING COVERING INDEX test_a (a=?)
1
2
# 複数カラムの指定では複合インデックスを使用する
EXPLAIN QUERY PLAN SELECT id FROM test WHERE a = 'a' AND b = 2;
1
QUERY PLAN
`--SEARCH TABLE test USING COVERING INDEX test_ab (a=? AND b=?)
1
2
# 条件付きインデックスは条件が一致する場合のみ使われる
EXPLAIN QUERY PLAN SELECT id FROM test WHERE b % 2 = 0;
1
QUERY PLAN
`--SCAN TABLE test USING COVERING INDEX test_b
1
2

# DROP INDEX

DROP INDEX 文は他のリレーショナルデータベースとほぼ同じです。

DROP INDEX test_ac;
1

# INSERT

INSERT は既存のリレーショナルデータベースとほぼ同じように使えます。

INSERT INTO test (a,b) VALUES("a",1);
1

重複が発生した場合に無視するには INSERT OR IGNORE構文を利用します。

INSERT OR IGNORE INTO test (a,b) VALUES("a",1);
1

# バルクインサート

他のデータベース同様、バルクインサートも可能です。

INSERT INTO test (a,b) VALUES
("a",1), 
("b",2),
("c",3);
1
2
3
4

# INSERT SELECT

INSERT のVALUES以降にSELECT文を指定することも可能です。

INSERT INTO test (a,b)
SELECT c,d FROM test2 WHERE c > 10;
1
2

# UPDATE

UPDATE は既存のリレーショナルデータベースとほぼ同じように使えます。

UPDATE test SET b = 10 WHERE a = 'b';
1

# DELETE

DELETE は既存のリレーショナルデータベースとほぼ同じように使えます。

DELETE FROM test WHERE a = 'b';
1

# SELECT

SELECT は既存のリレーショナルデータベースとほぼ同じように使えます。

SELECT * FROM test WHERE a = 'b' ORDER BY b ASC;
1

ただし、先述の通り、ROWIDが必要な場合は明示的に指定する必要があります。

SELECT rowid, * FROM test WHERE a = 'b' ORDER BY b ASC;
1

# UPSERT

# REPLACE

REPLACEはPRIMARY KEYやUNIQUE INDEXで重複するレコードが存在する場合はDELETEしてからINSERTする構文です。

内部的には重複する場合にレコードを削除してINSERTしなおしているので、REPLACE文で指定していないフィールドもそのままというわけではなくDEFAULT値に変換されることに注意が必要です。（そういう意味ではREPLACEはUPSERTというよりは言葉の通り、レコードを置き換える、というイメージです）

下記のテーブルに a フィールドを重複させる形で REPLACEしてみます。

.schema test
1
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT, 
c INTEGER NOT NULL DEFAULT 0,
a INTEGER NOT NULL DEFAULT 0, 
b TEXT NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX test_a ON test(a);
1
2
3
4
5
6
7
SELECT rowid, * FROM test;
1
id          id          c           a           b         
----------  ----------  ----------  ----------  ----------
1           1           0           a           1         
2           2           0           b           2         
3           3           0           c           3         
4           4           0           d           4
1
2
3
4
5
6
REPLACE INTO test (a,b,c) VALUES("c", 5, 1);
SELECT rowid, * FROM test;
1
2
id          id          c           a           b         
----------  ----------  ----------  ----------  ----------
1           1           0           a           1         
2           2           0           b           2         
4           4           0           d           4         
5           5           1           c
1
2
3
4
5
6

rowid = 3のレコードがrowid = 5に入れ替わりました。

  • 内部的にはDELETE -> INSERTのため、ROWIDとPRIMARY KEYは新たに割り振られています。
  • 指定していないbは元の値のままではなく、DEFAULT値となっています。

# ON CONFLICT

いわゆるUPSERT（重複があればUPDATE、なければINSERT）です。
MySQLのON DUPLICATE KEY UPDATEと同じようなことができます。

/* 重複した場合は無視する。 */
INSERT INTO test (a,b,c) VALUES("a",5,0) 
ON CONFLICT DO NOTHING;

/* INSERT OR IGNOREとしても同じ。 */
INSERT OR IGNORE INTO test (a,b,c) VALUES("a",5,0);

/* 重複がない場合は普通のINSERTだが、重複した場合はUPDATEする */
/* この場合は CONFLICT(...) で重複確認するインデックス名を指定する必要がある */
INSERT INTO test (a,b,c) VALUES("e",5,3) 
ON CONFLICT(a) DO UPDATE SET c = 3;

/* 最後にWHERE句をつけると、重複したレコードがWHERE句の条件に一致する場合のみUPDATEが行われる */
/* WHERE句をつけようがつけまいが、重複した対象レコード以外は関係がないことに注意 */
INSERT INTO test (a,b,c) VALUES("e",5,3) 
ON CONFLICT(a) DO UPDATE SET c = 3 WHERE b % 2 == 0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Category:
SQLite
Tags:
データベースMySQLSQLite

←

Copyright © Web Ninja All Rights Reserved.