【PHP】PDOで「ON DUPLICATE KEY UPDATE」の使い方
MySQLで、
「重複しているデータが既にある場合はUPDATE」
「そうでない場合はINSERT」
の分岐を1つのクエリで使用できる「ON DUPLICATE KEY UPDATE」の使い方をまとめました。
INSERT文にUPDATE文を付け足すような書き方になります。
サンプルのテーブルとして、「アクセスした人のipをカウントする」という目的のテーブルをMySQLに作っておきます。
| table名(ip_log) | |||
|---|---|---|---|
|
id int(11) AUTO INCLUMENT |
ip_str (VARCHAR(16)) UNIQUE |
ip_count (int(11)) |
ip_last_access (CHAR(10)) |
| 1 | 111.222.333.444 | 1 | 2020-01-15 |
| 2 | 55.66.77.88 | 1 | 2020-01-30 |
| 3 | 111.333.555.666 | 1 | 2020-02-03 |
構成は、idが自動連番、ip_strがUNIQUE制約を持った文字型のカラムです。
ON DUPLICATE KEY UPDATEの重複の判定は、プライマリキーまたはユニーク制約で行っているので、重複させないカラムにインデックスを設定してください。
PDOでON DUPLICATE KEY UPDATEの使い方
先ほどのテーブルを元に、ユーザーからアクセスがあった時にそのユーザーのipを保存していきます。
そして、
「まだテーブルにないipだったら挿入する」
「既に保存済みのipだったらアクセス回数、最終アクセス日を更新する」
という処理をON DUPLICATE KEY UPDATEで行いましょう。
$sql = "INSERT INTO ip_log
(ip_str, ip_count, ip_last_access)
VALUES
('55.66.77.88', 1, '2024-04-20'),
('444.555.666.777', 1, '2024-04-20')
ON DUPLICATE KEY UPDATE
ip_count = ip_count + 1,
ip_last_access = VALUES(ip_last_access)";
$pdo -> query($sql);
ON DUPLICATE KEY UPDATEは、INSERT文の末尾に付け足す必要があります。
ON DUPLICATE KEY UPDATE ip_count = ip_count + 1, ip_last_access = VALUES(ip_last_access)
この処理は、挿入しようとした「ip_str」が既に存在している場合、INSERTではなくUPDATEになる、という意味です。
アクセス数を集計する「ip_count」は+1を行い、最終アクセス日を記録する「ip_last_access」はINSERTしようとした値に変更します。
今回の場合、既にデータが存在する「55.66.77.88」と、まだデータが存在しない「444.555.666.777」をINSERTしましたので、結果を見てみましょう。
赤字が更新、青字が新規挿入です。
| id | ip_str | ip_count | ip_last_access |
|---|---|---|---|
| 1 | 111.222.333.444 | 1 | 2020-01-15 |
| 2 | 55.66.77.88 | 2 | 2024-04-20 |
| 3 | 111.333.555.666 | 1 | 2020-02-03 |
| 4 | 444.555.666.777 | 1 | 2024-04-20 |
既に存在する「55.66.77.88」は、ip_countが+1され、最終アクセス日が更新されていることが分かりますね。
対して、新規のデータだった「444.555.666.777」は、行として挿入されていることが分かります。
PDOのrowCount()をON DUPLICATE KEY UPDATEで行った場合
PDOには、クエリの処理件数を取得できるrowCount()というメソッドがあります。
挿入と更新が同時に行われるON DUPLICATE KEY UPDATEではどうなるかを検証してみました。
$sql = "INSERT INTO ip_log
(ip_str, ip_count, ip_last_access)
VALUES
('55.66.77.88', 1, '2020-02-05'),
('444.555.666.777', 1, '2020-02-05')
ON DUPLICATE KEY UPDATE
ip_count = ip_count + 1,
ip_last_access = VALUES(ip_last_access)";
$sth = $pdo -> query($sql);
$count = $sth -> rowCount();
echo $count;
echoした結果、帰ってきた数字はこちら。
挿入したのは1行、更新したのも1行ですが、帰ってきた数字は「3」でした。
試したところ、ON DUPLICATE KEY UPDATEの「更新」は、INSERTも同時に行っているという判断なのか、1行に対して2件処理したという扱いになっています。(INSERTはそのまま)
なので、「INSERT(1)」+「ON DUPLICATE KEY UPDATE(2)」で3が返ってきた、ということですね。
ON DUPLICATE KEY UPDATEでrowCountを使用する場合は注意してください。
ON DUPLICATE KEY UPDATEは、一度のクエリでINSERTとUPDATEを使い分けることが出来る便利なクエリなので、必要な場面に応じて使いこなしていきましょう。
以上、PDOで「ON DUPLICATE KEY UPDATE」の使い方、でした。