MySQL插入如果不存在两列对否则更新 [英] MySql Insert if not exist two column pair otherwise update

问题描述

我有这样的Mysql表

I got Mysql table like this

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

lets say example data id that table

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查询

now I want insert in that table if combine or pair (member_id,doc_id) is exists else update status if status is 0. here is sql query i used

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更新第二行

it should update second row with data 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

it should insert a new row with data 4 2 1 0

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

but I can't work this out. and also I think in innodb engine as member_id and doc_id is referenced to another table's primary key they are unique. and in innodb I can't create unique indexes.

任何帮助将不胜感激.预先感谢.

Any help will be appreciated. thanks in advance.

推荐答案

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

in order to work the ON DUPLICATE KEY UPDATE statement, you need to define unique key on the two columns,

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演示
• SQLFiddle Demo

这篇关于MySQL插入如果不存在两列对否则更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！