はじめに
Oracle Database にJDBCから以下のSQLを実行した結果が0件になりました。
もちろんCOL1には'ア'という文字データがINSERTされています。
String sql = "SELECT count(*) FROM HOGE WHERE COL1 = ?";
//略
statement.setString(1, "ア");
//略
COL1のデータ型はCHAR(2)、DBのNLS_CHARACTERSETはAL32UTF8です。
同じDBに対して、今度はSQL*Plusで以下のSQLを実行してみました。
SELECT count(*) FROM HOGE WHERE COL1 = 'ア';
こちらは0件ではなく、正しい結果が返ってきます。
次に、COL1のデータ型をVARCHAR2(2)にしてsample.javaを実行してみました。
今度はsample.sqlと同様、正しい結果が返ってきました。
何がダメなのでしょうか？
本記事では、この原因を解明するのに必要なOracle Databaseにおける文字列データの扱いについてまとめてみます。
CHAR型とVARCHAR2型
Oracle Databaseにおいて、文字列型を格納するデータ型で最もよく使われるのが、CHAR型とVARCHAR2型です。
まずは、この2つのデータ型の違いを意識する必要があります。
一言で言うと、CHAR型は固定長、VARCHAR2型は可変長です。
例えば、CHAR(5)というデータ型に、'123'という文字列を格納するとします。
'123'という文字列は3バイトですが、指定バイト数は5です。
この場合、CHAR型は半角スペースで空白を埋めます。
一方、VARCHAR2(5)というデータ型に'123'という文字列を格納すると、こちらはデータのバイト数で格納します。
勘の良い方はお気づきになられたと思いますが、CHAR型で0件になり、VARCHAR2型では正しい結果が返ってくるのはこの違いが原因です。
CHAR型は指定バイトに満たない場合、半角スペースで残りを埋めるため、実際に格納されているデータとしては'ア△'になります。
※△は半角スペース
ですので、sample.javaでは以下のように検索すれば、正しく結果が返ってきます。
String sql = "SELECT count(*) FROM HOGE WHERE COL1 = ?";
//略
statement.setString(1, "ア△");
//略
ここで、文字コードに知見のある方は違和感を覚えるかもしれません。
「CHAR(2)に'ア'って格納できるの？'ア'ってそもそもUTF8だと3バイト扱いでは？」
はい、その通りです。実はこのOracle Databaseでは事前にある設定をしていました。
NLS_LENGTH_SEMANTICS
最初に言え、と思われたかもしれませんが、実はこのDBではNLS_LENGTH_SEMANTICSというパラメータを変更していました。
NLS_LENGTH_SEMANTICSは、データベース・オブジェクトのPL/SQL変数に使用するデフォルトの長さセマンティクスを指定するパラメータです。
このパラメータは以下のSQLで確認できます。
SELECT * FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_LENGTH_SEMANTICS';
PARAMETER VALUE CON_ID
_______________________ ________ _________
NLS_LENGTH_SEMANTICS BYTE 11
デフォルトでは、このようにVALUE=BYTEとなっているはずです。
このNLS_LENGTH_SEMANTICSは、BYTEもしくはCHARという設定が可能です。
デフォルトであるBYTEの場合、CHAR(2)というデータ型には2バイトまで格納できます。
一方、CHARの場合、CHAR(2)というデータ型には2文字まで格納できることになります。
これは'a'のような1文字＝1バイトであるシングルバイト文字であれば違いはないですが、今回の全角カタカナのようなマルチバイト文字を格納する場合には意識する必要があります。
なお、NLS_LENGTH_SEMANTICSはALTER SYSTEM文、またはALTER SESSION文で変更可能です。
ALTER SYSTEM SET NLS_LENGTH_SEMANTICS = 'CHAR';
このパラメータを変更していたため、CHAR(2)にも本来であれば3バイト使うため格納できない'ア'を格納できたわけです。
実際には1文字分余っているので'ア△'で格納されています。
さて、それではなぜSQL*Plusでは正しく検索できたのでしょうか？
答えはOracle Databaseの文字列比較セマンティクスにあります。
空白埋め比較セマンティクスと非空白埋め比較セマンティクス
Oracle Databaseでは、文字列の比較を行う際に2つの比較セマンティクスがあります。
空白埋め比較セマンティクスでは、2つの値の長さが異なる場合、まず短い方の値の最後に空白を追加して、2つの値が同じ長さになるようにします。
次に1文字ずつ比較していく、2つの値の後続空白数のみが異なる場合、その2つの値は等しいとみなされます。
つまり、'a△' = 'a' となります。
比較する両方の値が、CHARデータ型、NCHARデータ型、テキスト・リテラルのいずれかの式の場合、またはUSERファンクションの戻り値の場合のみ空白埋め比較セマンティクスを使用します。
一方、非空白埋め比較セマンティクスでは、2つの値を、最初に異なる文字まで1文字ずつ比較します。最初に異なる文字の位置で、大きい方の文字を持つ値の方が大きいとみなされます。長さが異なる2つの値を短い方の値の最後まで比較して、すべて同じ文字だった場合、長い方の値が大きいとみなされます。
つまり、'a△' ＞ 'a' となります。
比較する片方または両方の値がVARCHAR2データ型またはNVARCHAR2データ型の場合、非空白埋め比較セマンティクスを使用します。
これを踏まえて、sample.javaとsample.sqlの違いを見てみます。
sample.sqlのSQL文は、文字列埋め込みのSQL文です。このとき、'ア'はテキスト・リテラルとして扱われます。
COL1はCHAR型であるため、空白埋め比較セマンティクスを使用する条件を満たします。
このため、実際は'ア△'で格納されていても、COL1 = 'ア' という条件で検索ができるというわけです。
一方、sample.javaのSQL文は、Stringでパラメータとして値を渡しています。この場合、'ア'はVARCHAR2として扱われます。
COL1はCHAR型ですが、'ア'の方がVARCHAR2であるため、非空白埋め比較セマンティクスを使用します。
COL1は'ア△'で格納されているため、'ア△' ＞ 'ア' となり、検索されないというわけです。
冒頭ではJDBCとSQL*Plusの違いが原因のように書いていますが、実際はパラメータとして扱われるかテキスト・リテラルとして扱われるかという違いによるものです。
SQL*Plusでも、以下のようにVARCHAR2のバインド変数を使えば0件になります。
variable str VARCHAR2(2);
execute :str := 'ア';
SELECT count(*) FROM HOGE WHERE COL1 = :str;
その他の例
ここまで文字列の扱いの違いについて見てきましたが、最後に2つ例をご紹介します。
以下の条件では結果が0件になるでしょうか？
条件：文字コードJA16EUC / NLS_LENGTH_SEMANTICSはBYTE / データ型CHAR(2) / Stringで値を渡す
- COL1 = 'ア'
答え：正しく結果が返る
文字コードがEUCなので、'ア'は2バイト分です。NLS_LENGTH_SEMANTICSがBYTEなので、CHAR(2)の2バイト分使いきることになります。
ちょうど'ア'の形で格納されるため、'ア'で検索可能です。
- COL1 = 'a'
答え：0件になる
'a'はシングルバイトなので、CHAR(2)では1バイト分余ります。空白で埋めるため、'a△'の形で格納されます。よって結果は0件です。