Порядок сортировки результатов MySQL по положению строки

Быстрый ответ

Если вам требуется сортировать результаты запроса в MySQL с учётом степени их соответствия критериям поиска, используйте функцию MATCH() ... AGAINST() с индексом FULLTEXT. Этот индекс позволяет возвращать записи, принимая во внимание их релевантность. Пример применения этой функции в запросе:

SELECT *, MATCH(content) AGAINST('поисковый запрос') AS relevance
FROM my_table
ORDER BY relevance DESC;

В результате выполнения этого запроса записи таблицы my_table будут отсортированы по убыванию степени соответствия содержимого поля content фразе 'поисковый запрос'.

Если же ваша таблица не поддерживает индекс FULLTEXT, вы можете использовать функции LIKE, LOCATE и INSTR, чтобы отсортировать записи альтернативным способом.

Альтернативные стратегии сортировки без использования FULLTEXT

Приоритеты функций LIKE и LOCATE

Если использование FULLTEXT невозможно, воспользуйтесь функциями LIKE и LOCATE, которые позволят установить приоритеты совпадений. Структуру CASE можно применить для задания порядка:

-- Представим, мы выстраиваем армию саламандр по росту:
SELECT word
FROM words
WHERE word LIKE '%поисковый запрос%'
ORDER BY CASE 
    WHEN word LIKE 'поисковый запрос%' THEN 1
    WHEN word LIKE '%поисковый запрос%' THEN 2
    WHEN word LIKE '%поисковый запрос' THEN 3
    ELSE 4
END, LOCATE('поисковый запрос', word), word;

В этом запросе слова, начинающиеся с фразы 'поисковый запрос', приоритетнее остальных; затем следуют слова, содержащие данную фразу в середине, а потом — в конце. Завершается запрос сортировкой по алфавиту.

Упорядочивание результатов с помощью INSTR

Уточнить результаты поиска, указав точное место начала совпадения, поможет функция INSTR:

-- Как искать изюм в печеньках:
SELECT word
FROM words
WHERE word LIKE '%поисковый запрос%'
ORDER BY INSTR(word, 'поисковый запрос'), word;

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

Упорядочивание совпадений по алфавиту

Если релевантность совпадений одинакова, окончательную сортировку можно произвести по алфавиту:

ORDER BY INSTR(word, 'поисковый запрос'), word ASC;

Особые условия и потенциальные подводные камни

Особенности разных языков

Для адекватного поиска на разных языках требуется корректировка условий CASE и логики ORDER BY. Обязательно учтите этот момент.

Использование шаблонов с LIKE

Если в функции LIKE слишком много шаблона %, влияние на производительность **может быть негативным. В данном случае методыLOCATEилиINSTR` являются более эффективными решениями.

Направление сортировки

Добавив в ORDER BY DESC или ASC, вы определите направление сортировки: от лучших результатов к худшим или в обратном порядке.

Соответствие между WHERE и ORDER BY

Убедитесь, что выраженное в WHERE условие соответствует вашему намерению по сортировке, чтобы избежать перспективы столкнуться с ошибками.

Рациональное использование SELECT и ORDER BY

При построении запросов выбирайте данные, которые вам действительно нужны. Так вы не перегрузите базу данных. Также проявляйте ум при подготовке упорядочивающих операторов: уделите основное внимание релевантности, а не только количеству.

Визуализация

Рассмотрите сортировку по наилучшему совпадению в контексте организации вашей библиотеки:

На полке (🧑‍🔬):
[🥇, 📚, 📚, 📚, 📚, 📚, 📚]

Использование сортировки в MySQL:

SELECT * FROM books
ORDER BY similarity_score DESC;

Приведёт к следующему расположению книг:

🥇 – Любимая книга
📚 – Сильно приближенная к теме
📚 – Весьма релевантная
📚 – Умеренно связанная с темой
📚 – Едва связанная с темой
📚 – Мало релевантная
📚 – Прочитать в последнюю очередь

MySQL поможет вам найти наиболее релевантную информацию.

Комплексные решения: подзапросы и несколько критериев

Улучшение точности результатов

Подзапросы могут улучшить точность результатов, учитывая связанные данные:

SELECT *, (
    SELECT COUNT(*)
    FROM related_table
    WHERE main_table.foreign_key = related_table.id
    AND related_table.content LIKE '%поисковый запрос%'
) AS related_count
FROM main_table
ORDER BY related_count DESC, relevance DESC;

Учет нескольких критериев

Вы можете учитывать несколько параметров, аналогично выбору мороженого разных вкусов, используя динамические запросы или комбинации LIKE:

-- В мире, где запрос — это мороженое:
SELECT *
FROM my_table
WHERE (content LIKE '%термин1%' OR content LIKE '%термин2%')
ORDER BY (content LIKE '%термин1%' AND content LIKE '%термин2%') DESC, relevance DESC;

Полезные материалы

1. MySQL :: MySQL 8.0 Reference Manual :: 14.9 Full-Text Search Functions – Официальная документация, с детальной информацией о функциях полнотекстового поиска.
2. MySQL :: MySQL 8.0 Reference Manual :: 14.9.1 Natural Language Full-Text Searches – Сегмент документации, посвящённый полнотекстовому поиску.
3. StackOverflow topics – место, где разработчики обсуждают разнообразные варианты решения проблем.
4. DZone – подробные статьи о том, как работает полнотекстовый поиск в MySQL.
5. StackOverflow thread – на этой странице представлены полезные примеры работы с ORDER BY и UNION.
6. .NET SQLCLR assembly: хоть эта ссылка и не связана напрямую с полнотекстовым поиском, она может помочь в понимании архитектуры и структуры сложных запросов.