MySQLの方言 — ON DUPLICATE KEY UPDATE・GROUP_CONCAT・ウィンドウ関数

SQLには標準仕様がありますが、データベース製品ごとに独自の構文や関数があります。MySQL向けに書いたSQLをPostgreSQLやSQLiteへ移すと、そのままでは動かない部分です。

SELECT、JOIN、GROUP BY の基本は SQLとデータベースの基礎 で学んだ内容がそのまま通用します。この章では、実務でよく使うMySQLならではの書き方だけに絞ります。

方言は便利だが移植性と交換する

MySQL固有構文を避ければ、別のDBMSへ移行しやすくなります。一方、複数のSQLを1文へまとめたり、集計結果を読みやすく返したりできるため、無理に標準SQLだけへ寄せる必要もありません。

この章では次の3つを扱います。

ON DUPLICATE KEY UPDATEでUPSERTする

UPSERTは「存在しなければINSERT、存在すればUPDATE」という操作です。MySQLでは ON DUPLICATE KEY UPDATE を使います。

CREATE TABLE product_stocks (
  sku VARCHAR(64) PRIMARY KEY,
  stock INT UNSIGNED NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP
);

新しい値へ行エイリアスを付け、重複時の更新式から参照します。

INSERT INTO product_stocks (sku, stock)
VALUES ('TS-001', 5) AS new
ON DUPLICATE KEY UPDATE
  stock = new.stock;

構文： INSERT ... VALUES (...) AS 行エイリアス ON DUPLICATE KEY UPDATE 列 = 式 [, ...]

結果： 重複がなければ新規行を挿入し、重複すれば該当行を更新する

在庫を置き換えるのではなく、加算することもできます。

INSERT INTO product_stocks (sku, stock)
VALUES ('TS-001', 5) AS new
ON DUPLICATE KEY UPDATE
  stock = product_stocks.stock + new.stock;

学習者

先にSELECTで存在確認して、INSERTかUPDATEを選ぶのと何が違うんですか？

SELECTとINSERTの間に別接続が同じキーを挿入する競合を避けられます。UNIQUE制約の判定と更新をMySQL側の1文へ任せられるのが利点です。

VALUES()を使う古い書き方

古い記事では次の構文を見かけます。

-- 新規コードでは使わない
ON DUPLICATE KEY UPDATE stock = VALUES(stock)

更新式の VALUES() はMySQL 8.0.20から非推奨です。MySQL 8.4では、先ほどの VALUES (...) AS new という行エイリアスを使います。

INSERT IGNORE・REPLACEとの違い

REPLACE は名前からUPDATEに見えますが、DELETE＋INSERTです。指定していない列が既定値へ戻る、外部キーやトリガーへ影響する、AUTO_INCREMENT が進むといった違いがあります。既存行を更新したいならUPSERTを使います。

GROUP_CONCATで複数行を1つにまとめる

カテゴリごとのタグ名を1行に並べたいとします。

SELECT
  category_id,
  GROUP_CONCAT(tag_name ORDER BY tag_name SEPARATOR ', ') AS tags
FROM product_tags
GROUP BY category_id;

category_id | tags
------------+-------------------------
1           | sale, summer, washable
2           | gift, new

構文： GROUP_CONCAT([DISTINCT] 式 [, ...] [ORDER BY ...] [SEPARATOR 文字列])

戻り値： グループ内のNULLでない値を連結した文字列。対象がすべてNULLなら NULL

SELECT
  category_id,
  GROUP_CONCAT(DISTINCT tag_name ORDER BY tag_name SEPARATOR ' / ') AS tags
FROM product_tags
GROUP BY category_id;

現在値とセッション単位の変更は次の通りです。

SELECT @@group_concat_max_len;
SET SESSION group_concat_max_len = 16384;

設定を大きくすれば、結果セットとメモリの使用量も増えます。大量データをアプリで扱うなら、1文字列に潰さず行のまま返す方が扱いやすい場合があります。

ウィンドウ関数は行を畳まずに集計する

通常の GROUP BY は、複数行をグループごとの1行へ畳みます。ウィンドウ関数は元の行を残したまま、順位や累計などの計算結果を追加します。

SELECT
  customer_id,
  ordered_at,
  total,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at DESC
  ) AS order_no
FROM orders;

構文： ウィンドウ関数(...) OVER ([PARTITION BY ...] [ORDER BY ...] [フレーム])

戻り値： 元の各行を維持したまま、ウィンドウ内で計算した値を各行へ返す

ROW_NUMBER・RANK・DENSE_RANK

順位付けには3種類あります。

構文： ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

引数： なし

戻り値： パーティション内で1から始まる行番号。ORDER BY がなければ採番順は不定

顧客ごとの最新注文だけを取り出すには、CTEまたはサブクエリで一度番号を付けます。

WITH ranked_orders AS (
  SELECT
    id,
    customer_id,
    ordered_at,
    total,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY ordered_at DESC, id DESC
    ) AS rn
  FROM orders
)
SELECT *
FROM ranked_orders
WHERE rn = 1;

ordered_at が同じ行でも結果を安定させるため、最後に一意な id を並び順へ加えています。

累計を計算する

集約関数も OVER を付けるとウィンドウ関数になります。

SELECT
  customer_id,
  ordered_at,
  total,
  SUM(total) OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW は、パーティションの先頭から現在行までを集計対象にする指定です。

先生

GROUP BYは行をまとめる。ウィンドウ関数は行を残したまま横に計算結果を足す。この違いから選ぶと分かりやすいよ。

よくあるハマりどころ

ON DUPLICATE KEY UPDATEを制約なしで使う

競合を判断するのはPRIMARY KEYまたはUNIQUEキーです。業務上同じとみなす列に制約がなければ、毎回新しい行がINSERTされます。

更新式でVALUES()を使い続ける

MySQL 8.4でも動く場合はありますが非推奨警告の対象です。新規コードは行エイリアスへ置き換えます。

GROUP_CONCATの順序を暗黙に期待する

関数内の ORDER BY を省略すると、連結順は保証されません。表示順に意味があるなら明示します。

GROUP_CONCATの切り詰めに気づかない

結果が返るため、途中で切れていてもアプリ側が成功扱いすることがあります。想定件数が多い用途では group_concat_max_len と警告を確認します。

ウィンドウ関数の別名を同じSELECTのWHEREで使う

ウィンドウ関数は WHERE より後に評価されるため、同じ階層の WHERE rn = 1 では絞れません。CTEやサブクエリで計算した外側から絞ります。

ちゃんと使うためのポイント

• ON DUPLICATE KEY UPDATE はUNIQUE制約を使ってINSERTとUPDATEを1文にする
• 挿入値の参照には VALUES() ではなく行エイリアスを使う
• REPLACE はUPDATEではなくDELETE＋INSERTなので区別する
• GROUP_CONCAT() は順序と区切り文字を関数内で指定できる
• group_concat_max_len の既定は1024で、超えた結果は切り詰められる
• ウィンドウ関数は元の行を残したまま順位・累計を計算する
• 順位付けのORDER BYは同値でも決まるようにする
• ウィンドウ関数の結果で絞るときはCTEかサブクエリを使う

SQLが決まったら、アプリケーションから安全に実行する接続管理が必要です。Node.jsからMySQLに接続するでは、mysql2のコネクションプール、プレースホルダー、トランザクション中の接続固定を扱います。

参考リンク

• MySQL 8.4 Reference Manual — INSERT ON DUPLICATE KEY UPDATE
• MySQL 8.4 Reference Manual — Aggregate Function Descriptions
• MySQL 8.4 Reference Manual — Window Function Concepts and Syntax
• MySQL 8.4 Reference Manual — Window Function Descriptions