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`
--
posted on 2012-01-07 18:42 blackcore 阅读(4505) 评论(0) 编辑 收藏 举报