💭

SQL におけるダブルクォートの罠

に公開
SQL
tech

カラム名を"Apple"で囲んだときに使用しているDBによっては違いがある。
テーブル定義としては、以下の通りとする。

CREATE TABLE fruit_master (
    "Apple" INT
);

取得用SQL

SELECT apple
FROM fruit_master

結論から述べると、各DBにおける挙動の違いは以下の通り。

DB ""あり ""なし
PostgreSQL 完全一致 (CS) 小文字に正規化される
Oracle 完全一致 (CS) 大文字に正規化される
SQL Server []や""で囲った場合は定義通り（基本はCIで区別なし） 大文字小文字は基本的に区別されない（CI）
MySQL 通常は識別子として扱い、 ``（バッククォート）を使用する。挙動は環境依存 大文字小文字の扱いはOSや設定依存
SQLite その名前として扱う かなり柔軟（実質ゆるい）

CS = Case Sensitive（大文字小文字を区別する）
CI = Case Insensitive（大文字小文字を区別しない）

実務上では、Oracleで構成されたDBをPostgreSQLに改修することはよくある。

Appleにダブルクォートを囲まずに、CREATE文を実行した場合は

CREATE TABLE fruit_master (
    Apple INT
);

違いとしては以下の通りになる。

DB テーブル名 カラム名
Oracle FRUIT_MASTER APPLE
PostgreSQL fruit_master apple

取得用SQL

SELECT apple
FROM fruit_master

結果は Oracle、PostgreSQLどちらでも問題なく実行されたのでOK。

これなら、良かったんだけど
"Apple"として、ダブルクォートで囲った場合。

CREATE TABLE fruit_master (
    "Apple" INT
);

このような状態になる。

DB テーブル名 カラム名
Oracle FRUIT_MASTER Apple
PostgreSQL fruit_master Apple

取得用SQLを実行すると

SELECT apple
FROM fruit_master

Oracleでの実行結果は、APPLEと解釈するため
カラム名のAppleと整合性が取れず、エラー。

PostgreSQLでの実行結果は、appleと解釈するため
カラム名のAppleと整合性が取れず、エラー。

DB定義を開発側で変更してもOKなら、DROPしたあとに
またテーブル作ればいいって思われがちだが
実務上、テーブル定義を開発側で変更することはアウトなケースも存在するので
対策としては、取得用SQLを変更するしかない。

--変更後 取得用SQL
SELECT "Apple"
FROM fruit_master

ソースコードに直でSQLが書いてあることもあるので
安直に、DB定義をするときに
テーブルやカラム名を""ダブルクォートで囲むのは正解なのか？
ちょっと検討して欲しい。

Discussion