使用INSERT INTO SELECT...ON DUPLICATE KEY UPDATE时MySQL新增行而非更新的问题

Hey there, let's work through the issue you're facing with your SQL query. First, let's restate the query you're running to make sure we're aligned:

INSERT INTO unit_storage (user_id, diagram_id, amount)
SELECT uf.user_id, uf.diagram_id, uf.amount 
FROM unit_factory uf 
WHERE uf.production_finish_time < /*当前Unix时间戳秒数*/ 
  AND uf.user_id = /*指定用户ID*/ 
ON DUPLICATE KEY UPDATE 
  unit_storage.amount = unit_storage.amount + uf.amount

Since you mentioned production_finish_time is an integer type, let's walk through the most common pitfalls and fixes for this kind of query:

Key Prerequisite: Verify Unique Constraints #

First things first—the ON DUPLICATE KEY clause only triggers if inserting the new row would violate a primary key or unique key constraint on unit_storage.

• Double-check that unit_storage has a composite unique key (or primary key) on (user_id, diagram_id). If this constraint doesn't exist, the database will just insert new rows instead of updating existing ones, which is probably not what you want.
• You can verify this with a quick query:

  SHOW CREATE TABLE unit_storage;
  

  Look for a line like UNIQUE KEY (user_id, diagram_id) or PRIMARY KEY (user_id, diagram_id) in the output.

Validate the SELECT Subquery #

Before worrying about the update logic, confirm that your SELECT is returning the data you expect:

• Run the SELECT part alone (replace the placeholders with actual values):

  SELECT uf.user_id, uf.diagram_id, uf.amount 
  FROM unit_factory uf 
  WHERE uf.production_finish_time < 1717248000 -- Replace with your Unix timestamp
    AND uf.user_id = 456 -- Replace with your target user ID
  

  • If no rows are returned, the issue is with your filter conditions (e.g., timestamp is too small, no matching user/production records exist).
  • If rows are returned, check if those (user_id, diagram_id) pairs already exist in unit_storage—if not, the query will just insert them instead of triggering an update.

Fix the Update Clause Logic #

Your current update line unit_storage.amount = unit_storage.amount + uf.amount might have edge cases or compatibility issues depending on your MySQL version:

• For older MySQL versions (pre-8.0), referencing the source table uf in the ON DUPLICATE KEY UPDATE clause can cause ambiguity. A safer alternative is to use the VALUES() function, which refers to the value that would have been inserted:

  ON DUPLICATE KEY UPDATE 
    amount = amount + VALUES(amount)
  

• Additionally, if uf.amount could ever be NULL, adding it will turn the entire result to NULL. Guard against this with COALESCE:

  ON DUPLICATE KEY UPDATE 
    amount = amount + COALESCE(VALUES(amount), 0)
  

Check Data Type & Filter Accuracy #

Since production_finish_time is an integer:

• Ensure the timestamp you're passing is an integer (not a string). Passing a string could lead to implicit type conversion errors that break the < comparison.
• If you're using MySQL, you can use the built-in UNIX_TIMESTAMP() function to get the current Unix timestamp instead of hardcoding it, which avoids manual input errors:

  WHERE uf.production_finish_time < UNIX_TIMESTAMP()
  

Verify Permissions #

Don't overlook the basics—make sure the user executing this query has:

• SELECT access to unit_factory
• INSERT and UPDATE access to unit_storage

内容的提问来源于stack exchange，提问作者Moe Epo