背景
SQL Serverでは、nvarcharを型として選択すると、自動的に文字コードはUTF-16で格納されます。
ここで、例えばアプリ側で書きだしたファイルをShift_JISで保存しようとすると、「UTF-16で表現できるが、Shift_JISでは表現できない文字」が含まれている場合にエラーが起きることも想定されます。
このようなデータはDBに書き込む前にバリデーションされていることが望ましいですが、DBレイヤーでもチェックを行う場合にどういった方法がとれるかについて考えてみました。
方法
①補助文字（サロゲートペア）が文字列に含まれるかどうかを検知
2文字分のデータを組み合わせて1文字を表現するサロゲートペア（例：「𠮷」）は、len()関数で2文字扱いとなる場合があります。
create table my_table (c1 nvarchar(10))
insert into my_table values (N'𠮷')
insert into my_table values (N'吉')
select datalength(c1) as datalength, len(c1) as len, c1 from my_table
一行目の値がサロゲートペアの文字です。一文字ですが、len()の結果は2となっています。
このような文字もShift_JISでは表現できないので、len()関数を使って工夫することで「サロゲートペアを含んでいるカラム」を検出できそうです。「_sc」を含む照合順序を使うと、サロゲートペアの文字もlen()関数で1文字扱いとなります。これらの性質を利用して、以下のクエリで「サロゲートペアを含んでいるカラム」を検出できます。
--ci_as_sc : サロゲートペアを使っている場合でも1文字扱いになる照合順序
select top 10 * from my_table with(nolock)
where len(c1 collate japanese_xjis_100_ci_as_sc) <> len(c1)
②「UTF-16で表現できるが、Shift_JISでは表現できない文字」が含まれるかどうかを検知
方法①ではサロゲートペアが含まれるかどうかは検知できますが、それ以外の文字でShift_JISで表現できない文字が入っていた場合は検知できません。例えば「姸」などの文字です。これは方法①では検出できません。
--ci_as_sc : 補助文字（＝サロゲートペア）を使っている場合でも1文字扱いになる照合順序
insert into my_table values (N'姸')
select top 10 * from my_table with(nolock)
where len(c1 collate japanese_xjis_100_ci_as_sc) <> len(c1)
→「姸」が検出されない。
そこで、SQL Serverの文字の扱いに関する仕様を利用します。
SQL Serverは、設定した文字コードに非対応な文字データがINSERTされた場合、一律で「?」に変換してINSERTする仕様となっています。詳しくは以下の記事をご参照ください。
したがって、「Shift_JISに変換した文字と、変換前の文字がイコールでないカラム」を抽出することで「UTF-16で表現できるが、Shift_JISでは表現できない文字」を検出できます。
select top 100 * from my_table with(nolock)
where c1 <> cast(c1 as varchar(400))
→どちらも検出できた。
check制約として実装
以上を踏まえて、例えば「Shift_JIS」に対応する文字だけがカラムに格納されるように制限するcheck制約を作ることができます。
クエリは以下の通りです。
create table sample_table (c1 nvarchar(100)) --カラム自体はUTF-16で作成
go
create function is_shift_jis (
@txt nvarchar(100)
)
returns bit
as
begin
return case
when @txt = cast(@txt as varchar(100)) --ユニコード文字とShift_JISTに変換したカラムが一致＝全文字がShift_JIS対応
then 1
else 0
end;
end
go
alter table sample_table add constraint ck_sample_table_column_must_be_shift_jis
check (dbo.is_shift_jis(c1) = 1);
これでcheck制約の設定が完了しました。カラム自体はUTF-16ですが、入る値としてはShift-JISに限定するような制約になっています。
試しにデータをINSERTしてみます。
insert into sample_table (c1) values (N'あいうえお')
go
insert into sample_table (c1) values (N'𠮷') --Shift_JIS未対応文字
go
insert into sample_table (c1) values (N'あいうえお𠮷') --shift_JIS未対応文字を含む文字列
go
↓のように、Shift_JIS未対応文字が入っている場合はINSERTに失敗します。
テーブルには1行だけがINSERTされていることも確認できました。
大量のレコードが一気にINSERTされるテーブルの場合は更新性能の劣化が気になりますが、そうでない場合や性能劣化が許容できる場合は有効な手法だと思います。
まとめ
以上、DBレイヤーで文字コードに関するチェックを行う方法をSQL Serverの実装で考えてみました。
他に良い方法があれば教えていただけると嬉しいです。