Часть 5.5: Сравнение данных в SQLite3. Порядок сортировки в SQLite3
Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. При работе с базами данных мы часто выполняем запросы, в которых необходимо выполнить сортировку значений или сравнить данные из строк. Каждая СУБД имеет свои собственные алгоритмы сравнения данных и сортировки данных. В данной записи мы познакомимся с тем, как реализовано сравнение данных в SQLite3 и в каком порядке SQLite3 сортирует данные.
Содержание статьи:
Порядок сортировки и сравнение данных в SQLite3
Для начала приведем SQL операторы сравнения, которые могут быть использованы в SQLite3:
«=», «==», «<», «<=», «>», «>=», «!=», «<>», «BETWEEN», «IN», «IS», «NOT IN» и «IS NOT»
Типичный набор оператор сравнения для любой СУБД.
В SQLite3, как и в любой другой СУБД, есть порядок сортировки и четкие правила, по которым значения сортируются:
- значения с классом NULL считаются меньше любого другого значения, даже другого значения NULL;
- значения с классом REAL или INTEGER считаются меньше, чем значения TEXT или BLOB, когда происходит сравнение INTEGER или REAL используется числовое сравнение, например: 5 больше 4.97645323223423421.
- значение с классом данных TEXT меньше значения с классом BLOB;
- для сравнения значений с классом BLOB между собой используется функция memcmp ().
SQLite3 перед тем, как проводить операцию сравнения, может попробовать преобразовать один класс данных в другой, но не стоит забывать, что SQLite выполняет преобразование только в том случае, если оно может быть выполнено без потерь. Для преобразования одного класса данных в другой SQLite применяет три простых правила:
- если одно значение имеет класс INTEGER, REAL или NUMERIC, а второе значение имеет класс TEXT или BLOB, то SQLite будет пытаться преобразовать второе значение к классу NUMERIC;
- если первое значение имеет класс TEXT, а у второго значения класс не задан, то второе значение будет преобразовано в TEXT;
- если сравниваемые значения не удовлетворяют первым двум правилам, то преобразование не применяется.
Приведем пример сравнений SQLite3 из документации данной библиотеки с переводом комментариев:
[php]
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©, 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
[/php]
Сравнение данных в SQLite3 при выполнении математических операций
SQLite3 ведет себя интересно при использовании математических операторов (+, — , *, / и другие). SQLite всегда преобразовывает значения в класс NUMERIC при выполнении математических операций, даже если преобразование будет происходить с потерями. Если математическая операция будет проводиться со значением NULL, то результатом всегда будет NULL вне зависимости от типа операции. Если SQLite3 не может преобразовать значение в класс NUMERIC, то значение преобразуется в ноль.
Сортирующие последовательности и сравнение строк в SQlite3
В SQLite3 есть сортирующие последовательности, они используются для сравнения двух строк, другие СУБД для этих целей используют значения из таблиц кодировок, а вот в SQLite3 для этого есть сортирующие последовательности. В SQLite3 есть три встроенных функции, которые выполняют сравнение строк: BINARY, NOCASE и RTRIM:
- Функция BINARY сравнивает строки при помощи функции memcmp (), ее результат не зависит от кодировки, поскольку происходит побайтное сравнение.
- Принцип работы NOCASE такой же, как и у BINARY, за исключение первых 26-ти прописных букв ASCII, которые перед сравнением преобразуются в свои эквиваленты в нижнем регистре.
- RTRIM работает, как и BINARY, но откидывает пробелы в конце строки.
Стоит отметить, что каждый столбец имеет функцию сравнения, ее можно указать при создании таблицы, если вы ее не объявили, то по умолчанию используется BINARY. В SQLite3 есть правила применения сортирующих последовательностей, их всего три:
- Если одному из операндов, расположенному справа, явно назначена сортирующая функция с использованием COLLATE, то для сравнения используется эта явная функция, но с предшествующим выполнением сортирующей функции сопоставленной левому операнду.
- Если одним из операндов является столбец, то его сортирующая функция является приоритетной и предшествует выполнению сортирующей функции следующего операнда. С целью указания приоритетности выполнения, имя столбца, которое может начинается с одного или нескольких унарных операторов «+». Такая конструкция будет по-прежнему считается именем.
- В остальных случаях для сравнения используется сортирующая функция BINARY.
Давайте посмотрим несколько примеров применения сортирующих последовательностей в SQLite3:
[php]
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
[/php]
Привет! Спасибо за публикацию, сколько пользуюсь SQLite3, но не знал про функции сравнения и правила сортировки строк в SQLite3. Возможно, не знал потому что не требовалось управлять механизмом сравнения при работе с таблицами. Но в любом случае очень интересный и полезный пост.
Привет!
Спасибо за отзыв! Следи за публикациями, подписывайся на RSS, планирую много разных подробностей по SQLite3.