SQLite Type Affinity: So tickt das Spaltentyp-System

Affinity ist eine Präferenz, keine Regel

SQLite arbeitet mit dynamischer Typisierung. Jeder Wert trägt seine eigene Speicherklasse mit sich (NULL, INTEGER, REAL, TEXT, BLOB), und der deklarierte Spaltentyp schränkt nicht strikt ein, was du dort ablegen darfst. Was der deklarierte Typ wirklich macht: Er gibt der Spalte eine Affinität — also eine bevorzugte Speicherklasse, in die SQLite eingehende Werte umzuwandeln versucht.

Schau dir an, was passiert, wenn die Affinität allein nicht reicht, um einen Typkonflikt zu verhindern:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'Apfel');
INSERT INTO items VALUES ('two', 'Banane');

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

Die zweite Zeile speichert den String 'two' in einer INTEGER-Spalte. SQLite hat versucht, 'two' in eine Zahl umzuwandeln, hat es nicht geschafft (kein numerischer Wert) – und es trotzdem als TEXT abgelegt. typeof() zeigt dir die tatsächliche Speicherklasse jedes Wertes an, und die deckt sich nicht immer mit dem, was die Spaltendeklaration vermuten lässt.

Wer von Postgres oder MySQL kommt, ist davon erstmal irritiert. Aber genau so ist es in SQLite gewollt.

Die fünf Type Affinities in SQLite

Jede Spalte in einer Tabelle ohne STRICT-Modus bekommt genau eine dieser Affinitäten zugewiesen:

• TEXT – bevorzugt Strings.
• NUMERIC – bevorzugt Zahlen, akzeptiert aber auch Text, wenn die Umwandlung nicht klappt.
• INTEGER – wie NUMERIC, speichert Werte ohne Nachkommastellen aber als Integer.
• REAL – bevorzugt Fließkommazahlen.
• BLOB – keine Vorliebe, speichert das, was du übergibst, einfach so.

Die BLOB-Affinität nennt man auch „keine Affinität" – sie greift, wenn du gar keinen Datentyp angibst.

CREATE TABLE samples (
    a,                 -- BLOB-Affinität (kein Typ angegeben)
    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;

Gleicher Input – der String '42' – fünf unterschiedlich gespeicherte Typen. Jede Spalte hat ihn entsprechend ihrer Affinity konvertiert (oder eben nicht).

Wie SQLite die Affinity aus deiner Deklaration ableitet

Hier kommt der Punkt, an dem viele stolpern: SQLite hat keine feste Liste „gültiger" Datentypen. Du kannst hinter einem Spaltennamen praktisch alles schreiben, und SQLite ermittelt die Affinity, indem es den Text in dieser Reihenfolge nach Teilstrings durchsucht:

1. Enthält INT → INTEGER
2. Enthält CHAR, CLOB oder TEXT → TEXT
3. Enthält BLOB oder gar keinen Typ → BLOB
4. Enthält REAL, FLOA oder DOUB → REAL
5. Alles andere → NUMERIC

Das ist der komplette Algorithmus. Und er erklärt einiges an scheinbar merkwürdigem Verhalten:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT (enthält CHAR)
    b BIGINT,             -- INTEGER (enthält INT)
    c DOUBLE PRECISION,   -- REAL (enthält DOUB)
    d DECIMAL(10,2),      -- NUMERIC (trifft auf keines der obigen zu)
    e BOOLEAN,            -- NUMERIC (trifft auf keines der obigen zu)
    f DATETIME,           -- NUMERIC (trifft auf keines der obigen zu)
    g FLOATING_POINTS,    -- INTEGER (enthält INT – Überraschung!)
    h GIBBERISH           -- NUMERIC (trifft auf keines der obigen zu)
);

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

FLOATING_POINTS wird zu INTEGER, weil die Zeichenfolge INT in POINTS vorkommt. Es gewinnt immer die erste passende Regel von oben nach unten. Genau deshalb kann es passieren, dass du beim simplen Übernehmen von Datentypen aus einer anderen Datenbank plötzlich etwas ganz anderes bekommst, als du erwartet hast.

SQLite Typumwandlung in der Praxis: Konvertierungen beim Insert

Die Type Affinity wird vor allem dann relevant, wenn SQLite entscheidet, ob dein Wert konvertiert oder unverändert gespeichert wird. Die Regeln im Überblick:

• Affinität TEXT: Zahlen und BLOBs werden zu Text konvertiert.
• Affinitäten NUMERIC, INTEGER, REAL: Text, der wie eine Zahl aussieht, wird konvertiert; alles andere bleibt Text.
• Affinität BLOB: Es wird nichts konvertiert.

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;

Zeile für Zeile:

• '123' in einer NUMERIC-Spalte wird zum Integer 123. Die Umwandlung von Text in eine Zahl hat funktioniert – und zwar verlustfrei.
• '12.5' wird zum Real-Wert 12.5.
• 'hello' in NUMERIC bleibt Text – da gibt es schlicht keine Zahl, in die man umwandeln könnte.
• Die TEXT-Spalte verwandelt Zahlen in ihre String-Darstellung.
• Die BLOB-Spalte speichert alles exakt so, wie es reinkommt – Typ inklusive.

Der feine Unterschied zwischen INTEGER und REAL

Die INTEGER-Affinität verhält sich praktisch genauso wie NUMERIC, aber mit einem kleinen Kniff: Ein Wert wie 3.0, der keinen echten Nachkommaanteil hat, wird platzsparend als Integer 3 abgelegt.

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 landet in beiden Spalten als INTEGER – diese Optimierung greift auch bei NUMERIC. 3.5 behält dagegen seinen Nachkommaanteil und bleibt REAL. Merke: Verlass dich nicht auf typeof(), um herauszufinden, ob eine Spalte als INTEGER oder REAL deklariert wurde. Die Funktion zeigt dir nur, was tatsächlich gespeichert ist – und das kann von Zeile zu Zeile unterschiedlich sein.

Wann SQLite Type Affinity zur Falle wird

Die dynamische Typisierung von SQLite ist praktisch – bis sie es nicht mehr ist. In echtem Code begegnen einem vor allem zwei Stolperfallen:

1. Müll-Daten rutschen durch. Wenn deine Anwendung wegen eines Bugs den Wert 'N/A' an eine INTEGER-Spalte schickt, speichert SQLite ihn anstandslos. Spätere Abfragen, die mit der Spalte rechnen, liefern dann komische Ergebnisse oder NULL. Keine Fehlermeldung, keine Warnung – stille Datenkorruption.

2. Vergleiche verhalten sich seltsam. Beim Sortieren und bei Gleichheitsprüfungen behandelt SQLite Werte unterschiedlicher Storage Classes nicht gleich:

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

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

Integers werden numerisch sortiert, danach kommen die Textwerte in lexikografischer Reihenfolge — und zwar nach allen Zahlen. Das Ergebnis ist also 2, 3, 10 (die Integers in numerischer Reihenfolge), gefolgt von '20', '100' (die Strings alphabetisch sortiert). In den meisten Fällen genau nicht das, was man eigentlich will.

Wenn du die Inserts selbst in der Hand hast und sauber validierst, reichen normale Tabellen völlig aus. Falls nicht — oder falls du die Typprüfung lieber der Datenbank überlassen möchtest — gibt es eine bessere Lösung.

Als Nächstes: STRICT Tables

Mit SQLite 3.37 kamen STRICT Tables dazu. Sie schalten die Type Affinity ab und weisen Werte zurück, die nicht zum deklarierten Spaltentyp passen. So bekommst du die dynamische Typisierung von SQLite dort, wo du sie willst, und eine strikte Typprüfung wie in Postgres dort, wo du sie brauchst. Genau darum geht es auf der nächsten Seite.