队列技术在PHP与MySQL中的消息持久化和延迟加载的应用

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL,
    status ENUM('pending', 'processed') NOT NULL DEFAULT 'pending'
);

<?php
// 连接到数据库
$connection = new mysqli('localhost', 'username', 'password', 'database');

// 插入消息到数据库
$content = '这是一条示例消息';
$statement = $connection->prepare('INSERT INTO messages (content) VALUES (?)');
$statement->bind_param('s', $content);
$statement->execute();

// 关闭数据库连接
$connection->close();
?>

CREATE TABLE delayed_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL,
    delay_time INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

<?php
// 连接到数据库
$connection = new mysqli('localhost', 'username', 'password', 'database');

// 插入延迟加载消息到数据库
$content = '这是一条延迟加载消息';
$delayTime = 60; // 延迟时间为60秒
$statement = $connection->prepare('INSERT INTO delayed_messages (content, delay_time) VALUES (?, ?)');
$statement->bind_param('si', $content, $delayTime);
$statement->execute();

// 关闭数据库连接
$connection->close();
?>

<?php
// 连接到数据库
$connection = new mysqli('localhost', 'username', 'password', 'database');

// 查询需要发送的延迟加载消息
$result = $connection->query('SELECT * FROM delayed_messages WHERE delay_time <= UNIX_TIMESTAMP()');

// 发送消息到消费者进行处理
while ($row = $result->fetch_assoc()) {
    $messageId = $row['id'];
    $content = $row['content'];

    // 执行消息处理逻辑
    // ...

    // 删除已处理的消息
    $connection->query("DELETE FROM delayed_messages WHERE id = $messageId");
}

// 关闭数据库连接
$connection->close();
?>