mysql - 使用 ON DUPLICATE UPDATE 忽略插入时的值

Question

我有一个插入语句，例如：

f"INSERT INTO `system_measurements`(`Global_irradiance_tilted_in_Wh_per_m2`, `a_id`, `subDate`) VALUES ('{temp}', '{temp_id}', '{i.date()}')"

并希望它忽略现有条目而不每次都检查日期。所以我想我可以使用

ON DUPLICATE KEY UPDATE a_id=a_id

但它仍然将所有值添加到表中。

标签： mysqlsql

Answer 1

I interpret your question as saying that a new row is inserted despite the on duplicate key.

In order for on duplicate key to work, you need a unique constraint or index. The update takes place when the query violates the unique constraint.

I am guessing that you want this on a_id, so be use you have something like:

alter table system_measurements add constraint unq_ system_measurements_a_id
    unique (a_id);