実行できるSQLiteドキュメント: 型親和性

Q: SQLiteの型親和性(Type Affinity)とは何ですか?

型親和性とは、その列に「優先的に格納したいストレージクラス」のことです。SQLiteには TEXT / NUMERIC / INTEGER / REAL / BLOB の5種類があります。値をINSERTすると、SQLiteはその列の親和性に合わせて変換を試みますが、変換するとデータが失われる場合や変換そのものができない場合は、元の型のまま格納します。つまり親和性は「ヒント」であって、強い制約ではありません。

Q: SQLiteは列の親和性をどうやって決めているのですか?

CREATE TABLE に書いた型名の文字列を、上から順にパターンマッチして決めます。具体的には、INT を含めば INTEGER、それ以外で CHAR / CLOB / TEXT を含めば TEXT、BLOB を含むか型指定なしなら BLOB、REAL / FLOA / DOUB を含めば REAL、どれにも当てはまらなければ NUMERIC です。VARCHAR(50) が TEXT になり、BIGINT が INTEGER になるのはこのためで、書いた文字列がそのまま判定対象になります。

Q: SQLiteの列に「型と違う値」を入れることはできますか?

通常のテーブルなら可能です。INTEGER で宣言した列にも、文字列 'hello' をそのまま格納できてしまいます。親和性はあくまで「変換を試みるためのヒント」だからです。きちんと型を強制したい場合は STRICT テーブルを使うと、型に合わない値は明確に拒否されます。これは次の記事で詳しく扱います。

型親和性は「強制」ではなく「好み」

SQLite は動的型付けのデータベースです。値そのものが ストレージクラス(NULL、INTEGER、REAL、TEXT、BLOB)を持っていて、カラムに宣言した型はそこに入れられる値を厳密に縛るわけではありません。宣言した型が果たす役割は、そのカラムに 型親和性(type affinity) を与えること。つまり、SQLite が値を入れるときに「できればこのストレージクラスに変換したい」という優先順位を決めているだけです。

型親和性だけでは型の不一致を防ぎきれない、という例を見てみましょう。

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'りんご');
INSERT INTO items VALUES ('two', 'バナナ');

SELECT id, typeof(id), name FROM items;

2行目では INTEGER カラムに文字列 'two' を入れています。SQLiteは 'two' を数値に変換しようとして失敗し（数字じゃないので当然ですね）、結局 TEXT のまま保存しました。typeof() を使うと、各値が実際にはどのストレージクラスで保存されているかが見えます。カラム定義どおりとは限らない、というわけです。

PostgreSQLやMySQLから来た人はだいたいここで驚きます。でも、これはSQLiteの仕様です。

SQLiteの5つの型親和性（type affinity）

STRICT でない通常のテーブルでは、すべてのカラムに次のいずれか1つの型親和性が割り当てられます。

TEXT — 文字列として扱いたいカラム。
NUMERIC — 数値を優先するが、変換できなければ文字列のまま受け入れる。
INTEGER — NUMERIC と似ているが、小数部のない値は整数として保存する。
REAL — 浮動小数点数を優先する。
BLOB — 何の優先もなし。渡されたものをそのまま保存する。

BLOB 親和性は「親和性なし（no affinity）」とも呼ばれます。型を一切宣言しなかった場合に割り当てられるのがこれです。

CREATE TABLE samples (
    a,                 -- BLOB 親和性（型宣言なし）
    b TEXT,            -- TEXT
    c INTEGER,         -- INTEGER
    d REAL,            -- REAL
    e NUMERIC          -- NUMERIC
);

INSERT INTO samples VALUES ('42', '42', '42', '42', '42');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM samples;

同じ入力 '42' を5つのカラムに入れただけで、保存される型が5種類に分かれます。それぞれのカラムが、自分の型親和性(affinity)に従って変換したり、しなかったりするわけです。

SQLiteはカラム宣言からどうやって型親和性を決めているか

ここがハマりどころなのですが、SQLiteには「使える型」の固定リストというものが存在しません。カラム名の後にはほとんど何でも書けてしまい、SQLiteはその文字列を以下の順番でスキャンして、含まれている部分文字列から型親和性を判断します。

INT を含む → INTEGER
CHAR、CLOB、TEXT のいずれかを含む → TEXT
BLOB を含む、または型指定なし → BLOB
REAL、FLOA、DOUB のいずれかを含む → REAL
それ以外すべて → NUMERIC

ルールはこれだけです。これさえ知っていれば、SQLiteの「なんで?」と思える挙動の多くに説明がつきます。

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT (CHAR を含む)
    b BIGINT,             -- INTEGER (INT を含む)
    c DOUBLE PRECISION,   -- REAL (DOUB を含む)
    d DECIMAL(10,2),      -- NUMERIC (上記のいずれにも一致しない)
    e BOOLEAN,            -- NUMERIC (上記のいずれにも一致しない)
    f DATETIME,           -- NUMERIC (上記のいずれにも一致しない)
    g FLOATING_POINTS,    -- INTEGER (INT を含む — びっくり!)
    h GIBBERISH           -- NUMERIC (上記のいずれにも一致しない)
);

SELECT name, type FROM pragma_table_info('quirks');

FLOATING_POINTS は INTEGER 扱いになります。なぜなら POINTS の中に INT という部分文字列が含まれているからです。マッチングは上から順に行われ、最初にヒットしたルールが採用されます。他のデータベースから型定義をそのままコピペすると意図しない型になってしまうのは、まさにこれが理由です。

型親和性の実際の挙動：INSERT 時の変換

sqlite 型親和性が一番効いてくるのは、値を変換して格納するか、そのまま格納するかを SQLite が判断する場面です。ルールは次のとおり。

TEXT affinity：数値や BLOB はテキストに変換されます。
NUMERIC、INTEGER、REAL affinity：数値として解釈できるテキストは数値に変換され、解釈できないものはテキストのまま残ります。
BLOB affinity：一切変換されません。

CREATE TABLE conv (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'hello');
INSERT INTO conv VALUES ('hello', 'world', 'world');

SELECT n, typeof(n), t, typeof(t), b, typeof(b) FROM conv;

行ごとに見ていきましょう。

NUMERICカラムの'123'は整数の123になります。文字列から数値への変換が成功し、しかも情報の欠落がなかったケースです。
'12.5'は実数の12.5になります。
NUMERICカラムの'hello'はそのままテキストとして保存されます。数値に変換しようがないからですね。
TEXTカラムは、数値を文字列形式に変換して格納します。
BLOBカラムは、型も含めて渡された値をそのまま保存します。

`INTEGER`と`REAL`の細かい違い

INTEGER型親和性(type affinity)の挙動はNUMERICとほぼ同じですが、ひとつだけ違いがあります。3.0のように小数部が実質ゼロの値は、容量節約のために整数の3として格納されるのです。

CREATE TABLE nums (
    i INTEGER,
    n NUMERIC
);

INSERT INTO nums VALUES (3.0, 3.0);
INSERT INTO nums VALUES (3.5, 3.5);

SELECT i, typeof(i), n, typeof(n) FROM nums;

3.0 はどちらのカラムでも INTEGER として格納されます。これは NUMERIC でも同じ最適化が働くためです。一方で 3.5 は小数部を保持したまま REAL のまま残ります。ここで覚えておいてほしいのは、typeof() はカラムが INTEGER で宣言されたか REAL で宣言されたかを教えてくれるわけではないということ。あくまで「実際に格納されている値の型」を返すので、行ごとに結果が変わることもあります。

sqlite 型親和性でハマるケース

この柔軟さは便利な反面、思わぬ落とし穴にもなります。実際のコードでよく遭遇する失敗パターンは2つあります。

1. 不正なデータが紛れ込む。 アプリ側のバグで INTEGER カラムに 'N/A' を渡してしまっても、SQLite はそのまま保存してしまいます。後からそのカラムを使って計算をかけると、結果が変な値になったり NULL が返ってきたり。エラーも警告も出ないまま、静かにデータが壊れていくわけです。

2. 比較が直感に反する挙動になる。 ソートや等価比較は、ストレージクラスが違う値同士で扱いが変わります:

CREATE TABLE mixed (val INTEGER);
INSERT INTO mixed VALUES (10), (2), ('100'), ('20'), (3);

SELECT val, typeof(val) FROM mixed ORDER BY val;

整数は数値として並び、文字列はその後ろに辞書順で並びます。つまり 2, 3, 10(整数が数値順)→ '20', '100'(文字列がアルファベット順)という並びになるわけです。たいていの人が期待する挙動ではないですよね。

INSERT を自分でコントロールできて、入力値もきちんとバリデーションしているなら、通常のテーブルでも問題ありません。でもそうでない場合、あるいは型のチェックを DB 側に任せたい場合は、もっといい選択肢があります。

次のステップ:STRICT テーブル

SQLite 3.37 から導入された STRICT テーブルは、型親和性(type affinity)を無効化し、宣言した型に合わない値を拒否してくれます。デフォルトの動的型付けが欲しいときはそのまま使えて、Postgres のような厳格な型チェックが欲しいときは STRICT を指定すればいい、という使い分けができるわけです。詳しくは次のページで見ていきましょう。

よくある質問

SQLiteの型親和性(Type Affinity)とは何ですか?

型親和性とは、その列に「優先的に格納したいストレージクラス」のことです。SQLiteには TEXT / NUMERIC / INTEGER / REAL / BLOB の5種類があります。値をINSERTすると、SQLiteはその列の親和性に合わせて変換を試みますが、変換するとデータが失われる場合や変換そのものができない場合は、元の型のまま格納します。つまり親和性は「ヒント」であって、強い制約ではありません。

SQLiteは列の親和性をどうやって決めているのですか?

CREATE TABLE に書いた型名の文字列を、上から順にパターンマッチして決めます。具体的には、INT を含めば INTEGER、それ以外で CHAR / CLOB / TEXT を含めば TEXT、BLOB を含むか型指定なしなら BLOB、REAL / FLOA / DOUB を含めば REAL、どれにも当てはまらなければ NUMERIC です。VARCHAR(50) が TEXT になり、BIGINT が INTEGER になるのはこのためで、書いた文字列がそのまま判定対象になります。

SQLiteの列に「型と違う値」を入れることはできますか?

通常のテーブルなら可能です。INTEGER で宣言した列にも、文字列 'hello' をそのまま格納できてしまいます。親和性はあくまで「変換を試みるためのヒント」だからです。きちんと型を強制したい場合は STRICT テーブルを使うと、型に合わない値は明確に拒否されます。これは次の記事で詳しく扱います。

SQLiteの型親和性(Type Affinity)を理解する