MySQL replace into 语句浅析(一)

  这篇文章主要介绍了MySQL replace into 语句浅析(一),本文讲解了replace into的原理、使用方法及使用的场景和使用示例,需要的朋友可以参考下

  一 介绍

  在笔者支持业务过程中,经常遇到开发咨询replace into 的使用场景以及注意事项,这里做个总结。从功能原理,性能和注意事项上做个说明。

  二 原理

  2.1 当表中存在主键但是不存在唯一建的时候。

  表结构

  代码如下:

  CREATE TABLE `yy` (

  `id` bigint(20) NOT NULL,

  `name` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  [email protected] 02:43:58>insert into yy values(1,'abc');

  Query OK, 1 row affected (0.00 sec)

  [email protected] 02:44:25>replace into yy values(2,'bbb');

  Query OK, 1 row affected (0.00 sec)

  [email protected] 02:55:42>select * from yy;

  +----+------+

  | id | name |

  +----+------+

  | 1 | abc |

  | 2 | bbb |

  +----+------+

  2 rows in set (0.00 sec)

  [email protected] 02:55:56>replace into yy values(1,'ccc');

  Query OK, 2 rows affected (0.00 sec)

  如果本来已经存在的主键值,那么MySQL做update操作。

   代码如下:

  ### UPDATE test.yy

  ### WHERE

  ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */

  ### @2='abc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

  ### SET

  ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */

  ### @2='ccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

  如果本来相应的主键值没有,那么做insert 操作 replace into yy values(2,'bbb');

   代码如下:

  ### INSERT INTO test.yy

  ### SET

  ### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */

  ### @2='bbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

  # at 623

  #140314 2:55:42 server id 136403306 end_log_pos 650 Xid = 6090885569

  2.2 当表中主键和唯一键同时存在时

   代码如下:

  CREATE TABLE `yy` (

  `id` int(11) NOT NULL DEFAULT '0',

  `b` int(11) DEFAULT NULL,

  `c` int(11) DEFAULT NULL

  PRIMARY KEY (`a`),

  UNIQUE KEY `uk_bc` (`b`,`c`)

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

  情形1 主键冲突

  代码如下:

  [email protected] 04:37:18>replace into yy values(1,2,3);

  Query OK, 1 row affected (0.00 sec)

  [email protected] 04:37:37>replace into yy values(2,2,4);

  Query OK, 1 row affected (0.00 sec)

  [email protected] 04:38:05>select * from yy;

  +----+------+------+

  | id | b | c |

  +----+------+------+

  | 1 | 2 | 3 |

  | 2 | 2 | 4 |

  +----+------+------+

  2 rows in set (0.00 sec)

  [email protected] 04:38:50>replace into yy values(1,2,5);

  Query OK, 2 rows affected (0.00 sec)

  [email protected] 04:38:58>select * from yy;

  +----+------+------+

  | id | b | c |

  +----+------+------+

  | 2 | 2 | 4 |

  | 1 | 2 | 5 |

  +----+------+------+

  2 rows in set (0.00 sec)

  主键冲突时,数据库对表做先删除然后插入的操作,也即先删除id=1的记录,然后插入新的id=1 的记录(1,2,5).

   代码如下:

  BINLOG '

  Io5hVROWYHC+KwAAAEICAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==

  Io5hVRmWYHC+KgAAAGwCAAAAAMoMAAAAAAAAA//4AQAAAAIAAAADAAAA

  ### DELETE FROM test.yy

  ### WHERE

  ### @1=1 /* INT meta=0 nullable=0 is_null=0 */

  ### @2=2 /* INT meta=0 nullable=1 is_null=0 */

  ### @3=3 /* INT meta=0 nullable=1 is_null=0 */

  Io5hVReWYHC+KgAAAJYCAAAAAMoMAAAAAAEAA//4AQAAAAIAAAAFAAAA

  '/*!*/;

  ### INSERT INTO test.yy

  ### SET

  ### @1=1 /* INT meta=0 nullable=0 is_null=0 */

  ### @2=2 /* INT meta=0 nullable=1 is_null=0 */

  ### @3=5 /* INT meta=0 nullable=1 is_null=0 */

  # at 662

  #150524 16:38:58 server id 3195035798 end_log_pos 689 Xid = 22962508

  COMMIT/*!*/

  情形2 唯一建冲突

  代码如下:

  [email protected] 04:48:30>select * from yy;

  +----+------+------+

  | id | b | c |

  +----+------+------+

  | 1 | 2 | 4 |

  | 2 | 2 | 5 |

  | 3 | 3 | 5 |

  | 4 | 3 | 6 |

  +----+------+------+

  4 rows in set (0.00 sec)

  [email protected] 04:53:21>replace into yy values(5,3,6);

  Query OK, 2 rows affected (0.00 sec)

  [email protected] 04:53:40>select * from yy;

  +----+------+------+

  | id | b | c |

  +----+------+------+

  | 1 | 2 | 4 |

  | 2 | 2 | 5 |

  | 3 | 3 | 5 |

  | 5 | 3 | 6 |

  +----+------+------+

  4 rows in set (0.00 sec)

  主键不冲突,唯一键冲突时,数据库对表 唯一键为(3,6)的行做update操作,将主键修改为要插入的值,id=4 改为id=5。

   代码如下:

  BINLOG '

  lJFhVROWYHC+KwAAANoAAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==

  lJFhVRiWYHC+OAAAABIBAAAAAMoMAAAAAAEAA///+AQAAAADAAAABgAAAPgFAAAAAwAAAAYAAAA=

  '/*!*/;

  ### UPDATE test.yy

  ### WHERE

  ### @1=4 /* INT meta=0 nullable=0 is_null=0 */

  ### @2=3 /* INT meta=0 nullable=1 is_null=0 */

  ### @3=6 /* INT meta=0 nullable=1 is_null=0 */

  ### SET

  ### @1=5 /* INT meta=0 nullable=0 is_null=0 */

  ### @2=3 /* INT meta=0 nullable=1 is_null=0 */

  ### @3=6 /* INT meta=0 nullable=1 is_null=0 */

  # at 274

  #150524 16:53:40 server id 3195035798 end_log_pos 301 Xid = 22962872

  COMMIT/*!*/

  情形3 主键和唯一键同时冲突,如果需要插入的值的主键 和唯一和表中已经存在的存在冲突。

   代码如下:

  [email protected] 04:53:52>replace into yy values(1,3,6);

  Query OK, 3 rows affected (0.00 sec) ---注意此处影响的行数是3

  [email protected] 04:55:35>select * from yy;

  +----+------+------+

  | id | b | c |

  +----+------+------+

  | 2 | 2 | 5 |

  | 3 | 3 | 5 |

  | 1 | 3 | 6 |

  +----+------+------+

  3 rows in set (0.00 sec)

  要插入的值(1,3,6) 主键于 表里面的id=1的值冲突,唯一键(3,6)和表中id=5的记录冲突,MySQL 处理的时候 ,先删除id=1的行,然后更新了id=5的行。

   代码如下:

  BINLOG '

  B5JhVROWYHC+KwAAAJwBAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==

  B5JhVRmWYHC+KgAAAMYBAAAAAMoMAAAAAAAAA//4AQAAAAIAAAAEAAAA

  ### DELETE FROM test.yy

  ### WHERE

  ### @1=1 /* INT meta=0 nullable=0 is_null=0 */

  ### @2=2 /* INT meta=0 nullable=1 is_null=0 */

  ### @3=4 /* INT meta=0 nullable=1 is_null=0 */

  B5JhVRiWYHC+OAAAAP4BAAAAAMoMAAAAAAEAA///+AUAAAADAAAABgAAAPgBAAAAAwAAAAYAAAA=

  '/*!*/;

  ### UPDATE test.yy

  ### WHERE

  ### @1=5 /* INT meta=0 nullable=0 is_null=0 */

  ### @2=3 /* INT meta=0 nullable=1 is_null=0 */

  ### @3=6 /* INT meta=0 nullable=1 is_null=0 */

  ### SET

  ### @1=1 /* INT meta=0 nullable=0 is_null=0 */

  ### @2=3 /* INT meta=0 nullable=1 is_null=0 */

  ### @3=6 /* INT meta=0 nullable=1 is_null=0 */

  # at 510

  #150524 16:55:35 server id 3195035798 end_log_pos 537 Xid = 22962904

  COMMIT/*!*/

  三 结论

  对表进行replace into操作的时候,

  当不存在冲突时,replace into 相当于insert操作。

  当存在pk冲突的时候是先delete再insert,如果主键是自增的,则自增主键会做 +1 操作。【5.5,5.6版本均做过测试】

  当存在uk冲突的时候是直接update。,如果主键是自增的,则自增主键会做 +1 操作。 【5.5,5.6版本均做过测试】

MySQL replace into 语句浅析(一)


  了解上述原理和结论之后,以后再遇到replace into 的时候,相信各位读者可以知道如何选择,由于篇幅限制,后续文章会基于replace into原理,讲述生产过程中的注意事项。

分类:mysql数据库 时间:2015-03-01 人气:1
本文关键词:
分享到:

相关文章

Copyright (C) quwantang.com, All Rights Reserved.

趣玩堂 版权所有 京ICP备15002868号

processed in 0.028 (s). 10 q(s)