结合了MySQL数据库、Elasticsearch和Redis，构建一个产品搜索和推荐系统

1. 数据库设置（MySQL）

首先，我们需要创建两个表来存储产品信息和产品类别信息。

CREATE DATABASE product_system;

USE product_system;

CREATE TABLE `categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `products` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `price` DECIMAL(10, 2),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `product_categories` (
    `product_id` INT,
    `category_id` INT,
    PRIMARY KEY (`product_id`, `category_id`),
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE
);

2. 安装依赖库

使用Composer安装Elasticsearch、Redis和数据库的依赖库。

composer require elasticsearch/elasticsearch predis/predis
composer require vlucas/phpdotenv # 用于环境变量管理

3. 配置环境变量

在项目根目录下创建一个.env文件，存储数据库和Elasticsearch的配置信息。

DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=product_system
DB_USERNAME=root
DB_PASSWORD=yourpassword

ELASTICSEARCH_HOST=127.0.0.1:9200
REDIS_HOST=127.0.0.1
REDIS_PORT=6379

4. 创建数据库连接和Elasticsearch客户端

require 'vendor/autoload.php';

use Elasticsearch\ClientBuilder;
use Predis\Client as RedisClient;
use PDO;

// 加载环境变量
$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();

// 数据库连接
function getDatabaseConnection() {
    $dsn = "mysql:host=" . getenv('DB_HOST') . ";dbname=" . getenv('DB_DATABASE');
    return new PDO($dsn, getenv('DB_USERNAME'), getenv('DB_PASSWORD'));
}

// Elasticsearch客户端
$elasticsearch = ClientBuilder::create()
    ->setHosts([getenv('ELASTICSEARCH_HOST')])
    ->build();

// Redis客户端
$redis = new RedisClient([
    'scheme' => 'tcp',
    'host'   => getenv('REDIS_HOST'),
    'port'   => getenv('REDIS_PORT'),
]);

5. Elasticsearch索引配置和数据同步

// 创建Elasticsearch索引
function createElasticsearchIndex($elasticsearch) {
    $params = [
        'index' => 'products',
        'body' => [
            'mappings' => [
                'properties' => [
                    'name' => ['type' => 'text'],
                    'description' => ['type' => 'text'],
                    'price' => ['type' => 'float'],
                ]
            ]
        ]
    ];

    $elasticsearch->indices()->create($params);
}

// 同步数据库中的产品到Elasticsearch
function syncProductToElasticsearch($product, $elasticsearch) {
    $params = [
        'index' => 'products',
        'id' => $product['id'],
        'body' => [
            'name' => $product['name'],
            'description' => $product['description'],
            'price' => $product['price'],
        ]
    ];
    $elasticsearch->index($params);
}

// 从数据库获取产品数据
function getProductFromDatabase($productId, $db) {
    $stmt = $db->prepare("SELECT * FROM products WHERE id = :id");
    $stmt->execute(['id' => $productId]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

6. 产品搜索与Redis缓存

function searchProducts($query, $elasticsearch, $redis) {
    $cacheKey = 'search_' . md5($query);

    // 从Redis中获取缓存数据
    if ($redis->exists($cacheKey)) {
        return json_decode($redis->get($cacheKey), true);
    }

    // 如果缓存未命中，则从Elasticsearch中搜索
    $params = [
        'index' => 'products',
        'body' => [
            'query' => [
                'multi_match' => [
                    'query' => $query,
                    'fields' => ['name', 'description']
                ]
            ]
        ]
    ];

    $response = $elasticsearch->search($params);
    $results = array_map(function ($hit) {
        return $hit['_source'];
    }, $response['hits']['hits']);

    // 将搜索结果缓存到Redis
    $redis->setex($cacheKey, 3600, json_encode($results));

    return $results;
}

7. 添加和查询产品

// 添加新产品并同步到Elasticsearch
function addProduct($name, $description, $price, $db, $elasticsearch) {
    $stmt = $db->prepare("INSERT INTO products (name, description, price) VALUES (:name, :description, :price)");
    $stmt->execute([
        'name' => $name,
        'description' => $description,
        'price' => $price
    ]);
    $productId = $db->lastInsertId();

    // 同步到Elasticsearch
    $product = getProductFromDatabase($productId, $db);
    syncProductToElasticsearch($product, $elasticsearch);
}

// 通过关键词搜索产品
function searchAndDisplayProducts($query, $elasticsearch, $redis) {
    $products = searchProducts($query, $elasticsearch, $redis);

    foreach ($products as $product) {
        echo "Name: " . $product['name'] . "\n";
        echo "Description: " . $product['description'] . "\n";
        echo "Price: $" . $product['price'] . "\n";
        echo "-----------------------\n";
    }
}

// 示例操作
$db = getDatabaseConnection();
addProduct('iPhone 14', 'Latest model of iPhone', 999.99, $db, $elasticsearch);
searchAndDisplayProducts('iPhone', $elasticsearch, $redis);

8. 完整项目结构

project-root/
│
├── .env
├── composer.json
├── vendor/
└── index.php

9. 运行项目

通过以下命令运行项目：

php -S localhost:8000

在浏览器中访问 http://localhost:8000，即可看到产品搜索结果。

这个项目展示了如何使用PHP结合MySQL、Elasticsearch和Redis，构建一个功能齐全的产品搜索和推荐系统。你可以根据自己的需要进一步扩展这个项目。