我有以下表格结构：
CREATE TABLE IF NOT EXISTS `reports` ( `id` int(11) NOT NULL AUTO_INCREMENT,`day` int(11) NOT NULL,`uid` int(11) NOT NULL,`siteid` int(11) NOT NULL,`cid` int(3) NOT NULL,`visits` int(11) NOT NULL,PRIMARY KEY (`id`) )
目前我检查使用以下代码段插入/更新：
$checkq = MysqL_query("SELECT count(*) as rowexist FROM reports WHERE day='$day' AND uid='$uid' AND siteid='$sid' AND cid='$cid'") or die(MysqL_error()); $checkr = MysqL_fetch_array($checkq); if ($checkr['rowexist'] > 0) { MysqL_query("UPDATE reports_adv SET visits=visits+1 WHERE day='$day' AND uid='$uid' AND siteid='$sid' AND cid='$cid'"); } else { MysqL_query("INSERT INTO reports_adv SET day='$day',uid='$uid',siteid='$sid',cid='$cid',visits='1'"); }
只需使用INSERT … ON DUPLICATE KEY UPDATE
INSERT INTO reports_adv (day,uid,siteid,cid,visits)
VALUES ('$day','$uid','$sid','$cid',1)
ON DUPLICATE KEY UPDATE visits=visits+1;
> INSERT … ON DUPLICATE KEY UPDATE Syntax
但在其他任何事情之前,您应该在列上定义一个UNIQUE约束.
ALTER TABLE reports_adv ADD CONSTRAINT tb_uq UNIQUE (day,cid)
