在重复密钥上更新组合密钥 [英] Update Composite Key On Duplicate Key

问题描述

我需要更新新行，即使同时使用date='$dat'和empId='$who(作为复合键).但是，如果其中之一或两者不同，请插入:

I need to update new row just if both: date='$dat' and empId='$who ( as composite key ) . But insert if one of them or both differ:

$sql= "INSERT INTO history 
       SET endtimestamp='$now',end='$signature',date='$dat',empId='$who' 
       ON DUPLICATE KEY UPDATE endtimestamp='$now',end='$signature'";

推荐答案

来自MySQL文档: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

From the MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

如果您指定ON DUPLICATE KEY UPDATE，并插入一行 会导致唯一索引或 PRIMARY KEY 中的值重复， 对旧行进行更新.

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

主键是单个的(每列).您需要创建一个包含两个列的复合UNIQUE索引，然后更新才能正常工作.

A primary key is individual (per column). You need to create a composite UNIQUE index containing both columns, then the update will work correctly.

在数据库上运行它:

CREATE UNIQUE INDEX unq_employee_id_date ON history (empId, date);

这篇关于在重复密钥上更新组合密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！