Rechercher dans le manuel MySQL
13.2.9 REPLACE Syntax
REPLACE
works exactly like
INSERT
, except that if an old row
in the table has the same value as a new row for a
PRIMARY KEY
or a UNIQUE
index, the old row is deleted before the new row is inserted. See
Section 13.2.6, “INSERT Syntax”.
REPLACE
is a MySQL extension to the
SQL standard. It either inserts, or deletes
and inserts. For another MySQL extension to standard
SQL—that either inserts or
updates—see
Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.
DELAYED
inserts and replaces were deprecated in
MySQL 5.6. In MySQL 8.0, DELAYED
is not supported. The server recognizes but ignores the
DELAYED
keyword, handles the replace as a
nondelayed replace, and generates an
ER_WARN_LEGACY_SYNTAX_CONVERTED
warning.
(“REPLACE DELAYED is no longer supported. The statement was
converted to REPLACE.”) The DELAYED
keyword will be removed in a future release.
REPLACE
makes sense only if a
table has a PRIMARY KEY
or
UNIQUE
index. Otherwise, it becomes
equivalent to INSERT
, 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 the
REPLACE
statement. Any missing
columns are set to their default values, just as happens for
INSERT
. You cannot refer to values
from the current row and use them in the new row. If you use an
assignment such as SET
, the reference
to the column name on the right hand side is treated as
col_name
=
col_name
+ 1DEFAULT(
,
so the assignment is equivalent to col_name
)SET
.
col_name
=
DEFAULT(col_name
) + 1
To use REPLACE
, you must have both
the INSERT
and
DELETE
privileges for the table.
If a generated column is replaced explicitly, the only permitted
value is DEFAULT
. For information about
generated columns, see
Section 13.1.20.8, “CREATE TABLE and Generated Columns”.
REPLACE
supports explicit partition selection
using the PARTITION
keyword with a list of
comma-separated names of partitions, subpartitions, or both. As
with INSERT
, if it is not possible
to insert the new row into any of these partitions or
subpartitions, the REPLACE
statement fails with
the error Found a row not matching the given partition
set. For more information and examples, see
Section 23.5, “Partition Selection”.
The REPLACE
statement returns a
count to indicate the number of rows affected. This is the sum of
the rows deleted and inserted. If the count is 1 for a single-row
REPLACE
, 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 whether
REPLACE
only 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 the
mysql_affected_rows()
function.
You cannot replace into a table and select from the same table in a subquery.
MySQL uses the following algorithm for
REPLACE
(and LOAD DATA ...
REPLACE
):
Try to insert the new row into the table
While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
Delete from the table the conflicting row that has the duplicate key value
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 the REPLACE
as 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 increments
Handler_
status
variables.
xxx
Because the results of REPLACE ... SELECT
statements depend on the ordering of rows from the
SELECT
and this order cannot always
be guaranteed, it is possible when logging these statements for
the master and the slave to diverge. For this reason,
REPLACE ... SELECT
statements 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 using
MIXED
mode. See also
Section 17.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 (see
Section 23.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”).
You should be aware that, if you do this, the results of
REPLACE
statements may be affected, just as
they would be if you modified the primary key of a nonpartitioned
table. Consider the table created by the following
CREATE TABLE
statement:
- );
When we create this table and run the statements shown in the mysql client, the result is as follows:
- Query OK, 1 row affected (0.04 sec)
- Query OK, 2 rows affected (0.04 sec)
- +----+------+---------------------+
- +----+------+---------------------+
- | 1 | New | 2014-08-20 18:47:42 |
- +----+------+---------------------+
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):
- );
When we run on test2
the same two
REPLACE
statements as we did on the original
test
table, we obtain a different result:
- Query OK, 1 row affected (0.05 sec)
- Query OK, 1 row affected (0.06 sec)
- +----+------+---------------------+
- +----+------+---------------------+
- | 1 | Old | 2014-08-20 18:47:00 |
- | 1 | New | 2014-08-20 18:47:42 |
- +----+------+---------------------+
This is due to the fact that, when run on
test2
, both the id
and
ts
column values must match those of an
existing row for the row to be replaced; otherwise, a row is
inserted.
A REPLACE
statement affecting a partitioned
table using a storage engine such as
MyISAM
that employs table-level locks
locks only those partitions containing rows that match the
REPLACE
statement WHERE
clause, as long as none of the table partitioning columns are
updated; otherwise the entire table is locked. (For storage
engines such as InnoDB
that employ
row-level locking, no locking of partitions takes place.) For more
information, see
Partitioning and Locking.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-replace.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.