Detailed explanation on how to avoid the pitfalls of replacing logical SQL in MySQL

Detailed explanation on how to avoid the pitfalls of replacing logical SQL in MySQL

The difference between replace into and insert into on duplicate key

Usage of replace

When there is no conflict, it is equivalent to insert, and the default values ​​of other columns are used. When there is a key conflict, the auto-increment column is updated to replace the conflicting column, and the default values ​​of other columns are used.
Com_replace will add 1
Innodb_rows_updated will add 1

Usage of Insert into …on duplicate key

When there is no conflict, it is equivalent to insert. When the default values ​​of other columns conflict with the key, only the corresponding field values ​​are updated.
Com_insert will add 1
Innodb_rows_inserted will increase by 1

Experimental demonstration

Table Structure

create table helei1(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL DEFAULT '',
age tinyint(3) unsigned NOT NULL default 0,
PRIMARY KEY(id),
UNIQUE KEY uk_name (name)
)
ENGINE=innodb AUTO_INCREMENT=1 
DEFAULT CHARSET=utf8;

Table Data

[email protected] (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | He Lei | 26 |
| 2 | Xiao Ming | 28 |
| 3 | Xiaohong | 26 |
+----+-----------+-----+
3 rows in set (0.00 sec)

replace into usage

[email protected] (helei)> replace into helei1 (name) values('贺磊');
Query OK, 2 rows affected (0.00 sec)
[email protected] (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 | Xiao Ming | 28 |
| 3 | Xiaohong | 26 |
| 4 | He Lei | 0 |
+----+-----------+-----+
3 rows in set (0.00 sec)
[email protected] (helei)> replace into helei1 (name) values('爱璇');
Query OK, 1 row affected (0.00 sec)
[email protected] (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 | Xiao Ming | 28 |
| 3 | Xiaohong | 26 |
| 4 | He Lei | 0 |
| 5 | Aixuan | 0 |
+----+-----------+-----+
4 rows in set (0.00 sec)

Usage of replace

When there is no key conflict, replace into is equivalent to insert, and the default values ​​of other columns are used.

When a key conflict occurs, the auto-increment column is updated to replace the conflicting column, and the remaining columns are set to their default values.

Insert into …on duplicate key:

[email protected] (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 | Xiao Ming | 28 |
| 3 | Xiaohong | 26 |
| 4 | He Lei | 0 |
| 5 | Aixuan | 0 |
+----+-----------+-----+
4 rows in set (0.00 sec)
[email protected] (helei)> insert into helei1 (name,age) values('贺磊',0) on duplicate key update age=100;
Query OK, 2 rows affected (0.00 sec)
[email protected] (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 | Xiao Ming | 28 |
| 3 | Xiaohong | 26 |
| 4 | He Lei | 100 |
| 5 | Aixuan | 0 |
+----+-----------+-----+
4 rows in set (0.00 sec)
[email protected] (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 | Xiao Ming | 28 |
| 3 | Xiaohong | 26 |
| 4 | He Lei | 100 |
| 5 | Aixuan | 0 |
+----+-----------+-----+
4 rows in set (0.00 sec)
[email protected] (helei)> insert into helei1 (name) values('爱璇') on duplicate key update age=120;
Query OK, 2 rows affected (0.01 sec)
[email protected] (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 | Xiao Ming | 28 |
| 3 | Xiaohong | 26 |
| 4 | He Lei | 100 |
| 5 | Aixuan | 120 |
+----+-----------+-----+
4 rows in set (0.00 sec)
[email protected] (helei)> insert into helei1 (name) values('does not exist') on duplicate key update age=80;
Query OK, 1 row affected (0.00 sec)
[email protected] (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 | Xiao Ming | 28 |
| 3 | Xiaohong | 26 |
| 4 | He Lei | 100 |
| 5 | Aixuan | 120 |
| 8 | does not exist | 0 |
+----+-----------+-----+
5 rows in set (0.00 sec)

Summarize

The usage of replace into is equivalent to performing a delete operation first and then an insert operation if a conflicting key is found. The default value is used for unspecified columns. This will cause the auto-increment primary key to change. If there are foreign keys in the table or the business logic depends on the primary key, an exception will occur. Therefore, it is recommended to use Insert into …on duplicate key. Due to the rush of writing time, some errors or inaccuracies are inevitable in the article. We sincerely ask readers to criticize and correct any inappropriateness.

Well, the above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • In-depth understanding of Mysql logical architecture
  • MYSQL stored procedures, that is, a summary of common logical knowledge points
  • MySQL Advanced Learning Notes (Part 3): Introduction to MySQL Logical Architecture, Detailed Explanation of MySQL Storage Engine
  • Detailed explanation of MySQL execution principle, logical layering, and changing database processing engine
  • Detailed explanation of Mysql logical architecture
  • Using PHP to access the MySql database logical operations and examples of adding, deleting, modifying and checking
  • Logical judgment and conditional control of MySql stored procedures
  • MySQL uses frm files and ibd files to restore table data
  • MySQL uses binlog logs to implement data recovery
  • MySQL quick recovery solution based on time point
  • Implementation of MySQL5.7 mysqldump backup and recovery
  • Summary of MySQL logical backup and recovery testing

<<:  How to upgrade https under Nginx

>>:  Detailed explanation of the practical application of regular expressions in JavaScript

Blog    

Recommend

Implementation of Vue top tags browsing history

Table of contents nonsense Functions implemented ...

Do designers need to learn to code?

Often, after a web design is completed, the desig...

Detailed steps for installing MinIO on Docker

Table of contents 1. Check whether the docker env...

Floating menu, can achieve up and down scrolling effect

The code can be further streamlined, but due to t...

Detailed explanation of MySQL trigger trigger example

Table of contents What is a trigger Create a trig...

Detailed explanation of the relationship between React and Redux

Table of contents 1. The relationship between red...

Let's talk about the size and length limits of various objects in MySQL

Table of contents Identifier length limit Length ...

Common functions of MySQL basics

Table of contents 1. Common function classificati...

How to quickly insert 10 million records into MySQL

I heard that there is an interview question: How ...

In-depth discussion of memory principles: Are variables stored in the heap or stack in JS?

Table of contents 1. The elephant that can’t fit ...

Create a virtual machine and install the Redhat Linux operating system on VMware (graphic tutorial)

How to create a virtual machine on VMware and ins...

How to use Gitlab-ci to continuously deploy to remote machines (detailed tutorial)

Long story short, today we will talk about using ...

Detailed explanation of nginx's default_server definition and matching rules

The default_server directive of nginx can define ...

How to remotely connect to MySQL database with Navicat Premium

The party that creates a new connection is equiva...

How to periodically clean up images that are None through Jenkins

Preface In the process of continuous code deliver...