MySQL INSERT IGNORE / ON DUPLICATE KEY UPDATE不检测重复 [英] MySQL INSERT IGNORE / ON DUPLICATE KEY UPDATE not detecting duplicates
问题描述
编辑:修正了!
这是我的代码:
//建立表和vars
mysql_query(CREATE TABLE IF NOT EXISTS pages(page varchar(255),content varchar(255)));
$ page ='你好' $ content =这是内容。
// Ive尝试过:
mysql_query(INSERT INTO pages SET page ='$ page'ON DUPLICATE KEY UPDATE content ='$ content');
// Ive还试过:
mysql_query(INSERT IGNORE INTO pages SET page ='$ page',content ='$ content'));
mysql_query(INSERT IGNORE INTO pages SET page ='$ page')); //没有内容
预期结果:使用页面创建一行值Hello('内容'值是这是内容)。当脚本第二次运行时,不会创建额外的行,因为已经有一个page值为Hello的行。
观察结果:每次运行脚本时,将创建一个页面值为Hello的新行,因为这不是重复的键。为什么不买?我已经尝试创建一个ID为AUTO_INCREMENT和主键的表,但我认为这是导致问题,所以我简化了它。
(该代码为:)
mysql_query(CREATE TABLE IF NOT EXISTS pages(id INT AUTO_INCREMENT NOT NULL,page varchar(255),content varchar(255),primary key(id)));
希望的结果:如果有的话,应该在表中插入一个新行没有行,页面字段为你好。一旦创建完成,再次运行相同的脚本不应该导致一个新行。
谢谢!
编辑:修正!
我的解决方案:
//表需要一个主键来搜索重复项。
mysql_query(CREATE TABLE IF NOT EXISTS pages(page varchar(255),content varchar(255),primary key(page))); //建立表
您的页面
字段需要是PRIMARY KEY或有一个UNIQUE索引,以使ON DUPLICATE KEY UPDATE工作。尝试:
CREATE TABLE IF NOT EXISTS页面(页面varchar(255)),内容varchar(255),主键(页) )
I'm having an issue here, and after 2 hours of Googling Stackoverflow answers and testing different ideas I can't figure it out. Hopefully it's a no-brainer for someone out there.
EDIT: Fixed! See solutions below.
Here is my code:
// Establish Table and vars
mysql_query("CREATE TABLE IF NOT EXISTS pages(page varchar(255), content varchar(255))");
$page = 'Hello'; $content = 'This is the content.';
// Ive tried this:
mysql_query("INSERT INTO pages SET page='$page' ON DUPLICATE KEY UPDATE content='$content'");
// Ive also tried:
mysql_query("INSERT IGNORE INTO pages SET page='$page', content='$content'"));
mysql_query("INSERT IGNORE INTO pages SET page='$page'")); // No content
Expected result: Create one row with 'page' value of 'Hello' ('content' value of 'This is the content.'). When script is run a second time, no additional row is created, because there is already a row with 'page' value of 'Hello'.
Observed Result: Every time script is run, a new row with page value of 'Hello' is created, assumedly because this is not a duplicate key. Buy why not? I have tried creating a table with an ID field that is AUTO_INCREMENT and the primary key, but I thought that was causing the problem so I simplified it.
(That code was:)
mysql_query("CREATE TABLE IF NOT EXISTS pages(id INT AUTO_INCREMENT NOT NULL, page varchar(255), content varchar(255), primary key (id))");
Desired Result: A new row should be inserted into the table if there is no row with a 'page' field of 'Hello.' Once this has been created, running the same script again should NOT result in a new row.
Thank you!
EDIT: Fixed! See solutions below.
My solution:
// Table needs a primary key to search for duplicates.
mysql_query("CREATE TABLE IF NOT EXISTS pages(page varchar(255), content varchar(255), primary key (page))"); // Establish Table
Your page
field needs to be a PRIMARY KEY or have a UNIQUE index on it for ON DUPLICATE KEY UPDATE to work. Try:
CREATE TABLE IF NOT EXISTS pages(page varchar(255), content varchar(255), primary key (page))
这篇关于MySQL INSERT IGNORE / ON DUPLICATE KEY UPDATE不检测重复的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!