13.2.8 REPLACE 構文

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

または:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    SET col_name={expr | DEFAULT}, ...

または:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]  
    [(col_name,...)]
    SELECT ...

REPLACE は、INSERT とまったく同じように機能します。ただし、テーブル内の古い行に、PRIMARY KEY または UNIQUE インデックスに関して新しい行と同じ値が含まれている場合、その古い行は新しい行が挿入される前に削除されます。セクション13.2.5「INSERT 構文」を参照してください。

REPLACE は、SQL 標準への MySQL 拡張です。これは挿入を行うか、または削除と挿入を行います。標準 SQL への別の MySQL 拡張 (挿入または更新を行います) については、セクション13.2.5.3「INSERT ... ON DUPLICATE KEY UPDATE 構文」を参照してください。

テーブルに PRIMARY KEY または UNIQUE インデックスが存在しないかぎり、REPLACE ステートメントを使用しても何も意味がありません。新しい行が別の行を複製したかどうかを判定するために使用されるインデックスが存在しないため、それは INSERT と同等になります。

すべてのカラムの値が REPLACE ステートメントで指定されている値から取得されます。カラムがない場合は、INSERT での処理と同様に、そのカラムはそのデフォルト値に設定されます。現在の行の値を参照し、それを新しい行で使用することはできません。SET col_name = col_name + 1 などの代入を使用した場合、右側にあるカラム名への参照は DEFAULT(col_name) として処理されるため、この代入は SET col_name = DEFAULT(col_name) + 1 と同等です。

REPLACE を使用するには、このテーブルに対する INSERT 権限と DELETE 権限の両方が必要です。

MySQL 5.6.2 から、REPLACE は、パーティション、サブパーティション、またはその両方の名前のカンマ区切りリストを含む PARTITION オプションを使用した明示的なパーティション選択をサポートしています。INSERT と同様に、これらのいずれかのパーティションまたはサブパーティションに新しい行を挿入できない場合、REPLACE ステートメントは Found a row not matching the given partition set.エラーで失敗します。詳細は、セクション19.5「パーティション選択」を参照してください。

REPLACE は、影響を受けた行数を示す数を返します。これは、削除された行と挿入された行の合計です。この数が単一行の REPLACE に対して 1 である場合は、行が挿入され、削除された行はありませんでした。この数が 1 より大きい場合は、新しい行が挿入される前に 1 つ以上の古い行が削除されました。テーブルに複数の一意のインデックスが存在するときに、新しい行が異なる一意のインデックス内の別の古い行の値を複製した場合は、単一行が複数の古い行を置き換えることがあります。

影響を受けた行数により、REPLACE が行を追加しただけか、または行の置き換えも行なったかを判定することが容易になります。その数が 1 (追加した) か、またはそれより大きい (置き換えた) かをチェックします。

C API を使用している場合は、mysql_affected_rows() 関数を使用して、影響を受けた行数を取得できます。

現在、テーブルへの置き換えを行い、さらにサブクエリーで同じテーブルから選択することはできません。

MySQL は、REPLACE (および LOAD DATA ... REPLACE) に次のアルゴリズムを使用します。

  1. テーブルへの新しい行の挿入を試みます

  2. 主キーまたは一意のインデックスに関する重複キーエラーが発生したために挿入が失敗している間、次のことを行います。

    1. 重複キー値を含む競合している行をテーブルから削除します

    2. テーブルへの新しい行の挿入を再試行します

重複キーエラーが発生した場合、ストレージエンジンが削除と挿入ではなく、更新として REPLACE を実行する可能性がありますが、そのセマンティクスは同じです。ストレージエンジンが Handler_xxx ステータス変数を増分する方法が異なる可能性がある以外、ユーザーに見える影響はありません。

REPLACE ... SELECT ステートメントの結果は SELECT からの行の順序に依存し、またこの順序を常に保証することはできないため、ロギング時に、これらのステートメントがマスターとスレーブで異なる可能性があります。このため、MySQL 5.6.4 以降では、REPLACE ... SELECT ステートメントには、ステートメントベースのレプリケーションには安全でないというフラグが付けられます。この変更により、このようなステートメントは、STATEMENT バイナリロギングモードを使用しているときはログ内に警告を生成し、MIXED モードを使用しているときは行ベース形式を使用してログに記録されます。セクション17.1.2.1「ステートメントベースおよび行ベースレプリケーションのメリットとデメリット」も参照してください。

パーティション化されていない既存のテーブルをパーティション化に対応するように変更しているときや、すでにパーティション化されたテーブルのパーティション化を変更しているときに、そのテーブルの主キーの変更を検討する可能性があります (セクション19.6.1「パーティショニングキー、主キー、および一意キー」を参照してください)。これを行うと、パーティション化されていないテーブルの主キーを変更した場合と同様に、REPLACE ステートメントの結果が影響を受ける可能性があります。次の CREATE TABLE ステートメントによって作成されたテーブルを考えてみます。

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)
);

このテーブルを作成し、mysql クライアントに示されているステートメントを実行すると、結果は次のようになります。

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)

ここで、次に示すように (強調表示されたテキスト) 主キーが 2 つのカラムになっている点を除き、最初のテーブルとほぼ同一の 2 番目のテーブルを作成します。

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)
);

元の test テーブルに対して実行したのと同じ 2 つの REPLACE ステートメントを test2 に対して実行すると、異なる結果が得られます。

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-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)

これは、test2 に対して実行した場合は id カラムと ts カラムの両方の値が、置き換えられる行に対する既存の行の値に一致している必要があり、そうでないと行が挿入されるためです。

MySQL 5.6.6 より前は、テーブルレベルのロックを採用した MyISAM などのストレージエンジンを使用しているパーティション化されたテーブルに影響を与える REPLACE によって、そのテーブルのすべてのパーティションがロックされました。これは、REPLACE ... PARTITION ステートメントにも当てはまりました。(これは、行レベルロックを採用した InnoDB などのストレージエンジンでは発生しておらず、現在も発生しません。)MySQL 5.6.6 以降では、MySQL はパーティションロックプルーニングを使用します。これにより、そのテーブルのどのパーティション化カラムも更新されないかぎり、REPLACE ステートメントの WHERE 句に一致する行を含むパーティションだけが実際にロックされるようになります。そうでなければ、テーブル全体がロックされます。詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。


User Comments
  Posted by no forms on October 28, 2004
Be careful with InnoDB tables and REPLACE:
If you run a replace on existing keys on table T, and table F references T with a forgein key constraint ON DELETE CASCADE, then table T will be updated - but table F will be emptied due to the DELETE before INSERT.
e.g.:

CREATE TABLE T (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
);

CREATE TABLE F (
`foreign_id` int(10) unsigned NOT NULL,
CONSTRAINT `fkey` FOREIGN KEY (`foreign_id`) REFERENCES `T` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE
);

<insert numbers 1..1000 into T>
<insert numbers 1..1000 into F>

REPLACE INTO T SELECT * FROM T;
=> T is updated
=> F is truncated - not the desired effect.

It is best to avoid REPLACE when working with constraints.
  Posted by R Lenard on January 12, 2005
Be careful with REPLACE INTO with server side prepared statements and the 3.1.6 driver - it doesn't support them. The 3.0.x driver did :-(
  Posted by Eric Stevens on February 3, 2005
Performance considerations:

Please note that REPLACE INTO is a much slower performer than an UPDATE statement. Keep in mind that a REPLACE INTO requires a test on the keys, and if a matching unique key is found on any or all columns, a DELETE FROM is executed, then an INSERT is executed. There's a lot of management of rows involved in this, and if you're doing it frequently, you'll hurt your performance unless you simply cannot do with any other syntax.

The only time when I can see where you'd actually need a REPLACE INTO is when you have multiple unique constraints on a table, and need to drop any rows that would match any of the constraints. Then REPLACE INTO becomes more efficient from DELETE FROM... INSERT INTO...

If you're looking at a single unique column table (Primary Key), please use UPDATE, or INSERT. Also, check out INSERT ... ON DUPLIATE KEY UPDATE... as an alternative if you're willing to stick to MySQL 4.1+
  Posted by Rolf Martin-Hoster on May 8, 2006
INNODB mysql 5.0 does not support "DELAYED" but does support LOW_PRIORITY :

mysql> REPLACE DELAYED INTO `online_users` SET `session_id`='3580cc4e61117c0785372c426eddd11c', `user_id` = 'XXX', `page` = '/', `lastview` = NOW();
ERROR 1031 (HY000): Table storage engine for 'online_users' doesn't have this option

  Posted by Atif Ghaffar on September 23, 2007
PLEASE Note that the REPLACE does a DELETE operation.

We did not realize this and had the triggers that should be triggered on DELETE triggered.

After checking all the code, we just found a script that does a replace to refresh the values of some fields.

We should have had used "insert into ... on duplicate update" syntax instead.

  Posted by J Mike on May 4, 2009
If you are using REPLACE INTO... triggers are fired in this order (if delete of duplcate key is used):
- before insert
- before delete
- after delete
- after insert

  Posted by Pablo Fernandez on September 7, 2009
This can also be used to merge databases http://serverprotectors.com/blog/64-merging-mysql-databases.html

  Posted by Abidir Rokhman on February 13, 2011
i already move my blog to another domain and realize that all images on my post still pointing to old domain, so i need to find and replace all url on my post with my new domain. and i found this function useful to update just old domain to all my post.

i am using this query :

UPDATE wp_posts SET post_content = REPLACE(post_content, 'olddomain.com', 'newdomain.com');

full explanation here http://cakarayam.com/find-and-replace-mysql-how-to-find-and-replace-data-in-mysql/
  Posted by Meir Guttman on July 6, 2014
"no forms" tip also applies to a much simpler situation:

Simply having an Auto-Increment as a primary key will insert a new record with the same VALUES (...),(...); whenever the same "REPLACE INTO..." query is executed.

Records then are ADDED, not REPLACE(d)!
  Posted by Nathan Neulinger on April 18, 2015
Should note that this warning about inserts and nulls

"For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types."

also appears to apply to a single row "replace into" query, which can be very confusing to debug when it appears to not obey the table constraints and just turns nulls/missing columns into empty strings. This can particularly be a problem if you have a unique constraint on one of those columns.
Sign Up Login You must be logged in to post a comment.