如何实现避免10分钟内重复插入消息的数据库查询函数？

Hey there! Let's work through this duplicate message problem together. Your current approach is on the right track—checking for existing messages before inserting—but we just need to add the missing time range filter and tweak things for reliability.

Step 1: 完善search_messages_five_mins_ago函数 #

Your current function is missing the critical time condition to limit checks to the last 10 minutes. Let's fix that, plus optimize it a bit:

public function search_messages_five_mins_ago($number, $msg) {
    // 只查询主键ID，比查所有字段更高效
    $this->db->select('id');
    $this->db->from('messages');
    $this->db->where('receiver_num', $number);
    $this->db->where('content', $msg);
    // 添加10分钟内的时间过滤（MySQL语法，如果你用PostgreSQL，看下面的备注）
    $this->db->where('created_at >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)');
    
    $query = $this->db->get();
    // 返回布尔值，上层判断更简洁，不用处理数组
    return $query->num_rows() > 0;
}

备注：不同数据库的时间语法 #

• 如果用PostgreSQL，把时间条件改成：

  $this->db->where('created_at >= NOW() - INTERVAL \'10 minutes\'');
  

• 确保你的created_at字段是datetime/timestamp类型，并且插入消息时会自动设置为当前时间（可以给字段加默认值CURRENT_TIMESTAMP）。

Step 2: 调整上层调用逻辑 #

现在函数返回的是布尔值（true表示存在重复，false表示可以插入），你的调用代码可以简化成：

$hasDuplicate = $this->messages_model->search_messages_five_mins_ago($recivernum, $msg);
if (!$hasDuplicate) {
    $newId = $this->Customer_m->insert_emaildata($add_email);
}

Step 3: 解决竞态条件（重要！） #

Wait a second—there's a hidden edge case here. If two identical messages come in at almost the same time, both could pass the "check first" step before either inserts, leading to duplicates anyway. To fix this, we need to make the check-and-insert process atomic at the database level.

Here's how to rewrite your insertion logic to use a single atomic query (MySQL example):

public function insert_message_safely($add_email) {
    $receiverNum = $add_email['receiver_num'];
    $content = $add_email['content'];
    // 其他字段根据你的表结构补充
    $otherFields = [
        'sender_num' => $add_email['sender_num'],
        // ... 其他字段
    ];

    // 构造原子插入语句：只有当10分钟内没有重复时才插入
    $sql = "INSERT INTO messages (receiver_num, content, created_at, " . implode(', ', array_keys($otherFields)) . ")
            VALUES (?, ?, NOW(), " . implode(', ', array_fill(0, count($otherFields), '?')) . ")
            WHERE NOT EXISTS (
                SELECT 1 FROM messages 
                WHERE receiver_num = ? 
                AND content = ? 
                AND created_at >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
            )";

    // 绑定参数：先放VALUES里的参数，再放NOT EXISTS里的参数
    $params = array_merge([$receiverNum, $content], array_values($otherFields), [$receiverNum, $content]);
    $this->db->query($sql, $params);

    // 返回是否成功插入（影响行数大于0表示插入成功）
    return $this->db->affected_rows() > 0;
}

Then call this function instead of separate check-and-insert:

$inserted = $this->messages_model->insert_message_safely($add_email);
if ($inserted) {
    // 插入成功后的逻辑
}

This way, the database handles the check and insert in one single operation, eliminating the race condition.

Bonus: 添加数据库索引优化 #

To make the duplicate check run faster, add a composite index on your messages table:

CREATE INDEX idx_receiver_content_created ON messages (receiver_num, content, created_at);

This will speed up the WHERE clause in your query significantly, especially as your table grows.

内容的提问来源于stack exchange，提问作者Kevin Foley