Часть 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 из документации данной библиотеки с переводом комментариев:

Сравнение данных в SQLite3 при выполнении математических операций

SQLite3 ведет себя интересно при использовании математических операторов (+, — , *, / и другие). SQLite всегда преобразовывает значения в класс NUMERIC при выполнении математических операций, даже если преобразование будет происходить с потерями. Если математическая операция будет проводиться со значением NULL, то результатом всегда будет NULL вне зависимости от типа операции. Если SQLite3 не может преобразовать значение в класс NUMERIC, то значение преобразуется в ноль.

Сортирующие последовательности и сравнение строк в SQlite3

В SQLite3 есть сортирующие последовательности, они используются для сравнения двух строк, другие СУБД для этих целей используют значения из таблиц кодировок, а вот в SQLite3 для этого есть сортирующие последовательности. В SQLite3 есть три встроенных функции, которые выполняют сравнение строк: BINARY, NOCASE и RTRIM:

  1. Функция BINARY сравнивает строки при помощи функции memcmp (), ее результат не зависит от кодировки, поскольку происходит побайтное сравнение.
  2. Принцип работы NOCASE такой же, как и у BINARY, за исключение первых 26-ти прописных букв ASCII, которые перед сравнением преобразуются в свои эквиваленты в нижнем регистре.
  3. RTRIM работает, как и BINARY, но откидывает пробелы в конце строки.

Стоит отметить, что каждый столбец имеет функцию сравнения, ее можно указать при создании таблицы, если вы ее не объявили, то по умолчанию используется BINARY. В SQLite3 есть правила применения сортирующих последовательностей, их всего три:

  1. Если одному из операндов, расположенному справа, явно назначена сортирующая функция с использованием COLLATE, то для сравнения используется эта явная функция, но с предшествующим выполнением сортирующей функции сопоставленной левому операнду.
  2. Если одним из операндов является столбец, то его сортирующая функция является приоритетной и предшествует выполнению сортирующей функции следующего операнда. С целью указания приоритетности выполнения, имя столбца, которое может начинается с одного или нескольких унарных операторов «+». Такая конструкция будет по-прежнему считается именем.
  3. В остальных случаях для сравнения используется сортирующая функция BINARY.

Давайте посмотрим несколько примеров применения сортирующих последовательностей в SQLite3:

 

 

 

 

 

 

 

 

 

2 комментария к записи Часть 5.5: Сравнение данных в SQLite3. Порядок сортировки в SQLite3

Misnik34

Привет! Спасибо за публикацию, сколько пользуюсь SQLite3, но не знал про функции сравнения и правила сортировки строк в SQLite3. Возможно, не знал потому что не требовалось управлять механизмом сравнения при работе с таблицами. Но в любом случае очень интересный и полезный пост.

Кирилл

Привет!

Спасибо за отзыв! Следи за публикациями, подписывайся на RSS, планирую много разных подробностей по SQLite3.

Текст комментария: