Explanation of the Usage of replace and replace and into in Mysql

Mysql replace and replace into are frequently used functions; replace actually did update operation once, instead of delete before insert;; 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 used for PRIMARY KEY or an UNIQUE index, the old record is deleted before the new record is inserted.

replace is mysql processing string more commonly used function, can replace the contents of the string. Similar strings are processed by trim interception operation, which will not be mentioned here.

replace into mainly acts like insert insertion operation. The main difference is that replace checks the existence of data based on the primary key or only 1 index, and deletes it before updating if it exists.

Examples:

# 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')

Insert 2 records as above, and insert into will prompt an error: 1062 - Duplicate entry ‘a' for key ‘idx_name', Time: 0.001000s . With replace, into will perform normally, except that id grows by 1.

ps: into keyword in replace into can be omitted. It looks like 1, and it is used differently

1. replace(object,search,replace)

Replace all search occurring in object with replace

select replace('www.ofstack.com','w','n') from ...    --->   nnn.ofstack.com

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

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；

The operation of REPLACE is very similar to that of INSERT. If an old record in a table has the same value as a new record for an PRIMARY KEY or an UNIQUE index, the old record is deleted before the new record is inserted.

Note that there is no point in using an REPLACE statement unless the table has an PRIMARY KEY or an UNIQUE index. This statement will be the same as INSERT, because no index is used to determine whether new rows copy other rows.

The values of all columns are taken from the values specified in the REPLACE INTO statement. All missing columns are set to their respective default values, just like INSERT INTO1. You cannot reference a value from the current row or use a value in a new row. If you use a SET col_name = col_name + 1 ", the reference to the column name on the right is treated as DEFAULT (col_name). Therefore, the assignment is equivalent to SET col_name = DEFAULT(col_name) + 1 .

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

The REPLACE statement returns a number indicating the number of rows affected. The number is the sum of the number of rows deleted and inserted. If the number is 1 for 1 single row REPLACE, 1 row is inserted and no row is deleted. If the number is greater than 1, one or more old rows are deleted before new rows are inserted. If the table contains multiple 1-only indexes and the new row copies the values of different old rows in different 1-only indexes, it is possible that a single 1 row replaces multiple old rows.

The number of affected rows can easily be determined whether REPLACE adds only 1 row or whether REPLACE replaces other rows as well: Check whether the number is 1 (added) or greater (replaced).

At present, you cannot change to 1 table in 1 subquery and select from the same table at the same time.

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

1. Try inserting a new row into the table

2. When an insert fails because of a duplicate keyword error for the primary key or the only 1 keyword:

a. Delete conflicting rows with duplicate key values from the table b. Try inserting a new row into the table again

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 if there is duplication, it will report an error; replace into means inserting replacement data. There is PrimaryKey or unique index in the demand table. If there is data in the database, replace it with new data. If there is no data effect, it is like insert into1; insert ignore indicates that if the same record already exists in, the current new data is ignored;

Summarize