MySQL replace into 語句淺析（一）

2024-07-24 13:07:24

字體：大中小

來源：轉載

供稿：網友

這篇文章主要介紹了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;
		root@test 02:43:58>insert into yy values(1,'abc');
		Query OK, 1 row affected (0.00 sec)
		root@test 02:44:25>replace into yy values(2,'bbb');
		Query OK, 1 row affected (0.00 sec)
		root@test 02:55:42>select * from yy;
		+----+------+
		| id | name |
		+----+------+
		| 1 | abc |
		| 2 | bbb |
		+----+------+
		2 rows in set (0.00 sec)
		root@test 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 主鍵沖突

復制代碼代碼如下:

		
		root@test 04:37:18>replace into yy values(1,2,3);
		Query OK, 1 row affected (0.00 sec)
		root@test 04:37:37>replace into yy values(2,2,4);
		Query OK, 1 row affected (0.00 sec)
		root@test 04:38:05>select * from yy;
		+----+------+------+
		| id | b | c |
		+----+------+------+
		| 1 | 2 | 3 |
		| 2 | 2 | 4 |
		+----+------+------+
		2 rows in set (0.00 sec)
		root@test 04:38:50>replace into yy values(1,2,5);
		Query OK, 2 rows affected (0.00 sec)
		root@test 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 唯一建沖突

復制代碼代碼如下:

		
		root@test 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)
		root@test 04:53:21>replace into yy values(5,3,6);
		Query OK, 2 rows affected (0.00 sec)
		root@test 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 主鍵和唯一鍵同時沖突,如果需要插入的值的主鍵和唯一和表中已經存在的存在沖突。

復制代碼代碼如下:

		
		root@test 04:53:52>replace into yy values(1,3,6);
		Query OK, 3 rows affected (0.00 sec) ---注意此處影響的行數是3 
		root@test 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.6版本均做過測試】
   當存在uk沖突的時候是直接update。,如果主鍵是自增的，則自增主鍵會做 +1 操作。   【5.5,5.6版本均做過測試】

MySQL replace into 語句淺析（一）