mysql 队列表设计

CREATE TABLE queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

START TRANSACTION;
SELECT message FROM queue WHERE status = 'pending' FOR UPDATE LIMIT 1;
UPDATE queue SET status = 'processing' WHERE id = <message_id>;
-- 处理消息
UPDATE queue SET status = 'completed' WHERE id = <message_id>;
COMMIT;

ALTER TABLE queue ADD COLUMN unique_id VARCHAR(255) UNIQUE;

START TRANSACTION;
SELECT message FROM queue WHERE status = 'pending' AND unique_id IS NULL FOR UPDATE LIMIT 1;
UPDATE queue SET status = 'processing', unique_id = <unique_id> WHERE id = <message_id>;
-- 处理消息
UPDATE queue SET status = 'completed' WHERE id = <message_id>;
COMMIT;