Блог

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

 CREATE TABLE `Customers` 
(
      `Id` int(11) UNSIGNED NOT NULL,
      `Firstname` varchar(50) DEFAULT NULL,
      `Insertion` varchar(12) DEFAULT NULL,
      `Lastname` varchar(50) NOT NULL,
      `ClientNumber` varchar(30) DEFAULT NULL,
      `DateCreated` int(10) UNSIGNED NOT NULL,
       -- More columns...
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
ALTER TABLE `Customers`
    ADD PRIMARY KEY (`Id`);

ALTER TABLE `Customers` 
    ADD FULLTEXT KEY `search_fulltext` (`Firstname`,`Lastname`,`ClientNumber`);
    
ALTER TABLE `Customers`
    MODIFY `Id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
 

Желаемый результат:

Когда пользователь выполняет поиск клиентов, приложение ищет совпадения в столбцах Firstname , Lastname , и ClientNumber . При поиске ‘John Doe’ должны быть выбраны все строки, содержащие любую подстроку этого поискового запроса в любом из столбцов Firstname, Lastname, ClientNumber. _{(Для принципа вопроса столбец ClientNumber можно поместить в фоновый режим).}

Мои попытки

Следующий запрос был моей первой попыткой:

 SELECT * 
FROM Customers 
WHERE Firstname LIKE CONCAT('%', :searchText, '%') 
   OR Lastname LIKE CONCAT('%', :searchText, '%') 
   OR ClientNumber LIKE CONCAT('%', :searchText, '%') 
LIMIT 0, 10
-- LIMIT here could be any limit.
 

Когда пользователь ищет строку, которая точно отображается как подстрока в столбце, она работает так, как ожидалось.

Скажем, имя пользователя клиента: Джон Доу ( Firstname = Джон, Lastname = Доу) Когда пользователь вводит поисковый запрос «Джон», или «Джо», или «Джо», он совпадает в Firstname столбце. Если пользователь вводит поисковый запрос ‘Doe’, он совпадает в Lastname столбце. Оба случая возвращают ожидаемые строки.

Проблема возникает, когда текст поиска представляет собой полное имя, например: ‘John Doe’. Несмотря на то, что этот текст поиска содержит возможные совпадения в нескольких столбцах, строки не выбраны.

Я понял, что запрос не подходит для того, чего я хочу достичь, поэтому я попытался создать FULLTEXT индекс для столбцов Firstname Lastname и ClientNumber . Оказывается, это работает только для значений, которые удовлетворяют совпадению в каждом отдельном столбце. Поиск полного имени по-прежнему не работает.

Это полнотекстовый запрос, который я использую:

 SELECT * 
FROM Customers 
WHERE MATCH (Firstname, Lastname, ClientNumber)
    AGAINST('John Doe' IN NATURAL LANGUAGE MODE)
 

Этот запрос тоже не решает мою проблему.

Итак, в более коротком предложении: пользователь должен иметь возможность выполнять поиск по полному имени, в то время как имя разделено на несколько столбцов.

Другой вариант, который я могу придумать сам, — это создать дополнительный столбец в таблице, просто сохранить полное имя в виде одной строки и использовать этот столбец для выполнения поиска.

_{В будущем мы также хотели бы поддерживать поиск по текстам, которые очень похожи на какое-то значение. Поэтому, когда пользователь ищет «Питер Джонссон», он также выбирает строки, содержащие «Пит Джонсон» (одиночный r / s). Но, возможно, это слишком далеко от контекста вопроса.}

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

• Манипулируйте текстом, который вы хотите сопоставить, и создайте более расширенный запрос, например:

 String[] splittedSearchTextBySpace = searchText.splite(" ");

String whereClause = "";
foreach(String word : splittedSearchTextBySpace){
    whereClause =
        whereClause  
        """Firstname LIKE '%:word%' OR
        Lastname LIKE '%:word%' OR
        ClientNumber LIKE '%:word%'"""
          " OR ";
}

whereClause = whereClause.substr(0,-4) // remove last " OR "

cosnt query = "SELECT * FROM Customers WHERE "
      whereClause  
    " LIMIT 0, 10";

callDB(query);

// this is just an example, you could do more than that

 

• В зависимости от используемого вами языка программирования вы найдете множество библиотек / пакетов, которые предоставляют утилиты поиска и охватывают упомянутые вами варианты использования сообщества и многое другое, например :
  • Java : Lucene …
  • Php : Tntsearch …
  • …
• Вы также можете выйти за рамки библиотек и использовать сервисные / поисковые системы (PAAS или самостоятельно размещенные один раз), которые предоставляют утилиты поиска, к которым вы будете подключаться, как и к вашей службе базы данных, например :
  • elasticSearch
  • Algolia
  • Solr
  • …

Бонус:

• Большинство этих сервисов и библиотек используют структуру данных, называемую инвертированным индексом.
• Некоторые связанные ключевые слова: стемминг, индексации, n-граммы, нечеткий поиск, полнотекстовый поиск, маркеры, алгоритмы ранжирования, Bm25 ..
• Чтобы просмотреть оценку, полученную при полнотекстовом поиске mysql, используйте :

 SELECT 
  id, 
  MATCH(Firstname, Lastname, ClientNumber) 
    AGAINST('John Doe' IN NATURAL LANGUAGE MODE) AS score 
FROM demo
 

• Взгляните на IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION другой режим полнотекстового поиска mysql.