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

1. Типы данных в SQLite

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

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

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

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

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

• NULL. Значение является значением NULL.

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

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

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

• BLOB. Значение — блок данных, хранящийся точно так, как он был введён.

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

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

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

2.1. Тип данных Boolean

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

SQLite распознаёт ключевые слова «TRUE» и «FALSE» с версии 3.23.0 (2 апреля 2018 года), но эти ключевые слова на самом деле просто альтернативные написания целочисленных литералов 1 и 0 соответственно.

2.2. Тип данных Дата и Время

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

• TEXT как строки ISO8601 ("ГГГГ-ММ-ДД ЧЧ:ММ:СС.МММ").
• REAL как числа Джулиана, количество дней с полудня в Гринвиче 24 ноября 4714 г. до н. э. по пролептическому григорианскому календарю.
• INTEGER как время Unix, количество секунд с 1970-01-01 00:00:00 UTC.

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

3. Сродство типа

Движки баз данных 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 поддерживает понятие «сродства типа» столбцов. Сродство типа столбца — это рекомендуемый тип данных для данных, хранящихся в этом столбце. Важно, что тип данных — это рекомендация, а не требование. Любой столбец по-прежнему может хранить любой тип данных. Просто некоторые столбцы, при выборе, будут отдавать предпочтение одному классу хранения перед другим. Предпочтительный класс хранения столбца называется «сродством».

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

• TEXT
• NUMERIC
• INTEGER
• REAL
• BLOB

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

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

Столбец со сродством NUMERIC может содержать значения всех пяти классов хранения. Когда текстовые данные вставляются в столбец NUMERIC, класс хранения текста преобразуется в INTEGER или REAL (в порядке предпочтения), если текст является правильно сформированным целочисленным или вещественным литералом соответственно. Если текстовое значение является правильно сформированным целочисленным литералом, который слишком велик для хранения в 64-битном знаковом целом числе, оно преобразуется в REAL. Для преобразований между классами хранения TEXT и REAL сохраняются только первые 15 значащих десятичных разрядов числа. Если текстовое значение не является правильно сформированным целочисленным или вещественным литералом, значение хранится как 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 не отдаёт предпочтение одному классу хранения перед другим, и никакие попытки принудительного преобразования данных из одного класса хранения в другой не предпринимаются.

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

Для таблиц, не объявленных как 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.

3.1.1. Примеры имен сродства

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

Примеры имён типов из оператора CREATE TABLE или выражения CAST	Результат родственного типа	Правило, используемое для определения родственного типа
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT	2
BLOB тип данных не указан	BLOB	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	5

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

3.2. Родственный тип выражений

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

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

• Правый операнд операторов IN или NOT IN не имеет родственного типа, если операнд представляет собой список, или имеет тот же родственный тип, что и выражение результата, если операнд представляет собой SELECT.

• Когда выражение представляет собой просто ссылку на столбец реальной таблицы (не представление или подзапрос), тогда выражение имеет тот же родственный тип, что и столбец таблицы.

  • Скобки вокруг имени столбца игнорируются. Следовательно, если X и Y.Z — имена столбцов, то (X) и (Y.Z) также считаются именами столбцов и имеют родственный тип соответствующих столбцов.

  • Любые операторы, применяемые к именам столбцов, включая унитарный оператор «+», преобразуют имя столбца в выражение, которое всегда не имеет родственного типа. Следовательно, даже если X и Y.Z являются именами столбцов, выражения +X и +Y.Z не являются именами столбцов и не имеют родственного типа.

• Выражение вида «CAST(expr AS type)» имеет родственный тип, который совпадает со столбцом с объявленным типом «type».

• Оператор COLLATE имеет тот же родственный тип, что и его левый операнд.

• В противном случае выражение не имеет родственного типа.

3.3. Родственный тип столбцов для представлений и подзапросов

«Столбцы» представления или подзапроса FROM-оператор — это на самом деле выражения в наборе результатов оператора SELECT, который реализует представление или подзапрос. Таким образом, родственный тип столбцов представления или подзапроса определяется правилами родственного типа выражений выше. Рассмотрим пример:

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, а выражения всегда не имеют родственного типа.

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

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

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

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

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

CREATE TABLE t1(
    t  TEXT,     -- text affinity by rule 2
    nu NUMERIC,  -- numeric affinity by rule 5
    i  INTEGER,  -- integer affinity by rule 1
    r  REAL,     -- real affinity by rule 4
    no BLOB      -- no affinity by rule 3
);

-- Values stored as 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

-- Values stored as 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

-- Values stored as 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

-- BLOBs are always stored as BLOBs regardless of column affinity.
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

-- NULLs are also unaffected by affinity
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

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

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

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

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

• Значение с типом хранения NULL считается меньше любого другого значения (включая другое значение с типом хранения NULL).

• Значение INTEGER или REAL меньше любого значения TEXT или BLOB. Когда INTEGER или REAL сравниваются с другим INTEGER или REAL, выполняется численное сравнение.

• Значение TEXT меньше значения BLOB. Когда сравниваются два значения TEXT, используется соответствующая последовательность сортировки для определения результата.

• При сравнении двух значений BLOB результат определяется с помощью memcmp().

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

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

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

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

• Если один операнд имеет родственный тип INTEGER, REAL или NUMERIC, а другой — TEXT или BLOB или не имеет родственного типа, то к другому операнду применяется родственный тип 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.

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

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».

5. Операторы

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

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

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

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

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

7. Последовательности сортировки

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

• BINARY - Сравнивает данные строк с помощью memcmp(), независимо от кодировки текста.
• NOCASE - Аналогично binary, за исключением того, что для сравнения используется sqlite3_strnicmp(). Таким образом, 26 заглавных символов ASCII преобразуются в соответствующие строчные перед сравнением. Обратите внимание, что преобразуются только символы ASCII. SQLite не пытается выполнить полное преобразование регистров UTF из-за размера необходимых таблиц. Также обратите внимание, что любые символы U+0000 в строке считаются терминаторами строки для целей сравнения.
• RTRIM - То же, что и binary, за исключением того, что символы пробелов в конце игнорируются.

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

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

7.1. Назначение последовательностей сортировки из 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.

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

Примеры ниже определяют последовательности сортировки, которые используются для определения результатов сравнения текста, которые могут выполняться различными операторами 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

/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' 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

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1

/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1

Эта страница была в последний раз изменена 27 апреля 2022 г. в 09:17:51 UTC