Типы данных и динамическая типизация

Разбираем главную особенность SQLite — гибкую систему типов.

Type affinity (сродство типа) — это рекомендуемый тип столбца, к которому SQLite старается привести значение, но не запрещает хранить значения других типов.

Пять классов хранения

В большинстве баз тип принадлежит столбцу: объявили INTEGER — туда можно класть только целые. SQLite смотрит на это иначе: тип принадлежит значению, а не столбцу. Каждое значение хранится в одном из пяти классов:

Функция typeof() показывает класс конкретного значения. Запустите — один и тот же столбец хранит значения разных типов:

CREATE TABLE box (val);

INSERT INTO box VALUES (42), (3.14), ('привет'), (NULL);

SELECT val, typeof(val) FROM box;

Вывод:

42|integer
3.14|real
привет|text
|null

Обратите внимание: у таблицы box столбец val объявлен вообще без типа — и это допустимо. SQLite спокойно хранит в нём числа, строки и NULL вперемешку.

Сродство типа: как столбец «подсказывает» тип

Если столбцу всё-таки указать тип, SQLite использует его как сродство — мягкую рекомендацию. При вставке он попытается аккуратно преобразовать значение к этому типу. Правила такие:

• Столбец со словом INT в типе получает сродство INTEGER.
• Столбец с CHAR, CLOB или TEXT — сродство TEXT.
• Столбец с REAL, FLOA, DOUB — сродство REAL.
• Без типа или с BLOB — сродство BLOB (значение хранится как есть).
• Всё остальное (например, NUMERIC, DECIMAL) — сродство NUMERIC.

Посмотрим на сродство в действии. Мы вставляем строку '123' в INTEGER-столбец, число 456 в TEXT-столбец и строку '7.5' в REAL-столбец:

CREATE TABLE t (a INTEGER, b TEXT, c REAL);

INSERT INTO t VALUES ('123', 456, '7.5');

SELECT typeof(a), typeof(b), typeof(c) FROM t;

Вывод:

integer|text|real

Каждое значение приведено к сродству столбца: текст '123' стал целым, число 456 — строкой, текст '7.5' — вещественным. Сродство помогает хранить данные единообразно, но, как мы видели выше, не заставляет.

Почему это важно знать

Гибкость удобна, но иногда удивляет: вы можете случайно записать строку туда, где ждали число, и SQLite не возразит. Поэтому относитесь к типам столбцов как к договорённости и следите за данными сами. С версии SQLite 3.37 появились STRICT-таблицы (CREATE TABLE ... STRICT), которые включают жёсткую проверку типов, как в «классических» СУБД, — если такая строгость вам нужна.

Дата и время — это текст или число

Отдельного типа «дата» в SQLite нет. Даты хранят как TEXT в формате 'ГГГГ-ММ-ДД ЧЧ:ММ:СС', как число секунд (Unix-время) или как юлианский день. Работать с ними помогают встроенные функции date(), datetime(), strftime() — об этом будет отдельный урок.

Итог

• В SQLite тип принадлежит значению; классов хранения пять: NULL, INTEGER, REAL, TEXT, BLOB.
• Тип столбца задаёт лишь сродство — мягкую рекомендацию, к которой SQLite приводит значения.
• Для строгой проверки типов есть STRICT-таблицы; даты хранятся как текст или число.