Web API插入重复行问题求助：附PHP实现代码

Let's break down the possible causes of your duplicate row issue and how to fix them:

1. Race Condition (Concurrency Conflict) #

This is the most common culprit when dealing with duplicate inserts in APIs. Imagine two requests hit your endpoint at nearly the same time:

• Both run your SELECT query and find no existing record for the given client_id and group_id
• Both proceed to execute the INSERT query, creating duplicate rows

Fix: Use an atomic database operation to eliminate the race condition. Replace your separate SELECT + INSERT/UPDATE logic with INSERT ... ON DUPLICATE KEY UPDATE. This lets the database handle the check and update/insert in a single, atomic step:

// First, ensure you have a unique constraint on (client_id, group_id) (see below)
$stmt = $con->prepare("INSERT INTO transaction_process (name, transaction_status, client_id, group_id) 
                       VALUES (:name, :transaction_status, :client_id, :group_id)
                       ON DUPLICATE KEY UPDATE 
                       name = :name, transaction_status = :transaction_status");

$stmt->bindParam(':name', $name);
$stmt->bindParam(':transaction_status', $transaction_status);
$stmt->bindParam(':client_id', $client_id);
$stmt->bindParam(':group_id', $group_id);

$stmt->execute();

2. Missing Unique Constraint #

Your code relies on a SELECT check to prevent duplicates, but this isn't enforced at the database level. Even if your code works perfectly, duplicates could still be inserted manually or via other code paths.

Fix: Add a unique index to your table to enforce uniqueness for client_id and group_id pairs:

ALTER TABLE transaction_process ADD UNIQUE INDEX idx_client_group (client_id, group_id);

This will throw an error if someone tries to insert a duplicate pair, which you can catch in your code if needed.

3. Unsafe SQL Query (String Interpolation) #

Your current SELECT query uses string interpolation instead of parameter binding. This is not only a SQL injection risk but can also lead to unexpected query behavior if your input values contain special characters (like quotes or backslashes).

Fix: Rewrite your SELECT query with proper parameter binding (if you still want to use the original check/update flow):

$statement = $con->prepare("SELECT transaction_id FROM transaction_process WHERE client_id = :client_id AND group_id = :group_id");
$statement->bindParam(':client_id', $client_id);
$statement->bindParam(':group_id', $group_id);
$statement->execute();
$data = $statement->fetch(PDO::FETCH_ASSOC);

4. Invalid or Unexpected Input Values #

If client_id or group_id are not being received correctly from $_POST (e.g., empty strings, leading/trailing whitespace), your SELECT query won't match existing records, leading to unnecessary inserts.

Fix: Validate and sanitize your input before using it:

// Get and sanitize input values
$name = trim($_POST['name'] ?? '');
$transaction_status = trim($_POST['transaction_status'] ?? '');
$client_id = trim($_POST['client_id'] ?? '');
$group_id = trim($_POST['group_id'] ?? '');

// Check for required fields
if (empty($client_id) || empty($group_id)) {
    // Handle invalid input (return error response)
    http_response_code(400);
    echo json_encode(["error" => "client_id and group_id are required"]);
    exit;
}

Key Takeaways #

• Always enforce uniqueness at the database level with unique constraints—this is your last line of defense against duplicates.
• Use atomic operations like INSERT ... ON DUPLICATE KEY UPDATE to avoid race conditions in concurrent environments.
• Never use string interpolation in SQL queries; always use parameter binding to prevent SQL injection and unexpected behavior.
• Validate and sanitize all user input to ensure you're working with correct values.

内容的提问来源于stack exchange，提问作者Teerath Kumar