Типы данных в SQLite: классы хранения и сродство типов

Транзакции обеспечивают целостность данных и должны быть в фокусе каждого разработчика. Транзакции в SQLite

Статическая и динамическая типизация: в чём разница

Все SQL-системы, кроме SQLite, следуют принципу статической, жесткой типизации, где тип данных определяется столбцом, в который он помещен

В отличие от этого, SQLite использует динамическую систему типов, связывая тип данных непосредственно с самим значением, а не с его контейнером. Это позволяет выполнять операции, которые невозможны в традиционных СУБД со статической типизацией, и характеризует динамическую типизацию как ключевую особенность SQLite

С версии 3.37.0 (от 27 ноября 2021 года) SQLite представила возможность создания STRICT-таблиц, обеспечивающих жесткий контроль типов для тех разработчиков, которые предпочитают такой подход

Классы хранения данных в SQLite

Значение, хранящееся в базе данных SQLite или обрабатываемое его движком, относится к одному из классов хранения:

NULL — значение является значением NULL

INTEGER — это знаковое целое число, хранящееся в 0, 1, 2, 3, 4, 6 или 8 байтах, в зависимости от его величины

REAL — значение является числом с плавающей точкой, хранящимся как 8-байтовое число с плавающей точкой IEEE

TEXT — значение является текстовой строкой, хранящейся с использованием кодировки базы данных (UTF-8, UTF-16BE или UTF-16LE)

BLOB — значение является блоком данных, хранящимся в точности так, как было введено

Класс хранения более общая идея, чем тип данных, и включает в себя 7 типов целых чисел различной длины. Этот аспект важен для дискового пространства, но при загрузке в память все INTEGER преобразуются в 8-байтовое целое число

В базе данных SQLite версии 3 любой столбец, кроме столбца INTEGER PRIMARY KEY, может хранить значение любого класса хранения

Все значения, использованные в SQL-операторах — будь то текстовые литералы или параметры, привязанные к подготовленным запросам (prepared statements), — имеют свой класс хранения

Булев тип данных

SQLite не определяет отдельный класс хранения для булевых значений; вместо этого они сохраняются как целые числа 0 (false) и 1 (true)

С версии 3.23.0 (от 2 апреля 2018 года) SQLite распознает ключевые слова "TRUE" и "FALSE", но они являются лишь альтернативными обозначениями для целочисленных литералов 1 и 0 соответственно

Как хранить дату и время в SQLite

Для хранения дат и времени SQLite не выделяет отдельный класс хранения; вместо этого встроенные функции позволяют сохранять их в формате TEXT, REAL или INTEGER

TEXT — в виде строк ISO8601 («YYYY-MM-DD HH:MM:SS.SSS»)

REAL — в виде юлианских дней, то есть количества дней, прошедших с полудня в Гринвиче 24 ноября 4714 года до н. э. по пролептическому григорианскому календарю

INTEGER — в виде Unix Time, то есть количества секунд, прошедших с 1970-01-01 00:00:00 UTC

Приложения могут использовать любой из этих форматов для хранения дат и времени и свободно конвертировать между ними с помощью встроенных функций

Сродство типов в SQLite (Type Affinity)

Движки SQL-баз данных с жёсткой типизацией, как правило, пытаются автоматически преобразовывать значения к соответствующему типу данных. Рассмотрим пример:

CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);

База данных с жёсткой типизацией преобразует строку '123' в целое число 123, а целое число 456 в строку '456' перед выполнением вставки

Для максимальной совместимости между SQLite и другими движками баз данных, а также для того, чтобы приведённый выше пример работал в SQLite так же, как в других движках SQL-баз данных, SQLite поддерживает концепцию «сродства типов» (type affinity) для столбцов. Сродство типа столбца — это рекомендуемый тип для данных, хранящихся в этом столбце

Важная идея здесь состоит в том, что тип является рекомендуемым, а не обязательным. Любой столбец по-прежнему может хранить данные любого типа. Просто некоторые столбцы, при наличии выбора, предпочтут использовать один класс хранения вместо другого. Предпочтительный класс хранения для столбца называется его «сродством»

Каждому столбцу в базе данных SQLite 3 присваивается одно из следующих сродств типов:

• TEXT
• NUMERIC
• INTEGER
• REAL
• BLOB

Историческая справка: сродство типа «BLOB» раньше называлось «NONE». Однако этот термин легко было спутать с «отсутствием сродства», поэтому он был переименован

Столбец со сродством TEXT хранит все данные с использованием классов хранения NULL, TEXT или BLOB. Если числовые данные вставляются в столбец со сродством TEXT, они преобразуются в текстовую форму перед сохранением

Столбец со сродством NUMERIC может содержать значения всех пяти классов хранения. Когда текстовые данные вставляются в столбец NUMERIC, класс хранения текста преобразуется в INTEGER или REAL (в порядке предпочтения), если текст является корректным целочисленным или вещественным литералом соответственно

Если значение TEXT является корректным целочисленным литералом, слишком большим для размещения в 64-битном знаковом целом числе, оно преобразуется в REAL. При преобразованиях между классами хранения TEXT и REAL сохраняется около 15,95 значащих десятичных цифр числа. Точность преобразования ограничена использованием кодировки IEEE 754 binary64, или «double», для значений с плавающей точкой

Если значение TEXT не является корректным целочисленным или вещественным литералом, значение хранится как TEXT. Для целей данного абзаца шестнадцатеричные целочисленные литералы не считаются корректными и хранятся как TEXT — это сделано для исторической совместимости с версиями SQLite до версии 3.8.6 от 2014-08-15, в которой шестнадцатеричные целочисленные литералы были впервые введены в SQLite

Если значение с плавающей точкой, которое может быть точно представлено как целое число, вставляется в столбец со сродством NUMERIC, значение преобразуется в целое число. Попытки преобразовать значения NULL или BLOB не предпринимаются

Строка может выглядеть как литерал с плавающей точкой с десятичной точкой и/или экспоненциальной нотацией, но если значение может быть выражено как целое число, сродство NUMERIC преобразует его в целое число. Таким образом, строка '3.0e+5' хранится в столбце со сродством NUMERIC как целое число 300000, а не как значение с плавающей точкой 300000.0

Столбец с сродством INTEGER ведёт себя так же, как столбец со сродством NUMERIC. Разница между сродством INTEGER и NUMERIC проявляется только в выражении CAST: выражение «CAST(4.0 AS INT)» возвращает целое число 4, тогда как «CAST(4.0 AS NUMERIC)» оставляет значение как число с плавающей точкой 4.0

Столбец со сродством REAL ведёт себя как столбец со сродством NUMERIC, за исключением того, что он принудительно преобразует целочисленные значения в представление с плавающей точкой

В качестве внутренней оптимизации небольшие значения с плавающей точкой без дробной части, хранящиеся в столбцах со сродством REAL, записываются на диск как целые числа для экономии места и автоматически преобразуются обратно в числа с плавающей точкой при считывании значения. Эта оптимизация полностью невидима на уровне SQL и может быть обнаружена только путём изучения необработанных битов файла базы данных

Столбец со сродством BLOB не отдаёт предпочтения ни одному классу хранения перед другим, и никаких попыток привести данные из одного класса хранения в другой не предпринимается

Определение сродства столбца

Для таблиц, не объявленных как STRICT, сродство столбца определяется объявленным типом столбца в соответствии со следующими правилами в указанном порядке:

1. Если объявленный тип содержит строку «INT», ему присваивается сродство INTEGER.
2. Если объявленный тип столбца содержит любую из строк «CHAR», «CLOB» или «TEXT», то этот столбец имеет сродство TEXT. Обратите внимание, что тип VARCHAR содержит строку «CHAR» и поэтому получает сродство TEXT.
3. Если объявленный тип столбца содержит строку «BLOB» или если тип не указан вовсе, столбец имеет сродство BLOB.
4. Если объявленный тип столбца содержит любую из строк «REAL», «FLOA» или «DOUB», столбец имеет сродство REAL.
5. В противном случае сродство равно NUMERIC.

Обратите внимание, что порядок правил определения сродства столбца важен. Столбец с объявленным типом «CHARINT» будет соответствовать и правилу 1, и правилу 2, однако первое правило имеет приоритет, поэтому сродство столбца будет INTEGER

Примеры названий сродств

В следующей таблице показано, как многие распространённые названия типов данных из более традиционных реализаций SQL преобразуются в сродства по пяти правилам предыдущего раздела. Таблица показывает лишь небольшое подмножество названий типов данных, которые SQLite принимает

Обратите внимание, что числовые аргументы в скобках после имени типа (например, «VARCHAR(255)») игнорируются SQLite — SQLite не накладывает никаких ограничений на длину строк, BLOB-значений или числовых значений (кроме глобального ограничения SQLITE_MAX_LENGTH)

Примеры имён типов из выражения CREATE TABLE или CAST	Результирующее сродство	Правило
BLOB (тип не указан)	BLOB	3

Обратите внимание, что объявленный тип «FLOATING POINT» даст сродство INTEGER, а не REAL, из-за строки «INT» в конце слова «POINT». А объявленный тип «STRING» имеет сродство NUMERIC, а не TEXT

Сродство выражений

Каждый столбец таблицы имеет сродство типа (одно из: BLOB, TEXT, INTEGER, REAL или NUMERIC), однако выражения не обязательно имеют сродство

Сродство выражения определяется следующими правилами:

• Правый операнд оператора IN или NOT IN не имеет сродства, если операнд является списком, или имеет ту же сродство, что и выражение результирующего набора, если операнд является SELECT.
• Когда выражение представляет собой простую ссылку на столбец реальной таблицы (не VIEW и не подзапрос), выражение имеет ту же сродство, что и столбец таблицы. Скобки вокруг имени столбца игнорируются. Таким образом, если X и Y.Z являются именами столбцов, то (X) и (Y.Z) также считаются именами столбцов и имеют сродство соответствующих столбцов.
• Любые операторы, применённые к именам столбцов, включая унарный оператор «+» (не выполняющий никаких действий), преобразуют имя столбца в выражение, которое всегда не имеет сродства. Поэтому даже если X и Y.Z являются именами столбцов, выражения +X и +Y.Z не являются именами столбцов и не имеют сродства.
• Выражение вида «CAST( expr AS type )» имеет сродство, аналогичное сродству столбца с объявленным типом «type».
• Оператор COLLATE имеет ту же сродство, что и его левый операнд.
• В противном случае выражение не имеет сродства.

Сродство столбцов для представлений и подзапросов

«Столбцы» VIEW (представления) или подзапроса в предложении FROM на самом деле являются выражениями в результирующем наборе оператора SELECT, реализующего VIEW или подзапрос. Таким образом, сродство столбцов VIEW или подзапроса определяется приведёнными выше правилами сродства выражений. Рассмотрим пример:

CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;

Сродство столбца v1.x будет таким же, как сродство t1.b (TEXT), поскольку v1.x напрямую отображается в t1.b. Однако столбцы v1.y и v1.z не имеют сродства, так как они отображаются в выражения a+c и 42, а выражения всегда не имеют сродства

Сродство столбцов для составных представлений

Когда оператор SELECT, реализующий VIEW или подзапрос в предложении FROM, является составным SELECT, сродство каждого столбца VIEW или подзапроса будет сродством соответствующего столбца результата одного из отдельных операторов SELECT, составляющих составной запрос. Однако не определено, какой именно из операторов SELECT будет использован для определения сродства

Разные составляющие операторы SELECT могут использоваться для определения сродства в разные моменты выполнения запроса. Выбор может различаться в разных версиях SQLite. Выбор может меняться от одного запроса к другому в одной и той же версии SQLite. Выбор может быть разным в разные моменты внутри одного и того же запроса

Поэтому никогда нельзя быть уверенным, какое сродство будет использовано для столбцов составного SELECT, имеющих разные сродства в составляющих подзапросах

Рекомендуется избегать смешивания сродств в составном SELECT, если вас важен тип данных результата. Смешивание сродств в составном SELECT может приводить к неожиданным и неинтуитивным результатам

Пример поведения сродства столбца

Следующий SQL демонстрирует, как SQLite использует сродство столбца для выполнения преобразований типов при вставке значений в таблицу

CREATE TABLE t1( t TEXT, -- сродство text по правилу 2 nu NUMERIC, -- сродство numeric по правилу 5 i INTEGER, -- сродство integer по правилу 1 r REAL, -- сродство real по правилу 4 no BLOB -- нет сродства по правилу 3
);
-- Значения хранятся как TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text
-- BLOB-значения всегда хранятся как BLOB независимо от сродства столбца.
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
blob|blob|blob|blob|blob
-- NULL-значения также не подвержены влиянию сродства.
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null

Выражения сравнения

SQLite версии 3 поддерживает стандартный набор операторов сравнения SQL, включая =, ==, <, <=, >, >=, !=, <>, IN, NOT IN, BETWEEN, IS и IS NOT

Порядок сортировки

Результаты сравнения зависят от классов хранения операндов согласно следующим правилам:

• Значение с классом хранения NULL считается меньше любого другого значения (включая другое значение с классом хранения NULL).
• Значение INTEGER или REAL меньше любого значения TEXT или BLOB. При сравнении INTEGER или REAL с другим INTEGER или REAL выполняется числовое сравнение.
• Значение TEXT меньше значения BLOB. При сравнении двух значений TEXT для определения результата используется соответствующая последовательность сопоставления.
• При сравнении двух значений BLOB результат определяется с помощью memcmp().

Преобразование типов перед сравнением

SQLite может попытаться преобразовать значения между классами хранения INTEGER, REAL и/или TEXT перед выполнением сравнения. Выполняются ли какие-либо преобразования перед сравнением, зависит от сродства типов операндов

«Применить сродство» означает преобразовать операнд в определённый класс хранения тогда и только тогда, когда преобразование не приводит к потере существенной информации. Числовые значения всегда можно преобразовать в TEXT. Значения TEXT можно преобразовать в числовые, если текстовое содержимое является корректным целочисленным или вещественным литералом, но не шестнадцатеричным целочисленным литералом

Значения BLOB преобразуются в значения TEXT путём простой интерпретации двоичного содержимого BLOB как текстовой строки в текущей кодировке базы данных

Сродство применяется к операндам оператора сравнения перед сравнением согласно следующим правилам в указанном порядке:

1. Если один операнд имеет сродство INTEGER, REAL или NUMERIC, а другой операнд имеет сродство TEXT, BLOB или не имеет сродства, то к другому операнду применяется сродство NUMERIC.
2. Если один операнд имеет сродство TEXT, а другой не имеет сродства, то к другому операнду применяется сродство TEXT.
3. В противном случае сродство не применяется и оба операнда сравниваются как есть.

Выражение «a BETWEEN b AND c» трактуется как два отдельных бинарных сравнения «a >= b AND a <= c», даже если это означает, что к 'a' в каждом из сравнений применяются разные сродства. Преобразования типов в сравнениях вида «x IN (SELECT y …)» обрабатываются так, как если бы сравнение было «x=y». Выражение «a IN (x, y, z, …)» эквивалентно «a = +x OR a = +y OR a = +z OR …»

Иными словами, значения справа от оператора IN (значения «x», «y» и «z» в данном примере) считаются не имеющими сродства, даже если они являются значениями столбцов или выражениями CAST

Пример сравнения

Я намеренно привожу этот пример полностью, поскольку он наглядно показывает, как сродство влияет на результаты сравнения в зависимости от типа столбца:

CREATE TABLE t1( a TEXT, -- text affinity b NUMERIC, -- numeric affinity c BLOB, -- no affinity d -- no affinity
);
-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer
-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40, a < 60, a < 600 FROM t1;
0|1|1
-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1
-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right. Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur. All
-- values are compared numerically.
SELECT b < 40, b < 60, b < 600 FROM t1;
0|0|1
-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers. Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1
-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40, c < 60, c < 600 FROM t1;
0|0|0
-- No affinity conversions occur. Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1
-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40, d < 60, d < 600 FROM t1;
0|0|1
-- No affinity conversions occur. INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1

Все результаты в примере остаются теми же, если сравнения переставить местами — если выражения вида «a<40» переписать как «40>a»

Операторы и неявные преобразования типов

Математические операторы (+, -, *, /, %, <<, >>, & и |) интерпретируют оба операнда как числа. Операнды STRING или BLOB автоматически преобразуются в значения REAL или INTEGER. Если STRING или BLOB выглядит как вещественное число (содержит десятичную точку или показатель степени) или если значение выходит за пределы диапазона, представимого как 64-битное знаковое целое, то оно преобразуется в REAL. В противном случае операнд преобразуется в INTEGER

Неявное преобразование типов математических операндов несколько отличается от CAST в NUMERIC тем, что строковые значения и значения BLOB, похожие на вещественные числа, но не имеющие дробной части, сохраняются как REAL, а не преобразуются в INTEGER, как это происходило бы при CAST в NUMERIC. Преобразование из STRING или BLOB в REAL или INTEGER выполняется даже в том случае, если оно является потерянным и необратимым

Некоторые математические операторы (%, <<, >>, & и |) ожидают операнды INTEGER. Для этих операторов операнды REAL преобразуются в INTEGER так же, как при CAST в INTEGER. Операторы <<, >>, & и | всегда возвращают результат INTEGER (или NULL), однако оператор % возвращает либо INTEGER, либо REAL (либо NULL) в зависимости от типа своих операндов. Операнд NULL в математическом операторе даёт результат NULL

Операнд математического оператора, который никак не выглядит числовым и не является NULL, преобразуется в 0 или 0.0. Деление на ноль даёт результат NULL

Сортировка, группировка и составные SELECT

Когда результаты запроса сортируются с помощью предложения ORDER BY, значения с классом хранения NULL идут первыми, затем следуют значения INTEGER и REAL, перемежаясь в числовом порядке, затем значения TEXT в порядке последовательности сравнения, и наконец значения BLOB в порядке memcmp(). Никаких преобразований классов хранения перед сортировкой не происходит

При группировке значений с помощью предложения GROUP BY значения с разными классами хранения считаются различными, за исключением значений INTEGER и REAL, которые считаются равными, если они численно равны. Никакие сродства не применяются ни к каким значениям в результате предложения GROUP BY

Составные операторы SELECT — UNION, INTERSECT и EXCEPT — выполняют неявные сравнения между значениями. Никакое сродство не применяется к операндам сравнения для неявных сравнений, связанных с UNION, INTERSECT или EXCEPT — значения сравниваются как есть

Последовательности сравнения (Collating Sequences)

Когда SQLite сравнивает две строки, он использует последовательность сравнения (collating sequence) или функцию сравнения — два термина для одного и того же — для определения того, какая строка больше, или равны ли две строки. SQLite имеет три встроенные функции сравнения: BINARY, NOCASE и RTRIM

BINARY — сравнивает строковые данные с помощью memcmp(), независимо от кодировки текста

NOCASE — аналогична BINARY, за исключением того, что для сравнения используется sqlite3_strnicmp(). Таким образом, 26 символов верхнего регистра ASCII приводятся к их эквивалентам в нижнем регистре перед выполнением сравнения. Обратите внимание, что приведение регистра выполняется только для символов ASCII. SQLite не пытается выполнять полное приведение регистра UTF из-за размера требуемых таблиц

Также обратите внимание, что любые символы U+0000 в строке считаются терминаторами строки для целей сравнения

RTRIM — то же, что и BINARY, за исключением того, что завершающие пробельные символы игнорируются

Приложение может зарегистрировать дополнительные функции сравнения с помощью интерфейса sqlite3_create_collation()

Функции сравнения имеют значение только при сравнении строковых значений. Числовые значения всегда сравниваются численно, а BLOB-значения всегда сравниваются побайтово с помощью memcmp()

Назначение последовательностей сравнения из SQL

Каждый столбец каждой таблицы имеет связанную функцию сравнения. Если функция сравнения явно не определена, то функция сравнения по умолчанию равна BINARY. Предложение COLLATE в определении столбца используется для определения альтернативных функций сравнения для столбца

Правила определения того, какую функцию сравнения использовать для бинарного оператора сравнения (=, <, >, <=, >=, !=, IS и IS NOT), следующие:

1. Если любой из операндов имеет явное назначение функции сравнения с помощью постфиксного оператора COLLATE, то для сравнения используется явная функция сравнения, с приоритетом функции сравнения левого операнда.
2. Если любой из операндов является столбцом, то используется функция сравнения этого столбца с приоритетом левого операнда. Для целей предыдущего предложения имя столбца, которому предшествует один или несколько унарных операторов «+» и/или операторов CAST, по-прежнему считается именем столбца.
3. В противном случае для сравнения используется функция сравнения BINARY.

Операнд сравнения считается имеющим явное назначение функции сравнения (правило 1 выше), если любое подвыражение операнда использует постфиксный оператор COLLATE. Таким образом, если оператор COLLATE используется где-либо в выражении сравнения, функция сравнения, определённая этим оператором, используется для сравнения строк независимо от того, какие столбцы таблицы могут быть частью этого выражения

Если два или более подвыражений с оператором COLLATE появляются где-либо в сравнении, используется самая левая явная функция сравнения независимо от того, насколько глубоко операторы COLLATE вложены в выражение и как выражение заключено в скобки

Выражение «x BETWEEN y and z» логически эквивалентно двум сравнениям «x >= y AND x <= z» и работает в отношении функций сравнения так, как если бы это были два отдельных сравнения. Выражение «x IN (SELECT y …)» обрабатывается так же, как выражение «x = y», для целей определения последовательности сравнения

Последовательность сравнения, используемая для выражений вида «x IN (y, z, …)», является последовательностью сравнения x. Если для оператора IN требуется явная последовательность сравнения, она должна применяться к левому операнду, вот так: «x COLLATE nocase IN (y,z, …)»

Элементам предложения ORDER BY, являющегося частью оператора SELECT, может быть назначена последовательность сравнения с помощью оператора COLLATE, в этом случае указанная функция сравнения используется для сортировки. В противном случае, если выражение, сортируемое предложением ORDER BY, является столбцом, то последовательность сравнения столбца используется для определения порядка сортировки

Если выражение не является столбцом и не имеет предложения COLLATE, то используется последовательность сравнения BINARY

Примеры последовательностей сравнения

Приведённые ниже примеры определяют последовательности сравнения, которые будут использоваться для определения результатов текстовых сравнений, которые могут выполняться различными операторами SQL. Обратите внимание, что текстовое сравнение может не потребоваться, и последовательность сравнения не используется, в случае числовых значений, BLOB или NULL

CREATE TABLE t1( x INTEGER PRIMARY KEY, a, /* collating sequence BINARY */ b COLLATE BINARY, /* collating sequence BINARY */ c COLLATE RTRIM, /* collating sequence RTRIM */ d COLLATE NOCASE /* collating sequence NOCASE */
);
/* x a b c d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc');
/* Text comparison a=b is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3
/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4
/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

Типичные ошибки при работе с типами данных в SQLite

Понимание системы типов SQLite на практике помогает избежать ряда распространённых ошибок, с которыми я сталкивался при работе с этой базой данных

Ожидание строгой типизации там, где её нет. Разработчики, привыкшие к PostgreSQL или MySQL, нередко удивляются, когда SQLite без ошибок принимает строку в столбец с объявленным типом INTEGER. Это не баг — это намеренное поведение динамической системы типов. Если нужна строгая типизация, следует явно объявить таблицу как STRICT

Смешивание сродств в составном SELECT. Когда UNION или INTERSECT объединяет подзапросы с разными сродствами в одном столбце, результирующее сродство не определено и может меняться от запроса к запросу и даже внутри одного запроса. Лучшая практика — приводить типы явно через CAST или избегать смешивания сродств в составных запросах

Неожиданное поведение шестнадцатеричных литералов. Шестнадцатеричные целочисленные литералы (например, 0xFF) не считаются корректными числовыми литералами для целей сродства NUMERIC и хранятся как TEXT. Это поведение сохранено для совместимости с версиями SQLite до 3.8.6

Путаница с BOOLEAN и DATE. SQLite не имеет отдельных классов хранения для булевых значений и дат. BOOLEAN хранится как INTEGER (0 или 1), а DATE — как TEXT, REAL или INTEGER в зависимости от выбранного формата. Объявление столбца как BOOLEAN или DATE даёт ему сродство NUMERIC по правилу 5, что может приводить к неожиданным преобразованиям при вставке

Деление на ноль. В отличие от многих других СУБД, SQLite не выбрасывает исключение при делении на ноль — результат просто равен NULL. Это поведение нужно учитывать при написании вычислительных запросов

Как проверить тип значения в SQLite

SQLite предоставляет функцию typeof(), которая возвращает класс хранения значения в виде строки: «null», «integer», «real», «text» или «blob». Это удобный инструмент для отладки и проверки того, как именно SQLite хранит конкретное значение после применения сродства столбца

SELECT typeof(NULL); -- null
SELECT typeof(42); -- integer
SELECT typeof(3.14); -- real
SELECT typeof('hello'); -- text
SELECT typeof(x'FF00'); -- blob

Функция typeof() особенно полезна при работе со столбцами, имеющими сродство NUMERIC или BLOB, где фактический класс хранения зависит от вставляемого значения. Например, вставка строки '500.0' в столбец NUMERIC даст integer, а не text — и именно typeof() позволяет убедиться в этом без изучения внутреннего формата файла базы данных

Дополнительно функция CAST(expr AS type) позволяет явно преобразовывать значения между типами, а её результирующее сродство соответствует сродству столбца с объявленным типом «type». Это делает CAST полезным инструментом не только для преобразования, но и для явного управления сродством в выражениях

Ответы на эти вопросы могут быть для вас полезными

Почему SQLite хранит число в столбце TEXT как текст, а не как число?

Потому что сродство TEXT принудительно преобразует все вставляемые числовые значения в текстовую форму перед сохранением. Это намеренное поведение: столбец со сродством TEXT всегда использует классы хранения NULL, TEXT или BLOB

Можно ли хранить дату в SQLite как тип DATE?

Да, объявить столбец как DATE можно, но SQLite не имеет отдельного класса хранения для дат. Столбец DATE получит сродство NUMERIC по правилу 5, и дата будет храниться как TEXT (ISO8601), REAL (юлианские дни) или INTEGER (Unix Time) — в зависимости от того, какое значение вы вставляете

Чем отличается сродство INTEGER от сродства NUMERIC?

Разница проявляется только в выражении CAST. CAST(4.0 AS INT) возвращает целое число 4, тогда как CAST(4.0 AS NUMERIC) оставляет значение как число с плавающей точкой 4.0. В остальных случаях поведение этих двух сродств идентично

Что происходит при сравнении значений разных классов хранения в SQLite?

SQLite применяет фиксированный порядок: NULL меньше всего остального, INTEGER и REAL меньше TEXT и BLOB, TEXT меньше BLOB. Перед сравнением SQLite может применить сродство операндов для преобразования типов — конкретные правила описаны в разделе о преобразовании типов перед сравнением

Как заставить SQLite использовать строгую типизацию, как в других СУБД?

Начиная с версии 3.37.0, SQLite поддерживает STRICT-таблицы. При объявлении таблицы с ключевым словом STRICT движок будет отклонять значения, не соответствующие объявленному типу столбца, — поведение становится аналогичным традиционным СУБД со статической типизацией