The Replace statement in MySQL

I. BACKGROUND

When you update the Access_apps table with the Replace statement, the information for the original Mark column, remark column is lost.

CREATETABLE`access_apps` (  `base` varchar(11) NOTNULLDEFAULT‘‘,  `business` varchar(64) NOTNULLDEFAULT‘‘,  `owt` varchar(64) NOTNULLDEFAULT ‘‘,  `pdl` varchar(64) NOTNULLDEFAULT‘‘,  `app_group_id` varchar(64) NOTNULL DEFAULT‘‘,  `app_artifact_id` varchar(64) NOTNULLDEFAULT‘‘,  `app` varchar(128) NOT NULLDEFAULT‘‘,  `appkey` varchar(128) NOTNULLDEFAULT‘‘,  `version` varchar(128) NOTNULLDEFAULT‘‘,  `status` tinyint(1) NOTNULLDEFAULT‘0‘,  `mark` int(11) NOTNULLDEFAULT‘0‘,  `remark` varchar(128) NOTNULLDEFAULT‘‘,  `update_time` timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,  PRIMARYKEY(`base`,`business`,`owt`,`pdl`,`app_group_id`,`app_artifact_id`)) ENGINE=InnoDB DEFAULTCHARSET=utf8mb4;

@Insert("replace into access_apps(base,business,owt,pdl,app_group_id,app_artifact_id,app,appkey,version,status) "+        "values(#{base},#{business},#{owt},#{pdl},#{app_group_id},#{app_artifact_id},#{app},#{appkey},#{version},#{status})")voidsaveAccessRecord(AccessRecord accessRecord);

There is a mark, remark column in the Access_apps table, and no mark, remark column in the Replace statement.

Cause of Error:

Error that the Replace statement is equivalent to a primary key conflict, the modified column will use the new value, and the unmodified column will use the old value

The Replace statement will use the default value for the missing column instead of the value in the current row, causing the original column information to be lost.

II. explanation of Replace statement

The specific algorithm for the Replace statement in MySQL is as follows:

1. Try inserting a new row into the table

2. When an insert fails because of a primary key (PRIMARY key) conflict error or a unique index duplicate error:

A. Removing conflicting rows from a table that contain duplicate key values

B. Try again to insert a new row into the table

You can see that the Replace statement is equivalent to an insert operation or a delete+insert operation, so you must have both insert and delete permissions in order to use the Replace statement.

When the Replace statement executes, the following two conditions are divided:

Situation 1:insert

When there is no primary key conflict or unique index conflict, the equivalent of the insert operation

Case 2:delete and insert

When there is a primary key conflict or a unique index conflict, the equivalent of a delete operation, insert operation

The values of all columns are taken from the value specified in the Replace statement, and the values of all missing columns are set to the default values for the columns, which is the same as insert.

You cannot reference the value of the current row and then use it to update the new row. (Because the current row is deleted when the current line is in conflict with the new row, it cannot be referenced!) )

For example, if you use an assignment with a shape such as "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.

Return value of the Replace statement

The Replace statement returns a number that indicates the number of rows affected. The number is the number of rows that are deleted and inserted.

1. If the return value is 1 for a replace statement, the row is inserted and no rows are deleted.

2. If the number is greater than 1, one or more old rows are deleted before the new row is inserted. If the table contains more than one unique index, and the new row is duplicated with a different unique index from the non-peers.

Example 1:test table ID as primary key

CREATETABLEtest (  id INT UNSIGNED NOTNULLAUTO_INCREMENT,  data VARCHAR(64) DEFAULTNULL,  ts TIMESTAMPNOTNULL DEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,  PRIMARYKEY(id));

mysql> REPLACEINTOtest VALUES(1, ‘Old‘, ‘2014-08-20 18:47:00‘);Query OK, 1 row affected (0.04 sec)mysql> REPLACEINTOtest VALUES(1, ‘New‘, ‘2014-08-20 18:47:42‘);Query OK, 2 rowsaffected (0.04 sec)mysql> SELECT* FROMtest;+----+------+---------------------+| id | data | ts                  |+----+------+---------------------+|  1 | New  | 2014-08-20 18:47:42 |+----+------+---------------------+1 row inset(0.00 sec)

When the first replace statement executes, there is no data in the test table, there is no conflict, so the equivalent of an insert operation with a return value of 1 (1 row affected).

When the second Replace statement executes, the data for id=1 already exists and a primary key conflict occurs, so it is equivalent to a delete operation followed by an insert operation with a return value of 2 (2 rows affected).

Example 2:test table Id,ts as primary key

CREATETABLEtest2 (  id INTUNSIGNED NOTNULLAUTO_INCREMENT,  data VARCHAR(64) DEFAULTNULL,  ts TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,  PRIMARYKEY(id, ts));

mysql> REPLACEINTO test2 VALUES(1, ‘Old‘, ‘2014-08-20 18:47:00‘);Query OK, 1 row affected (0.05 sec)mysql> REPLACEINTOtest2 VALUES(1, ‘New‘, ‘2014-08-20 18:47:42‘);Query OK, 1 row affected (0.06 sec)mysql> SELECT* FROMtest2;+----+------+---------------------+| id | data | ts                  |+----+------+---------------------+|  1 | Old  | 2014-08-20 18:47:00 ||  1 | New  | 2014-08-20 18:47:42 |+----+------+---------------------+2 rowsinset(0.00 sec)

Because Id,ts is the primary key, replace has no primary key conflict, so it is equivalent to an insert operation.

Iii. references

https://dev.mysql.com/doc/refman/5.7/en/replace.html MySQL official website replace syntax

Http://www.cnblogs.com/c-961900940/p/6197878.html the use of replace into in MySQL

http://www.cnblogs.com/martin1009/archive/2012/10/08/2714858.html MySQL Replace into usage detailed description

The Replace statement in MySQL