Die Spalte heißt INTEGER, enthält aber „42“ als Zeichenkette. SQLite bindet den Datentyp an den Wert, nicht an die Spalte — und meckert nicht.
Aufwand:
ca. 25 Min.
Voraussetzung:
Kommandozeilenwerkzeug sqlite3
Gilt für:
SQLite 3.37.0 und neuer
Das Symptom
Eine Auswertung liefert Unsinn. Die Summe einer Mengenspalte ist zu klein, eine
Sortierung stellt 100 vor 20, ein Vergleich WHERE menge > 5 übergeht Zeilen, die
sichtbar größere Werte enthalten. In der Tabellendefinition steht menge INTEGER,
und in der Anzeige des Datenbankwerkzeugs sieht alles aus wie Zahlen.
Erst typeof(menge) deckt auf, dass ein Teil der Werte als text gespeichert ist.
Eine Fehlermeldung hat es dabei nie gegeben — weder beim Anlegen der Tabelle noch
beim Einfügen der Daten.
Die „Layer 8“-Wahrheit
SQLite hält sich hier nicht an das, was die meisten aus anderen Datenbanken kennen.
Der Datentyp gehört zum Wert, nicht zur Spalte. Was in der Tabellendefinition steht,
ist keine Zusage, sondern eine Vorliebe: SQLite versucht, einen ankommenden Text in
eine Zahl umzuwandeln, wenn er wie eine Zahl aussieht. Sieht er nicht so aus, wird er
als Text abgelegt. Kein Fehler, kein Hinweis, keine Rückfrage.
Der Auslöser ist fast nie die Datenbank, sondern die Schicht davor. Eine
CSV-Zeile mit 12,5 statt 12.5. Ein Formularfeld, das " 42" mit Leerzeichen
liefert. Eine JSON-Antwort, in der Zahlen aus Bequemlichkeit als Strings kodiert
sind. Alles davon ist laut Dokumentation kein wohlgeformtes Zahlenliteral, und alles
davon landet klaglos in der INTEGER-Spalte. Besonders schön ist der Typname STRING:
Er enthält keine der Zeichenketten, nach denen SQLite sucht, und bekommt deshalb
NUMERIC-Affinität — eine Textspalte, die sich für eine Zahlenspalte hält.
Reparieren lässt sich das, aber es gibt keinen Grund, die Falle offen zu lassen.
Seit Version 3.37.0 kennt SQLite STRICT-Tabellen, die den Typ tatsächlich erzwingen
und einen Fehler werfen, statt kreativ zu werden. Wer die Wahl hat zwischen einer
Datenbank, die alles annimmt, und einer, die einmal nein sagt, sollte sich daran
erinnern, wer am Ende die Summen liest.
Die echte, verifizierte Lösung
1000 % faktenbasiert. Jeder Befehl ist real, jeder Pfad existiert. Schritt für Schritt:
1.
Nachsehen, was wirklich in der Spalte steht
Nicht die Tabellendefinition befragen, sondern die Werte. Die Funktion typeof gibt die tatsächliche Speicherklasse jedes einzelnen Wertes zurück. Die Ausgabe zeigt, ob dort integer, real oder eben text steht — und wie viele Zeilen betroffen sind.
SELECT typeof(menge), COUNT(*) FROM bestellungen GROUP BY 1;
2.
Die betroffenen Zeilen sichtbar machen
Bevor irgendetwas geändert wird, erst die Ausreißer ansehen. Meist verrät die Schreibweise sofort die Ursache: ein Komma als Dezimaltrennzeichen, ein Leerzeichen, eine Einheit im Wert oder ein leerer String statt NULL. Genau diese Fälle sind laut Dokumentation keine wohlgeformten Literale und bleiben deshalb Text.
SELECT rowid, menge FROM bestellungen WHERE typeof(menge) = 'text' LIMIT 20;
3.
Die Ursache im Programm abstellen, nicht nur die Daten putzen
Der Text kommt nicht aus der Datenbank, sondern aus dem, was die Anwendung übergibt. Wer Werte aus einem Formular, einer CSV-Datei oder einer JSON-Antwort ungeprüft durchreicht, liefert Zeichenketten — und SQLite nimmt sie an. Die Umwandlung gehört in den Code vor dem Einfügen, sonst ist die Spalte nach der nächsten Importrunde wieder gemischt.
4.
Vorhandene Werte gezielt umwandeln
Nur dort eingreifen, wo der Text tatsächlich eine saubere Zahl ist. Der Umweg über CAST erzwingt die Umwandlung; vorher unbedingt eine Sicherung anlegen, denn CAST liefert bei unbrauchbarem Text stillschweigend 0 statt eines Fehlers. Die Bedingung prüft deshalb den Rückweg: Nur wenn die Zahl, zurück in Text verwandelt, wieder exakt dem gespeicherten Wert entspricht, wird die Zeile angefasst. Alles andere — Kommazahlen mit Komma, Werte mit Einheit, Leerzeichen — bleibt liegen und will von Hand angesehen werden.
UPDATE bestellungen
SET menge = CAST(menge AS INTEGER)
WHERE typeof(menge) = 'text'
AND CAST(CAST(menge AS INTEGER) AS TEXT) = menge;
5.
Für neue Tabellen den Typzwang einschalten
Seit SQLite 3.37.0 vom 27.11.2021 gibt es STRICT-Tabellen. Dazu wird das Schlüsselwort STRICT hinter die schließende Klammer der CREATE-TABLE-Anweisung gesetzt. Erlaubt sind dann nur noch die Typen INT, INTEGER, REAL, TEXT, BLOB und ANY, und ein Wert vom falschen Typ löst einen SQLITE_CONSTRAINT_DATATYPE-Fehler aus. Damit meldet sich der Fehler beim Einfügen statt ein halbes Jahr später in einer Auswertung.
CREATE TABLE bestellungen (
id INTEGER PRIMARY KEY,
menge INTEGER NOT NULL,
artikel TEXT NOT NULL
) STRICT;
Häufige Fragen (FAQ)
?Ich habe die Spalte als INTEGER angelegt. Wieso nimmt SQLite überhaupt Text an?
Weil SQLite dynamisch typisiert. Die Dokumentation sagt es in einem Satz: In SQLite ist der Datentyp eines Wertes mit dem Wert selbst verbunden, nicht mit seinem Behälter. Und weiter: Jede Spalte einer SQLite-3-Datenbank außer einer INTEGER-PRIMARY-KEY-Spalte darf einen Wert jeder Speicherklasse aufnehmen. Der Typ in der Tabellendefinition legt also keine Regel fest, sondern nur eine Neigung — die sogenannte Affinität. SQLite versucht umzuwandeln; klappt es nicht, speichert es den Wert trotzdem, nur eben als Text.
?Wann wird ein eingefügter Text zur Zahl und wann bleibt er Text?
Das entscheidet allein die Form der Zeichenkette. Laut Dokumentation wird die Speicherklasse eines Textes beim Einfügen in eine NUMERIC-Spalte nach INTEGER oder REAL umgewandelt — in dieser Vorzugsreihenfolge —, wenn der Text ein wohlgeformtes Ganzzahl- oder Gleitkommaliteral ist. Ist der TEXT-Wert kein wohlgeformtes Literal, wird er als TEXT gespeichert. Ein führendes Leerzeichen, ein Komma statt eines Punktes oder ein angehängtes Prozentzeichen genügen also, damit aus einer Zahl still eine Zeichenkette wird.
?Meine Spalte heißt STRING. Ist das dasselbe wie TEXT?
Nein, und das ist eine der hübscheren Fallen. SQLite bestimmt die Affinität über Zeichenketten im Typnamen, in fester Reihenfolge: enthält der Name INT, wird es INTEGER; enthält er CHAR, CLOB oder TEXT, wird es TEXT; enthält er BLOB oder ist er leer, wird es BLOB; enthält er REAL, FLOA oder DOUB, wird es REAL; sonst NUMERIC. STRING enthält keine dieser Zeichenketten und bekommt deshalb NUMERIC-Affinität — also ausgerechnet das Gegenteil dessen, was der Name verspricht. Dasselbe gilt für Fantasienamen wie VARIANT.
Belege
Diese Seite ist gegen die folgenden Primärquellen geprüft – Hersteller, Standard oder Gesetzestext. Wer es nicht glaubt, muss es nicht: Da steht, wo es nachzulesen ist.
Der Login wird abgewiesen, obwohl das Passwort stimmt? Bei MySQL zählt auch, von welchem Host aus man kommt — der Benutzer ist an eine Herkunft gebunden.