Updating a many-to-many connector table all at once

Say I have two tables:

articles
categories

There's a many to many table that connects them.

CREATE TABLE cat2art (
  article_id INT,
  category_id INT
);

For a specific article, I have a 'new list' of category id's, and we need to update the cat2art table with these.

开发者_StackOverflow社区Some categories got removed, some got added and some stayed where they were. What is the most effective way to update this table?

I could naively delete all records with the specified article_id and simply add them again. However, if I were to record a date in that same table that tracks when an article was linked to a category, that information will now be destroyed.

I'm looking for a great pattern that easily solves this issue. This question is specifically for PHP and MySQL, but answers in other languages are also fine provided they are applicable to PHP+MySQL as well.

Other systems support MERGE statement which would do exactly what you want.

However, in MySQL, you would need at least two queries (it cannot delete and insert/update in a single statement):

DELETE
FROM    cat2art
WHERE   art_id = $art_id
        AND cat_id NOT IN ($new_cat_1, $new_cat_2, …);

INSERT
IGNORE
INTO    cat2art
VALUES
($art_id, $new_cat_1),
($art_id, $new_cat_2),
…;

You can define (article_id, category_id) as unique key, and when inserting a connection use INSERT IGNORE syntax. That way if this connection already exists it will not be added again, nor will it update the existing record, and the create_date column stays untouched.

example:

INSERT IGNORE INTO cat2art (article_id, category_id, create_date) 
VALUES(100,200,NOW());