mysql的replace into和on duplicate key update測試筆記

標籤：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

mysql的replace into和on duplicate key update測試筆記