Summary of replace into usage in MySQL

Introduction

Replace is similar to an insert statement.
If an old record in the table has the same value as a new record used for the primary key or a UNIQUE index, the old record is deleted before the new record is inserted.

Unless the table has a primary key or UNIQUE index, it makes no sense to use a REPLACE statement.

This statement is the same as INSERT, because no index is used to determine whether other rows have been copied in the new row. The values of all columns are equal to the value specified in the REPLACE statement. All missing columns are set as their default values, which is the same as INSERT.

The value cannot be referenced from the current row or be used in a new row. If you use a value such as "SET col_name = col_name + 1", the reference to the column name on the right will be processed as DEFAULT (col_name. Therefore, the value is equivalent to SET col_name = DEFAULT (col_name) + 1.

To be able to use REPLACE, you must have both the INSERT and DELETE permissions for the table.

The REPLACE statement returns a number to indicate the number of affected rows. This is the sum of the number of deleted and inserted rows. If this number is 1 for a single row, one row is inserted and no row is deleted. If the number is greater than 1, one or more old rows are deleted before the new row is inserted. If the table contains multiple unique indexes, and the new row copies the values of different old rows in different unique indexes, it is possible that a single row replaces multiple old rows. The number of affected rows can be easily determined whether REPLACE only adds one row, or whether REPLACE also replaces other rows: Check whether the number is 1 (Added) or larger (replaced ). If you are using c api, you can use the mysql_affected_rows () function to obtain the number of affected rows.

Detailed description of the main algorithms used (this algorithm is also used for load data... REPLACE ):
1. Try to Insert a new row into the table
2. when insertion fails due to a duplicate keyword error for the primary key or unique Keyword:. delete conflicting Row B that contains duplicate keyword values from the table. try to Insert a new row into the table again.

Main form

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 ,...
4. load data infile replace into tbl_name

Load data... REPLACE INTO TABLE

If REPLACE is specified, the input row replaces the existing row (that is, the row with the same primary index value as the existing row ).
If IGNORE is specified, the input row that already has a primary key value is skipped.
If either of them is not specified, the operation behavior depends on whether the LOCAL keyword is specified.
If no LOCAL is specified, if duplicate key values are found, an error is generated and the rest of the text file is ignored. If LOCAL is specified, the default operation will be the same as the IGNORE; this is because the server cannot terminate file transfer during the operation.

Instance resolution

Create a new table with id as the primary key and name as the unique index. The table creation language is as follows:

Create table 'test '(
'Id' int (11) not null,
'Name' varchar (20) not null,
Primary key ('id '),
Unique key 'name' ('name ')
) ENGINE = MyISAM default charset = gbk

Insert a record, id = 1, name = harvey

Insert into test values (1, 'Harvey ')

Use replace to insert a record again, id = 2, name = 'Qiu'

Replace into test values (2, 'Qiu ')

At this time, the number of affected rows is 1, and the data in the table is:
Id name
1 harvey
2 qiu

Insert a record using replace, id = 1, name = 'liu'

Replace into test values (1, 'liu ')

The number of affected rows is 2, and the result of select * from test is
Id name
1 liu
2 qiu

Insert a record using replace, id = 3, name = 'zhi'

Replace into test values (3, 'zhi ')

The number of affected rows is 2, and the result of select * from test is
Id name
3 zhi
2 qiu

Insert a record using replace, id = 2, name = 'zhi'
The number of affected rows is 3, and the result of select * from test is
Id name
2 zhi