mysql 中使用INSERT … ON DUPLICATE KEY UPDATE(insert ignore)

這個語句非常好，

例如：

IF(SELECT * FROM test.relation_seo_keyword_webpage WHERE keywordid = 1 AND webpageid =1)
{
UPDATE test.relation_seo_keyword_webpage 
SET positionCount= positionCount+1,searchCount = searchCount +1
WHERE keywordid = 1 AND webpageid =1;
}
ELSE
{
INSERT INTO test.relation_seo_keyword_webpage(keywordid,webpageid,positionCount,searchCount) VALUES (1,1,1,1);
}

三條SQL，現在如果用INSERT ... ON DUPLICTE KEY UPDATE來實現就方便多了

INSERT INTO test.relation_seo_keyword_webpage VALUES(1,1,1,1) 
ON DUPLICATE KEY UPDATE keywordid=1,webpageid=1,positionCount= positionCount+1,searchCount = searchCount +1;

最好是指定列名

INSERT INTO test.relation_seo_keyword_webpage(keywordid,webpageid,positionCount,searchCount) VALUES(1,1,1,1) 
ON DUPLICATE KEY UPDATE keywordid=1,webpageid=1,positionCount= positionCount+1,searchCount = searchCount +1;

注意，如果表中keywordid和webpageid在表中不是主鍵（組合主鍵），如果執行以上語句就會插入重複資料，所以需要主鍵。

修改表的主鍵語句：

ALTER TABLE test.relation_seo_keyword_webpage ADD(CONSTRAINT PRIMARY KEY(keywordid,webpageid));

注意我的mysql版本是：5.0.51b

建表語句：

--
-- Table structure for table `relation_seo_keyword_webpage`
--

DROP TABLE IF EXISTS `relation_seo_keyword_webpage`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `relation_seo_keyword_webpage` (
  `keywordid` int(11) NOT NULL,
  `webpageid` int(11) NOT NULL,
  `positionCount` int(10) unsigned zerofill default '0000000000',
  `searchCount` int(10) unsigned zerofill default '0000000000',
PRIMARY KEY  (`keywordid`,`webpageid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `relation_seo_keyword_webpage`
--