（mysql）与duplicat。。。
背景：
我们对数据库操作时常常有这种需求：如果不存在该记录则新增，存在则更新！
传统的思路：先select判断是否存在，再选择insert或者update，这样的话步骤较多。
为了解决这种需求，mysql提供了两种常⽤的关键字⽅法：replace into 与 insert into … on duplicate key update，现在我们测试下这两种⽅法吧！
⼀、replace into 测试分析
介绍：
replace into 跟 insert 功能类似，不同点在于：replace into ⾸先尝试插⼊数据到表中， 1. 如果发现表中已经有此⾏数据（根据主键或者唯⼀索引判断）则先删除此⾏数据，然后插⼊新的数据。 2. 否则，直接插⼊新数据。
要注意的是：插⼊数据的表必须有主键或者是唯⼀索引！否则的话，replace into 会直接插⼊数据，这将导致表中出现重复的数据。mysql删除重复的数据保留一条
准备测试表：
CREATE TABLE`customer` (
`id`int(11) NOT NULL AUTO_INCREMENT,
`name`varchar(20) DEFAULT NULL,
`phone`varchar(20) DEFAULT NULL,
`data`varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14DEFAULT CHARSET=utf8
注：id字段为⾃增主键
插⼊基础数据：
INSERT INTO customer(NAME,phone,DATA) VALUES("⼩⼀","176********","1")
INSERT INTO customer(NAME,phone,DATA) VALUES("⼩⼆","176********","2")
测试1： replace into ⼀条数据（不含主键）
REPLACE INTO customer(NAME,phone,DATA) VALUES("⼩三","176********","3")
结论：由于插⼊数据不包含主键或唯⼀索引，则判断该条数据不存在，此时效果等同于insert into
测试2： replace into ⼀条数据（含主键）
REPLACE INTO customer(id,NAME,phone,DATA) VALUES(2,"⼩四","176********","4")
注：共2⾏收到影响，即先删除再新增！
结论： 插⼊数据中存在已存在主键：id=2，则判断该条数据已存在，故先删除再新增（即更新)测试3： replace into 的数据中减少⼀个字段：data
结论： replace into 的数据中如果⽐原数据少字段，则该字段更新时恢复为默认值（此处默认为NULL）。故可以确定replace into 删除已存在记录时，很彻底，没有做备份，之后直接新增replace into 后⾯确定的数据，没有值的字段设为默认值。
注：这点就和 insert into … on duplicate key update 不同了！测试4： insert into 2条数据，看主键id是否有变化
结论： 虽然第⼆条记录做了2次 replace into 操作，但后⾯新增数据时主键id 并没有+1，那问题来了，⽹上说的主键+1是什么时候发⽣的呢？
测试5： 我们将name字段设置 unique索引
，再replace into
这次语句中没有涉及主键id，为了判断记录存在性，所以我们为name设置了unique索引（此时根据name判断记录是否存在）
REPLACE  INTO  customer(id,NAME,phone) VALUES (2,"⼩五","176********")
INSERT  INTO  customer(NAME,phone,DATA) VALUES ("⼩六","176********","6")
INSERT  INTO  customer(NAME,phone,DATA) VALUES ("⼩七","176********","7")
REPLACE  INTO  customer(NAME,phone,DATA) VALUES ("⼩七","176********","8")
结论： 上⼀个测试主键没有变化的原因是我们在replace into 的数据中设置了确切的主键（相当于固定住了），⽽现在我们没有设置主键，故主键⾃增+1，此处需要注意！
测试6： 为name + phone字段设置 unique联合索引
（后⾯测试都这样设置）
此时是根据 name + phone判断记录存在性！
结论： 此时存在，故更新data（9 -> 10），同时主键id也+1测试7： 在name+phone为联合索引情况下去除phone字段再replace into
REPLACE  INTO  customer(NAME,phone,DATA) VALUES ("⼩七","176********","9")
REPLACE  INTO  customer(NAME,phone,DATA) VALUES ("⼩七","176********","10")
REPLACE  INTO  customer(NAME,DATA) VALUES ("⼩七","11")
REPLACE  INTO  customer(NAME,DATA) VALUES ("⼩七","11")
结论： 由于没加phone字段，数据库⽆法判断存在性，故默认不存在，即新增2条记录，phone默认为NULL。
此处特别说明：在MYSQL中UNIQUE索引将会对null字段失效，故这两条记录能同时存在不报错！
⼆、insert into … on duplicate key update 测试分析
注：接着上⾯的测试继续
测试8：
结论：此时根据 name + phone  判断出数据库不存在该记录，故新增，等同于直接 insert into，ON DUPLICATE KEY UPDATE DATA = "88"⽆效！测试9：
INSERT  INTO  customer(NAME,phone,DATA) VALUES ("⼩⼋","176********","8") ON  DUPLICATE KEY  UPDATE  DATA = "88"
INSERT  INTO  customer(NAME,phone,DATA) VALUES ("⼩⼋","176********","9") ON  DUPLICATE KEY  UPDATE  DATA = "99"
结论： 此时判断存在，更新data数据为99，这个没啥问题。但和replace into 不同的是，主键id竟然没有+1，依旧是11…测试10： 简单insert into，接着上⾯测试主键+1问题
结论： 发现这⾥新增时，主键id才额外+1，这⼀点确实和replace into 不⼀样，可以和之前的测试对⽐看下！
测试11： 继续测试主键+1问题
INSERT  INTO  customer(NAME,phone,DATA) VALUES ("⼩九","176********","9")
INSERT  INTO  customer(id,NAME,phone,DATA) VALUES ("13","⼩九","176********","10") ON  DUPLICATE KEY  UPDATE  DATA = "100"INSERT  INTO  customer(NAME,phone,DATA) VALUES ("⼩⼗","176********","10")

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。