SoFunction
Updated on 2025-03-09

Detailed explanation of the usage example of Replace statement in MySQL

Preface

replace into is rarely used in development. This time, because it is working on an open source project that generates distributed IDs, and investigates a unique id generation solution based on database generation: a knowledge blind spot encountered by flickr. I will only record it in this article.

1. replace into function

Table structure

CREATE TABLE `id_generator` (  
`id` bigint(20) unsigned NOT NULL auto_increment,  
`stub` char(1) NOT NULL default '',  
  PRIMARY KEY  (`id`),  
UNIQUE KEY `stub` (`stub`)  
) ENGINE=MyISAM;

The id column is the primary key index, and the stub column is the unique index.

When replace into the database, if the data is repeated, the duplicate row of data is deleted and then insert a row.

replace has the ability to replace duplicate data with unique index or primary key index. That is, if the unique index or primary key index of the data inserted using replace into is duplicated with the previous data, the original data will be deleted and then added.

Then the criterion for how to judge whether it is duplicated is: whether the unique index or primary key index is consistent. Syntax: replace into table( col1, col2, col3 ) values ​​( val1, val2, val3 )
Semantics: replace data from the col1, col2, col3 columns in the table table

Take the id_generator table as an example:

REPLACE INTO id_generator(stub) VALUES ('trade')
 
 
The execution information is as follows：
No errors; 1 rows affected, taking 20ms

Indicates that only one line is added.

Execution results:

+----+--------------+
| id | stub         |
+----+--------------+
| 1  | trade        |
+----+--------------+

Continue to execute the following statement:

REPLACE INTO id_generator(stub) VALUES ('trade')
 
 
The execution information is as follows：
No errors; 2 rows affected, taking 23ms

Because one line was deleted, one line was added.

Execution results:

+----+--------------+
| id | stub         |
+----+--------------+
| 3  | trade        |
+----+--------------+

Because the stub column creates a column unique index, replace into When there are duplicate data rows in the stub, the data will be deleted and added again, causing the id to change.

What happens if replace into unique index id is repeated?

REPLACE INTO id_generator(id) VALUES (3)
 
 
The execution information is as follows：
No errors; 2 rows affected, taking 28ms

Because one line was deleted, one line was added.

Execution results:

+----+--------------+
| id | stub         |
+----+--------------+
| 3  |              |
+----+--------------+

At this time, we see that the value of the stub column is an empty string. Because id is the unique index, the record with id=3 already exists, so replace into will delete the record with id=3 and add it again, resulting in the stub being empty.

2. The difference between replace into, insert ignore and insert into

replace into functions similar to insert into.

The difference is:

insert into The most common insert. If there is data with the same primary key in the table, an error will be reported in the execution.

insert ignore If data with the same primary key in the table does not insert the data, otherwise it will be inserted (ignore if it exists, otherwise it will be inserted).

replace into First try to insert data into the table.

  1. If you find that this row of data already exists in the table (judged based on the primary key or unique index), delete the row of data first and then insert new data.
  2. Otherwise, insert new data directly.

Note: **The table inserted in the data must have a primary key or a unique index! ** Otherwise, replace into will directly insert the data, which will cause duplicate data in the table

3. Replace function

Syntax: replace(field,search,replace)

Description: field - column name of database table

search - strings that need to be replaced

replace - replace string

Semantics: Replace all search strings that appear in column name: field with replace strings.

Example:

update id_generator set stub = replace(stub,'trade','user')
 
select replace(uuid(), '-', '');

Attachment: The use of replace into in Mysql is similar to insert into

  • INSERT: A new data will be inserted each time.
  • REPLACE: First check whether this data exists in the table. If it exists, delete the data first, and then insert a new data; if it does not exist, insert a new data directly.

Key points:

  • Judging from the primary key or unique index in the table, if there is no primary key or unique index in the table, then REPLACE INTO is equivalent to INSERT

INTO will insert a piece of data directly.

Summarize

This is the end of this article about the detailed explanation of the usage of Replace statements in MySQL. For more related content on MySQL Replace usage, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!