ビジネス課題への解決策(アイディア)と、新たな発想(+α)が見つかるIT情報メディア

Menu
  1. TOP
  2. データ活用
  3. SQL DBのNULLと文字列

SQL DBのNULLと文字列

  • LINEで送る
  • このエントリーをはてなブックマークに追加

今日は、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プロバイダが空白1文字に置き換えるというとってもヘンな動作をします。
Microsoft製のOracle用OLEDBプロバイダもあるのですが、こちらではそのような動作はしません。
もちろんODBCドライバも空白はNULLです。
Oracleは自分の仕様がヘンなことに気づいているが、互換性のために変更できないってことでしょうか?

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


追記:Waha! Transformer 製品サイトの関連コンテンツ

Waha! Transformer の対応データソース

ERP移行・基幹系システムの再構築を成功させる5つのステップ

ERP:基幹系システム刷新時のデータ移行「17のあるあるをチェック!」セミナー

データの抽出や加工、連携にお悩みではありませんか?

20年以上の実績に裏打ちされた信頼のデータ連携ツール「Waha! Transformer」で、自社に眠るデータを有効活用。まずは無料のハンズオンセミナーや体験版で効果を実感していただけます。

> 純国産ETLツール「Waha! Transformer」

Waha! Transformer
メールマガジンの登録はこちらから
メルマガ登録 お問い合わせ