SQLite データベースの Boolean データ型

SQLiteには独立したBooleanストレージクラスはありません。それでもBoolean値は表現できます。一般的な方法は、整数列にfalseを0、trueを1として保存することです。アプリケーションやデータをやり取りするインターフェースに適している場合は、テキスト表現を一貫して使う方法もあります。

SQLiteデータベースのBooleanデータ型

SQLiteにはネイティブのBooleanストレージクラスがありません。スキーマ、挿入する値、クエリで表現方法を統一して使いましょう。実用上は整数を使うのが標準的です。宣言した型だけでは値を2状態に制限できないため、CHECK制約で列に許可する値を制限できます。

Booleanデータ型を整数で表す

2状態のフラグには、falseを0、trueを1として保存する整数表現を使います。列の用途が分かるようにINTEGERを宣言し、さらにCHECK (flag IN (0, 1))を追加して、保存される値をこの2つに制限します。SQLiteは柔軟な型付けを採用しているため、型の宣言だけでは保存される値が必ずこのどちらかになるとは限りません。この制約はデータベース内にルールを設定するもので、SQLiteはどのクライアントからの挿入や更新でもそれを強制します。ただし、その強制には明確にしておくべき限界が1つあります。CHECK式が失敗するのは、その評価結果がfalseの場合だけです。NULL IN (0, 1)はfalseではなくNULLと評価されるため、この制約だけではNULLを排除できません。NULLを排除するのは、次に説明するNOT NULLの独立した役割です。

NOT NULLは別の点、つまり各行でフラグの値を必須にするかどうかを指定します。これを付けない場合、行の値はNULLになることがあります。NULLはfalseではなく、不明または未指定を意味します。すべてのレコードに明確な値が必要なら、両方の制約を使います。未回答のフラグを意図的に許可する場合はNOT NULLを省き、クエリでNULLを明示的に考慮してください。

数値の0と1を挿入し、アプリケーションコードではバインドパラメーターを使うのがおすすめです。trueの行はWHERE flag = 1、falseの行はWHERE flag = 0で検索します。'true'のような文字列を使った比較と混在させないでください。こうすると各層で表現方法が明確になり、アプリケーションの部分ごとに同じ列の解釈が異なるのを防げます。この例ではstudentsテーブルを作成して5行を挿入し、それぞれに保存されたフラグを読み取ります。また、2つの表現を直接比較できるよう、テキストラベルを使う別のテーブルも作成します。

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("""
    CREATE TABLE students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER NOT NULL,
        international_student INTEGER NOT NULL
            CHECK (international_student IN (0, 1))
    )
""")
connection.executemany(
    "INSERT INTO students (id, name, age, international_student) VALUES (?, ?, ?, ?)",
    [
        (1, "Stefan", 13, 1),
        (2, "Damon", 14, 0),
        (3, "Elena", 12, 1),
        (4, "Caroline", 12, 1),
        (5, "Bonnie", 13, 0),
    ],
)
integer_rows = connection.execute(
    "SELECT name, international_student FROM students ORDER BY id"
).fetchall()

connection.execute("""
    CREATE TABLE student_flags (
        student_id INTEGER PRIMARY KEY,
        international_student TEXT NOT NULL
            CHECK (international_student IN ('true', 'false'))
    )
""")
connection.executemany(
    "INSERT INTO student_flags VALUES (?, ?)",
    [(1, "true"), (2, "false"), (3, "true"), (4, "true"), (5, "false")],
)
text_rows = connection.execute(
    "SELECT student_id, international_student FROM student_flags ORDER BY student_id"
).fetchall()

print("Integer convention:", integer_rows)
print("Text convention:", text_rows)

Integer convention: [('Stefan', 1), ('Damon', 0), ('Elena', 1), ('Caroline', 1), ('Bonnie', 0)]
Text convention: [(1, 'true'), (2, 'false'), (3, 'true'), (4, 'true'), (5, 'false')]

表示された行では、最初のテーブルに整数値が、2つ目のテーブルにテキストラベルが保持されています。保存形式を確認しやすくするため、この例では全行を選択しています。アプリケーションのクエリで整数を使うテーブルから対象を絞るには、WHERE international_student = 1のような条件を追加します。列の規約に合わせて、リテラルの型を統一してください。

Booleanデータ型を文字列で表す

外部形式ですでにtrueやfalseのようなラベルを使っている場合や、保存された値を確認しやすくしたい場合は、テキストが便利です。列をTEXTとして宣言し、表記と大文字・小文字を統一したうえで、上のstudent_flagsのようにCHECK制約で値を検証します。クエリはテキストラベルをそのまま返すため、アプリケーションコードでも読み書きに同じ規約を使ってください。SQLiteの型アフィニティによって、どちらの宣言もBoolean型になるわけではなく、それだけで列の値が2種類に制限されるわけでもありません。

この規約では、挿入時に選んだテキストラベルを使い、クエリでも同じラベルと比較します。たとえばWHERE international_student = 'true'のように指定します。CHECKのリストで表記の揺れを防ぎ、NOT NULLで各行にいずれかのラベルを必須にできます。単語をやり取りするインターフェースやファイル形式にはテキストが適する場合がありますが、読み書きするすべての側が同じ規約に従う必要があります。1、TRUE、trueのような値を混在させると、比較やアプリケーション側の解析に追加のルールが必要になるため避けてください。

データベース内で2状態の値として使うフラグなら、多くのテーブルでは0と1を使うINTEGERが適しています。各行に明確な値が必要で、許可する値も制限したい場合は、NOT NULLとCHECKも加えます。既存のテキスト形式のインターフェースに合わせる実用上の理由がある場合は、テキストを選びます。どちらかに決める前に、既存のデータやクライアントがそのフィールドをどう表現しているか確認してください。対応関係を文書化し、スキーマで規則を適用し、同じ表現で値をバインドし、すべてのクエリで一致する比較方法を使いましょう。SQLiteではBOOLEANという型名もアフィニティラベルとして宣言できますが、それによって独立したBooleanストレージクラスが作られるわけではなく、アプリケーションの規則を適用する制約の代わりにもなりません。