mysql replace语句的内部机制

结论：replace是做了一次update操作。而不是先delete再insert

I then realized that about my test table not having a primary key was the reason that I was seeing the behaviour that I had been seeing.

My original test case was failing, expecting to see a DELETE + an INSERT, when a REPLACE INTO was issued against a table. When I placed the PRIMARY KEY on the table in my test case and re-ran the test case, it still failed because the DELETE still was not in the transaction log. Well, it turns out that the reason was because ha_update_row() was actually called and not ha_delete_row() + ha_write_row(). And, because of the documentation for the REPLACE command, I wasn't checking that ha_update_row() may have been called — since I didn't realize a REPLACE could actually do an UPDATE.

Anyway, I wanted to post to say that most of this whole was my fault. Though I think that both the online and code documentation should reflect the fact that a REPLACE can do an UPDATE, the source of the failure was not what I originally wrote. In contrast, ha_write_row() does indeed return ER_FOUND_DUPP_KEY appropriately during a REPLACE call.

Mmmmm, that piece of humble pie was delicious.

So I have verified my earlier conclusions: please look at the following:

drop table t2;
CREATE TABLE t2 (msg VARCHAR(200));

drop table t1;
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, padding VARCHAR(200) NOT NULL
);

CREATE TRIGGER t1_AI AFTER INSERT ON t1
FOR EACH ROW
INSERT INTO t2 VALUES (CONCAT("insert: ", NEW.padding));

CREATE TRIGGER t1_AD AFTER DELETE ON t1
FOR EACH ROW
INSERT INTO t2 VALUES (CONCAT("delete: ", OLD.padding));

CREATE TRIGGER t1_AU AFTER UPDATE ON t1
FOR EACH ROW
INSERT INTO t2 VALUES (CONCAT("update: ", NEW.padding));

INSERT INTO t1 VALUES (1, "I love testing.");
INSERT INTO t1 VALUES (2, "I hate testing.");
REPLACE INTO t1 VALUE (2, "I love testing.");

SELECT * FROM t2;
+-------------------------+
| msg |
+-------------------------+
| insert: I love testing. |
| insert: I hate testing. |
| delete: I hate testing. |
| insert: I love testing. |
+-------------------------+