MySQL replace into and on duplicate key update test notes

MySQL replace into and on duplicate key update test notes

Mysql> CREATE TABLE tbl_insert_tmp (ID int (5), Addr_number Int (ten), name varchar, primary key (ID), unique key Udx_add R_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 (ten) is DEFAULT NULL,
' Name ' varchar (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, test on duplicate key update (is based on the primary key or unique key if the corresponding key is updated, there is no corresponding key to insert, so use does not delete normal data)


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>

Primary key ID and addr_number unique key are not duplicated, directly inserted
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>



Because the ID is the primary key there is a duplicate 1 direct update

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)



Because Addr_number unique key has duplicate 400 direct update

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>


Remove Addr_number Unique key to insert duplicate values directly
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 (ten) is DEFAULT NULL,
' Name ' varchar (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, test replace into (is based on the primary key or unique key first delete the corresponding key value and then insert, so the normal data will be deleted, use special caution)


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 (ten) is DEFAULT NULL,
' Name ' varchar (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 (ten) is DEFAULT NULL,
' Name ' varchar (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>


Remove Unique key 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>

This article is from the "Wish" blog, please be sure to keep this source http://xinyuan8.blog.51cto.com/677906/1655416

MySQL replace into and on duplicate key update test notes