Закажите SQL по сильным LIKE?

У меня есть следующий запрос:

SELECT * FROM table_name
WHERE (col_1 LIKE '%$keyword%'
    OR col_2 LIKE '%$keyword%'
    OR col_3 LIKE '%$keyword%')
  AND .... <some optional filters> ...

существует ли стратегия сортировки на основе наиболее релевантных результатов?

Ответ 1

Когда вы говорите о "релевантности", вы действительно хотите искать естественный язык, который поддерживается MySQL полнотекстовым поиском. Синтаксис отличается от обычного как запросы, и вам нужно добавить специальный индекс в таблицу, но упорядочение по релевантности возможно таким образом.

Здесь, как MySQL вычисляет релевантность (по ссылке):

Когда MATCH() используется в предложении WHERE, как в примере, показанном ранее, возвращаемые строки автоматически сортируются с наивысшей релевантностью. Значения релевантности - неотрицательные числа с плавающей запятой. Нулевая релевантность означает отсутствие сходства. Релевантность вычисляется на основе количества слов в строке, количества уникальных слов в этой строке, общего количества слов в коллекции и количества документов (строк), содержащих определенное слово.

Чтобы создать полнотекстовый индекс в существующей таблице, используйте модификатор FULLTEXT:

CREATE FULLTEXT INDEX index_name
ON table_name (col1, col2, col3)

Затем вы можете выполнить такой запрос, чтобы получить результаты в порядке актуальности:

SELECT * FROM table_name
WHERE MATCH (col1,col2,col3)
AGAINST ('keyword' IN NATURAL LANGUAGE MODE);

Ответ 2

Если вы имеете в виду, что col_1 более уместен, чем col_2 и т.д., то:

select *
      ,case when col_1 like '%$keyword%' then 1
            when col_2 like '%$keyword%' then 2
            when col_3 like '%$keyword%' then 3
       end as [priority]
  from table_name
 where col_1 like '%$keyword%'
    or col_2 like '%$keyword%'
    or col_3 like '%$keyword%'
 order by [priority]

Если вы имели в виду наибольшее совпадение столбцов, то:

select *
      ,(case when col_1 like '%$keyword%' then 1 else 0 end) +
      ,(case when col_2 like '%$keyword%' then 1 else 0 end) +
      ,(case when col_3 like '%$keyword%' then 1 else 0 end) as [priority]
  from table_name
 where col_1 like '%$keyword%'
    or col_2 like '%$keyword%'
    or col_3 like '%$keyword%'
 order by [priority] desc

Ответ 3

Вы не можете. Вы запрашиваете у вашей РСУБД сортировку результатов по строкам, когда совпадение столбцов "LIKE" уменьшается.

MySQL, например, делает это с помощью указателя FULLTEXT.