MySQL: специальный алгоритм поиска с использованием поиска по релевантности MySQL

Я пытаюсь выполнить поиск в MySQL, где у пользователя есть только одно поле. Таблица выглядит так:

ID          BIGINT
TITLE       TEXT
DESCRIPTION TEXT
FILENAME    TEXT
TAGS        TEXT
ACTIVE      TINYINT

Теперь, если пользователь вводит только blah blubber, поиск должен проверять, появляется ли каждое слово в полях TITLE, DESCRIOTION, FILENAME или TAGS. Сам результат должен быть упорядочен по релевантности, так как часто строка появляется в записи. Я получил эти данные для примера:

ID   | TITLE   | DESCRIPTION  | FILENAME | TAGS | ACTIVE
1    | blah    | blah         | bdsai    | bdha | 1
2    | blubber | blah         | blah     | adsb | 1
3    | blah    | dsabsadsab   | dnsa     | dsa  | 1

В этом примере ID 2 должен быть вверху (2x blah, 1x blubber), затем 1 (2x blah) и затем 3 (1x blah). Этот процесс должен быть динамичным, чтобы пользователь мог также вводить больше слов, а релевантность работала так же, как для одного или нескольких слов.

Возможно ли это реализовать только в MySQL, или мне нужно использовать PHP? Как именно это будет работать?

Спасибо большое за помощь! С уважением, Флориан

РЕДАКТИРОВАТЬ: Вот результат после того, как я попробовал ответ Тома Мака:

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

ID  | TITLE | DESCRIPTION | FILENAME | TAGS                          | ACTIVE
1   | s     | s           | s        | s                             | 1
2   | 0     | fdsadf      | sdfs     | a,b,c,d,e,f,s,a,a,s,s,as,sada | 1
3   | 0     | s           | s        | s                             | 1
4   | a     | a           | a        | a                             | 1

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

ID | TITLE | DESCRIPTION | FILENAME | TAGS                          | ACTIVE
2  | 0     | fdsadf      | sdfs     | a,b,c,d,e,f,s,a,a,s,s,as,sada | 1        <== 8x s
1  | s     | s           | s        | s                             | 1        <== 4x s
3  | 0     | s           | s        | s                             | 1        <== 3x s

Теперь я попробовал свой запрос (имя таблицы PAGES):

select t . *
  from (

        select 
              match(title) against('*s*' in boolean mode) 
            + match(description) against('*s*' in boolean mode) 
            + match(filename) against('*s*' in boolean mode) 
            + match(tags) against('*s*' in boolean mode) 
            as matchrank,
                bb . *
          from pages bb) t
 where t.matchrank > 0
 order by t.matchrank desc

Этот запрос возвращает это:

matchRank | ID  | TITLE | DESCRIPTION | FILENAME | TAGS                          | ACTIVE
2         | 2   | 0     | fdsadf      | sdfs     | a,b,c,d,e,f,s,a,a,s,s,as,sada | 1

Это из-за подстановочных знаков? Я думаю, что строка *s* также должна найти значение, равное только s ...


mysql php relevance
person Florian Müller    schedule 24.10.2011    source источник
comment
оказывается, что решение с подстановочными знаками не является допустимым обходным путем для параметра ft_min_word_len. Лучшая идея - изменить этот параметр на 1 и перезапустить mysql. В противном случае вы могли бы написать обходной путь, используя LIKE, но он не будет работать слишком хорошо для более чем одной строки и менее 4 символов, например. ('SS')   -  person Tom Mac    schedule 28.10.2011

Ответы (2)


arrow_upward
2
arrow_downward

Это может вам помочь. Это вроде как предполагает, что ваша таблица MySQL использует движок MyISAM:

create table blubberBlah (id int unsigned not null primary key auto_increment,
title varchar(50) not null,
description varchar(50) not null,
filename varchar(50) not null,
tags varchar(50)not null,
active tinyint not null
) engine=MyISAM;

insert into blubberBlah (title,description,filename,tags,active) 
values ('blah','blah','bdsai','bdha',1);
insert into blubberBlah (title,description,filename,tags,active) 
values ('blubber','blah','blah','adsb',1);
insert into blubberBlah (title,description,filename,tags,active) 
values ('blah','dsabsadsab','dnsa','dsa',1);

select t.*
from
(
 select MATCH (title) AGAINST ('blubber blah' IN BOOLEAN MODE)
       +MATCH (description) AGAINST ('blubber blah' IN BOOLEAN MODE)
       +MATCH (fileName) AGAINST ('blubber blah' IN BOOLEAN MODE)
       +MATCH (tags) AGAINST ('blubber blah' IN BOOLEAN MODE) as matchRank,
       bb.*
from blubberBlah bb
) t
order by t.matchRank desc;

ИЗМЕНИТЬ

Другое предположение, которое делает это решение, заключается в том, что строка, которую вы ищете, имеет длину> = 4 символа. Если есть вероятность, что строка «search for», например, «blubber» или «blah» будет иметь длину 1, 2 или 3 символа, вы всегда можете перейти в свой файл my.cnf и добавить ft_min_word_len=1 под [mysqld] параметрами конфигурации. Затем перезапустите MySQL, и все будет в порядке.

И последнее: если вы планируете использовать этот подход, вам следует добавить ПОЛНЫЙ ТЕКСТ ИНДЕКС в каждый из столбцов. Следовательно:

ALTER TABLE blubberBlah add fulltext index `blubberBlahFtIdx1`(`title`);
ALTER TABLE blubberBlah add fulltext index `blubberBlahFtIdx2`(`description`);
ALTER TABLE blubberBlah add fulltext index `blubberBlahFtIdx3`(`filename`);
ALTER TABLE blubberBlah add fulltext index `blubberBlahFtIdx4`(`tags`);

Вы можете найти более подробную информацию о поиске BOOLEAN FULLTEXT в MySQL Docs .

person Tom Mac    schedule 24.10.2011
comment
какая версия MySQL у вас установлена? Получите ли вы значение matchRank, равное 0, при выполнении запроса к вашей таблице или к таблице из моего ответа? - person Tom Mac; 24.10.2011
comment
Я использую 5.0.45 и заменил имя таблицы на свое ^^ (PAGES) - person Florian Müller; 24.10.2011
comment
Можете ли вы попробовать запустить точный пример из моего ответа, то есть с точными данными, которые вы указали в своем вопросе, и сообщить мне, работает он или нет? Если это сработает, я подозреваю, что проблема связана с данными в вашей таблице PAGES. - person Tom Mac; 24.10.2011
comment
Черт, это сработало с вашим столом ... Я пробовал вот так: ykg-clan. de / tables.png - person Florian Müller; 25.10.2011
comment
С вашим столом все работает нормально. Если я попробовал со своим (см. Ссылку в комментариях раньше), я получу неправильный результат. - person Florian Müller; 25.10.2011
comment
@ Флориан. Разобрался, в чем ваша проблема. Вы выполняете поиск с использованием односимвольной строки, и в моем примере использовалась строка из 4 символов («бла») и 7 символов («ворвань»). Минимальная длина по умолчанию равна 4 - все, что меньше этого, вернет значение совпадения 0. Я изменил свой ответ, добавив несколько предложений по решению этой проблемы. Удачи! - person Tom Mac; 25.10.2011
comment
Было что-то, что не сработало - я пробовал с '* bla *', но мне все равно, какую строку я ввожу, и всегда показывает каждую запись. Почему? - person Florian Müller; 27.10.2011
comment
Что значит «не сработало»? Он всегда будет показывать каждую запись, поскольку нет предложения where. Если вы хотите исключить, скажем, строки с нулевым рейтингом соответствия, включите предикат where matchRank > 0. Более того, учитывая описанные вами тестовые данные, «bla» присутствует как минимум в одном из столбцов в каждой строке! Если вы собираетесь написать «не сработало», добавьте детали к своему вопросу! В вашем вопросе не указано, что вы хотите исключить строки с нулевой релевантностью .... - person Tom Mac; 27.10.2011
comment
Хорошо, извините, это был не очень хороший комментарий, я знаю. Ну, я хочу отображать только записи, в которых есть хотя бы одно совпадение - это делает предложение where, я забыл об этом. Но теперь, если я использую пример только с буквой s и подстановочным знаком (так что он выглядит как *s*), я получаю только одну из трех записей. Если вы посмотрите на мой ответ, я объяснил, как действительно выглядят мои три записи теста. - person Florian Müller; 28.10.2011

arrow_upward
1
arrow_downward

Вместо поиска «в логическом режиме» используйте Match () Against () для определения результата. Сложите эти оценки, чтобы получить релевантность.

person Robert Smith    schedule 06.06.2012