MYSQL重复密钥不工作 [英] MYSQL Duplicate Key Not Working
问题描述
为什么我的 ON DUPLICATE KEY UPDATE
语句不起作用,我有一种方式在我的表中没有重复。使用以下代码,我得到重复的
Why is my ON DUPLICATE KEY UPDATE
statement not working, I am after a way to not have duplicates in my table. With the below code I get duplicates
CMS::insertQuery("INSERT INTO {table} SET canid=?, categoryid=? ON DUPLICATE KEY UPDATE canid=?, categoryid=?", array($emailCheck['id'], $id, $emailCheck['id'], $id));
DB:
CREATE TABLE `table` ( `canid` int(10) NOT NULL, `categoryid` int(10) NOT NULL, UNIQUE KEY `canid` (`canid`,`categoryid`)) ENGINE=MyISAM DEFAULT CHARSET=latin1
当前行我试图运行:
CMS::insertQuery("INSERT INTO {table} SET canid=?, categoryid=? ON DUPLICATE KEY UPDATE canid=?, categoryid=? WHERE canid=?, categoryid=?", array($emailCheck['id'], $id, $emailCheck['id'], $id,$emailCheck['id'],$id));
推荐答案
这只是一个猜测,因为你没有提供您的模式,但是您的唯一键中的列之一是可空的?
This is just a guess since you haven't provided your schema, but is one of the columns in your unique key nullable?
编辑:
正如@ G-Nugget在评论中所说,您需要在相关列中有一个唯一的键:
As @G-Nugget says in the comments, you need a unique key on the columns in question:
如果您指定ON DUPLICATE KEY UPDATE,并且插入一行,
将在UNIQUE索引或PRIMARY KEY中导致重复值,MySQL
执行旧行的更新。
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.
http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html
您需要有一个
例如:
ALTER TABLE yourTable ADD UNIQUE KEY (`canid`, `categoryid`);
另外,当询问数据库问题时,请包含 SHOW CREATE TABLE
为有问题的表。
Also, when asking database questions, please include SHOW CREATE TABLE
for the tables in question.
这篇关于MYSQL重复密钥不工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!