mysql如何实现消息队列存储

CREATE TABLE message_queue (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    message_body TEXT NOT NULL,          -- 消息内容（JSON 或文本）
    status TINYINT DEFAULT 0,            -- 状态：0-待处理，1-处理中，2-已完成，3-失败
    retry_count INT DEFAULT 0,           -- 重试次数
    created_at TIMESTAMP DEFAULT NOW(),  -- 创建时间
    updated_at TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
    next_process_time TIMESTAMP DEFAULT NOW(), -- 下次可处理时间（支持延迟消息）
    INDEX idx_status_next_time (status, next_process_time)
) ENGINE=InnoDB;

INSERT INTO message_queue (message_body, status, next_process_time)
VALUES ('{"order_id": 1001, "action": "pay"}', 0, NOW());

-- 尝试获取一条待处理消息（使用 LIMIT 1 和 ORDER BY）
UPDATE message_queue 
SET status = 1, retry_count = retry_count + 1, updated_at = NOW()
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM message_queue 
        WHERE status = 0 AND next_process_time <= NOW()
        ORDER BY created_at ASC 
        LIMIT 1
    ) AS tmp
);

SELECT id, message_body FROM message_queue WHERE status = 1 AND updated_at >= NOW() - INTERVAL 1 MINUTE;

UPDATE message_queue SET status = 2 WHERE id = ?;

UPDATE message_queue 
SET status = 0, next_process_time = DATE_ADD(NOW(), INTERVAL 1 MINUTE), updated_at = NOW()
WHERE id = ? AND retry_count < 5;