Типы данных в SQLite версии 3

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

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

1.0. Классы хранения и типы данных

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

• NULL. Пустое значение в таблице базы.
• INTEGER. Целочисленное значение, хранящееся в 1, 2, 3, 4, 6 или 8 байтах, в зависимости от величины самого значения.
• REAL. Числовое значение с плавающей точкой. Хранится в формате 8-байтного числа IEEE с плавающей точкой.
• TEXT. Значение строки текста. Хранится с использованием кодировки базы данных (UTF-8, UTF-16BE или UTF-16LE).
• BLOB. Значение бинарных данных, хранящихся точно в том же виде, в каком были введены.

Отметим, что класс хранения — более широкое понятие, чем тип данных. К примеру, класс хранения INTEGER включает 6 различных типов целочисленных данных различной длины. На диске это записывается по-разному. Но как только целочисленные значения считываются с диска и поступают для обработки в оперативную память, они преобразуются в наиболее общий тип данных (8-байтное целое число). Следовательно, хранение по системе класса данных в практическом плане неотличимо от хранения по типу данных, и они могут быть взаимозаменяемыми.

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

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

1.1. Логические типы данных

SQLite не имеет отдельного логического класса хранения. Вместо этого, логические значения хранятся как целые числа 0 (false) и 1 (true).

1.2 Типы данных даты и времени

SQLite не имеют классов, предназначенных для хранения дат и/или времени. Вместо этого, встроенные функции даты и времени в SQLite способны работать с датами и временем, сохраненными в виде значений TEXT, REAL и INTEGER в следующих форматах:

• TEXT как строка формата ISO8601 ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL как числа юлианского календаря. То есть число дней с полудня 24 ноября 4714 г. до н.э. по Гринвичу в соответствии с ранним григорианским календарём.
• INTEGER как время Unix, — количество секунд с 1970-01-01 00:00:00 UTC.

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

2.0. Аффинированные типы

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

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

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

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

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

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

Столбец, который аффинирован с INTEGER, ведет себя так же, как столбцы, аффинированные с NUMERIC. Разница между аффинированными INTEGER и NUMERIC проявляется только в выражении CAST.

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

Столбец с аффинированным NONE не предпочитает какой-либо определённый класс хранения и не предпринимает никаких попыток принудительного преобразования данных из одного класса в другой.

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

Аффинированность столбца определяется объявлением типа столбца, по следующим правилам в указанном порядке:

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

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

2.2 Примеры названий аффинированных типов

В следующей таблице показано, как много общих названий типов данных из более традиционных реализаций SQL преобразуются в аффинированные типы в соответствии с пятью правилам, изложенными в предыдущем разделе. Эта таблица показывает только небольшое подмножество типов данных, которые будет принимать SQLite. Обратите внимание, что числовые аргументы в скобках после имени типа (например: "VARCHAR (255)") игнорируются SQLite, так как он не накладывает ограничений (кроме общего большого ограничения SQLITE_MAX_LENGTH) на длину строк, бинарных или числовых значений.

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

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

В следующем 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

-- Значения сохраняются как TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
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|real

-- Значения сохраняются как TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer

-- Бинарные данные сохраняются как 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

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

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

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

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

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

3.2. Аффинированность операций сравнения

Перед выполнением сравнения SQLite может попытаться конвертировать значения между классами хранения INTEGER, REAL, и/или TEXT. Предпринимаются попытки конвертирования или нет, но сравнение происходит в зависимости от аффинированности операндов. Афиннированность операндов определяется по следующим правилам:

• Выражение, которое является простой ссылкой на значение столбца, имеет такую же аффинированность, как и столбцы. Заметим, что если X и Y.Z — столбцы, то выражения +X и +Y.Z вычисляются соответственно определенной аффинированности.
• Выражение вида "CAST (выражение AS тип)" имеет ту же аффинированность, что и столбец, объявленный с указанным типом.
• В остальных случаях выражение не имеет вообще никакой аффинированности.

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

"Применить аффинированность" означает конвертировать операнд в определённый класс хранения только в том случае, если преобразование происходит без потерь и является обратимым. Аффинированность используется для операндов сравнения до самого процесса сравнения в соответствии со следующими правилами, применяемыми в указанной последовательности:

• Если один из операндов имеет аффинированность INTEGER, REAL или NUMERIC, а другой операнд — родство TEXT или NONE, то к другому операнду применяется аффинированность NUMERIC.
• Если один из операндов имеет аффинированность TEXT, а у другого аффинированности нет, то к этому другому применяется аффинированность TEXT.
• В противном случае никакая аффинированность не применяется и операнды сравниваются как есть.

Выражение "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.

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

CREATE TABLE t1(
    a TEXT,      -- аффинирован с TEXT
    b NUMERIC,   -- аффинирован с NUMERIC
    c BLOB,      -- неаффинирован
    d            -- неаффинирован
);

-- Значения сохраняются как TEXT, INTEGER, TEXT и INTEGER соответственно
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer

-- Поскольку столбец "a" аффинирован с TEXT, числовые значения с правой стороны
-- выражений будут перед сравнением преобразованы в TEXT.
SELECT a < 40,   a < 60,   a < 600 FROM t1;
0|1|1

-- К правым операндам должна быть применена аффинированность с TEXT, но
-- поскольку они уже принадлежат классу TEXT, это не имеет смысла.
-- Никаких преобразований не происходит.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1

-- Поскольку столбец "b" аффинирован с NUMERIC, к операндам с правой стороны
-- также будет применена аффинированность NUMERIC. Поскольку эти операнды уже
-- являются числовыми, применение аффинированности ненужно.
-- Никаких преобразований не происходит. Все значения сравниваются как числа.
SELECT b < 40,   b < 60,   b < 600 FROM t1;
0|0|1

-- К операндам справа будет применена аффинированность NUMERIC, и они будут
-- преобразованы из TEXT в INTEGER. Затем будет произведено числовое сравнение.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1

-- Никаких аффинированных преобразований не производится. Значения в правых
-- частях принадлежат классу хранения INTEGER и потому меньше значений TEXT
-- слева.
SELECT c < 40,   c < 60,   c < 600 FROM t1;
0|0|0

-- Никаких аффинированных преобразований не производится.
-- Значения сравниваются как значения TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1

-- Никаких аффинированных преобразований не производится. Значения с правой
-- стороны принадлежат классу хранения INTEGER и сравниваются со значениями
-- INTEGER слева как числа.
SELECT d < 40,   d < 60,   d < 600 FROM t1;
0|0|1

-- Никаких аффинированных преобразований не производится.
-- Значения INTEGER слева всегда меньше значений TEXT справа.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1

В примере все результаты останутся такими же, если во всех сравнениях заменить выражения в форме "a<40" на "40>a".

4.0. Операторы

Все математические операторы (+, -, *, /, %, <<, >>, & и |) преобразуют операнды в NUMERIC до выполнения операций. Преобразование происходит даже в том случае, если оно необратимое и с потерями. Операнд NULL приводит к пустому (NULL) результату любую математическую операцию. Операнд в математической операции, который не приводится каким-либо образом к числовому и не является NULL, преобразуется в 0 или 0.0.

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

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

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

Составные операторы для запросов SELECT, то есть UNION, INTERSECT и EXCEPT, производит неявное сравнение между значениями. По отношению к операндам сравнения не применяется аффинированность, а если происходит неявное сравнение, связанное с UNION, INTERSECT и EXCEPT, значения сравниваются «как есть».

6.0. Сортирующие последовательности

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

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

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

6.1. Назначение сортирующих последовательностей в SQL

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

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

1. Если одному из операндов, расположенному справа, явно назначена сортирующая функция с использованием COLLATE, то для сравнения используется эта явная функция, но с предшествующим выполнением сортирующей функции сопоставленной левому операнду.
2. Если одним из операндов является столбец, то его сортирующая функция является приоритетной и предшествует выполнению сортирующей функции следующего операнда. С целью указания приоритетности выполнения, имя столбца, которое может начинается с одного или нескольких унарных операторов "+". Такая конструкция будет по-прежнему считается именем.
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.

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

6.2. Примеры сортирующих последовательностей

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

CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* сортирующая последовательность BINARY */
    b COLLATE BINARY,  /* сортирующая последовательность BINARY */
    c COLLATE RTRIM,   /* сортирующая последовательность RTRIM  */
    d COLLATE NOCASE   /* сортирующая последовательность 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');
 
/* Сравнение строк a=b выполняется с использованием
** сортирующей последовательностью BINARY. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--результат 1 2 3

/* Сравнение строк a=b выполняется с использованием
** сортирующей последовательностью RTRIM. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--результат 1 2 3 4

/* Сравнение строк a=b выполняется с использованием
** сортирующей последовательностью NOCASE. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--результат 1 2 3 4

/* Сравнение строк a=b выполняется с использованием
** сортирующей последовательностью BINARY. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--результат 1 4

/* Сравнение строк 'abc'=c выполняется с использованием
** сортирующей последовательностью RTRIM. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--результат 1 2 3

/* Сравнение строк c='abc' выполняется с использованием
** сортирующей последовательностью RTRIM. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--результат 1 2 3

/* Группировка выполняется с использованием
** сортирующей последовательности NOCASE.
** Значения 'abc', 'ABC' и 'Abc' попадают в одну и ту же группу. */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--результат 4

/* Группировка выполняется с использованием
** сортирующей последовательности BINARY.
** Значения 'abc', 'ABC' и 'Abc' попадают в разные группы */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--результат 1 1 2

/* Сортировка по столбцу c выполняется с использованием
** сортирующей последовательности RTRIM. */
SELECT x FROM t1 ORDER BY c, x;
--результат 4 1 2 3

/* Сортировка по (c||'') выполняется с использованием
** сортирующей последовательности BINARY. */
SELECT x FROM t1 ORDER BY (c||''), x;
--результат 4 2 3 1

/* Сортировка по столбцу c выполняется с использованием
** сортирующей последовательности NOCASE. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--результат 2 4 3 1

Перевод с английского: vanilinkin, специально для xBB.uz, 16.02.2011
Оригинальный текст доступен по адресу http://www.sqlite.org/datatype3.html