SOFTELメモ

Softel Inc.

SQLite3のメモ

SQLite3

特徴

などなど。

格納されるデータの型

以下のいずれかになる。

Booleanはない。

Date、Time、Datetimeもない(数値か文字列で対応する)。

カラムの型

以下のいずれか。

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|実数のつもりで
*/

関連するメモ

コメント