Carry の Blog
Carry の Blog
2022-03-10

MySQL insert on duplicate key update,replace into , insert ignore的理解

假设有一个表test，结构如下：

CREATE TABLE test (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
) ENGINE=InnoDB
1
2
3
4
5

现在要执行一条REPLACE INTO语句，用于向表中插入数据，如果发现id重复，则更新该记录的name和age字段。例如：

mysql> REPLACE INTO test(id, name, age) VALUES(1, 'replace', 30);select * from test;
Query OK, 1 row affected (0.01 sec)

+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | replace |   30 |
+----+---------+------+
1 row in set (0.00 sec)

mysql> REPLACE INTO test(id, name, age) VALUES(1, 'replace', 33);select * from test;
Query OK, 2 rows affected (0.00 sec)

+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | replace |   33 |
+----+---------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO test(id, name, age) VALUES(1, 'ON DUPLICATE KEY UPDATE', 33)  ON DUPLICATE KEY UPDATE `id`=VALUES(`id`),`name`=VALUES(`name`),`age`=VALUES(`age`);select * from test;
Query OK, 2 rows affected, 3 warnings (0.01 sec)
+----+-------------------------+------+
| id | name                    | age  |
+----+-------------------------+------+
|  1 | ON DUPLICATE KEY UPDATE |   33 |
+----+-------------------------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

binlog 记录如下

BEGIN
/*!*/;
# at 7006
#230317  4:06:28 server id 1  end_log_pos 7087 CRC32 0x2cd47a60 	Rows_query
# REPLACE INTO test(id, name, age) VALUES(1, 'replace', 30)
# at 7087
#230317  4:06:28 server id 1  end_log_pos 7148 CRC32 0x60606164 	Table_map: `carry`.`test` mapped to number 98
# at 7148
#230317  4:06:28 server id 1  end_log_pos 7200 CRC32 0x55b54522 	Write_rows: table id 98 flags: STMT_END_F
### INSERT INTO `carry`.`test`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='replace' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=30 /* INT meta=0 nullable=1 is_null=0 */
# at 7200
#230317  4:06:28 server id 1  end_log_pos 7231 CRC32 0xe178ee43 	Xid = 126
COMMIT/*!*/;
# at 7231
#230317  4:06:34 server id 1  end_log_pos 7310 CRC32 0xabcd17c8 	Anonymous_GTID	last_committed=22	sequence_number=23	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 7310
#230317  4:06:34 server id 1  end_log_pos 7387 CRC32 0xa584b414 	Query	thread_id=14	exec_time=0	error_code=0
SET TIMESTAMP=1678997194/*!*/;
BEGIN
/*!*/;
# at 7387
#230317  4:06:34 server id 1  end_log_pos 7468 CRC32 0x0d6bbbb0 	Rows_query
# REPLACE INTO test(id, name, age) VALUES(1, 'replace', 33)
# at 7468
#230317  4:06:34 server id 1  end_log_pos 7529 CRC32 0xf3688ef0 	Table_map: `carry`.`test` mapped to number 98
# at 7529
#230317  4:06:34 server id 1  end_log_pos 7599 CRC32 0xa914e987 	Update_rows: table id 98 flags: STMT_END_F
### UPDATE `carry`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='replace' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=30 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='replace' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=33 /* INT meta=0 nullable=1 is_null=0 */
# at 7599
#230317  4:06:34 server id 1  end_log_pos 7630 CRC32 0x5cc70722 	Xid = 128
COMMIT/*!*/;
# at 7630
#230317  4:06:46 server id 1  end_log_pos 7709 CRC32 0x028a0392 	Anonymous_GTID	last_committed=23	sequence_number=24	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 7709
#230317  4:06:46 server id 1  end_log_pos 7786 CRC32 0x8ffcb18b 	Query	thread_id=14	exec_time=0	error_code=0
SET TIMESTAMP=1678997206/*!*/;
BEGIN
/*!*/;
# at 7786
#230317  4:06:46 server id 1  end_log_pos 7967 CRC32 0x56a81bce 	Rows_query
# INSERT INTO test(id, name, age) VALUES(1, 'ON DUPLICATE KEY UPDATE', 33)  ON DUPLICATE KEY UPDATE `id`=VALUES(`id`),`name`=VALUES(`name`),`age`=VALUES(`age`)
# at 7967
#230317  4:06:46 server id 1  end_log_pos 8028 CRC32 0x83a6d339 	Table_map: `carry`.`test` mapped to number 98
# at 8028
#230317  4:06:46 server id 1  end_log_pos 8114 CRC32 0x83832170 	Update_rows: table id 98 flags: STMT_END_F
### UPDATE `carry`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='replace' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=33 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ON DUPLICATE KEY UPDATE' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=33 /* INT meta=0 nullable=1 is_null=0 */
# at 8114
#230317  4:06:46 server id 1  end_log_pos 8145 CRC32 0xb32f2b14 	Xid = 130
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

由此看来，无论是replace into 还是 insert into on duplicate key update 在遇到主键重复的时候，在binlog里记录的都是update,没有出现任何的DELETE语句。

  • 并不是谣传的：replace在binlog里记录的是先删除再插入

  • 他们唯一区别就是：

    replace是只能将一行数据的所有字段全部更新为新的数据，跟先删除后插入的效果一模一样，所有才会有这种谣传。

    insert into on duplicate key update 可以将重复主键的一行数据中的部分字段修改掉。比如一下例子: insert 了一条(1, 'myname', 43)，但是其中的age并不会被覆盖成43，只有name会被修改。用replace是做不到的。

INSERT INTO test(id, name, age) VALUES(1, 'myname', 43)  ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);  select * from test;
1

MySQL 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_name=value, ...

  1. insert ignore into

    当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。例如：

INSERT IGNORE INTO books (name) VALUES ('MySQL Manual')

  1. on duplicate key update

    当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。例如，为了实现name重复的数据插入不报错，可使用一下语句：

    INSERT INTO books (name) VALUES ('MySQL Manual') ON duplicate KEY UPDATE id = id INSERT INTO test(id, name, age) VALUES(1, 'myname', 43) ON DUPLICATE KEY UPDATE name=VALUES(name); select * from test;

  2. insert … select … where not exist

    根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：

    INSERT INTO books (name) SELECT 'MySQL Manual' FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)

  3. replace into

    如果存在primary or unique相同的记录，则先删除掉。再插入新记录。

    REPLACE INTO books SELECT 1, 'MySQL Manual' FROM books

上次更新: 8/28/2024

← MySQL不同字符集之间的区别和选择→

最近更新
01
GPT分区使用 parted 扩展分区的操作流程 原创
08-28
02
VictoriaMetrics 集群版安装与配置 原创
08-24
03
Kubernetes (k8s) 相关名词详解 原创
06-27
更多文章>
Theme by Vdoing