MySQLでCASE式を使うUPDATE文を使うと複合ユニーク制約を回避できるのか検証

概要

SQLでは、以下のようにCASE式を使うと1回のSQL実行で複数のUPDATEを実行することができる。

UPDATE article_image_url SET display_order = (
    CASE id
        WHEN 1 THEN 2
        WHEN 2 THEN 3
        WHEN 3 THEN 1
    END
) WHERE id in (1, 2, 3);

MySQLでこれを使うと複合ユニーク制約を回避できるのかを検証した。

MySQLのバージョン

5.7

検証したかった理由

Django ORMのbulk_updateメソッドは内部でCASE式を使うUPDATE文を生成するので、挙動を知りたかった。
(ググったら、挙動はSQL製品によるがMySQLはエラーになるという記事がいくつか見つかったが、自分でも検証してみたかった。)

bulk_updateが内部で発行するSQLに関しては、以下の記事を参照させて頂いた。
tokibito.hatenablog.com

結論

回避できない。

検証過程

無機質に検証するだけでもよいが、せっかくなので、実際にありそうなケースとして、ブログに表示する画像のURLを保存するテーブルを例とする。

「画像に紐づく記事のID」と「表示順」で複合ユニーク制約が存在するとする。

CREATE TABLE文は以下

CREATE TABLE article_image_url (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    article_id INTEGER NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    display_order INTEGER NOT NULL,
    UNIQUE unique_article_id_display_order (article_id, display_order)
) ENGINE = InnoDB DEFAULT CHARACTER 
SET = utf8 DEFAULT COLLATE = utf8_general_ci;

初期データを投入する。

INSERT INTO article_image_url VALUES (null, 1, "http://example.com/images/1", 1);
INSERT INTO article_image_url VALUES (null, 1, "http://example.com/images/2", 2);
INSERT INTO article_image_url VALUES (null, 1, "http://example.com/images/3", 3);

この時点でのテーブルの中身は以下

mysql> select * from article_image_url;
+----+------------+-----------------------------+---------------+
| id | article_id | image_url                   | display_order |
+----+------------+-----------------------------+---------------+
|  1 |          1 | http://example.com/images/1 |             1 |
|  2 |          1 | http://example.com/images/2 |             2 |
|  3 |          1 | http://example.com/images/3 |             3 |
+----+------------+-----------------------------+---------------+

以下のCASE式を使うUPDATE文を実行する

UPDATE article_image_url SET display_order = (
    CASE id
        WHEN 1 THEN 2
        WHEN 2 THEN 3
        WHEN 3 THEN 1
    END
) WHERE id in (1, 2, 3);

結果

mysql> UPDATE article_image_url SET display_order = (
    ->     CASE id
    ->         WHEN 1 THEN 2
    ->         WHEN 2 THEN 3
    ->         WHEN 3 THEN 1
    ->     END
    -> ) WHERE id in (1, 2, 3);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'unique_article_id_display_order'
mysql>

残念ながらDuplicate entryが発生。

TODO

他のSQL製品でも試してみたい。