如果列不唯一，如何更新重复键？

问题描述：

How do I insert only when the 2 row values are not the same exactly? Here is my scenario so my question makes sense:

ID      employee_id     client_id
1           1              10
2           2              11
3           1              11
4           2              10

I want to insert something like this to the column in PDO:

insert into yourTableName (employee_id, client_id)
    values (:employee_id, :client_id)
    on duplicate key update 
        employee_id=:$employee_id2, client_id=:client_id2

But the insert on duplicate above only seems to work if one of them columns were unique. In my case, the values in employee_id and client_id are not unique and the values can repeat multiple times. However there can only be 1 combination of it.

How can I only insert the row only if the exact combination of column values doesnt exit?

如果2行值不完全相同，我该如何插入？这是我的方案所以我的问题是有道理的： p>

  ID employee_id client_id 
1 1 10 
2 2 11 
3 1 11 
4 2 10 
  code>  
 
 我想在PDO中的列中插入这样的内容： p> 
 
 
 插入yourTableName（employee_id，client_id）
值（：  employee_id，：client_id）
重复密钥更新
 employee_id =：$ employee_id2，client_id =：client_id2 
  code>  pre> 
 
 但上面的重复插入只有在其中一列是唯一的时才会起作用。 在我的例子中，employee_id和client_id中的值不是唯一的，值可以重复多次。 但是只能有一个组合。 p> 
 
 
如果列值的确切组合没有退出，我怎么才能只插入行？ p> 
  div>

答

INSERT ... ON DUPLICATE KEY UPDATE ... requires a unique key. The unique key can be on multiple columns. Simply create a multi-column unique key on both columns (employee_id, client_id).

There is a caveat to using INSERT ... ON DUPLICATE KEY UPDATE .... With default settings, if ID is an auto-increment column, the counter will increment each time you call INSERT ... ON DUPLICATE KEY UPDATE ... even if it just updates because the next id is generated first. If that is a problem, you should use SELECT FOR UPDATE within a transaction to see if the record exists, then behave accordingly.

答

UNIQUE constraint doesn't have to be limited to a single column. You can alter your table like this:

ALTER TABLE `yourTableName` ADD UNIQUE `unique_index`(`employee_id`, `client_id`);

答

How can I only insert the row only if the exact combination of column values doesnt exit?

To do that you have to create a unique key with both column "employee_id" and "client_id".

如果列不唯一，如何更新重复键？

相关推荐