MySql 如果不存在则插入两列对否则更新

Question

我得到了这样的Mysql表

CREATE  TABLE IF NOT EXISTS tbl_member_doc_read (
`read_id` INTEGER(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT ,
`member_id` INTEGER(10) UNSIGNED NOT NULL ,
`doc_id` INTEGER(10) UNSIGNED NOT NULL ,
`status` INTEGER(1) DEFAULT '0',
FOREIGN KEY (`member_id`) REFERENCES tbl_member(`member_id`),
FOREIGN KEY (`doc_id`) REFERENCES tbl_doc(`doc_id`)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

假设该表的示例数据 id

read_id  member_id  doc_id  status
 1          1           1       1
 2          1           2       0
 3          2           2       1

现在，如果组合或对 (member_id,doc_id) 存在，我想在该表中插入，如果状态为 0，则更新状态。这是我使用的 sql 查询

INSERT INTO tbl_member_doc_read (member_id, doc_id, status) VALUES(1,2,1) ON DUPLICATE KEY UPDATE status = VALUES(status) WHERE status = 0;

它应该用数据 2 1 2 1 更新第二行

如果我插入这个

INSERT INTO tbl_member_doc_read (member_id, doc_id, status) VALUES(2,1,0) ON DUPLICATE KEY UPDATE status = VALUES(status) WHERE status = 0;

它应该插入一个包含数据 4 2 1 0 的新行

但我无法解决这个问题。而且我认为在 innodb 引擎中，member_id 和 doc_id 被引用到另一个表的主键，它们是唯一的。在 innodb 中，我无法创建唯一索引。

任何帮助将不胜感激。提前致谢。

Answer 1

为了使用ON DUPLICATE KEY UPDATE 语句，您需要在两列上定义unique 键，

CREATE  TABLE IF NOT EXISTS tbl_member_doc_read 
(
`read_id` INTEGER(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT ,
`member_id` INTEGER(10) UNSIGNED NOT NULL ,
`doc_id` INTEGER(10) UNSIGNED NOT NULL ,
`status` INTEGER(1) DEFAULT '0',
FOREIGN KEY (`member_id`) REFERENCES tbl_member(`member_id`),
FOREIGN KEY (`doc_id`) REFERENCES tbl_doc(`doc_id`),
CONSTRAINT tb_uq UNIQUE (member_id, doc_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

然后删除 WHERE 子句

INSERT INTO tbl_member_doc_read (member_id, doc_id, status) 
VALUES(1,2,1) 
ON DUPLICATE KEY UPDATE 
status = VALUES(status)

• SQLFiddle Demo