Часть 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 из документации данной библиотеки с переводом комментариев:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
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(c), 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 |
Сравнение данных в 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
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 |
Привет! Спасибо за публикацию, сколько пользуюсь SQLite3, но не знал про функции сравнения и правила сортировки строк в SQLite3. Возможно, не знал потому что не требовалось управлять механизмом сравнения при работе с таблицами. Но в любом случае очень интересный и полезный пост.
Привет!
Спасибо за отзыв! Следи за публикациями, подписывайся на RSS, планирую много разных подробностей по SQLite3.