🐳

MySQLのVALUE(S)とは何者なのか

2023/09/08に公開

どうも、MySQL初学者のgawettoです。
MySQLの勉強をしていてVALUE(S)の使い方が分からなくなり、リファレンスマニュアルを読んだらすっきりしたのでここにまとめます。

突然ですが、問題です。下記のSQL文のうちMySQL8.0.30でシンタックスエラーとなるものをすべて選んでください。考える観点としては"VALUEにSを付けるかどうか","括弧はつけるかどうか","ROWを付けるかどうか","REPLACEかINSERTか"です

1.INSERT INTO test (id,score) VALUE (1,1);
2.INSERT INTO test (id,score) VALUE (2,2),(3,3);
3.INSERT INTO test (id,score) VALUE ROW(4,4);
4.INSERT INTO test (id,score) VALUES (5,5);
5.INSERT INTO test (id,score) (VALUES (6,6));
6.INSERT INTO test (id,score) VALUES ROW(7,7), ROW(8,8);
7.INSERT INTO test (id,score) (VALUES ROW(9,9), ROW(10,10));
8.REPLACE INTO test (id,score) VALUES (1,9);
9.REPLACE INTO test (id,score) VALUES ROW(2,10);
10.INSERT INTO test (id,score) VALUES (3,11) ON DUPLICATE KEY UPDATE score=VALUES (score);
11.INSERT INTO test (id,score) VALUES (4,12) ON DUPLICATE KEY UPDATE score=VALUES ROW(score);
12.INSERT INTO test (id,score) VALUES (5,13) AS new ON DUPLICATE KEY UPDATE score = new.score;
13.INSERT INTO test (id,score) VALUES ROW(6,14) AS new ON DUPLICATE KEY UPDATE score = new.score;

正解は

3と5と11と13がシンタックスエラーになります

何故そうなるのか説明します。

3種類のVALUES

MySQLではVALUESには下記3つの全く異なる使い方があります

INSERTステートメント及びREPLACEステートメントで使われるVALUE(S)キーワード参照
サブクエリとしても使えるVALUESステートメント参照
ON DUPLICATE KEY UPDATE構文で使われるVALUES関数参照

VALUE(S)キーワード

INSERTステートメント及びREPLACEステートメントで使われるVALUE(S)キーワードでは下記の構文で利用されます

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ... }
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { SELECT ... 
      | TABLE table_name 
      | VALUES row_constructor_list
    }
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

assignment:
    col_name = 
          value
        | [row_alias.]col_name
        | [tbl_name.]col_name
        | [row_alias.]col_alias

assignment_list:
    assignment [, assignment] ...

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ...
      |
      VALUES row_constructor_list
    }

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

VALUESのあとにvalue_listが続くパターンとrow__constructor_listが続くパターンの両方があるのでROWはあってもなくても良いということが分かります。
ただ、よく見てみるとrow_constructor_listが続くパターンではVALUESが使えてもVALUEが使えません。つまりROWを使うときはVALUESにしないといけません。これはリストが単数か複数かは関係ありません。

VALUESステートメント

VALUESはステートメントとしても存在します。しかもサブクエリとして利用できます。

VALUES row_constructor_list [ORDER BY column_designator] [LIMIT BY number]

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

value_list:
    value[, value][, ...]

column_designator:
    column_index

VALUESステートメントでは必ずrow_constructor_listが続くのでROWが必須であることが分かります。VALUESステートメントをINSERTステートメント等のサブクエリとして利用するには括弧でくくる必要があります。また、VALUESステートメントは存在しますがVALUEステートメントは存在しません。

VALUES関数

VALUESは関数としても存在します。この関数はINSERT ... ON DUPLICATE KEY UPDATE構文のUPDATE句の中で利用し、INSERT部分からカラム値を参照できます。しかしこの関数は非推奨で、エイリアスを利用する方法が推奨されています。関数なのでVALUES()という形になりROWはいりません。また、VALUE関数は存在しません。

問題の解説

1.INSERT INTO test (id,score) VALUE (1,1);

このVALUEはINSERTステートメントのVALUEキーワードです。特に問題はありません。

2.INSERT INTO test (id,score) VALUE (2,2),(3,3);

これもINSERTステートメントのVALUEキーワードです。VALUEに続くのが複数か単数かは関係ないのでこれも問題ありません。

3.INSERT INTO test (id,score) VALUE ROW(4,4);

これはINSERTステートメントのVALUEキーワードっぽいですが、VALUEキーワードの後にrow_constructor_listは続かないのでエラーとなります。

4.INSERT INTO test (id,score) VALUES (5,5);

これはINSERTステートメントのVALUESキーワードです。VALUESだからといって複数である必要はないので問題ありません。

5.INSERT INTO test (id,score) (VALUES (6,6));

これは4に括弧がついただけですが、括弧を付けるとサブクエリとして扱われます。つまりVALUESステートメントになるのですが、VALUESステートメントはrow_constructor_listが続く必要があるのでROWが必要です。よってエラーになります。

6.INSERT INTO test (id,score) VALUES ROW(7,7), ROW(8,8);

これはINSERTステートメントのVALUESキーワードです。特に問題ありません。ROWがついてますがあってもなくても構いません。

7.INSERT INTO test (id,score) (VALUES ROW(9,9), ROW(10,10));

これは6に括弧がついただけですが、5と同様にVALUESステートメントになっています。こちらはROWがついているので問題ありません。これを見ると6のVALUESも実は内部的にはVALUESステートメントなのでは？という気もしますがリファレンスマニュアルを読んだだけでは分かりませんでした。

8.REPLACE INTO test (id,score) VALUES (1,9);

これはREPLACEのVALUESキーワードです。REPLACEもINSERTと同じようにVALUESを使えるので問題ありません。複数か単数かは関係ありません。

9.REPLACE INTO test (id,score) VALUES ROW(2,10);

こちらもREPLACEになっただけなので問題ありません。複数か単数かは関係ありません。

10.INSERT INTO test (id,score) VALUES (3,11) ON DUPLICATE KEY UPDATE score=VALUES (score);

前半のVALUESはINSERTステートメントのVALUESキーワードで、後半のVALUESはVALUES関数です。VALUES関数の一般的な使い方で特に問題はありません。ちなみに関数なのでVALUES(score)とスペースはなくてもよいです。

11.INSERT INTO test (id,score) VALUES (4,12) ON DUPLICATE KEY UPDATE score=VALUES ROW(score);

こちらも前半はINSERTステートメントのVALUESキーワードで、後半はVALUES関数っぽいですが、VALUES関数はVALUES()の形になるのでROWが入っているとエラーになります。

12.INSERT INTO test (id,score) VALUES (5,13) AS new ON DUPLICATE KEY UPDATE score = new.score;

これはON DUPLICATE KEY UPDATEでVALUES関数を使わずにエイリアスを使ったパターンです。特に問題はありません。

13.INSERT INTO test (id,score) VALUES ROW(6,14) AS new ON DUPLICATE KEY UPDATE score = new.score;

これも12と同じでエイリアスを使ったパターンかと思いきや、構文をよく見てみるとrow_constructor_listが続くパターンでは何故かASを利用することができません。なのでROWを入れるとエラーになります。

まとめ

誰かが書いたSQL文を読むときやMySQLのバージョンに縛りがある場合はこの限りではないが最新のMySQLでSQL文を自分で書くときは下記を覚えておけば大体なんとかなりそう

VALUEは必要ない、全部VALUESでいい
VALUESを括弧で括るとサブクエリになりVALUESステートメントとなる
VALUESステートメントの時はROWが必要
VALUESキーワードの時はROWは必要ない
ON DUPLICATE KEY UPDATEでVALUES関数は使わずにエイリアスを使え