10bet网址
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

13.2.8 REPLACE Statement

REPLACE [LOW_PRIORITY | DELAYED] [INTO]tbl_name[PARTITION (partition_name[,partition_name] ...)] [(col_name[,col_name] ...)] {VALUES | VALUE} (value_list) [, (value_list)] ... REPLACE [LOW_PRIORITY | DELAYED] [INTO]tbl_name[PARTITION (partition_name[,partition_name] ...)] SETassignment_listREPLACE [LOW_PRIORITY | DELAYED] [INTO]tbl_name[PARTITION (partition_name[,partition_name] ...)] [(col_name[,col_name] ...)] SELECT ...value: {expr| DEFAULT}value_list:value[,value] ...assignment:col_name=valueassignment_list:assignment[,assignment] ...

REPLACEworks exactly likeINSERT, except that if an old row in the table has the same value as a new row for aPRIMARY KEYor aUNIQUEindex, the old row is deleted before the new row is inserted. SeeSection 13.2.5, “INSERT Statement”.

REPLACEis a MySQL extension to the SQL standard. It either inserts, ordeletesand inserts. For another MySQL extension to standard SQL—that either inserts orupdates—seeSection 13.2.5.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.

DELAYEDinserts and replaces were deprecated in MySQL 5.6. In MySQL 5.7,DELAYEDis not supported. The server recognizes but ignores theDELAYEDkeyword, handles the replace as a nondelayed replace, and generates anER_WARN_LEGACY_SYNTAX_CONVERTEDwarning:REPLACE DELAYED is no longer supported. The statement was converted to REPLACE. TheDELAYEDkeyword is scheduled for removal in a future release. release.

Note

REPLACEmakes sense only if a table has aPRIMARY KEYorUNIQUEindex. Otherwise, it becomes equivalent toINSERT, because there is no index to be used to determine whether a new row duplicates another.

Values for all columns are taken from the values specified in theREPLACEstatement. Any missing columns are set to their default values, just as happens forINSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such asSETcol_name=col_name+ 1, the reference to the column name on the right hand side is treated asDEFAULT(col_name), so the assignment is equivalent toSETcol_name= DEFAULT(col_name) + 1.

To useREPLACE, you must have both theINSERTandDELETEprivileges for the table.

If a generated column is replaced explicitly, the only permitted value isDEFAULT. For information about generated columns, seeSection 13.1.18.7, “CREATE TABLE and Generated Columns”.

REPLACEsupports explicit partition selection using thePARTITIONclause with a list of comma-separated names of partitions, subpartitions, or both. As withINSERT, if it is not possible to insert the new row into any of these partitions or subpartitions, theREPLACEstatement fails with the errorFound a row not matching the given partition set. For more information and examples, seeSection 22.5, “Partition Selection”.

TheREPLACE语句返回一个计数显示of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-rowREPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.

The affected-rows count makes it easy to determine whetherREPLACEonly added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).

If you are using the C API, the affected-rows count can be obtained using themysql_affected_rows()function.

You cannot replace into a table and select from the same table in a subquery.

MySQL uses the following algorithm forREPLACE(andLOAD DATA ... REPLACE):

  1. Try to insert the new row into the table

  2. While the insertion fails because a duplicate-key error occurs for a primary key or unique index:

    1. Delete from the table the conflicting row that has the duplicate key value

    2. Try again to insert the new row into the table

It is possible that in the case of a duplicate-key error, a storage engine may perform theREPLACEas an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine incrementsHandler_xxxstatus variables.

Because the results ofREPLACE ... SELECTstatements depend on the ordering of rows from theSELECTand this order cannot always be guaranteed, it is possible when logging these statements for the source and the replica to diverge. For this reason,REPLACE ... SELECTstatements are flagged as unsafe for statement-based replication. such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when usingMIXEDmode. See alsoSection 16.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

When modifying an existing table that is not partitioned to accommodate partitioning, or, when modifying the partitioning of an already partitioned table, you may consider altering the table's primary key (seeSection 22.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”). You should be aware that, if you do this, the results ofREPLACEstatements may be affected, just as they would be if you modified the primary key of a nonpartitioned table. Consider the table created by the followingCREATE TABLEstatement:

CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, data VARCHAR(64) DEFAULT NULL, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) );

When we create this table and run the statements shown in the mysql client, the result is as follows:

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00'); Query OK, 1 row affected (0.04 sec) mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42'); Query OK, 2 rows affected (0.04 sec) mysql> SELECT * FROM test; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | New | 2014-08-20 18:47:42 | +----+------+---------------------+ 1 row in set (0.00 sec)

Now we create a second table almost identical to the first, except that the primary key now covers 2 columns, as shown here (emphasized text):

CREATE TABLE test2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, data VARCHAR(64) DEFAULT NULL, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (id, ts));

When we run ontest2the same twoREPLACEstatements as we did on the originaltesttable, we obtain a different result:

mysql >替换成test2值(2014 - 1‘老’”08-20 18:47:00'); Query OK, 1 row affected (0.05 sec) mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42'); Query OK, 1 row affected (0.06 sec) mysql> SELECT * FROM test2; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | Old | 2014-08-20 18:47:00 | | 1 | New | 2014-08-20 18:47:42 | +----+------+---------------------+ 2 rows in set (0.00 sec)

This is due to the fact that, when run ontest2, both theidandtscolumn values must match those of an existing row for the row to be replaced; otherwise, a row is inserted.

AREPLACEstatement affecting a partitioned table using a storage engine such asMyISAMthat employs table-level locks locks only those partitions containing rows that match theREPLACEstatementWHEREclause, as long as none of the table partitioning columns are updated; otherwise the entire table is locked. (For storage engines such asInnoDBthat employ row-level locking, no locking of partitions takes place.) For more information, seeSection 22.6.4, “Partitioning and Locking”.