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

Recommend

TypeScript learning notes: type narrowing

Table of contents Preface Type Inference Truth va...

Table Tag (table) In-depth

<br />Table is a tag that has been used by e...

How to run multiple MySQL instances in Windows

Preface In Windows, you can start multiple MySQL ...

Sharing of SQL optimization experience when offset is too large during MySQL paging

Find the problem When we display the contents in ...

vue+node+socket io realizes multi-person interaction and releases the entire process

1. Background 1. The front end uses vue + vuex + ...

Javascript to achieve the drag effect of the login box

This article shares the specific code of Javascri...

Summary of MySQL LOAD_FILE() function method

In MySQL, the LOAD_FILE() function reads a file a...

Cross-host communication between docker containers-overlay-based implementation method

Overlay network analysis Built-in cross-host netw...

The pitfall record of case when judging NULL value in MySQL

Table of contents Preface Mysql case when syntax:...

A brief discussion on how Tomcat breaks the parent delegation mechanism

Table of contents JVM Class Loader Tomcat class l...

Detailed explanation of MySQL string concatenation function GROUP_CONCAT

In the previous article, I wrote a cross-table up...

VMware Workstation Pro installs Win10 pure version operating system

This article describes the steps to install the p...

Solve the problem of running node process in Linux system but unable to kill the process

Let me first introduce to you that the node proce...

Some Linux file permission management methods you may not know

Why do we need permission management? 1. Computer...

Summary of flex layout compatibility issues

1. W3C versions of flex 2009 version Flag: displa...