Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. При работе с базами данных мы часто выполняем запросы, в которых необходимо выполнить сортировку значений или сравнить данные из строк. Каждая СУБД имеет свои собственные алгоритмы сравнения данных и сортировки данных. В данной записи мы познакомимся с тем, как реализовано сравнение данных в SQLite3 и в каком порядке SQLite3 сортирует данные.
Содержание статьи:
Для начала приведем SQL операторы сравнения, которые могут быть использованы в SQLite3:
«=», «==», «<», «<=», «>», «>=», «!=», «<>», «BETWEEN», «IN», «IS», «NOT IN» и «IS NOT»
Типичный набор оператор сравнения для любой СУБД.
В SQLite3, как и в любой другой СУБД, есть порядок сортировки и четкие правила, по которым значения сортируются:
SQLite3 перед тем, как проводить операцию сравнения, может попробовать преобразовать один класс данных в другой, но не стоит забывать, что SQLite выполняет преобразование только в том случае, если оно может быть выполнено без потерь. Для преобразования одного класса данных в другой SQLite применяет три простых правила:
Приведем пример сравнений 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 ведет себя интересно при использовании математических операторов (+, — , *, / и другие). SQLite всегда преобразовывает значения в класс NUMERIC при выполнении математических операций, даже если преобразование будет происходить с потерями. Если математическая операция будет проводиться со значением NULL, то результатом всегда будет NULL вне зависимости от типа операции. Если SQLite3 не может преобразовать значение в класс NUMERIC, то значение преобразуется в ноль.
В SQLite3 есть сортирующие последовательности, они используются для сравнения двух строк, другие СУБД для этих целей используют значения из таблиц кодировок, а вот в SQLite3 для этого есть сортирующие последовательности. В SQLite3 есть три встроенных функции, которые выполняют сравнение строк: BINARY, NOCASE и RTRIM:
Стоит отметить, что каждый столбец имеет функцию сравнения, ее можно указать при создании таблицы, если вы ее не объявили, то по умолчанию используется BINARY. В SQLite3 есть правила применения сортирующих последовательностей, их всего три:
Давайте посмотрим несколько примеров применения сортирующих последовательностей в 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.