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

Vue realizes the whole process of slider drag verification function

Rendering Define the skeleton, write HTML and CSS...

Nginx proxy forwarding implementation code uploaded by Alibaba Cloud OSS

Preface Because the mini program upload requires ...

React+Koa example of implementing file upload

Table of contents background Server Dependencies ...

React implements dynamic pop-up window component

When we write some UI components, if we don't...

Several methods of calling js in a are sorted out and recommended for use

We often use click events in the a tag: 1. a href=...

Markup Language - Image Replacement

Click here to return to the 123WORDPRESS.COM HTML ...

How to make the height of child div fill the remaining space of parent container in CSS

1. Use floating method Effect picture: The code i...

Why the table file size remains unchanged after deleting data in MySQL

For databases that have been running for a long t...

How to use the vue timeline component

This article example shares the specific implemen...

Native JavaScript to achieve the effect of carousel

This article shares the specific code for JavaScr...

Commonly used HTML format tags_Powernode Java Academy

1. Title HTML defines six <h> tags: <h1&...

5 ways to make your JavaScript codebase cleaner

Table of contents 1. Use default parameters inste...

The handler PageHandlerFactory-Integrated has a bad module ManagedPipelineHandler in its module list

When developing a web project, you need to instal...

Three.js realizes Facebook Metaverse 3D dynamic logo effect

Table of contents background What is the Metavers...

Learn about CSS label display mode in one article

Tag type (display mode) HTML tags are generally d...