CODEWITHSUNDEEP
mysqlduplicates
Trey
Trey

Reputation: 51

MySQL INSERT IGNORE / ON DUPLICATE KEY UPDATE not detecting duplicates

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

Upvotes: 0

Views: 1078

Answers (2)

Marc B
Marc B

Reputation: 360632

As the query says... on duplicate KEY. that only triggers if the insert would violate a unique constraint on the table. if there's no keys, the DB will not scan the entire table to see if there's a duplicate.

Upvotes: 2

Martin Wilson
Martin Wilson

Reputation: 3386

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

Upvotes: 2

Related Questions