Как установить sqlite3 для нечувствительности к регистру при сравнении строк?
Я хочу выбрать записи из базы данных sqlite3 путем сопоставления строк. Но если я использую ‘ = ‘ в предложении where, я обнаружил, что sqlite3 чувствителен к регистру. Может ли кто-нибудь сказать мне, как использовать сравнение строк без учета регистра?
10 ответов
можно использовать COLLATE NOCASE в своем SELECT запрос:
кроме того, в SQLite вы можете указать, что столбец должен быть нечувствительным к регистру при создании таблицы, указав collate nocase в определении столбца (другие параметры: binary (по умолчанию) и rtrim ; см. здесь). Вы можете указать collate nocase при создании индекса. Например:
выражения с участием Test.Text_Value теперь должно быть нечувствительным к регистру. Например:
оптимизатор также может потенциально использовать индекс для поиска и сопоставления без учета регистра в столбце. Вы можете проверить это, используя explain команда SQL, например:
вы можете сделать это так:
(не на решение, но в некоторых случаях очень удобно)
Это не относится к SQLite, но вы можете просто сделать
другой вариант-создать собственные параметры сортировки. Затем можно задать параметры сортировки в столбце или добавить их в предложения select. Он будет использоваться для упорядочения и сравнения.
Это можно использовать, чтобы сделать «вуаля», как «вуаля».
упорядоченной функция должна возвращать целое число, которое является отрицательным, нулевым или положительным, если первая строка меньше, равно или больше второго соответственно.
очевидно, что это добавляет избыточность и потенциал для несогласованности, но если ваши данные статичны, это может быть подходящим вариантом.
вы можете использовать подобный запрос для сравнения соответствующей строки с таблицей vales.
выберите имя столбца из table_name, где имя столбца, как «соответствующее значение сравнения»;
просто вы можете использовать COLLATE NOCASE в своем запросе SELECT:
его работа для меня отлично. SELECT NAME FROM TABLE_NAME WHERE NAME = ‘test Name’ COLLATE NOCASE
SQLite и UNICODE
Несмотря на то, что эта тема затрагивалась на Хабре и раньше, некоторые ключевые вещи не прозвучали. В этой статье делается попытка «закрыть тему». Замечания по дополнению/исправлению приветствуются.
Формат строк в базе
БД SQLite может в базе хранить текст (строковые значения) в формате UTF-8 или в формате UTF-16. При этом порядок байт в 16-битных символах UTF-16 может быть как little-endian, так и big-endian.
То есть в реальности есть три разных формата БД SQLite: UTF-8, UTF-16le, UTF-16be.
Любой из этих форматов может быть использован на любой платформе (то есть ничего не мешает создать на x86 базу с форматом UTF-16be, хотя это неразумно, см. ниже).
Формат строк — это настройка БД, которая задается до создания базы. После создания базы сменить формат нельзя; попытки это сделать молча игнорируются ядром SQLite.
Итак,
формат строк у базы SQLite может быть одним из:
— UTF-8 (используется по умолчанию);
— UTF-16le (родной для x86);
— UTF-16be
и сменить его после создания БД нельзя.
1. И UTF-8, и UTF-16 (см. «суррогатные пары») используют переменное (не фиксированное) число байт для хранения одного символа.
2. ATTACH-ить к базе можно базу только с таким же форматом строк, иначе будет ошибка.
3. Из вашей версии SQLite поддержка UTF-16 возможно была «удалена» при сборке (см. sqlite3.c на предмет SQLITE_OMIT_UTF16).
Передача строк в вызовы API
SQLite API вызовы (на языке C) делятся на два типа: принимающие строки в формате UTF-16 (порядок байт «родной» для платформы) и принимающие строки в формате UTF-8. Например:
Если формат строки базы не совпал с форматом строки, которую передали в API, то переданная строка «на лету» перекодируется в формат базы. Этот процесс — плохой, поскольку отнимает ресурсы и, разумеется, его следует избегать. Однако, он плохой не только из-за этого, см. ниже.
Collation: способ сравнения строк
Следующая тема связана с упорядочиванием строк относительно друг друга (по возрастанию или убыванию) и получению ответа на вопрос «равны ли эти две строки». Не существует «естественного» способа сравнить две строки. Как минимум, встает вопрос: регистро-зависимое или регистро-независимое сравнение? Более того, в одной и той же базе данных могут использоваться оба таких сравнения для разных полей.
В SQLite (да и в любой базе) вводится понятие collation: способ сравнения двух строк между собой. Существуют стандартные (встроенные) collation и можно создавать свои в любом количестве.
Collation это, по сути, метод, который получает строки A и B и возвращает один из трех результатов:
«строка A меньше строки B»,
«строки A и B равны»,
«строка A больше строки B».
Но это не все. Сравнение строк обязано быть транзитивным, иначе оно «поломает» механизмы поиска, который исходят из определенных предположений. Более строго: для всех строк A,B и C должно быть гарантировано, что:
1. Если A==B, то B==A.
2. Если A==B и B==C, то A==C.
3. Если A A.
4 Если A »
(… WHERE name = ‘Alice’).
Итак,
когда SQLite необходимо сравнить две строки, то это сравнение всегда основано на каком-то collation.
Если collation-ы у сравниваемых строк не совпадают, то используется хитрый механизм предпочтения одного collation другому, на который лучше не нарываться.
Стандартные (встроенные) collation
Полноценная поддержка сравнения любых UNICODE символов (без учета регистра) требует довольно много дополнительных данных (таблиц). Разработчики SQLite не стали «раздувать» ядро и встроили простейшие методы сравнения.
Существует три встроенных collation:
BINARY: обычное побайтовое сравнение двух блоков памяти: старый, добрый memcmp()
(используется по умолчанию, если не указан другой collation);
RTRIM: тоже, что и BINARY, но игнорирует концевые пробелы (‘abc ‘ = ‘abc’);
NOCASE: тоже, что и BINARY, но игнорирует регистр для 26 букв латинского алфавита (и только для них).
Реализация стандартных collation
Заглянем «внутрь» SQLite и посмотрим на реализацию функций сравнения для разных collation.
Функция binCollFunc(). Если padFlag <> 0, то делает сравнение RTRIM, иначе BINARY:
А вот функция сравнения строк для collation NOCASE:
Что привлекает внимание? Что строка якобы UTF-8 формата, но все символы строки обрабатываются подряд: нет извлечения и перекодировки символов в UTF-32.
Если долго медитировать над этим кодом, то можно понять, что он, как ни странно, корректно работает и на строках UTF-8, и на любой другой одно-символьной кодировке (например, windows-1251). Откровениями в понимании «почему так» можно делится в комментариях :).
Это плавно подводит нас к пониманию важного тезиса, что
SQLite не интересуется реальным форматом строки UTF-8 до момента, когда требуется выполнять перекодировку строки в UTF-16.
Разумеется, упорядочивание реальных UTF-8 строк стандартными collation даст довольно странные результаты. Но равенство будет работать корректно, а сравнение будет транзитивным и не нарушит работу SQLite.
Выходит, действительно, в базе SQLite можно хранить строки в кодировке, скажем, windows-1251 при условии, что вы нигде не используете UTF-16. Это касается и как строковых литералов внутри SQL, так и строк, передаваемых через привязку параметров.
Аргументы в пользу формата UTF-8 как формата «по умолчанию»
Давайте посмотрим на код функции API sqlite3Prepare16(), которая выполняет парсинг и компиляцию SQL оператора. Интересует нас комментарий в начале тела функции.
парсера SQL операторов в формате UTF-16 в данный момент в ядре SQLite не существует (что не исключает его появления в будущем).
Итак, если строка с SQL оператором передается в формате UTF-16 она всегда вначале переводится в формат UTF-8.
Таким образом, в пользу формата UTF-8:
— нет лишних преобразований при компиляции SQL;
— данные (как правило) занимают меньше места на диске;
— можно хранить данные в любой «байт-на-символ» кодировке, если UTF-16 нигде не используется (а самописные collation-ы учитывают новый формат строки).
Как создать и использовать собственный collation
В параметре eTextRep необходимо указать в каком из форматов ожидаются строки:
Можно задать несколько функций для одной и той же collation, но принимающие разные форматы.
SQLite пытается подобрать метод под формат, иначе (если форматы передаваемой строки и зарегистрированной функции различаются) опять выполняется перекодировка «на лету». Функция сравнения должна вернуть отрицательное число, если первая строка меньше второй; ноль — если строки равны; положительное число, если первая строка больше второй. Сравнение, как уже говорилось, должно быть транзитивным.
Создадим collation (по имени «RU»), который даст нам:
— регистронезависимое сравнение для кириллицы и латиницы
— обычное сравнение для всех остальных символов;
— корректную позицию в алфавите буквы «ё» (точнее, «ё» считается равной «е»).
Это, пока, не полноценная поддержка UNICODE, но это простое решение, которое подходит в 95% случаев.
Примеры будут на Delphi, не пугайтесь.
Код, вообщем, простой и наверное не нуждается в дополнительных пояснениях.
При создании таблицы задаем тип сравнения для столбца name:
Важно! Регистрация collation выполняется в соединении к БД (не в самой БД). Это, по сути, присоединение своего кода к коду SQLite. Другое соединение, которое не задало такой же collation, не сможет использовать эту базу.
LIKE, upper(), lower() и пр.
Код collation-а «RU», разумеется, нетрудно доработать для поддержки других алфавитов. А можно сделать (почти) полноценное UNICODE сравнение, если использовать Windows API вызовы для заполнения таблицы LowerTable.
Почему «почти»? Есть такое понятие как «нормализация» UNICODE, то есть приведение к однозначному представлению. Гугл в помощь!
Однако, поддержка UNICODE находится не только в одних collation. Существует еще:
— оператор LIKE (сравнение по шаблону);
— SQL функции lower() и upper() (переводят символы строки в нижний/верхний регистр).
И некоторые другие функции манипулирования строками: fold(), title() и пр.
Это отдельные механизмы, которые никак collation не используют.
SQLite позволяет, однако, переопределить эти функции (LIKE — это тоже функция) своими.
Почти полноценный UNICODE малой кровью
В предыдущих статьях упоминались библиотеки ICU: International Components for Unicode. Это и есть полноценная поддержка UNICODE. Беда, однако, в том, что это тащит за собой гигантское количество кода и данных, который в 95% случаев не нужен. Если в вашем SQLite уже есть встроенный ICU, то дальше можно не читать.
Так вот, нашелся один умный парень, выпиливший из этого кода не нужное и создавший некий «выжимок» из ICU.
Его оригинальное сообщение, видимо, это.
Речь вот о чем. На базе кода ICU он создал файл sqlite3_unicode.c, который компилируется в DLL (обычно это sqlite3u.dll). Полученная DLL экспортирует функцию sqlite3_unicode_init():
Если вызвать эту функцию для соединения, то она:
— зарегистрирует почти полноценные UNICODE-версии функций lower,upper,fold,title, unaccent;
— введет почти полноценный UNICODE регистронезависимый LIKE.
Размер этой DLL всего 80 Kb и работает она быстро (используются таблицы). Оговорка «почти» важна — это не полноценный UNICODE, но в 95% случаев этой библиотеки хватит.
1. если LIKE переопределен, то SQLite не сможет его оптимизировать индексом (A LIKE ‘XXX%’ не будет использовать индекс по A, если он есть).
2. функции lower(),upper() и пр., вообще говоря, не обязаны быть в движке базы, можно и в коде приложения выполнять эти преобразования.
Юз зис лайбрари эт ёр оун риск, то есть автор этой статьи не несет ответственности никогда и ни за что.
Еще одна реализация регистронезависимого поиска по кириллическим символам в SQLite
Доброго времени суток, Хабровчане!
Проблема поиска по русским символам в SQLite давно уже стала притчей во языцех, причины ее появления довольно подробно описаны здесь. Впрочем, есть довольно распространенные решения данной проблемы, самым популярным из которых является подключение ICU, библиотеки, с помощью которой можно реализовать полноценный поиск по Unicode. Но мне хотелось более короткого в плане кода решения чтобы в результате поиск был:
Для сравнения строк в ядре БД есть функция patternCompare. Как известно, она реализует nocase сравнение только для первых 128 символов UTF-8, на что есть соответствующие проверки в коде. Идеей было написать ещё небольшой блок кода, выполняющей проверку на то, является ли символ кириллическим. Если заглянуть в таблицу UTF-8, то для знакомых сердцу символов выделены позиции с 0x400 по 0x45F. Теперь создадим проверку на кириллический символ и таблицы перевода в верхний и нижний регистр. Немного кода.
Дело за малым, посмотреть все проверки на то, что символ меньше 0x80 и добавить еще одну проверку на кирилличность. Это происходит в двух местах, при проверке первого символа и всех остальных. Не буду приводить всю функцию patternCompare, лишь эти два места (уже заботливо подправленные).
Регистронезависимый поиск русского и английского текста теперь исправно работает для пэттерна WHERE LIKE ‘%xxx%’ но не будет работать для WHERE LIKE ‘xxx%’, если поле индексировано стандартной NOCASE функцией. Так что если вы хотите, чтобы и индексированный поиск тоже работал, придется еще немного повозиться. Заглянем поглубже в код библиотеки и увидим, что за создание индекса c collation NOCASE отвечает функция sqlite3_strnicmp. Вообще, их две, одна с параметром, отвечающим за проверку длины текста, другая без. Нам нужная первая. Но вот только текст в ней, в отличие от patternCompare не декодируется из UTF-8, так что реализуем еще одну проверку на то, что первый байт является старшим для закодированного в UTF-8 кириллического символа, а также реализуем декодирование кириллического символа из UTF-8 с преобразованием его в нижний регистр.
К сожалению, функцию sqlite3_strnicmp пришлось переписать, так как она была в значительной мере оптимизирована под ASCII, теперь она выглядит вот так:
Все, можно поливать соусом и есть компилировать. Также необходимо в конце выполнить простой запрос к БД REINDEX NOCASE; для пересоздания всех регистронезависимых индексов.
Как настроить Sqlite3 без учета регистра при сравнении строк?
Я хочу выбрать записи из базы данных sqlite3 путем сопоставления строк. Но если я использую ‘=’ в предложении where, я обнаружу, что sqlite3 чувствителен к регистру. Может кто-нибудь сказать мне, как использовать сравнение строк без учета регистра?
Вы можете использовать COLLATE NOCASE в своем SELECT запросе:
Кроме того, в SQLite вы можете указать, что столбец должен быть нечувствительным к регистру при создании таблицы, указав collate nocase в определении столбца (другие параметры binary (по умолчанию) и rtrim ; см. Здесь ). Вы также можете указать collate nocase при создании индекса. Например:
Выражения с участием Test.Text_Value теперь должны быть без учета регистра. Например:
Оптимизатор также может использовать индекс для поиска и сопоставления без учета регистра в столбце. Вы можете проверить это с помощью команды explain SQL, например:
Вы можете сделать это так:
(Это не решение, но в некоторых случаях очень удобно)
Это не относится к sqlite, но вы можете просто сделать
Это может быть использовано, чтобы сделать ‘VOILA’ LIKE ‘voilà’.
Функция сортировки должна возвращать целое число, которое является отрицательным, нулевым или положительным, если первая строка меньше, равна или больше второй, соответственно.
Очевидно, что это добавляет избыточность и возможность несогласованности, но если ваши данные статичны, это может быть подходящим вариантом.
Задачи по Python с решениями
Свежие записи
Доступ к базе данных SQLite из Python. Поиск без учета регистра символов
На этом шаге мы рассмотрим использование метода create_function().
Как уже говорилось на 192 шаге, сравнение строк и поиск с помощью оператора LIKE для русских букв производится с учетом регистра символов.
Поэтому следующие выражения вернут значение 0:
Одним из вариантов решения проблемы является преобразование символов обеих строк к верхнему или нижнему регистру. Но встроенные функции SQLite UPPER () и LOWER () с русскими
буквами также работают некорректно. Модуль sqlite3 позволяет создать пользовательскую функцию и связать ее с названием функции в SQL-запросе. Таким образом, можно создать
пользовательскую функцию преобразования регистра символов, а затем указать связанное с ней имя в SQL-запросе.
Связать название функции в SQL-запросе с пользовательской функцией в программе позволяет метод create_function() объекта соединения. Формат метода:
В первом параметре в виде строки указывается название функции, которое будет использоваться в SQL-командах. Количество параметров, принимаемых функцией, задается во втором
параметре, причем параметры могут быть любого типа. Если функция принимает строку, то ее типом данных будет str. В третьем параметре указывается ссылка на пользовательскую функцию
в программе. Для примера произведем поиск рубрики без учета регистра символов:
Архив с файлом можно взять здесь.
Результат работы приложения:
В этом примере предполагается, что значение переменной string получено от пользователя. Обратите внимание на то, что строку для поиска в метод execute () мы передаем в нижнем
регистре. Если этого не сделать и указать преобразование в SQL-запросе, то лишнее преобразование регистра будет производиться при каждом сравнении.
Архив с файлом можно взять здесь.
Результат работы приложения:
На следующем шаге мы рассмотрим создание агрегатных функций.