replace into 写入数据原理

测试案例：

CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `msg` char(10) DEFAULT NULL,
  `zy` varchar(40) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

DELIMITER ;;
CREATE TRIGGER `yuyuyu_test_insert_before` before insert ON `test` FOR EACH ROW BEGIN
  set NEW.zy='insertbefore';
END;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `yuyuyu_test_update_before` before UPDATE ON `test` FOR EACH ROW BEGIN
  set NEW.zy='updatebefore';
END;;
DELIMITER ;

drop TRIGGER `yuyuyu_test_delete_before`
DELIMITER ;;
CREATE TRIGGER `yuyuyu_test_delete_before` before delete ON `test` FOR EACH ROW BEGIN
 insert into test2(id) values (111);
END;;
DELIMITER ;

select * from test2;
id
null

replace into `test` (id,msg,zy) values(3,'ccc','ccc');
3 ccc insertbefore

replace into `test` (id,msg,zy) values(3,'ddd','ccc');
3 ddd insertbefore

select * from test;
1    aaaaa    before
2    bbb    updatebefore
3    ddd    insertbefore

select * from test2;
id
111

结论：
replace into 只对 insert和delete触发器有影响，
影响顺序：
依次触发before insert, before delete, after delete,after insert

 

证明：replace into 是先删后增(先试图增，发现冲突，然后删除，最后增。)