如果存在行或插入，则更新MySQL表的最快方法.超过2个非唯一键 [英] Fastest way to update a MySQL table if row exists else insert. More than 2 non-unique keys

问题描述

我具有以下表格结构:

 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`)
 )

目前，我正在检查&使用以下代码片段进行插入/更新:

Currently i check & insert/update with the following snippet:

 $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'");
}

如果存在行，否则用超过2个非唯一键插入，这是更新此MySQL表的最快方法吗?

Is a fastest way to update this MySQL table if row exists else insert with more than 2 non-unique keys?

推荐答案

只需使用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约束.

  but before anything else, you should define a UNIQUE constraint on the columns.

  ALTER TABLE reports_adv  ADD CONSTRAINT tb_uq UNIQUE (day, uid, siteid, cid)
  

  这篇关于如果存在行或插入，则更新MySQL表的最快方法.超过2个非唯一键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！