MySQL replace into and on duplicate key update test notes

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.