PostgreSQL: как сделать «нечувствительный к регистру» запрос

214

Есть ли способ написать нечувствительные к регистру запросы в PostgreSQL, например. Я хочу, чтобы следующие 3 запроса возвращали одинаковый результат.

SELECT id FROM groups where name='administrator'

SELECT id FROM groups where name='ADMINISTRATOR'

SELECT id FROM groups where name='Administrator'
  • 0
    если citext поставляется с вашей установкой Postgres, попробуйте тип citext. Это текст без учета регистра
  • 1
    Для новичков в этом вопросе эта ссылка на официальную документацию postgres содержит все ответы, приведенные здесь, а также несколько других вариантов.
Показать ещё 1 комментарий
Теги:

7 ответов

297
Лучший ответ

Используйте LOWER для преобразования строк в нижний регистр перед сравнением.

Попробуйте следующее:

SELECT id 
  FROM groups
 WHERE LOWER(name)=LOWER('Administrator')
Поделиться
  • 68
    Важно отметить, что использование LOWER (или любой функции) в столбцах предикатов - в данном случае «name» - приведет к тому, что любые индексы больше не будут доступны для поиска. Если это большая или часто запрашиваемая таблица, это может вызвать проблемы. Сравнение без учета регистра, citext или индекс на основе функций улучшат производительность.
  • 92
    Или просто создайте индекс следующим образом: CREATE INDEX idx_groups_name ON groups lower (name);
Показать ещё 8 комментариев
111

используя ILIKE вместо LIKE

SELECT id FROM groups WHERE name ILIKE 'Administrator'
Поделиться
  • 22
    В 9 раз медленнее, чем равный ...
  • 4
    Я не вижу, что он добавляет по сравнению с ответом @ Jaiswal, который был дан 8 месяцами ранее.
Показать ещё 2 комментария
96

Наиболее распространенным подходом является либо строчная, либо прописная строка поиска и данные. Но есть две проблемы.

  • Он работает на английском языке, но не на всех языках. (Возможно, даже в большинство языков.) Не каждая строчная буква имеет соответствующий Прописная буква; не каждая буква верхнего регистра имеет соответствующую строчная буква.
  • Использование таких функций, как lower() и upper(), даст вам последовательность сканирования. Он не может использовать индексы. В моей тестовой системе использование lower() принимает примерно в 2000 раз больше, чем запрос, который может использовать индекс. (Тестовые данные имеют чуть более 100 тыс. Строк.)

Есть, по крайней мере, три менее часто используемых решения, которые могут быть более эффективными.

  • Используйте модуль citext, который в основном имитирует поведение типа данных, не учитывающих регистр. Загрузив этот модуль, вы можете создать индекс без учета регистра на CREATE INDEX ON groups (name::citext);. (Но см. Ниже.)
  • Используйте регистр без учета регистра. Это устанавливается при инициализации база данных. Использование без учета регистра соответствия означает, что вы можете принять почти любой формат из клиентского кода, и вы все равно вернетесь полезные результаты. (Это также означает, что вы не можете делать запросы с учетом регистра. Duh.)
  • Создайте функциональный индекс. Создайте индекс нижнего регистра, используя CREATE INDEX ON groups (LOWER(name));. Сделав это, вы можете воспользоваться индекса с запросами типа SELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR'); или SELECT id FROM groups WHERE LOWER(name) = 'administrator'; Однако вы должны помнить, что используете LOWER().

Модуль citext не обеспечивает истинный тип данных, нечувствительных к регистру. Вместо этого он ведет себя так, как если бы каждая строка была нижней. То есть, он ведет себя так, как будто вы вызывали lower() для каждой строки, как в номере 3 выше. Преимущество состоит в том, что программистам не нужно запоминать строчные строки. Но вам нужно прочитать разделы "Поведение сравнения строк" ​​и "Ограничения" в документах, прежде чем вы решите использовать citext.

Поделиться
  • 1
    О # 1: Это не должно быть проблемой, так как это будут две разные строки (подумайте об этом как о выполнении col = 'a' и col = 'b' ). О # 2: Как вы сказали, вы можете создать индекс для выражения, так что это на самом деле не проблема. Но я согласен с вами, что изменение параметров сортировки, скорее всего, является лучшим решением.
  • 0
    Слышали ли вы, чтобы кто-нибудь говорил, что citext не работает с postgresql jdbc (hibernate)?
Показать ещё 16 комментариев
81

Вы можете использовать ILIKE. то есть.

SELECT id FROM groups where name ILIKE 'administrator'
Поделиться
  • 0
    Это правильно и работает нормально для меня, я использую MAC OS X (Mountain Lion).
  • 4
    Это будет работать, но с медленным ответом. Чтобы получить быстрый доступ к таблицам по результатам вычислений, предлагаю использовать lower функцию. Смотрите больше деталей
Показать ещё 1 комментарий
46

Вы также можете прочитать ключевое слово ILIKE. Иногда это может быть весьма полезно, хотя оно не соответствует стандарту SQL. См. Здесь для получения дополнительной информации: http://www.postgresql.org/docs/9.2/static/functions-matching.html

Поделиться
  • 6
    Здесь следует обратить внимание на злонамеренный ввод данных пользователем. Если вы выполняете запрос, такой как email ILIKE 'user-input-email-here' , обязательно email ILIKE 'user-input-email-here' пользователя. В противном случае люди могут вводить символы, такие как%, которые соответствуют чему угодно.
  • 1
    @MattDeLeon Привет. Хорошо сказано. Но я просто хочу спросить вас, если я буду использовать ILIKE и prepared statements , это защитит меня от sql injection ?
Показать ещё 3 комментария
15

Вы также можете использовать регулярные выражения POSIX, например

SELECT id FROM groups where name ~* 'administrator'

SELECT 'asd' ~* 'AsD' возвращает t

Поделиться
  • 0
    У меня была та же проблема, мне нужно было поиск без учета регистра в моей базе данных PostgreSQL. Я думал о преобразовании пользовательской строки ввода в регулярное выражение. Теперь использование ~ * вместо = или LIKE работает отлично! Мне не нужно было создавать новые индексы, столбцы или что-то еще. Конечно, поиск по регулярному выражению медленнее, чем прямое байтовое сравнение, но я не думаю, что влияние на производительность было бы гораздо более значительным, чем необходимость обрабатывать два набора данных (один в нижнем или верхнем регистре только для поиска, а затем для получения соответствующего оригинала). данные из другого набора). Кроме того, это чище!
  • 1
    Хорошо, но как сделать, например, regexp_matches ()?
Показать ещё 1 комментарий
1

Использование ~* может значительно улучшить производительность, с функциональностью INSTR.

SELECT id FROM groups WHERE name ~* 'adm'

возвращать строки с именем, содержащим OR, равным "adm".

Поделиться
  • 0
    Привет, Робин, добро пожаловать на ТАК. Ответ Джеймса Брауна уже предложил это решение. Кроме того, предложенный вами ответ никоим образом не использует регулярные выражения.

Ещё вопросы

Сообщество Overcoder
Наверх
Меню