澳门皇冠金沙网站-澳门皇冠844网站

热门关键词: 澳门皇冠金沙网站,澳门皇冠844网站

MySQL使用Replace操作时造成数据丢失的问题解决_

前言

语法:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]  
    [(col_name,...)]
    SELECT ...

  

本学科重要拿了两篇小说来介绍有关mysql的replace into语句的用法,有须求的敌人能够参照一下。

合营社开采职员在更新数据时行使了 replace into 语句,由于使用不当引致了数据的大气有失,到底是什么样以致的数额错失本文对此实行解析。

原理

replace的干活机制有一点像insert,只但是假诺在表里假若意气风发行有PRubiconIMA宝马X3Y KEY或然UNIQUE索引,那么就能够把老行删除然后插入新行。如:

root@test 03:23:55>show create table lingluoG
*************************** 1. row ***************************
       Table: lingluo
Create Table: CREATE TABLE `lingluo` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),--------------------------同时存在PK约束
  UNIQUE KEY `uk_bc` (`b`,`c`)----------------唯一索引约束
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.01 sec)

root@test 02:01:44>select * from lingluo;
Empty set (0.00 sec)

root@test 03:27:40>replace into lingluo values(1,10000,3,4);--------表里没有已存在的记录相当于insert
Query OK, 1 row affected (0.00 sec)-----------------------affect_rows是1

binlog格式:

图片 1

root@test 02:11:18>replace into lingluo values(1,10000,3,5);-------已经存在记录,且PK和UK同时冲突的时候,相当于先delete再insert
Query OK, 2 rows affected (0.00 sec)----------------------affect_rows是2,是delete和insert行数的总和

binlog格式:

图片 2

root@test 02:26:09>select * from lingluo;
 --- ------- ------ ------ 
| a | b     | c    | d    |
 --- ------- ------ ------ 
| 1 | 10000 |    3 |    5 |
 --- ------- ------ ------ 
1 row in set (0.00 sec)

root@test 02:31:54>replace into lingluo values(1,10000,4,5);-------已经存在记录,且PK同时冲突的时候,相当于先delete再insert
Query OK, 2 rows affected (0.00 sec)---------------------------------affect_rows是2,是delete和insert行数的总和

root@test 02:32:02>select * from lingluo;
 --- ------- ------ ------ 
| a | b     | c    | d    |
 --- ------- ------ ------ 
| 1 | 10000 |    4 |    5 |
 --- ------- ------ ------ 

binlog格式:

图片 3

root@test 02:37:04>replace into lingluo values(4,10000,6,5);
Query OK, 1 row affected (0.00 sec)
root@test 02:37:59>replace into lingluo values(6,10000,6,5);-------已经存在记录,且UK同时冲突的时候,直接update
Query OK, 2 rows affected (0.00 sec)---------------------------------affect_rows是2

root@test 02:40:31>select * from lingluo;
 --- ------- ------ ------ 
| a | b     | c    | d    |
 --- ------- ------ ------ 
| 1 | 10000 |    4 |    5 |
| 3 | 10000 |    5 |    5 |
| 6 | 10000 |    6 |    5 |
 --- ------- ------ ------ 
3 rows in set (0.00 sec)

图片 4

 

疑问:

既然uk矛盾的时候是update,那么为啥affect_rows都是2呢?

指定列replace:
root@test 03:34:37>select * from u;
 ---- ------ ------ 
| id | age  | d    |
 ---- ------ ------ 
|  0 |    1 |  126 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
 ---- ------ ------ 
6 rows in set (0.00 sec)

root@test 03:34:37>select * from u;
 ---- ------ ------ 
| id | age  | d    |
 ---- ------ ------ 
|  0 |    1 |  126 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
 ---- ------ ------ 
6 rows in set (0.00 sec)

root@test 03:34:40>replace into u (age,d)values(0,130);
Query OK, 2 rows affected, 1 warning (0.01 sec)

root@test 03:40:39>show warnings;
 --------- ------ ----------------------------------------- 
| Level   | Code | Message                                 |
 --------- ------ ----------------------------------------- 
| Warning | 1364 | Field 'id' doesn't have a default value |
 --------- ------ ----------------------------------------- 
1 row in set (0.00 sec)

root@test 03:40:47>select * from u;
 ---- ------ ------ 
| id | age  | d    |
 ---- ------ ------ 
|  0 |    0 |  130 |-----------------因为id是parimary但是没有auto_creasement,由126变成130
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
 ---- ------ ------ 
6 rows in set (0.00 sec)

用的时候供给当心的是:

  1. 万一内定replace列的话,尽量写全,要不然未有输入值的列数据会被赋成私下认可值(因为是先delete在insert卡塔 尔(阿拉伯语:قطر‎,就和普通的insert是千篇豆蔻年华律的,所以借使您要推行replace语句的话是亟需insert和delete权限的。

    若是您须求实践 SET col_name = col_name  1,就约等于推行col_name = DEFAULT(col_name) 1.

  2. replace语句假如不深切看的话,就和insert同样,试行完后没什么影响

例:

root@test 04:20:04>select * from u;
 ---- ------ ------ 
| id | age  | d    |
 ---- ------ ------ 
|  0 |    0 |  130 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
 ---- ------ ------ 
6 rows in set (0.00 sec)

root@test 04:20:10>replace into u (id,d) values(8,232);
Query OK, 1 row affected (0.01 sec)

root@test 04:20:39>select * from u;
 ---- ------ ------ 
| id | age  | d    |
 ---- ------ ------ 
|  0 |    0 |  130 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
|  8 | NULL |  232 |
 ---- ------ ------ 
7 rows in set (0.00 sec)

root@test 04:20:43>replace into u (id,d) values(7,232);
Query OK, 3 rows affected (0.01 sec)----------注意这里affect_rows是3,因为主键7已经存在,唯一索引232已经存在,所以需要删除id为7和8的行,然后插入新行

root@test 04:20:52>select * from u;
 ---- ------ ------ 
| id | age  | d    |
 ---- ------ ------ 
|  0 |    0 |  130 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 | NULL |  232 |
 ---- ------ ------ 
6 rows in set (0.00 sec)

root@test 04:20:55>

MySQL给replace和load data....replace用的算法是:

  1. 品尝向表里插入新行

  2. 当表里唯一索引只怕primary key冲突的时候:

    a. delete冲突行

    b.往表里再一次插入新行

倘诺碰着重复行冲突,存款和储蓄进程很可能当做update执行,实际不是delete insert,不过显式上都以如出风流浪漫辙的。这里未有顾客可知的震慑除了存款和储蓄引擎层Handler_xxx的状态变量。

因为REPLACE ... SELECT语句的结果注重于select的行的逐豆蔻梢头,不过各样无法保障皆以千篇生龙活虎律的,有十分的大可能率从master和slave的都不平等。就是基于这几个缘故,MySQL 5.6.4从今以往,REPLACE ... SELECT语句被标识为遵照statement的复制格局不安全的。基于那一个转换,当使用STATEMENT记录二进制日志的时候,若是有像这种类型的讲话就能在log里面输出一个报告急察方,相符当使用MIXED行复制形式也会记录报警。

在MySQL5.6.6之前的版本,replace影响分区表就像是MyISAM使用表级锁锁住有所的分区表相符。当使用 REPLACE ... PARTITION讲话时确实会发生上述情状。(使用基于行锁的InnoDB引起不会时有爆发这种状态。)在MySQL 5.6.6过后的版本MySQL使用分区锁,独有当分区(只要未有分区表的列更新)包蕴了REPLACE语句并且WHERE实际相配到的才会锁住那多少个分区;不然的话就能够锁住整个表。

操作情势:

图片 5

binlog格式:

图片 6

REPLACE的运作与INSERT很相同。只有有个别除了,假如表中的叁个旧记录与叁个用以P福睿斯IMA锐界Y KEY或二个UNIQUE索引的新记录具有同等的值,则在新记录被插入早先,旧记录被剔除。

风流倜傥、难点求证

结论

 

  1. 当存在pk冲突的时候是先delete再insert

  2. 当存在uk冲突的时候是一贯update

瞩目,除非表有三个PEvoqueIMATiguanY KEY或UNIQUE索引,否则,使用贰个REPLACE语句未有意义。该语句会与INSERT相通,因为从没索引被用来分明是不是新行复制了别的的行。

厂家开辟职员在更新数据时行使了 replace into 语句,由于使用不当招致了多少的豁达有失,到底是怎么样以致的多寡错失现深入分析如下。

这掌握了这么些,对大家有哪些用啊?

举多少个例证:

  1. 主备复制

在主备复制的时候,row格局会对replace into语句发生increment主键的自增加,主从两侧不相通难题。
主库上如上试行后,备Curry假使是auto_increment是不会变的!那会有哪些难题呢?把这么些slave 提高为 master 之后,由于 AUTO_INCREMENT 比实际的 next id 还要小,写入新记录时就能够时有爆发 duplicate key error,每回冲突之后 AUTO_INCREMENT = 1,直到增进为 max(id) 1 从此以后本领恢复生机平常。
这正是说对于这种主题素材的消除办法是怎样吧?@小强-zju 同学已经在那处给出了答案: 

  1. 数据迁移 
    无缘无故意识有个别字段的值被隐瞒
    鉴于此,相当多选拔 REPLACE INTO 的情景,实际上要求的是 INSERT INTO … ON DUPLICATE KEY UPDATE,在正确精通 REPLACE INTO 行为和副效用的前提下,稳重使用 REPLACE INTO。

具有列的值均取自在REPLACE语句中被钦命的值。全数缺点和失误的列被安装为独家的暗中认可值,那和INSERT相同。您不能够从脚下行中引用值,也不能在新行中接收值。假诺你使用四个诸如“SET col_name = col_name 1”的赋值,则对位于右边的列名称的援用会被充当DEFAULT(col_name)管理。由此,该赋值相当于SET col_name = DEFAULT(col_name) 1。

二、难题浅析

为了能够利用REPLACE,您必需同不时候具备表的INSERT和DELETE权限。

a. REPLACE 原理

REPLACE语句会再次来到叁个数,来提示受影响的行的数额。该数是被去除和被插入的行数的和。假如对于多个单行REPLACE该数为1,则意气风发行被插入,同期未有行被删除。假诺该数大于1,则在新行被插入前,有三个或八个旧行被去除。假使表包罗多少个独一索引,并且新行复制了在不一样的独占鳌头索引中的差别旧行的值,则有非常的大只怕是四个单一行替换了两个旧行。

REPLACE INTO 原理的法定解释为:

受影响的行数可以轻易地规定是还是不是REPLACE只增添了大器晚成行,可能是否REPLACE也交替了此外行:检查该数是还是不是为1(增多卡塔尔国或越来越大(替换卡塔尔。

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

借让你正在选取C API,则足以应用mysql_affected_rows()函数拿到受影响的行数。

倘使新插入行的主键或唯生机勃勃键在表中已经存在,则会删除原有记录并插入新行;假诺在表中不设有,则直接插入

近期,您无法在一个子询问中,向叁个表中更动,同不日常候从同三个表中甄选。

地址:

以下是所用算法的更详实的验证(该算法也用于LOAD DATA…REPLACE卡塔尔:

b. 难题现象

  1. 品味把新行插入到表中

  2. 当因为对于主键或唯意气风发主要字现身重复第一字错误而形成插入失利时:

不见数据的表结构如下:

a. 从表中删除含有重复第一字值的冲突行

CREATE TABLE `active_items` (

b. 再度尝试把新行插入到表中

实行的replace语句如下:

前些天脑仁疼死作者了,闪人睡觉去.

本文由澳门皇冠金沙网站发布于数据库研究,转载请注明出处:MySQL使用Replace操作时造成数据丢失的问题解决_