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

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>

本文出自 “心愿” 博客，请务必保留此出处http://xinyuan8.blog.51cto.com/677906/1655416