【PHP】PDOで「ON DUPLICATE KEY UPDATE」の使い方

MySQLで、

「重複しているデータが既にある場合はUPDATE」
「そうでない場合はINSERT」

の分岐を1つのクエリで使用できる「ON DUPLICATE KEY UPDATE」の使い方をまとめました。

INSERT文にUPDATE文を付け足すような書き方になります。

参考 PDOでINSERTの使い方【PHP】

参考 PDOでUPDATEの使い方【PHP】

サンプルのテーブルとして、「アクセスした人の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, '2020-08-05'),
        ('444.555.666.777', 1, '2020-08-05')
        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 2020-08-05
3 111.333.555.666 1 2020-02-03
4 444.555.666.777 1 2020-08-05

既に存在する「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した結果、帰ってきた数字はこちら。

3

挿入したのは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」の使い方、でした。

コメント

タイトルとURLをコピーしました