眠気が家出してしまったので、メモっておこうと思ったことを書く。※執筆中現時刻、朝5時
仕事でSQLを書いてきた際に、ぼーっとしていて以下のような判定を作ってしまった。
select * from TABLE1 where COL2 <> '' -- 空でないレコードを取得する
COL2はchar型だったため、実際には固定長でスペースが存在し、正しく取得されない判定となっているハズだった。
しかし、実際には正しく取得されていることに後から気づいたのだ。
「スペースと判定しちゃうから絶対に”空でない”という判定結果になるはずでは？？？」と思ったため、少し調べてみた。
実際にテスト用のレコードで挙動を確かめてみる。
-- テスト用のテーブル作成
create table SPACE_TEST_TABLE(
col_char char(2) null, -- char型のカラムを作成
col_varchar varchar(2) null) -- varchar型のカラムを作成
-- 空文字のレコードを追加
insert SPACE_TEST_TABLE values('','')
-- 取得
select
-- char型を確認する
col_char -- 普通に取得
,len(col_char) as char_LEN -- 文字の長さを取得
,datalength(col_char) as char_DATALENGTH -- データの長さを取得
,case when col_char = '' then 'BLANK' else 'SPACE' end as char_STATUS -- 空文字との比較結果を表示
-- varchar型を確認する
,col_varchar -- 普通に取得
,len(col_varchar) as varchar_LEN -- 文字の長さを取得
,datalength(col_varchar) as varchar_DATALENGTH -- データの長さを取得
,case when col_varchar = '' then 'BLANK' else 'SPACE' end as varchar_STATUS -- 空文字との比較結果を表示
from SPACE_TEST_TABLE結果はこうなる
どういうことかと言うと。
・len関数は、文字の長さを取得するため、char型でも余分なスペースは切り落としてbyte数を取得する
⇒頭に入れておかないとどっかでバグを起こしそう
・datalength関数は、データの長さを取得するため、char型の場合はスペース分込みのbyte数を取得する
⇒わかる
・空文字と中身がスペースだけのchar型を比較すると、結果はTRUEとなる
⇒は？
・空文字と中身が空文字のvarchar型を比較すると、結果はTRUEとなる
⇒わかる
という結果である。
調べたところ、以下のドキュメントに辿り着いた。
ついでにこちらも参考にした。
https://sql55.com/column/string-comparison.php
どうやら、SQLSERVERでは文字列と文字列を比較する際、短い方に空白を追加してbyte数を合わせてから比較を行う仕様らしい。
知ってしまえばどうということは無いかもしれないが、性能にも影響が出そうな仕様だなと感じた。
例えば最初のケースのような、where句で””かどうかをchar型を対象に判定するような記載をした場合、検索対象のレコードすべてに対して比較の都度、空文字を追加する処理を行っているのではないか？
備忘録として書き留めておく。