企业官网建设流程全解析

使用网站模板快速建站,建设和管理环保网站,做机械网站,建设工程招投标网最专业的网站文章目录 一、replace into原理二、replace into的三种形式三、replace into 使用案例 3.1、replace into values 3.1.1、只有主键且主键冲突3.1.2、有主键有唯一索引且主键冲突3.1.3、有主键有唯一索引且唯一索引冲突(有坑)3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯…文章目录一、replace into原理二、replace into的三种形式三、replace into 使用案例3.1、replace into values3.1.1、只有主键且主键冲突3.1.2、有主键有唯一索引且主键冲突3.1.3、有主键有唯一索引且唯一索引冲突(有坑)3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑)3.2、replace into select3.3、replace into set3.3.1、唯一索引不重复3.3.2、唯一索引重复四、replace into与on duplicate key update的区别五、replace into的坑5.1、replace into与自动更新时间的坑5.2、replace into用唯一索引更新时会导致自增值15.3、replace into同时有主键或唯一索引冲突时可能会删除多条记录官网地址https://dev.mysql.com/doc/refman/8.0/en/replace.html以下内容基于mysql 8.0版本进行讲解。replace into与on duplicate key update在一定程度上都能实现无记录时插入有记录时更新这个需求。但是强烈推荐使用on duplicate key update 原因见下方两者的区别。replace into的坑太多若要使用请详细阅读本文。一、replace into原理replace into 跟 insert 功能类似不同点在于replace into 首先尝试插入数据到表中 1. 如果发现表中已经有此行数据根据主键或者唯一索引判断则先删除此行数据然后插入新的数据。 2. 否则直接插入新数据。要注意的是插入数据的表必须有主键或者是唯一索引否则的话replace into与insert into功能一致会直接插入数据这将导致表中出现重复的数据。如果归纳一下 REPLACE INTO 语句的执行规则基本会有以下几种情况当表没有主键和唯一键时REPLACE INTO 相当于普通的 INSERT 操作binlog 记录事件为 INSERT返回影响行数为 INSERT 的数量。当表有主键没有唯一键时REPLACE INTO 插入记录与主键冲突时会进行 DELETEINSERT 操作binlog 记录事件为 UPDATE返回影响行数为 DELETEINSERT 的数量如果主键自增则 AUTO_INCREMENT 值不变。当表有唯一键没有主键时REPLACE INTO 插入记录与唯一键冲突时会进行 DELETEINSERT 操作binlog 记录事件为 UPDATE返回影响行数为 DELETEINSERT 的数量。当表同时存在主键和唯一键时REPLACE INTO 插入记录与主键冲突的时候进行 DELETEINSERT 操作binlog 记录事件为 DELETEINSERT返回影响行数为 DELETEINSERT 的数量。如果主键自增则 AUTO_INCREMENT 值不变。当表同时存在主键和唯一键时REPLACE INTO 插入记录与唯一键冲突的时候进行 DELETEINSERT 操作binlog 记录事件为 UPDATE返回影响行数为 DELETEINSERT 的数量。如果主键自增则新插入的这条记录的主键会变成最新 AUTO_INCREMENT 的值而对应的AUTO_INCREMENT 值会 1。当表同时存在主键和唯一键时REPLACE INTO 插入记录与一条记录主键和一条记录唯一键都冲突的时候进行 DELETEINSERT 操作binlog 记录事件为 DELETEUPDATE返回影响行数为 DELETE*2INSERT 的数量。第五点会有副作用见本文《3.1.3、有主键有唯一索引且唯一索引冲突(有坑)》第六点会有副作用见本文《3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑)》总结以下总结replace into时可能会遇到的场景pk主键 uk唯一索引无pk无uk场景replace into时相当于replace into在binlog中的表现形式返回影响行数无pk无ukinsertinsertinsert行数只有pk场景replace into时相当于replace into在binlog中的表现形式返回影响行数pk冲突deleteinsertupdatedeleteinsert行数pk不冲突insertinsertinsert行数只有uk场景replace into时相当于replace into在binlog中的表现形式返回影响行数uk冲突deleteinsertupdatedeleteinsert行数有pk有uk场景replace into时相当于replace into在binlog中的表现形式返回影响行数pk冲突 uk不冲突delete insertdelete insertdeleteinsert行数pk不冲突 uk冲突delete insertupdatedeleteinsert行数pk冲突 uk冲突delete insertdeleteinsertdeleteinsert行数pk不冲突 uk不冲突insertinsertinsert行数二、replace into的三种形式1. replace into tbl_name(col_name, ...) values(...) 2. replace into tbl_name(col_name, ...) select ... 3. replace into tbl_name set col_namevalue, ...第一种形式相似于insert into的用法第二种replace select的用法也相似于insert select这种用法并不必定要求列名匹配事实上MYSQL甚至不关心select返回的列名它需要的是列的位置。例如replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2; 这个例子使用replace into从 tb2中将全部数据导入tb1中。第三种replace set用法相似于update set用法使用一个例如“SET col_name col_name 1”的赋值则对位于右侧的列名称的引用会被做为DEFAULT(col_name)处理。所以该赋值至关于SET col_name DEFAULT(col_name) 1。前两种形式用的多些。其中 “into” 关键字能够省略不过最好加上 “into”这样意思更加直观。另外对于那些没有给予值的列MySQL 将自动为这些列赋上默认值。三、replace into 使用案例3.1、replace into values3.1.1、只有主键且主键冲突当只有主键且主键冲突时会执行deleteinsert操作如下sql:drop table if exists user_test; create table user_test( id int primary key auto_increment comment 主键, name varchar(30) not null comment 姓名, update_time timestamp on update CURRENT_TIMESTAMP comment 更新时间 ) comment 测试表; insert into user_test(name,update_time) value (zhangsan,2000-01-01 00:00:00);此时执行replace intoreplace into user_test(id,name) value (1,zhangsan);上图可以看出执行replace into之后返回的受影响条数是2且update_time的字段被更新为空了。由此证明replace into时当只有主键且主键冲突时执行了deleteinsert操作。注意 当时间字段设置自动更新后若没有设置默认值replace into更新时需要手动赋予时间字段否则会插入为空。3.1.2、有主键有唯一索引且主键冲突当有主键有唯一索引且主键冲突时会执行deleteinsert操作drop table if exists user_test; create table user_test( id int primary key auto_increment comment 主键, name varchar(30) unique not null comment 姓名, update_time timestamp on update current_timestamp comment 更新时间 ) comment 测试表; insert into user_test(name,update_time) value (zhangsan,2000-01-01 00:00:00); replace into user_test(id,name) value (1,lisi);上图可以看出执行replace into之后返回的受影响条数是2且update_time的字段被更新为空了。由此证明replace into时当有主键有唯一索引且主键冲突时执行了deleteinsert操作。3.1.3、有主键有唯一索引且唯一索引冲突(有坑)当有主键有唯一索引且唯一索引冲突时会执行deleteinsert操作且AUTO_INCREMENT自增1drop table if exists user_test; create table user_test( id int primary key auto_increment comment 主键, name varchar(30) unique not null comment 姓名, update_time timestamp on update current_timestamp comment 更新时间 ) comment 测试表; insert into user_test(name,update_time) value (zhangsan,2000-01-01 00:00:00);此时执行replace into语句replace into user_test(name) value (zhangsan);上图可以看出执行replace into之后返回的受影响条数是2且update_time的字段被更新为空了。由此证明replace into时当有主键有唯一索引且唯一索引冲突时执行了deleteinsert操作。注意唯一索引冲突时会带来副作用主键id被改变了。由于主键设置了自增所以当唯一索引冲突时导致AUTO_INCREMENT的值自增1。3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑)当有主键与唯一索引时若replace into与其中一条主键冲突、与另一条记录唯一索引冲突此时执行deleteinsert操作。注意此时受影响的条数是3且会删除两条数据可能会有坑drop table if exists user_test; create table user_test( id int primary key auto_increment comment 主键, name varchar(30) unique not null comment 姓名, update_time timestamp on update current_timestamp comment 更新时间 ) comment 测试表; insert into user_test(id,name,update_time) values (1,zhangsan,2000-01-01 00:00:00), (2,lisi,2000-01-01 00:00:00); replace into user_test(id,name) value (1,lisi);上图可以看出执行replace into之后返回的受影响条数是3。注意 在该场景下会删除2条数据然后插入1条数据所以可能会造成坑需要特别注意。3.2、replace into select语法类似insert into select示例 replace into t(id, update_time) select 1, now(); replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2;3.3、replace into set表结构如下drop table if exists user_test; create table user_test( id int primary key auto_increment comment 主键, name varchar(30) unique not null comment 姓名, age int default 2 comment 年龄, sex varchar(10) comment 性别男、女 ) comment 测试表;其中 name为唯一索引age默认值是23.3.1、唯一索引不重复执行以下sql插入第一条数据replace into user_test set name zhangsan,age age1, sex 男;可以看到age3是因为agedefault(age)1默认age2然后又1所以是3若age字段建表时没有设置默认值则上述sql执行完后age为null。3.3.2、唯一索引重复接着上面的示例运行以下sqlreplace into user_test set name zhangsan,age age1;我们看到id1的数据被删除了新增了一条id2的数据它的sex字段的值没有继承第一条数据的值age属性的值也只是由默认值决定与上一条的值无关。由此证明结论在没有唯一键/主键重复时replace into所做的事情就是新添加一个条目条目各个属性的值与运行的语句内容有关。在有唯一键/主键重复时replace into所做的事情就是直接删除掉那条重复的旧条目然后新添加一个条目。新条目各个属性的值只取决于运行的语句内容与被删除的旧条目的值无关。四、replace into与on duplicate key update的区别on duplicate key update更多内容参见《MySQL中ON DUPLICATE KEY UPDATE的介绍与使用、批量更新、存在即更新不存在则插入 – 》replace into与on duplicate key update在一定程度上都能实现无记录时插入有记录时更新这个需求。但是强烈推荐使用on duplicate key update 原因如下replace into在唯一索引冲突时会删除原记录然后新增一条记录如果主键id是自增的会导致主键被改变 而on duplicate key update在唯一索引冲突时是更新原记录主键不变。replace into唯一索引冲突时会导致主键自增值增加但由于 binlog 事件记录为 UPDATE 会导致主从环境中表的 AUTO-INCREMENT 值不同从库执行 UPDATE 事件并不会导致 AUTO-INCREMENT 值增加所以从库表的 AUTO-INCREMENT 值会小于等于当前表的最新记录主键当发生主从切换时向新的主库插入记录就会报 duplicate key 错误。鉴于此很多使用 REPLACE INTO 的场景实际上需要的是 INSERT INTO … ON DUPLICATE KEY UPDATE虽然也会增加自增值但是不会出现从库表的 AUTO-INCREMENT 值会比当前表的最新记录主键小的情况与 ‘REPLACE INTO …’ 不同它只是更新重复行上的值没有删除也就不会导致原有主键值的变化。1replace into2on duplicate key update一两者在数据库中无记录时都执行插入操作在数据库中有记录时都执行“更新”操作。二二者在判断有无记录时都是根据表里的主键或唯一索引。三检索到存在记录时1是先删除原记录再插入新记录而2是保持原记录不变更新指定的字段。如表中有如下记录其中id是自增主键col1是唯一索引(id, col1, col2,col3) (1, a,b,c)执行replace into tabelname(col1, col2) values (a,d)则表中数据变为(id, col1, col2,col3) (2, a,d,null)原记录完全删除id发生自增。执行insert into tablename(col1) values (a)on duplicate key update col2d则表中数据变位(id, col1, col2,col3) (1, a,d,c)col3保持不变id未自增不过在插入下一条记录时id会跳跃。五、replace into的坑5.1、replace into与自动更新时间的坑replace into在有主键或唯一索引冲突时会执行deleteinsert操作若字段设置了自动更新需要手动设置时间或者字段设置默认时间否则时间字段会插入为空如下sql:drop table if exists user_test; create table user_test( id int primary key auto_increment comment 主键, name varchar(30) not null comment 姓名, update_time timestamp on update CURRENT_TIMESTAMP comment 更新时间 ) comment 测试表; insert into user_test(name,update_time) value (zhangsan,2000-01-01 00:00:00);此时执行replace intoreplace into user_test(id,name) value (1,zhangsan);上图可以看出执行replace into之后update_time的字段被更新为空了。解决方案# 方案一建表时设置字段有默认时间 create table user_test( id int primary key auto_increment comment 主键, name varchar(30) not null comment 姓名, update_time timestamp default current_timestamp on update CURRENT_TIMESTAMP comment 更新时间 ) comment 测试表; # 方案二replace into时手动设置时间 replace into user_test values(1,zhangsan,now());5.2、replace into用唯一索引更新时会导致自增值1见本文《3.1.3、有主键有唯一索引且唯一索引冲突(有坑)》5.3、replace into同时有主键或唯一索引冲突时可能会删除多条记录注意使用replace into时只能用主键更新或者唯一键更新二选其一。若同时都冲突了可能会导致异常见本文《3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑)》