SQL DBのNULLと文字列

今日は、SQL DBの文字型におけるNULLと空白文字（スペース）のお話しをします。

SQL DBにはNULLという物があります。NULLとは、「何も入っていないことを表す値」で、「ゼロバイトの文字列」とは異なります。

ところが、Oracleでゼロバイトの文字列をデータベースに格納すると、ちょっとおかしな現象が起きます。

SQL> create table nulltest ( id numeric(5), c1 varchar(10) );
表が作成されました。
SQL> insert into nulltest values ( 1, 'ABC' );
1行が作成されました。
SQL> insert into nulltest values ( 2, '' );
1行が作成されました。
SQL> insert into nulltest values ( 3, null );
1行が作成されました。
SQL> select * from nulltest;
ID C1
---------- ----------
1 ABC
2
3
SQL> select * from nulltest where c1 = '';
レコードが選択されませんでした。
SQL> select * from nulltest where c1 is null;
ID C1
---------- ----------
2
3
SQL>

ゼロバイト文字列を格納したのに、検索しても出て来ません。IS NULLで検索して初めてレコードが見つかりました。

同じ操作をDb2でやってみましょう。

db2 => create table nulltest ( id numeric(5), c1 varchar(10) )
DB20000I SQL コマンドが正常に完了しました。
db2 => insert into nulltest values ( 1, 'ABC' )
DB20000I SQL コマンドが正常に完了しました。
db2 => insert into nulltest values ( 2, '' )
DB20000I SQL コマンドが正常に完了しました。
db2 => insert into nulltest values ( 3, null )
DB20000I SQL コマンドが正常に完了しました。
db2 => select * from nulltest
ID C1
------- ----------
1. ABC
2.
3. -
3 レコードが選択されました。
db2 => select * from nulltest where c1 = ''
ID C1
------- ----------
2.
1 レコードが選択されました。
db2 => select * from nulltest where c1 is null
ID C1
------- ----------
3. -
1 レコードが選択されました。
db2 =>

Db2はSELECTでNULL値を「-」で表示してくれるみたいですね。わかりやすいです。
ゼロバイト文字列で検索するときちんとゼロバイト文字列でヒットしますし、IS NULLで検索すればNULLのレコードだけがきちんと検索できました。

実はOracleは、ゼロバイトの文字列はNULLとして格納するという昔からの仕様があります。
そのため、文字型のカラムにゼロバイト文字列は挿入できません。
一方DB2は、ゼロバイト文字列でもきちんと格納できます。

おかしなことに、Oracle製のOLEDBプロバイダでは、ゼロバイトの文字列を格納するとき、NULLにならないようにOLEDBプロバイダが空白１文字に置き換えるというとってもヘンな動作をします。
Microsoft製のOracle用OLEDBプロバイダもあるのですが、こちらではそのような動作はしません。
もちろんODBCドライバも空白はNULLです。
Oracleは自分の仕様がヘンなことに気づいているが、互換性のために変更できないってことでしょうか？

ところで、VARCHARのカラムには、「１文字の空白」とか「５文字の空白」というデータも格納することが可能です。SQL*Plus等のコマンドラインツールではその差が表現できず、宝の持ち腐れのような気もしますが、ODBC等のAPIを使用してアクセスする場合にはきちんと空白が格納数ぶんだけ返って来ます。
検索の場合には空白文字が何文字あっても同じに扱われるという、ヘンなんだか親切なんだかわからない仕様もありますね。
文字型は実は奥が深いのです。

他のSQLDBではどうなのでしょう。筆者が調べたところ以下のようになっていました。

	Oracle	Microsoft SQL Server	IBM DB2	MySQL	PostgreSQL
長さゼロの文字列を格納	NULL	長さゼロの文字列	長さゼロの文字列	長さゼロの文字列	長さゼロの文字列
全角空白の扱い	ただの漢字	比較においては半角空白と同じ。ただしデータは正しく格納されている。	ただの漢字	ただの漢字	ただの漢字
検索時の空白文字の比較（CHAR)	半角の空白は無視	半角と全角の空白は無視	半角の空白は無視	半角の空白は無視	半角の空白は無視
検索時の空白文字の比較（VARCHAR)	半角の空白は完全に文字として扱う	半角と全角の空白は無視	半角の空白は無視	半角の空白は無視	半角の空白は完全に文字として扱う
UNIQUEチェック時の空白文字の比較(CHAR)	半角の空白は無視	半角と全角の空白は無視	半角の空白は無視	半角の空白は無視	半角の空白は無視
UNIQUEチェック時の空白文字の比較(VARCHAR)	空白を含むと制約違反にならない	半角と全角の空白は無視	半角の空白は無視	半角の空白は無視	空白を含むので制約違反にならない

ここでの空白とは、空白だけの文字列も含みますが、「文字列の後ろに余計な空白が入っていたらどうなるの？」という観点で調べています。
網がけされた部分が他のSQLDBと異なる部分ですね。

Oracle以外のどのSQL DBも空白文字をNULLで格納するようなことはしていません。完全にOracleの一人仕様ですね。
SQL Serverは全角の空白も半角の空白と同様に扱うようです。ほかのSQL DBは漢字一文字として扱っています。
VARCHARの比較の仕様ですが、例えば「ABC 」という文字列と「ABC」という文字列の比較は、PostgreSQLとOracle以外は無視されるようですね。

いずれの場合も文字列はきちんとSQL DBに格納されていて、取り出すことが可能ですが、「空白が１個だったら」というような、目に見えないコードを意味のある情報にするようなことは、DB設計上の観点から避けるべきだと思います。
デバッグやトラブル時に空白の個数を数えるなんてクエリツール上でやりたくないですしね。
メインフレームからの移植ではありがちなんですが、もしそのような設計のDBがあるなら、早めに設計変更することをお勧めします。