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「パーティショニングとロック」を参照してください。