Как вы пишете нечувствительный к регистру запрос для MySQL и Postgres?

Question

Я использую базу данных MySQL для разработки, но развертываю ее в Heroku, которая использует Postgres. Heroku обрабатывает почти все, но мои независимые от регистра слова Like становятся чувствительными к регистру. Я мог бы использовать операторы iLike, но моя локальная база данных MySQL не может справиться с этим.

Каков наилучший способ написать нечувствительный к регистру запрос, совместимый как с MySQL, так и с Postgres? Или мне нужно написать отдельные операторы Like и iLike в зависимости от базы данных, с которой работает мое приложение?

Answer 1

select * from foo where upper(bar) = upper(?);

Если вы установили параметр в верхний регистр в вызывающей стороне, вы можете избежать второго вызова функции.

Answer 2

Мораль этой истории такова: не используйте другой программный стек для разработки и производства. Никогда.

Вы просто получите ошибки, которые не можете воспроизвести в dev; Ваше тестирование будет бесполезным. Просто не делай этого.

Использование другого механизма базы данных не может быть и речи - будет гораздо больше случаев, когда он будет вести себя иначе, чем просто НРАВИТСЯ (также вы проверяли параметры сортировки, используемые базами данных? Они идентичны в КАЖДОМ СЛУЧАЕ? Если нет, вы можете забудьте ORDER BY на столбцах varchar, работающих одинаково)

Answer 3

Используйте Arel:

Author.where(Author.arel_table[:name].matches("%foo%"))

matches будет использовать ILIKE оператор для Postgres, и LIKE для всего остального.

Answer 4

В postgres вы можете сделать это:

SELECT whatever FROM mytable WHERE something ILIKE 'match this';

Я не уверен, что есть эквивалент для MySQL, но вы всегда можете сделать это немного уродливо, но должно работать как в MySQL, так и в postgres:

SELECT whatever FROM mytable WHERE UPPER(something) = UPPER('match this');

Answer 5

Есть несколько ответов, ни один из которых не является удовлетворительным.

• LOWER (bar) = LOWER (?) Будет работать на MySQL и Postgres, но, вероятно, будет работать ужасно на MySQL: MySQL не будет использовать свои индексы из-за функции LOWER. На Postgres вы можете добавить функциональный индекс (на LOWER (bar)), но MySQL не поддерживает это.
• MySQL будет (если вы не установили сопоставление с учетом регистра) автоматически выполнять сопоставление без учета регистра и использовать его индексы. (бар =?).
• Из вашего кода вне базы данных сохраните поля bar и bar_lower, где bar_lower содержит результат lower (bar). (Это также возможно при использовании триггеров базы данных). (Смотрите обсуждение этого решения на Drupal). Это неуклюже, но, по крайней мере, одинаково работает практически на всех базах данных.

Answer 6

REGEXP нечувствителен к регистру (если не используется с BINARY) и может использоваться, например, так...

    SELECT id FROM person WHERE name REGEXP 'john';

... чтобы соответствовать "Джон", "Джон", "Джон" и т. д.

Answer 7

Если вы используете PostgreSQL 8.4, вы можете использовать модуль citext для создания текстовых полей без учета регистра.

Answer 8

Используйте COLLATE.

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

Answer 9

Вы также можете рассмотреть возможность поиска плагина searchlogic, который переключает LIKE/ILIKE за вас.

Answer 10

Вы также можете использовать ~* в postgres, если вы хотите сопоставить подстроку в блоке. ~ соответствует чувствительной к регистру подстроке, ~* нечувствительной к регистру подстроке. Это медленная операция, но могу ли я найти ее полезной для поиска.

Select * from table where column ~* 'UnEvEn TeXt';
Select * from table where column ~ 'Uneven text';

И то, и другое попало бы в "Некоторый неравномерный текст здесь". Только первое попало в "Некоторый НЕВЕННЫЙ ТЕКСТ здесь".

Answer 11

Лучше всего конвертировать в верхний, поскольку он охватывает совместимый синтаксис для 3 наиболее часто используемых серверных баз данных Rails. PostgreSQL, MySQL и SQLite поддерживают этот синтаксис. У него есть (незначительный) недостаток, заключающийся в том, что вы должны вводить строку поиска в вашем приложении или в строке условий, что делает ее немного уродливой, но я думаю, что совместимость, которую вы получаете, делает ее полезной.

И MySQL, и SQLite3 имеют нечувствительный к регистру оператор LIKE. Только PostgreSQL имеет чувствительный к регистру оператор LIKE и специфичный для PostgreSQL (согласно инструкции) оператор ILIKE для поиска без учета регистра. Вы можете указать ILIKE вместо LIKE в своих условиях в приложении Rails, но имейте в виду, что приложение перестанет работать под MySQL или SQLite.

Третий вариант - проверить, какой механизм базы данных вы используете, и соответствующим образом изменить строку поиска. Это может быть лучше сделано путем взлома / monkeypatching адаптеров подключения ActiveRecord и заставить адаптер PostgreSQL изменить строку запроса, чтобы заменить "LIKE" на "ILIKE" до выполнения запроса. Это решение, однако, является наиболее запутанным и в свете более простых способов, таких как верхний регистр обоих терминов, я думаю, что это не мешает усилиям (хотя вы получите много очков брауни за то, что делаете это таким образом).