Организация поиска по товарам: от MySQL до специализированных систем

Расширенные примеры программного кода

Пример 1: Полнотекстовый поиск BOOLEAN MODE с экранированием и пагинацией

Функция для экранирования специальных символов полнотекстового поиска MySQL:

function escapeFulltext($string) {
    $specialChars = ['+', '-', '>', '<', '(', ')', '~', '*', '"', '@'];
    foreach ($specialChars as $char) {
        $string = str_replace($char, '\\' . $char, $string);
    }
    return $string;
}

$userQuery = '+ноутбук -acer "игровой"';
$search = escapeFulltext($userQuery);

$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;

$query = "SELECT SQL_CALC_FOUND_ROWS * FROM products WHERE MATCH(name, description) AGAINST(:search IN BOOLEAN MODE) LIMIT :offset, :perPage";
$stmt = $pdo->prepare($query);
$stmt->execute([
    'search'  => $search,
    'offset'  => $offset,
    'perPage' => $perPage
]);
$results = $stmt->fetchAll();

// Получение общего количества
$totalStmt = $pdo->query("SELECT FOUND_ROWS()");
$totalRows = $totalStmt->fetchColumn();

Результат:

Найдено 12 товаров.
Страница 1 из 2.

Пример 2: Поиск с учётом нескольких таблиц (связь товар-категория)

Пусть есть таблицы products и categories, необходимо искать одновременно по имени товара и имени категории.

$search = 'ноутбук';
$query = "SELECT p.*, c.name AS category_name
          FROM products p
          JOIN categories c ON p.category_id = c.id
          WHERE MATCH(p.name, p.description) AGAINST(:search IN BOOLEAN MODE)
             OR MATCH(c.name) AGAINST(:search IN BOOLEAN MODE)
          ORDER BY MATCH(p.name) AGAINST(:search2 IN BOOLEAN MODE) DESC";
$stmt = $pdo->prepare($query);
$stmt->execute([
    'search' => $search,
    'search2' => $search
]);
$results = $stmt->fetchAll();

Результат:

Товар "Ноутбук Lenovo" (категория "Компьютеры") - релевантность по имени высокая.
Товар "Мышь" (категория "Ноутбуки и аксессуары") - найдена по категории.

Пример 3: Интеграция с Elasticsearch - полная индексация и поиск с русской морфологией

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

$params = [
    'index' => 'catalog',
    'body'  => [
        'settings' => [
            'analysis' => [
                'analyzer' => [
                    'russian_analyzer' => [
                        'tokenizer' => 'standard',
                        'filter'    => ['lowercase', 'russian_morphology']
                    ]
                ]
            ]
        ],
        'mappings' => [
            'properties' => [
                'name' => [
                    'type'     => 'text',
                    'analyzer' => 'russian_analyzer'
                ]
            ]
        ]
    ]
];
$client->indices()->create($params);

Поисковый запрос с подсветкой результатов:

$params = [
    'index' => 'catalog',
    'body'  => [
        'query' => [
            'multi_match' => [
                'query'  => 'ноутбуках',
                'fields' => ['name^3', 'description']
            ]
        ],
        'highlight' => [
            'fields' => [
                'name'        => new \stdClass(),
                'description' => new \stdClass()
            ]
        ]
    ]
];
$response = $client->search($params);

Результат (после обработки):

Найдено 5 товаров.
Название: ... <em>Ноутбук</em> Lenovo ...
Описание: ... подходит для <em>игр</em> ...

Пример 4: Использование Sphinx Search как альтернатива

Sphinx - ещё один полнотекстовый движок, простой в настройке. Пример конфигурации sphinx.conf:

source src1 {
    type = mysql
    sql_host = localhost
    sql_user = root
    sql_pass = 
    sql_db = catalog
    sql_query = SELECT id, name, description FROM products
}

index idx1 {
    source = src1
    path = /var/data/sphinx/catalog
    min_word_len = 2
    charset_table = 0..9, A..Z->a..z, a..z, U+410..U+44F->U+430..U+44F, U+401->U+451
}

searchd {
    listen = 9312
    log = /var/log/sphinx/searchd.log
}

Запрос из PHP через SphinxQL:

$sphinx = new PDO('mysql:host=127.0.0.1;port=9306');
$query = "SELECT id, WEIGHT() AS w FROM idx1 WHERE MATCH(:search) ORDER BY w DESC LIMIT 10";
$stmt = $sphinx->prepare($query);
$stmt->execute(['search' => 'ноутбук* игровой']);
$ids = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

// Затем из MySQL выбрать товары по этим id
if ($ids) {
    $placeholders = implode(',', array_fill(0, count($ids), '?'));
    $mysqlQuery = "SELECT * FROM products WHERE id IN ($placeholders)";
    $stmt = $pdo->prepare($mysqlQuery);
    $stmt->execute($ids);
    $products = $stmt->fetchAll();
}

Результат:

Товары, полученные от Sphinx, отсортированы по релевантности.