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

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

1.0 Отсутствие типов

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

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

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

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

2.0 Целочисленный первичный ключ

Одно исключение из бестиповости SQLite — это столбец, тип которого INTEGER PRIMARY KEY. (И вы должны использовать "INTEGER", а не "INT". Столбец типа INT PRIMARY KEY является бестиповым, как и любой другой.) Столбцы типа INTEGER PRIMARY KEY должны содержать 32-битное целое число со знаком. Любая попытка вставить данные, не являющиеся целыми числами, приведёт к ошибке.

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

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

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

Если данные имеют тип строковый, то сравнение определяется стандартными функциями сравнения данных C, memcmp() или strcmp(). Сравнение рассматривает байты из двух входных данных по одному и возвращает первое неравное значение. Строки завершаются '\000', поэтому более короткие строки сортируются раньше, чем более длинные строки, как и ожидалось.

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

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

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

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

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

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

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

BLOB
CHAR
CLOB
TEXT

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

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

5.0 Примеры

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

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

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

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

Числовые и текстовые типы также влияют на ключевое слово DISTINCT:

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

Эта страница была обновлена 08 января 2022 г. 05:02:57 UTC