mysql的replace into和on duplicate key update测试笔记

时间：2022-03-14 09:46

mysql的replace into和on duplicate key update测试笔记

mysql> create table tbl_insert_tmp(id int(5),addr_number int(10), name varchar(20),primary key (id),unique key udx_addr_number (addr_number));
Query OK, 0 rows affected (0.05 sec)

mysql> show create table tbl_insert_tmp\G
*************************** 1. row ***************************
       Table: tbl_insert_tmp
Create Table: CREATE TABLE `tbl_insert_tmp` (
`id` int(5) NOT NULL DEFAULT ‘0‘,
`addr_number` int(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `udx_addr_number` (`addr_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

1、测试on duplicate key update（是根据主键或唯一键如果有对应键则更新，没有对应键就插入，所以使用起来不会删除正常数据）

mysql> insert into tbl_insert_tmp values(1,100,‘a‘),(2,200,‘b‘),(3,300,‘c‘);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         100 | a    |
| 2 |         200 | b    |
| 3 |         300 | c    |
+----+-------------+------+
3 rows in set (0.00 sec)

mysql>

主键id和addr_number唯一键没有重复，直接插入
mysql> insert into tbl_insert_tmp(id,addr_number,name) values(4,400,‘d‘) on duplicate key update name=‘haha‘;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         100 | a    |
| 2 |         200 | b    |
| 3 |         300 | c    |
| 4 |         400 | d    |
+----+-------------+------+
4 rows in set (0.00 sec)

mysql>

因为id是主键有重复1直接更新

mysql> insert into tbl_insert_tmp(id,addr_number,name) values(1,500,‘d‘) on duplicate key update name=‘haha‘;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         100 | haha |
| 2 |         200 | b    |
| 3 |         300 | c    |
| 4 |         400 | d    |
+----+-------------+------+
4 rows in set (0.00 sec)

因为addr_number唯一键有重复400直接更新

mysql> insert into tbl_insert_tmp(id,addr_number,name) values(5,400,‘d‘) on duplicate key update name=‘hehe‘;
Query OK, 2 rows affected (0.03 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         100 | haha |
| 2 |         200 | b    |
| 3 |         300 | c    |
| 4 |         400 | hehe |
+----+-------------+------+
4 rows in set (0.00 sec)

mysql>

删除addr_number唯一键就可以直接插入重复值
mysql> alter table tbl_insert_tmp drop key udx_addr_number;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table tbl_insert_tmp\G
*************************** 1. row ***************************
       Table: tbl_insert_tmp
Create Table: CREATE TABLE `tbl_insert_tmp` (
`id` int(5) NOT NULL DEFAULT ‘0‘,
`addr_number` int(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>
mysql> insert into tbl_insert_tmp(id,addr_number,name) values(5,400,‘d‘) on duplicate key update name=‘hehe‘;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         100 | haha |
| 2 |         200 | b    |
| 3 |         300 | c    |
| 4 |         400 | hehe |
| 5 |         400 | d    |
+----+-------------+------+
5 rows in set (0.00 sec)

mysql>

2、测试replace into （是根据主键或者唯一键先删除对应键值再插入，所以会删除正常数据，使用时要特别小心）

mysql> truncate table tbl_insert_tmp;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table tbl_insert_tmp\G
*************************** 1. row ***************************
       Table: tbl_insert_tmp
Create Table: CREATE TABLE `tbl_insert_tmp` (
`id` int(5) NOT NULL DEFAULT ‘0‘,
`addr_number` int(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table tbl_insert_tmp add unique key udx_addr_number(addr_number);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table tbl_insert_tmp\G
*************************** 1. row ***************************
       Table: tbl_insert_tmp
Create Table: CREATE TABLE `tbl_insert_tmp` (
`id` int(5) NOT NULL DEFAULT ‘0‘,
`addr_number` int(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `udx_addr_number` (`addr_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>
mysql>
mysql> insert into tbl_insert_tmp values(1,100,‘a‘),(2,200,‘b‘),(3,300,‘c‘),(4,400,‘d‘);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         100 | a    |
| 2 |         200 | b    |
| 3 |         300 | c    |
| 4 |         400 | d    |
+----+-------------+------+
4 rows in set (0.00 sec)

mysql>

mysql> replace into tbl_insert_tmp values(1,500,‘e‘);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         500 | e    |
| 2 |         200 | b    |
| 3 |         300 | c    |
| 4 |         400 | d    |
+----+-------------+------+
4 rows in set (0.00 sec)

mysql>
mysql> replace into tbl_insert_tmp values(2,500,‘f‘);
Query OK, 3 rows affected (0.00 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 2 |         500 | f    |
| 3 |         300 | c    |
| 4 |         400 | d    |
+----+-------------+------+
3 rows in set (0.00 sec)

mysql>

mysql> replace into tbl_insert_tmp values(5,500,‘h‘);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 3 |         300 | c    |
| 4 |         400 | d    |
| 5 |         500 | h    |
+----+-------------+------+
3 rows in set (0.00 sec)

mysql>

删除唯一键udx_addr_number

mysql> alter table tbl_insert_tmp drop key udx_addr_number;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>
mysql> replace into tbl_insert_tmp values(1,500,‘g‘);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         500 | g    |
| 3 |         300 | c    |
| 4 |         400 | d    |
| 5 |         500 | h    |
+----+-------------+------+
4 rows in set (0.00 sec)

mysql>
mysql> replace into tbl_insert_tmp values(2,300,‘i‘);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         500 | g    |
| 2 |         300 | i    |
| 3 |         300 | c    |
| 4 |         400 | d    |
| 5 |         500 | h    |
+----+-------------+------+
5 rows in set (0.00 sec)

mysql> replace into tbl_insert_tmp values(2,200,‘b‘);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 |         500 | g    |
| 2 |         200 | b    |
| 3 |         300 | c    |
| 4 |         400 | d    |
| 5 |         500 | h    |
+----+-------------+------+
5 rows in set (0.00 sec)

mysql>