SQLiteの型は緩い — 型親和性（TYPE AFFINITY）とSTRICTテーブル

SQLiteを他のデータベースと同じ感覚で使っていると、必ず一度は驚く場面があります。

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  age INTEGER
);
 
INSERT INTO users (age) VALUES ('こんにちは');

MySQLやPostgreSQLならエラーになるこのSQLが、SQLiteでは成功します。age 列に日本語の文字列がそのまま入ります。

これはバグではなく、SQLiteの設計です。この章では、その仕組みと付き合い方を整理します。

学習者

えっ、INTEGER って書いたのに文字列が入るんですか？ それだと型を書く意味がないのでは……。

先生

意味はあるんだ。SQLiteの型は「絶対に守るルール」じゃなくて「できればこの型に寄せる」という優先度なんだよ。これを型親和性と呼ぶ。

値の型は「列」ではなく「値」が持つ

多くのデータベースでは、型は列に属します。age INTEGER と宣言したら、その列には整数しか入りません。

SQLiteでは、型は値に属します。列は「どんな型に寄せたいか」という希望を持つだけで、実際の型は1行ごとに異なっていて構いません。

実際にどの型で保存されたかは typeof() 関数で確認できます。

構文： typeof(値)

戻り値： 'null' / 'integer' / 'real' / 'text' / 'blob' のいずれか（文字列）

SELECT age, typeof(age) FROM users;

┌────────────┬─────────────┐
│    age     │ typeof(age) │
├────────────┼─────────────┤
│ 25         │ integer     │
│ こんにちは │ text        │
└────────────┴─────────────┘

5つのストレージクラス

SQLiteが実際に値を保存するときの型は、次の5種類だけです。これをストレージクラスと呼びます。

型親和性（TYPE AFFINITY）とは

CREATE TABLE で書いた型名は、「この列に入れる値は、できればこの型に変換して保存してほしい」という親和性として扱われます。親和性は5種類です。

学習者

親和性が5種類なのはわかりましたが、VARCHAR(255) みたいな型を書いたときはどれになるんですか？

宣言した型名から親和性を決めるルールが決まっています。上から順に判定し、最初に当てはまったものが採用されます。

このルールは、他のデータベース向けに書かれた CREATE TABLE 文をなるべくそのまま受け入れるために作られています。だから VARCHAR(255) も NVARCHAR2 も、とりあえず動きます。

CREATE TABLE posts (
  title TEXT NOT NULL CHECK (length(title) <= 100)
);

型親和性が引き起こす実際の事故

親和性を知らないまま使うと、次のような形で表面化します。

数字の文字列が数値に変換されてしまう

INTEGER 親和性の列に文字列 '0123' を入れると、数値に変換できるため 123 として保存されます。先頭のゼロが消えます。

CREATE TABLE codes (code INTEGER);
INSERT INTO codes VALUES ('0123');
SELECT code, typeof(code) FROM codes;
-- 123 | integer

郵便番号・電話番号・商品コードのように「数字だが数値ではないもの」は、必ず TEXT 型で宣言します。

比較が期待通りに動かない

型が違う値どうしを比較すると、SQLiteはストレージクラスの順序で大小を決めます。順序は NULL < INTEGER = REAL < TEXT < BLOB です。

SELECT 100 > '9';   -- 0（偽）

数値の 100 と文字列の '9' を比べると、「INTEGERはTEXTより小さい」というルールが先に効くため、偽になります。数値のつもりで文字列を入れていると、WHERE や ORDER BY が静かに壊れます。

先生

エラーが出ないのが一番厄介なところ。テストが通っているのに集計結果だけがおかしい、という形で出てくるんだ。

JavaScriptから入れた値の型がバラバラになる

アプリケーション側で Number と String を取り違えていても、SQLiteはエラーを出しません。同じ列に 25 と '25' が混ざり、WHERE age = 25 で片方だけがヒットする、という状態になります。

STRICTテーブルで型を守らせる

SQLite 3.37.0 以降では、テーブル定義の末尾に STRICT を付けることで、他のデータベースと同じ厳密な型チェックを有効にできます。

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER
) STRICT;
 
INSERT INTO users (name, age) VALUES ('田中', 'こんにちは');

Runtime error: cannot store TEXT value in INTEGER column users.age

STRICT テーブルでは、使える型名が次の6つだけに制限されます。

VARCHAR(255) や BOOLEAN、DATETIME は使えなくなります。これは制限に見えますが、「実際には効いていない型名」が書けなくなるという意味で、むしろ定義が正直になります。

STRICTを使うときの注意

STRICT は既存テーブルに後から付けられません。付けたい場合はテーブルを作り直す必要があります。

CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER
) STRICT;
 
INSERT INTO users_new SELECT id, name, age FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

また、STRICT は 3.37.0（2021年11月）以降の機能です。古いSQLiteでは構文エラーになるため、配布するアプリで使う場合は動作環境のバージョンを確認してください。

日付をどう保存するか

日付時刻型がないため、保存方法は自分で決める必要があります。実務では次の3つが使われます。

CREATE TABLE logs (
  id INTEGER PRIMARY KEY,
  message TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

日付関数の詳しい使い方は SQLiteのSQL方言 で扱います。

よくあるハマりどころ

真偽値を 'true' という文字列で入れてしまう

SQLiteに BOOLEAN 型はありません。TRUE / FALSE キーワードは整数の 1 / 0 になりますが、アプリ側から文字列 'true' を渡すとそのまま文字列として保存されます。

SELECT * FROM users WHERE is_active = 1;      -- 1が入っている行だけヒット
SELECT * FROM users WHERE is_active = 'true'; -- 'true'が入っている行だけヒット

両方が混在すると、どちらのクエリも一部の行を取りこぼします。CHECK (is_active IN (0, 1)) を付けておくと防げます。

NUMERIC 親和性で小数が整数に丸められると勘違いする

DECIMAL(10,2) は NUMERIC 親和性になりますが、(10,2) の桁数指定は効きません。金額を扱う場合、浮動小数点の誤差を避けるために最小単位の整数（円なら円、ドルならセント）で保存するのが安全です。

既存プロジェクトで急に全テーブルをSTRICTにしようとする

STRICT 化はテーブルの作り直しを伴い、既存データの型違反が見つかると移行が止まります。まずは新規テーブルから STRICT にし、既存テーブルは typeof() で汚染状況を確認してから順に移行してください。

ちゃんと使うためのポイント

• SQLiteでは型は列ではなく値に属する。同じ列に別の型が混在できる
• 宣言した型名は「型親和性」という希望として扱われる
• VARCHAR(10) の桁数指定は効かない。制限したいなら CHECK
• 郵便番号・電話番号など「数字だが数値ではないもの」は必ず TEXT
• 型の混在は typeof() で調べられる
• 新規テーブルは STRICT を付けて、他のDBMSと同じ厳密さにするのが安全
• 日付は TEXT のISO 8601形式が扱いやすい

次の章では、SQLiteのすべてのテーブルが暗黙に持っている rowid と、INTEGER PRIMARY KEY の特別な関係、そしてデフォルトで無効になっている外部キー制約について見ていきます。

参考リンク

• SQLite公式 — Datatypes In SQLite
• SQLite公式 — STRICT Tables