mysql replace into bug…

June 22, 2006

My friend Joe Stump ran into a problem today with MySQL’s REPLACE INTO functionality.  REPLACE INTO does a full-on DELETE of the record, then a new INSERT.  When you have triggers that do things based on DELETEing, the REPLACE INTO trips the DELETE triggers, which caused unintended consequences.

I first got excited when I heard about “ON DUPLICATE KEY UPDATE” functionality in MySQL, but realized that it’s just a way of letting you type in a full UPDATE command.  I realize there might be situations where you’d want a different UPDATE statement than the values in an INSERT statement, but it would be really useful if you could write something like

INSERT INTO foo (id,name,email) values (4,’mike’,'mgkimsal@gmail.com’) ON DUPLICATE KEY UPDATE;

and have MySQL replicate mSQL’s old ‘INSERT OR UPDATE’ functionality.

Posted in MySQL