MySQL replace into the detailed

The run of replace is very similar to insert. Except for one point, if an old record in the table has the same value as a new record for primary key or a unique index, the old record is deleted before the new record is inserted.

Note that it is meaningless to use a Replace statement unless the table has a primary key or a unique index. The statement is the same as the insert, because no indexes are used to determine whether the new row replicates other rows.

The values for all columns are taken from the value specified in the Replace statement. All missing columns are set to their default values, which are the same as inserts. You cannot reference a value from the current row, nor can you use a value in a new row. If you use an assignment such as "SET col_name = col_name + 1", a reference to the column name on the right side is treated as default (col_name). Therefore, the assignment is equivalent to set col_name = DEFAULT (col_name) + 1.

In order to be able to use replace, you must have both insert and delete permissions for the table.

The Replace statement returns a number that indicates the number of rows affected. This number is the same as the number of rows that were deleted and inserted. If the number is 1 for a single line replace, the row is inserted and no rows are deleted. If the number is greater than 1, one or more old rows are deleted before the new row is inserted. If a table contains more than one unique index, and a new row duplicates a value from a different old row in a different unique index, it is possible that a single row replaces more than one old row.

The number of rows affected can easily determine whether replace has added only one row, or if replace has also replaced other rows: check to see if the number is 1 (added) or larger (replace).

If you are using the C API, you can use the Mysql_affected_rows () function to get the number of rows affected.

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

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

1. Try inserting the new row into the table

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

A. Delete conflicting rows that contain duplicate key values from the table

B. Try inserting the new row into the table again

My throat is killing me today, I'm going to sleep.

Use format:

The code is as follows	Copy Code
REPLACE [Low_priority | Delayed] [Into] tbl_name [(Col_name,...)] VALUES ({expr | DEFAULT},...), (...),... Or: REPLACE [Low_priority | Delayed] [Into] Tbl_name SET col_name={expr | DEFAULT}, ... Or: REPLACE [Low_priority | Delayed] [Into] tbl_name [(Col_name,...)] SELECT ...

One. REPLACE into table_name (' col_a ', ' Col_b ') VALUES (' Col A data ', ' col B data ');

Replaces items in a row. This action was dependant on the ' id ' because when doing a REPLACE and you must include the PRIMARY (unique) column. Since we established the "ID" column as our PRIMARY key (while establishing the table), MySQL needs this info so it knows W Hich row we are talking about. If we didn ' t include the ' ID ' coumn, MySQL'll have no idea which row we are trying to replace.

In this example, we are replacing row #2

The code is as follows	Copy Code
$sql = "REPLACE into music (id,artist,album) VALUES (' 2 ', ' The ' Beatles ', ' let It is ')"; mysql_query ($sql); ID artist album title track year 1 The Beatles Abbey Road 2 The Beatles let It be 3 Abbey Road 3 test Records:3

Here, we haven ' t defined the ' ID ' column. Hence, MySQL doesn ' t know which row to replace, so it just adds a new row.

So as we can, the REPLACE feature acts very similar to INSERT. We can use the to our advantage!

Again, REPLACE behaves much like inserts except that:
-if the PRIMARY ("unique column") is supplied, the existing R ow'll be updated
-if the PRIMARY ("unique column") is not provided, a new row would be added.

code is as follows	copy code
$sql = " REPLACE into music (artist,album) VALUES (' The Beatles ', ' The Magical Mystery Tour '); mysql_query ($sql); ID artist album title track year 1 The Beatles Abbey road    2 The Beatles let It BE &N bsp;  3  Abbey Road 3 test    4 The Beatles the magical Mystery Records:4

Errors when trying this kind of REPLACE, because usually the "unique column" can note:you null (or empty). Some people like to set up the database where the ' unique column ' is automatically incremented by MySQL. This is kind of confusing and can leads to a headache. So just remember to include the "unique column" as using the REPLACE statement, or you'll get duplicate rows ... OR use the UPDATE statement.

Two. The difference between Mysql replace into and insert into on duplicate key update is that if records are updated in the database, the new record is inserted, but there is a difference in how it works. Summarized as follows: 1. If a primary key record does not exist in the table, replace and insert*update are the same characteristics as insert. 2. If a primary key record exists in the table, replace is equivalent to performing a delete and insert two operation, whereas insert*update is equivalent to executing the IF exist do update else does insert. Therefore, if the replace fill has incomplete fields, the field that is not updated will be modified to the default value, and if there is an ID, the ID will change to the most recent value (so that the record may be lost if it is marked with a self ID); insert* Update only updates some of the fields and does not change for fields that are not updated (the default values are not enforced).

Three. The case of a unique key

If a unique key (whether a field or a combined field) exists, the effect of replace into is to update the primary key if the content of the unique key already exists, or to insert a record if the corresponding content of the unique key does not exist.