Исполняемые доки SQLite: Type Affinity

Q: Что такое type affinity в SQLite?

Type affinity — это предпочтительный класс хранения для колонки. В SQLite их пять: TEXT, NUMERIC, INTEGER, REAL и BLOB. При вставке значения SQLite пытается привести его к сродству колонки, но если преобразование приведёт к потере данных или невозможно — значение сохраняется как есть. Affinity — это подсказка движку, а не жёсткое ограничение.

Q: Как SQLite определяет affinity колонки?

SQLite ищет в типе, который вы написали в CREATE TABLE, подстроки — строго по порядку: если встречается INT — это INTEGER; иначе CHAR, CLOB или TEXT дают TEXT; иначе BLOB (или вообще отсутствие типа) — это BLOB; иначе REAL, FLOA или DOUB — это REAL; во всех остальных случаях — NUMERIC. Именно поэтому VARCHAR(50) превращается в TEXT, а BIGINT — в INTEGER: то, что вы написали, просто прогоняется через сопоставление с шаблоном.

Q: Может ли колонка SQLite хранить значение «неправильного» типа?

В обычных таблицах — да, без проблем. Колонка с типом INTEGER спокойно примет строку 'hello', потому что affinity лишь предлагает преобразование, а не требует его. Если нужен жёсткий контроль типов — используйте STRICT-таблицы: они отвергают значения с несовпадающим типом сразу. О них поговорим в следующей статье.

Affinity — это предпочтение, а не правило

SQLite использует динамическую типизацию. У каждого значения есть свой класс хранения (NULL, INTEGER, REAL, TEXT, BLOB), и объявленный тип колонки на самом деле не ограничивает жёстко то, что в неё можно положить. Объявленный тип задаёт колонке affinity — предпочитаемый класс хранения, к которому SQLite пытается привести входящие значения.

Посмотрите, что происходит, когда type affinity не спасает от несоответствия типов:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'яблоко');
INSERT INTO items VALUES ('two', 'банан');

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

Во второй строке мы пытаемся записать значение 'two' в колонку с типом INTEGER. SQLite попробовал привести 'two' к числу, не смог (это просто не число) — и всё равно сохранил его, но уже как TEXT. Функция typeof() показывает реальный класс хранения каждого значения, и он далеко не всегда совпадает с тем, что заявлено в описании колонки.

Тех, кто пришёл из Postgres или MySQL, это обычно ставит в тупик. Но так задумано.

Пять видов type affinity

Каждой колонке в таблице без режима STRICT присваивается ровно один из этих типов:

TEXT — предпочитает строки.
NUMERIC — предпочитает числа, но если привести не удалось — сохранит текст как есть.
INTEGER — то же, что NUMERIC, но значения без дробной части хранит как целые.
REAL — предпочитает числа с плавающей точкой.
BLOB — без предпочтений, кладёт ровно то, что дали.

BLOB ещё называют "отсутствием affinity" — именно его получает колонка, у которой тип вообще не указан.

CREATE TABLE samples (
    a,                 -- BLOB affinity (тип не указан)
    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;

Один и тот же '42' на входе — и пять разных типов на выходе. Каждая колонка либо преобразовала значение, либо нет — в зависимости от своего affinity.

Как SQLite определяет affinity по объявлению типа

Вот тут многих сбивает с толку: в SQLite нет фиксированного списка «допустимых» типов. После имени колонки можно написать практически что угодно — SQLite определит affinity, просматривая эту строку на наличие подстрок, причём строго в таком порядке:

Содержит INT → INTEGER
Содержит CHAR, CLOB или TEXT → TEXT
Содержит BLOB или тип не указан вовсе → BLOB
Содержит REAL, FLOA или DOUB → REAL
Всё остальное → NUMERIC

Вот и весь алгоритм. Он, кстати, объясняет уйму странностей:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT  (содержит CHAR)
    b BIGINT,             -- INTEGER (содержит INT)
    c DOUBLE PRECISION,   -- REAL (содержит DOUB)
    d DECIMAL(10,2),      -- NUMERIC (не совпадает ни с одним из вышеуказанных)
    e BOOLEAN,            -- NUMERIC (не совпадает ни с одним из вышеуказанных)
    f DATETIME,           -- NUMERIC (не совпадает ни с одним из вышеуказанных)
    g FLOATING_POINTS,    -- INTEGER (содержит INT — сюрприз!)
    h GIBBERISH           -- NUMERIC (не совпадает ни с одним из вышеуказанных)
);

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

FLOATING_POINTS превращается в INTEGER, потому что внутри POINTS встречается подстрока INT. Срабатывает первое подходящее правило сверху вниз — поэтому, если бездумно скопировать типы из другой СУБД, результат может оказаться совсем не таким, как вы ожидали.

Type affinity на практике: преобразования при вставке

Type affinity играет ключевую роль в момент, когда SQLite решает: преобразовать значение или сохранить его как есть. Правила такие:

Affinity TEXT: числа и BLOB приводятся к тексту.
Affinity NUMERIC, INTEGER, REAL: текст, похожий на число, преобразуется в число; остальной текст остаётся текстом.
Affinity BLOB: никаких преобразований не происходит.

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;

Разберём построчно:

'123' в колонке с affinity NUMERIC превращается в целое число 123. Преобразование текста в число прошло успешно и без потерь.
'12.5' становится вещественным 12.5.
'hello' в NUMERIC остаётся текстом — конвертировать в число попросту нечего.
Колонка TEXT приводит числа к их строковому представлению.
А колонка BLOB хранит всё ровно так, как ей передали, вместе с исходным типом.

Нюанс между `INTEGER` и `REAL`

По поведению INTEGER affinity почти не отличается от NUMERIC, но есть один тонкий момент: значение вроде 3.0, у которого дробная часть фактически нулевая, будет сохранено как целое 3 — ради экономии места.

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 попадает в обе колонки как INTEGER — эта оптимизация срабатывает и для NUMERIC. А вот 3.5 сохраняет дробную часть и остаётся REAL. Вывод простой: не полагайтесь на typeof(), чтобы понять, объявлена колонка как INTEGER или REAL. Функция показывает, что реально лежит в ячейке, а это может отличаться от строки к строке.

Когда type affinity стреляет в ногу

Динамическая типизация sqlite удобна ровно до тех пор, пока не начинает мешать. В реальном коде чаще всего всплывают два сценария:

1. В базу проскакивает мусор. Если из-за бага приложение отправит строку 'N/A' в колонку с типом INTEGER, SQLite её спокойно сохранит. А потом запросы с арифметикой по этой колонке начнут возвращать странные числа или NULL. Без ошибок, без предупреждений — тихая порча данных.

2. Сравнения ведут себя непредсказуемо. Сортировка и проверки на равенство по-разному обрабатывают значения из разных классов хранения:

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

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

Сначала идут целые в обычном числовом порядке, а уже после них — строки, отсортированные лексикографически. То есть на выходе получаем 2, 3, 10 (целые по возрастанию), а потом '20', '100' (строки по алфавиту). Согласитесь, мало кто ожидает именно такого результата.

Если вы сами контролируете вставку данных и тщательно их валидируете — обычных таблиц вполне хватит. А если нет, или просто хочется, чтобы за типами следила сама база — есть способ получше.

Что дальше: таблицы STRICT

В SQLite 3.37 появились таблицы STRICT — они отключают type affinity и просто отклоняют значения, не подходящие по типу. По сути, вы получаете динамическую типизацию по умолчанию, когда она нужна, и контроль типов в духе Postgres, когда нужен он. Об этом — на следующей странице.

Часто задаваемые вопросы

Что такое type affinity в SQLite?

Type affinity — это предпочтительный класс хранения для колонки. В SQLite их пять: TEXT, NUMERIC, INTEGER, REAL и BLOB. При вставке значения SQLite пытается привести его к сродству колонки, но если преобразование приведёт к потере данных или невозможно — значение сохраняется как есть. Affinity — это подсказка движку, а не жёсткое ограничение.

Как SQLite определяет affinity колонки?

SQLite ищет в типе, который вы написали в CREATE TABLE, подстроки — строго по порядку: если встречается INT — это INTEGER; иначе CHAR, CLOB или TEXT дают TEXT; иначе BLOB (или вообще отсутствие типа) — это BLOB; иначе REAL, FLOA или DOUB — это REAL; во всех остальных случаях — NUMERIC. Именно поэтому VARCHAR(50) превращается в TEXT, а BIGINT — в INTEGER: то, что вы написали, просто прогоняется через сопоставление с шаблоном.

Может ли колонка SQLite хранить значение «неправильного» типа?

В обычных таблицах — да, без проблем. Колонка с типом INTEGER спокойно примет строку 'hello', потому что affinity лишь предлагает преобразование, а не требует его. Если нужен жёсткий контроль типов — используйте STRICT-таблицы: они отвергают значения с несовпадающим типом сразу. О них поговорим в следующей статье.

Связанные концепции

SQLite Type Affinity: как типы колонок ведут себя на самом деле