Типы данных в SQLite Version 2

1.0. Typelessness

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

Тип данных в SQLite это любая последовательность из ноля или большего количества имен, произвольно сопровождаемых списком из одного или двух целых чисел со знаком. Заметьте в особенности, что тип данных может быть нолем или большим количеством имен. Это означает, что пустая строка это действительный тип данных. Таким образом, можно объявить таблицы, где тип данных каждой колонки оставляют неуказанным:

CREATE TABLE ex1(a,b,c);

Даже при том, что SQLite позволяет типу данных быть опущенным, все еще хорошая идея включать его в ваши запросы CREATE TABLE, так как тип данных часто служит хорошим намеком другим программистам о том, что вы намереваетесь вставить в колонку. И если вы когда-нибудь портируете свой код к другому ядру базы данных, это, вероятно, потребует типа данных некоторого вида. SQLite принимает все обычные типы данных. Например:

CREATE TABLE ex2(a VARCHAR(10), b NVARCHAR(15), c TEXT, d INTEGER,
                 e FLOAT, f BOOLEAN, g CLOB, h BLOB, i TIMESTAMP,
                 j NUMERIC(10,5) k VARYING CHARACTER (24),
                 l NATIONAL VARYING CHARACTER(16));

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

2.0. INTEGER PRIMARY KEY

Одно исключение из typelessness SQLite это колонка, тип которой INTEGER PRIMARY KEY. Необходимо использовать "INTEGER", не "INT". Колонка типа INT PRIMARY KEY является typeless точно так же, как все прочие. Столбцы INTEGER PRIMARY KEY должны содержать 32-битное целое число со знаком. Любая попытка вставить данные нецелого числа приведет к ошибке.

INTEGER PRIMARY KEY может использоваться, чтобы осуществить эквивалент AUTOINCREMENT. При попытке вставить NULL в колонку INTEGER PRIMARY KEY, колонка на самом деле будет заполнена целым числом, которое является на единицу больше, чем самый большой ключ в таблице. Или если самый большой ключ будет 2147483647, то колонка будет заполнена случайным целым числом. Так или иначе колонке INTEGER PRIMARY KEY назначат уникальное целое число. Можно восстановить это целое число, используя sqlite_last_insert_rowid() API или функцию SQL last_insert_rowid() в последующем операторе SELECT.

3.0. Сравнение и порядок сортировки

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

Если данные имеют тип text, сравнение определяется стандартными C-функциями сравнения данных memcmp() или strcmp(). Сравнение смотрит на байты от двух входов один за другим и возвращает первое различие отличное от нуля.

Для числовых данных эта ситуация более сложна. Если оба входа похожи на правильно построенные числа, то они преобразованы в значения с плавающей точкой, используя atof(), и сравнены численно. Если один вход не правильно построенное число, но другой все же число, то число считается меньше, чем нечисло. Если никакой ввод не представляет правильно построенное число, то strcmp() используется, чтобы сделать сравнение.

Не смущайтесь тем, что у колонки мог бы быть "числовой" тип данных. Это не означает, что колонка может содержать только числа. Это просто означает что, если колонка действительно содержит число, оно сортируется в числовом порядке.

Для текста и для числовых значений NULL сортируются перед любым другим значением. Сравнение любого значения с NULL, используя операторы вроде "<" или ">=", всегда false.

4.0. Как SQLite определяет типы данных

Для SQLite version 2.6.3 и ранее все значения использовали числовой тип данных. Тип данных text появляется в версии 2.7.0. В продолжении предполагается, что вы используете версию SQLite 2.7.0 или позже.

Для выражения тип данных результата часто определяется наиболее удаленным оператором. Например, арифметические операторы ("+", "*", "%") всегда возвращают числовые результаты. Оператор объединения строк ("||") возвращает текстовый результат. Если вы когда-либо спрашиваете о типе данных выражения, можно использовать специальную функцию SQL typeof(), чтобы определить, каков тип данных. Например:

sqlite> SELECT typeof('abc'+123);
numeric
sqlite> SELECT typeof('abc'||123);
text

Для столбцов таблицы тип данных определяется описанием типа в CREATE TABLE. Тип данных текст, если и только если описание типа содержит одну или больше следующих последовательностей:

BLOB
CHAR
CLOB
TEXT

Поиск этих последовательностей в описании типа нечувствительный к регистру, конечно. Если какая-либо из вышеупомянутых последовательностей происходит где-нибудь в описании типа, то тип данных колонки text. Заметьте, что тип "VARCHAR" содержит подстроку "CHAR", таким образом, это считают текстом.

Если ни одна из последовательностей выше не происходит нигде в описании типа, то тип данных числовой. Отметьте в особенности, что тип данных для колонок с пустым описанием типа числовой.

5.0. Примеры

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

CREATE TABLE t1(a INTEGER UNIQUE);        CREATE TABLE t2(b TEXT UNIQUE);
INSERT INTO t1 VALUES('0');               INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES('0.0');             INSERT INTO t2 VALUES(0.0);

В последовательности слева потерпит неудачу вторая вставка. В этом случае последовательности '0' и '0.0' рассматривают как числа, так как они вставляются в числовую колонку, но 0==0.0, что нарушает ограничение уникальности. Однако, вторая вставка справа работает. В этом случае константы 0 и 0.0 рассматривают как последовательности, что означает, что они отличны.

SQLite всегда преобразовывает числа в двойную точность (64-битные) с плавающей точкой в целях сравнения. Это означает, что длинная последовательность цифр, которые отличаются только по незначительным цифрам, выдержит сравнение равенства, если они будут в числовой колонке, но выдержит сравнение неравенства, если они находятся в столбце текста. Мы имеем:

INSERT INTO t1                            INSERT INTO t2
   VALUES('12345678901234567890');           VALUES(12345678901234567890);
INSERT INTO t1                            INSERT INTO t2
   VALUES('12345678901234567891');           VALUES(12345678901234567891);

Как прежде, вторая вставка слева потерпит неудачу, потому что сравнение преобразует обе последовательности в число с плавающей запятой сначала, и единственная разница в последовательностях находится в 20-й цифре, которая превышает разрешение 64-битного числа с плавающей точкой. Напротив, вторая вставка справа будет работать, потому что в этом случае, вставляемые числа являются последовательностями и сравнены, используя memcmp().

Числовой и текстовый типы имеют значение также для ключевого слова DISTINCT:

CREATE TABLE t3(a INTEGER);               CREATE TABLE t4(b TEXT);
INSERT INTO t3 VALUES('0');               INSERT INTO t4 VALUES(0);
INSERT INTO t3 VALUES('0.0');             INSERT INTO t4 VALUES(0.0);
SELECT DISTINCT * FROM t3;                SELECT DISTINCT * FROM t4;

Оператор SELECT слева вернет единственную строку, так как '0' и '0.0', рассматриваемые как числа, а потому indistinct. Но SELECT справа вернет две строки, так как 0 и 0.0 рассматриваются как последовательности, которые отличаются.