Explanation of the usage of replace and replace into in MySQL

MySQL replace and replace into are both frequently used functions; replace actually performs an update operation, rather than deleting first and then inserting; and replace into is actually very similar to insert into, but for replace into, if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

Replace is a commonly used function in MySQL to process strings, which can replace the content in a string. There is also a similar string processing operation called trim, which I will not go into detail here.

The main function of replace into is similar to the insert operation. The main difference is that replace will check whether the data exists based on the primary key or unique index, and if it exists, it will be deleted before updating.

example:

#Table structure:
CREATE TABLE `t_test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(1) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 UNIQUE KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Insert the first record

INSERT INTO t_test (`name`) VALUES ('a')
#or REPLACE INTO t_test (`name`) VALUES ('a')

1062 - Duplicate entry 'a' for key 'idx_name', Time: 0.001000s . Using replace into will execute normally, except that id will increase by 1.

ps: The keyword into in replace into can be omitted. They look the same, but they are used a little differently.

1. replace(object,search,replace)

Replace all occurrences of search in object with replace

select replace('www.jb51.net','w','n') from ... ---> nnn.jb51.net

Example: Replace detail in the name field in the table table with description

update table set name=replace(name,'detail','description')

2. replace into

Equivalent to: if not exists (select 1 from t where id = 1) insert into t(id, update_time) values(1, getdate()) else update t set update_time = getdate() where id = 1；

REPLACE operates much like INSERT. If an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement does not make sense. This statement would be identical to INSERT because no index is used to determine whether the new row duplicates other rows.

The values ​​of all columns are taken from the values ​​specified in the REPLACE INTO statement. Any missing columns are set to their default values, just as with INSERT INTO. You cannot reference values ​​from the current row, nor can you use values ​​in a new row. If you use an assignment such as " SET col_name = col_name + 1 ", the reference to the column name on the right side is treated as DEFAULT(col_name). Therefore, this assignment is equivalent to SET col_name = DEFAULT(col_name) + 1 .

To use REPLACE INTO, you must have INSERT and DELETE permissions for the table.

The REPLACE statement returns a number indicating the number of rows affected. This number is the sum of the number of deleted and inserted rows. If this number is 1 for a single-row REPLACE, a row is inserted and no rows are deleted. If this number is greater than 1, one or more old rows were deleted before the new row was inserted. If the table contains multiple unique indexes, and the new row duplicates the values ​​of different old rows in different unique indexes, it is possible for a single row to replace multiple old rows.

The number of rows affected makes it easy to determine whether REPLACE added just one row, or whether REPLACE also replaced other rows: check whether the number is 1 (addition) or greater (replacement).

Currently, you cannot alter to a table and select from the same table in a subquery.

Here is a more detailed description of the algorithm used (this algorithm is also used for LOAD DATA...REPLACE):

1. Try to insert a new row into the table

2. When an insert fails due to a duplicate key error for a primary key or unique key:

• a. Delete conflicting rows containing duplicate key values ​​from the table
• b. Try again to insert the new row into the table

Three forms:

• 1. replace into tbl_name(col_name, ...) values(...)
• 2. replace into tbl_name(col_name, ...) select ...
• 3. replace into tbl_name set col_name=value, ...

PS:

Three commonly used statements for inserting data in MySQL:

• insert into means inserting data. The database will check the primary key and report an error if there is a duplication.
• replace into means inserting and replacing data. The table must have a PrimaryKey or a unique index. If data already exists in the database, it will be replaced with new data. If there is no data, the effect is the same as insert into.
• insert ignore means that if the same record already exists, the current new data will be ignored;

Summarize

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. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links