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

Большинство СУБД, используют статические типы. Со статическим контролем типов тип данных значения определяется контейнером, конкретной колонкой, в которой сохранено значение.

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

Обновлено: с version 3.37.0 (2021-11-27) SQLite предоставляет таблицы STRICT, которые делают принудительное присвоение типов для разработчиков, которые предпочитают такую вещь.

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

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

• NULL. Значение NULL.

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

• REAL. Значение floating point, хранится в 8-байтовом числе IEEE floating point.

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

• BLOB. Значение blob, сохранено точно как это было введено.

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

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

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

2.1. Boolean

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

SQLite признает ключевые слова "TRUE" и "FALSE", с version 3.23.0 (2018-04-02), но те ключевые слова действительно просто альтернативное правописание для 1 и 0 соответственно.

2.2. Date и Time

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

• TEXT как строки ISO8601 ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL как дневные числа Джулиана, число дней с полудня по Гринвичу 24 ноября, 4714 до н.э. согласно proleptic Gregorian calendar.
• INTEGER как Unix Time, число секунд с 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 так, как это делается в других СУБД, SQLite поддерживает понятие "близости типа" на колонках. Близость типа колонки это рекомендуемый тип для данных в той колонке. Важная идея здесь состоит в том, что тип рекомендуется, но не требуется. Любая колонка может все еще сохранить любой тип данных. Просто некоторые колонки, учитывая выбор, предпочтут использовать один класс памяти вместо другого. Предпочтительный класс памяти для колонки называют ее "близостью".

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

• TEXT
• NUMERIC
• INTEGER
• REAL
• BLOB

Близость типа "BLOB" раньше называлась "NONE". Но тот термин было легко перепутать с "no affinity" и таким образом, это было переименовано.

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

Колонка с близостью NUMERIC может содержать значения, используя все пять классов памяти. Когда текстовые данные вставляются в колонку NUMERIC, класс памяти текста преобразовывается в INTEGER или REAL (в порядке предпочтения), если текст это правильно построенное целое число или литерал. Если значение TEXT это правильно построенный литерал целого числа, который является слишком большим, чтобы поместиться в 64-битное целое число со знаком, он преобразовывается в REAL. Для преобразований между TEXT и классами памяти сохранены только первые 15 значительных десятичных цифр числа. Если значение TEXT это не правильно построенное целое число или литерал, то значение сохранено как TEXT. В целях этого параграфа шестнадцатеричные литералы целого числа не считают правильно построенными и хранят как TEXT. Это сделано для исторической совместимости с версиями SQLite до version 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)" вернет integer 4, выражение "CAST(4.0 AS NUMERIC)" вернет floating-point 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_MAX_LENGTH) на длину последовательностей, BLOB или числовых значений.

Имена типов в качестве примера для 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.

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

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

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

• Выражение формы "CAST(expr AS type)" имеет сходство, которое совпадает с колонкой с заявленным типом "type".

• У оператора COLLATE есть та же самая близость, как у операнда его левой стороны.

• Иначе у выражения нет близости.

3.3. Близость колонки к обзорам и подзапросам

"Колонки" 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, а у выражений нет близости.

3.3.1. Близость колонки к составным обзорам

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

Наиболее успешная практика должна избежать смешивать сходства в составном SELECT, если вы заботитесь о типе данных результата. Смешивание сходств в составном SELECT может привести к удивлению и неинтуитивным результатам. См. forum post 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. Выражения сравнения

У версии 3 SQLite есть обычный набор операторов сравнения 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", даже если это означает, что придут к различным сходствам в каждом из сравнений. Преобразования типа данных в сравнениях формы "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

5. Операторы

Математические операторы (+, -, *, /, %, <<, >>, & и |) интерпретируют оба операнда, как будто они были числами. STRING или BLOB автоматически преобразовывают в REAL или INTEGER. Если STRING или BLOB похожи на вещественное число (если у этого есть десятичная точка или экспонента), или если значение вне диапазона, который может быть представлен как 64-битное целое число со знаком, то это преобразовывает в REAL. Иначе операнд преобразовывает в INTEGER. Подразумеваемое преобразование типов математических операндов немного отличается от CAST to NUMERIC в той последовательности и значениях BLOB, которые похожи на вещественные числа, но не имеют никакой дробной части, сохранены как REAL вместо того, чтобы быть преобразованным в INTEGER, как для CAST to NUMERIC. Преобразование от STRING или BLOB в REAL или INTEGER выполняется, даже если это с потерями и необратимо. Некоторые математические операторы (%, <<, >>, & и |) ожидают операнды INTEGER. Для тех операторов операнды REAL преобразовываются в INTEGER таким же образом, как CAST to 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().

Сопоставление функций имеет значение, только сравнивая последовательности. Числовые значения всегда сравниваются численно, и BLOB всегда byte-by-byte через 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-операторами. Обратите внимание на то, что текстовое сравнение не может требоваться, и никакая последовательность сопоставления не применяется в случае значений numeric, 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