Type affinity w SQLite: jak naprawdę działają typy kolumn

Affinity to preferencja, a nie reguła

SQLite jest typowany dynamicznie. Wartość niesie własną klasę przechowywania (NULL, INTEGER, REAL, TEXT, BLOB), a zadeklarowany typ kolumny nie ogranicza ściśle tego, co możesz w niej zapisać. Zadeklarowany typ nadaje natomiast kolumnie affinity, czyli preferowaną klasę przechowywania, na którą SQLite próbuje konwertować przychodzące wartości.

Zobacz, co się dzieje, gdy affinity nie wystarcza, żeby zatrzymać niezgodność:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'apple');
INSERT INTO items VALUES ('two', 'banana');

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

Drugi wiersz zapisuje napis 'two' w kolumnie INTEGER. SQLite spróbował przekonwertować 'two' na liczbę, nie dał rady (to nie jest liczba) i i tak zapisał wartość jako TEXT. typeof() pokazuje rzeczywistą klasę przechowywania każdej wartości, a ta nie zawsze zgadza się z deklaracją kolumny.

To zaskakuje osoby przychodzące z Postgresa albo MySQL. Tak jest jednak celowo.

Pięć rodzajów affinity

Każda kolumna w tabeli bez STRICT dostaje dokładnie jedną z nich:

• TEXT: preferuje napisy.
• NUMERIC: preferuje liczby, ale przyjmuje tekst, jeśli nie da się go przekonwertować.
• INTEGER: jak NUMERIC, ale wartości bez części ułamkowej zapisuje jako liczby całkowite.
• REAL: preferuje liczby zmiennoprzecinkowe.
• BLOB: brak preferencji, zapisuje wszystko, co dostanie.

Affinity BLOB nazywa się też „brakiem affinity”: dostajesz ją, gdy w ogóle nie zadeklarujesz typu.

CREATE TABLE samples (
    a,                 -- affinity BLOB (brak zadeklarowanego typu)
    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;

To samo wejście, napis '42', i pięć różnych zapisanych typów. Każda kolumna przekonwertowała wartość (albo nie) zgodnie ze swoją affinity.

Jak SQLite wybiera affinity na podstawie deklaracji

Oto część, na której ludzie się potykają: SQLite nie ma stałej listy „poprawnych” typów. Po nazwie kolumny możesz napisać niemal cokolwiek, a SQLite ustala affinity, przeszukując tekst pod kątem fragmentów, w tej kolejności:

1. Zawiera INT → INTEGER
2. Zawiera CHAR, CLOB albo TEXT → TEXT
3. Zawiera BLOB albo nie ma typu → BLOB
4. Zawiera REAL, FLOA albo DOUB → REAL
5. Wszystko inne → NUMERIC

To cały algorytm. Wyjaśnia sporo dziwactw:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT  (zawiera CHAR)
    b BIGINT,             -- INTEGER (zawiera INT)
    c DOUBLE PRECISION,   -- REAL (zawiera DOUB)
    d DECIMAL(10,2),      -- NUMERIC (nie pasuje do żadnej reguły)
    e BOOLEAN,            -- NUMERIC (nie pasuje do żadnej reguły)
    f DATETIME,           -- NUMERIC (nie pasuje do żadnej reguły)
    g FLOATING_POINTS,    -- INTEGER (zawiera INT, niespodzianka!)
    h GIBBERISH           -- NUMERIC (nie pasuje do żadnej reguły)
);

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

FLOATING_POINTS staje się INTEGER, bo fragment INT występuje w POINTS. Wygrywa pierwsza pasująca reguła, od góry do dołu. Dlatego bezmyślne kopiowanie typów z innej bazy danych może dać coś innego, niż się spodziewasz.

Affinity w praktyce: konwersje przy wstawianiu

Affinity ma największe znaczenie, gdy SQLite decyduje, czy przekonwertować wartość, czy zapisać ją bez zmian. Zasady:

• Affinity TEXT: liczby i BLOBy są konwertowane na tekst.
• Affinity NUMERIC, INTEGER, REAL: tekst, który wygląda jak liczba, jest konwertowany; tekst, który tak nie wygląda, zostaje tekstem.
• Affinity BLOB: nic nie jest konwertowane.

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;

Wiersz po wierszu:

• '123' w kolumnie NUMERIC staje się liczbą całkowitą 123. Konwersja tekstu na liczbę się udała i była bezstratna.
• '12.5' staje się liczbą rzeczywistą 12.5.
• 'hello' w NUMERIC zostaje tekstem: nie ma liczby, na którą można by go przekonwertować.
• Kolumna TEXT zamienia liczby na ich zapis tekstowy.
• Kolumna BLOB zapisuje wszystko dokładnie tak, jak zostało podane, łącznie z typem.

Niuans INTEGER vs REAL

Affinity INTEGER działa niemal identycznie jak NUMERIC, z jednym wyjątkiem: wartość taka jak 3.0, która nie ma rzeczywistej części ułamkowej, jest zapisywana jako liczba całkowita 3, żeby oszczędzić miejsce.

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 trafia do obu kolumn jako INTEGER: ta optymalizacja działa też dla NUMERIC. 3.5 zachowuje część ułamkową i zostaje REAL. Wniosek: nie polegaj na typeof(), żeby sprawdzić, czy kolumna została zadeklarowana jako INTEGER czy REAL. Ta funkcja mówi, co jest faktycznie zapisane, a to może się różnić w zależności od wiersza.

Kiedy affinity daje w kość

Ta elastyczność jest wygodna, dopóki nie przestaje. W prawdziwym kodzie pojawiają się dwa rodzaje problemów:

1. Wkradają się złe dane. Jeśli twoja aplikacja ma błąd, który wysyła 'N/A' do kolumny INTEGER, SQLite to zapisze. Późniejsze zapytania wykonujące obliczenia na tej kolumnie zwracają dziwne wyniki albo NULL. Bez błędu, bez ostrzeżenia, po prostu ciche psucie danych.

2. Porównania zaczynają dziwnie działać. Sortowanie i sprawdzanie równości traktują wartości o różnych klasach przechowywania w różny sposób:

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

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

Liczby całkowite sortują się numerycznie, a potem wartości tekstowe sortują się leksykograficznie i trafiają za wszystkie liczby. Dostajesz więc 2, 3, 10 (liczby całkowite w kolejności numerycznej), a potem '20', '100' (napisy w kolejności alfabetycznej). Nie tego chce większość ludzi.

Jeśli kontrolujesz wstawianie danych i starannie je walidujesz, zwykłe tabele są w porządku. Jeśli nie, albo po prostu chcesz, żeby baza pilnowała typów za ciebie, jest lepsza opcja.

Dalej: tabele STRICT

SQLite 3.37 dodał tabele STRICT, które wyłączają affinity i odrzucają wartości niepasujące do zadeklarowanego typu. Dostajesz domyślne typowanie dynamiczne, gdy go chcesz, i wymuszanie typów w stylu Postgresa, gdy go nie chcesz. O tym jest następna strona.