SQLite3のメモ
特徴
- SQL92の機能の多くを実装。トランザクション、ビュー、トリガーのサポートもあり。
- サーバではなくライブラリ。
- 要するにファイル。
- セットアップ不要。
- 設定ファイルがない。
などなど。
格納されるデータの型
以下のいずれかになる。
- NULL - NULL用
- INTEGER - 符号付整数。値の大きさに応じて1, 2, 3, 4, 6, 8 bytes で保存される。
- REAL - 浮動小数点数。8-byte IEEE floating point number。
- TEXT - 文字列。databaseの文字コードで保存される(UTF-8, UTF-16BE or UTF-16LE)。
- BLOB - バイナリ。そのまま保存。
Booleanはない。
Date、Time、Datetimeもない（数値か文字列で対応する）。
カラムの型
以下のいずれか。
- TEXT
- NUMERIC
- INTEGER
- REAL
- NONE
INTやBLOB、VARCHAR、CHARなどよそでよくある型を指定できるが、上のいずれかに読み替えられる（Type Affinity）。
NONEがもっともSQLiteらしいというか、何でもありなカラムの型。
カラムの型を指定しておくと、ある程度の変換がされる。ある程度。
INTEGERとNUMERICは何が違うのかというとほとんど一緒でCASTのときちょっと違う。
使ってみると
カラムの型を指定しなくてもまったく問題ない。何の補正もされないだけで、以下のようになる。
/* 型の指定なしでテーブルが作れる */
create table test1 (id, a, b);
/* この3行、区別される */
insert into test1 (id, a) values (1, '整数');
insert into test1 (id, a) values ('1', '文字列');
insert into test1 (id, a) values (1.0, '実数');
/* 確認すると、INTEGER型とTEXT型とREAL型 */
select id, typeof(id) from test1;
id|typeof(id)
1|integer
1|text
1|real
/* 抽出の条件にも影響がある */
select id, a from test1 where id = '1';
id|a
1|文字列
select id, a from test1 where id = 1;
id|a
1|整数
1|実数
カラムの型を指定したとしても、必ずその型になるわけではない。INTEGERを指定しても、なるべく整数にしてくれるだけで、文字列でもエラーなく格納される。型を指定したらMySQLのような挙動になるわけでもない。
/* 型の指定をしておくと */
create table test2 (id integer, a, b);
/* このようにデータを入れた場合 */
insert into test2 (id) values (1);
insert into test2 (id) values ('1');
insert into test2 (id) values (1.0);
insert into test2 (id) values ('テストです');
/* 可能な限りINTEGER型にしてくれて、どうしようもないときそのまま */
select id, typeof(id) from test1;
id|typeof(id)
1|integer
1|integer
1|integer
テストです|text
各カラムの型に対して、各種のデータを格納しようとしたときの挙動。
CREATE TABLE t1(
t TEXT, -- text affinity by rule 2
nu NUMERIC, -- numeric affinity by rule 5
i INTEGER, -- integer affinity by rule 1
r REAL, -- real affinity by rule 4
no BLOB -- no affinity by rule 3（型の指定なしと同じ）
);
-- 文字列を格納すると
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text
-- REALを格納すると
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|real
-- INTEGERを格納すると
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer
-- BLOBはいつでもBLOBで格納される
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
blob|blob|blob|blob|blob
-- NULLはNULL
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null
演算子
|| * / % + - << >> & | < <= > >= = == != <> IS IS NOT IN LIKE GLOB MATCH REGEXP AND OR
- + ~ NOT
関数
集計など基本的な関数はある。
データに日付型がないためか、日付計算関数もない。
now()もないのでdatetime()などを使う。
select date(), datetime(), time(); date()|datetime()|time() 2013-09-07|2013-09-07 17:36:59|17:36:59
php経由で使ってみると
以下のように、使い方に少々気をつける必要がありそう。
//create table test1 (id, a);
$db = new PDO('sqlite:/tmp/db.sqlite3');
//数値のつもりでinsert文
$db->exec("insert into test1 (id, a) values (1, '数値のつもりでinsert文')");
//プリペアドステートメントで
$s = $db->prepare("insert into test1 (id, a) values (?, ?)");
$s->execute(array('2', '文字列つもりで'));
$s->execute(array(3, '数値のつもりで'));
$s->execute(array(4.0, '実数のつもりで'));
/*
その後結果を確かめてみると
select id, typeof(id) from test1;
id|typeof(id)|a
1|integer|数値のつもりでinsert文
2|text|文字列つもりで
3|text|数値のつもりで
4|text|実数のつもりで
*/