SQL Serverでnvarchar(x)のカラムにx文字入らないのはなぜ

目次

あらすじ

SQL Serverにおける文字列型の長さは、nvarcharクラスを使っていればバイト数に関するあれこれは SQL Server側で吸収してくれて、特に気にする必要ないと思ってたが違った。

環境

SELECT SERVERPROPERTY('productversion') as VERSION;
VERSION
14.0.1000.169

事象

CREATE TABLE dbo.sampletable (
	nvc nvarchar(3)
);

例えば、nvarchar(3)カラムを持つテーブルを作ったら、以下のような長さのデータは全部INSERTできると考えていた。

  • abc
  • あいう
  • ｱｲｳ
  • 亜位宇

実際これらはINSERTできる。でもこれはINSERTできない。

  • 𠮷野家

原因と対策

サロゲート文字は1文字でデータ長2として扱われる模様。なので、SQL Serverの文字列型は

補助文字によって 2 つのバイト ペア (またはサロゲート ペア) が使用されるため、格納できる文字数は n よりも少なくなる場合があります。

  • nvarchar型を使う
  • サロゲート文字がまざると定義した長さの最大値は入らない
    • 照合順序によってはLEN関数も正しくカウントされない
  • カラムのサイズは想定したMAXデータ長×2で定義した方がよさげ(全部サロゲート文字の場合)
    • 文字数を正確にカウントしたい場合、SCの照合順序を使用する

SC の照合順序を使用する場合、返される整数値では、UTF-16 サロゲート ペアが 1 文字としてカウントされます。

このあたりを気を付けた方が良いっぽい。

実際に確認してみる

テーブル作成

CREATE TABLE dbo.sampletable( 
  id int NOT NULL IDENTITY (1, 1)
  , vc varchar (3)
  , nvc nvarchar(3)
  , altvc varchar (3) -- varcharからnvarcharに変えてみる
  , kakusu_nvc nvarchar(3) -- 照合順序をJapanese_Bushu_Kakusu_100_CI_AS_SCに設定してみる
); 

ALTER TABLE dbo.sampletable ALTER COLUMN altvc nvarchar(3); 
ALTER TABLE dbo.sampletable ALTER COLUMN kakusu_nvc nvarchar(3) COLLATE Japanese_Bushu_Kakusu_100_CI_AS_SC;

出来上がったテーブルがこちら。

SELECT
  TABLE_SCHEMA               AS schema,        TABLE_NAME             AS table
  , COLUMN_NAME              AS column,        DATA_TYPE              AS type
  , CHARACTER_SET_NAME       AS character_set, COLLATION_NAME         AS collation
  , CHARACTER_MAXIMUM_LENGTH AS maximum_len,   CHARACTER_OCTET_LENGTH AS octet_len
FROM
  INFORMATION_SCHEMA.COLUMNS 
WHERE
  TABLE_SCHEMA = 'dbo';
schema table column typ character_set collation maximum_len octet_len
dbo sampletable id int
dbo sampletable vc varchar cp932 Japanese_CI_AS 3 3
dbo sampletable nvc nvarchar UNICODE Japanese_CI_AS 3 6
dbo sampletable altvc nvarchar UNICODE Japanese_CI_AS 3 6
dbo sampletable kakusu_nvc nvarchar UNICODE Japanese_Bushu_Kakusu_100_CI_AS_SC 3 6

データINSERT

コードブロックの中から1文字選んで3文字分INSERTしてみる。INSERT文の典型的な形は以下。

INSERT INTO dbo.sampletable VALUES (N'ぁぁぁ', N'ぁぁぁ', N'ぁぁぁ', N'ぁぁぁ');

1カラムずつINSERTしてもいいし、入らない場合は適当に入らないところを削ったりしてみる。

パターン1: U+3040～U+309F Hiragana 平仮名

  • ぁぁぁ(U+3041)
    • いわゆるひらがな
    • varcharには3文字分入らない

パターン2: U+3400～U+4DBF CJK Unified Ideographs Extension A CJK統合漢字拡張A

  • 㐁㐁㐁(U+3401)
    • いわゆる漢字
    • varcharには入るが???となってしまう

パターン3: U+FF00～U+FFEF Halfwidth and Fullwidth Forms 半角・全角形

  • ｧｧｧ(U+FF67)
    • いわゆる半角カナ
    • 全部3文字分入る

パターン4: U+20000～U+2A6DF CJK Unified Ideographs Extension B CJK統合漢字拡張B

パターン4.1: CJK統合漢字拡張をB3文字分
  • 𠮷𠮷𠮷(U+20BB7)
    • サロゲート文字
    • 全部入らない
    • 𠮷野家でも入らない
      • 野はU+91CE、屋はU+5BB6でどちらもCJK統合漢字
パターン4.2: CJK統合漢字拡張を1文字だけ
  • 𠮷(U+20BB7)
    • 一文字分なら全部入る
    • varcharは??となる
    • 1文字しか追加していないのにLENおよびDATALENGTHでは×2分確保されている
    • 照合順序がJapanese_Bushu_Kakusu_100_CI_AS_SCのカラムはLENがこちらの想定通り戻ってくる

結果

上記のINSERT結果をLENおよびDATALENGTHと共に取得した。

SELECT 
  vc           AS 'vc',  LEN(vc)         AS l1, DATALENGTH(vc)         AS d1
  , nvc        AS 'nvc', LEN(nvc)        AS l2, DATALENGTH(nvc)        AS d2
  , altvc      AS 'alt', LEN(altvc)      AS l3, DATALENGTH(altvc)      AS d3
  , kakusu_nvc AS 'kak', LEN(kakusu_nvc) AS l4, DATALENGTH(kakusu_nvc) AS d4
FROM dbo.sampletable;
パターン vc l1 d1 nvc l2 d2 alt l3 d3 kak l4 d4
1 ぁぁぁ 3 6 ぁぁぁ 3 6 ぁぁぁ 3 6
2 ??? 3 3 㐁㐁㐁 3 6 㐁㐁㐁 3 6 㐁㐁㐁 3 6
3 ｧｧｧ 3 3 ｧｧｧ 3 6 ｧｧｧ 3 6 ｧｧｧ 3 6
4.1
4.2 ?? 2 2 𠮷 2 4 𠮷 2 4 𠮷 1 4

※ パターン列は便宜上くっつけた

LENの仕様:

指定された文字列式の、末尾の空白を除いた文字数を返します。

SC の照合順序を使用する場合、返される整数値では、UTF-16 サロゲート ペアが 1 文字としてカウントされます。

DATALENGTHの仕様:

この関数では、式を表すために必要なバイト数が返されます。

どの文字がやばいのか

やばいのはこの辺。一文字ずつ適当に例を上げてみる。

※ 𠮷野家の正式な方の𠮷(つちよし)もCJK統合漢字拡張Bに入っている。(U+20BB7)

あとこの辺。

サロゲートペアになっていないこの辺は大丈夫。歴史的経緯がわからないけどCJK統合漢字拡張Aはセーフ。

参考

関連記事(この記事の初版より古い記事はリンクがグレーで表示されます)

  1. 2019/06/03 [SQL Server] [A5 SQL Mk-2] SQL Serverのカラムに設定されている論理名をまとめて取得する
  2. 2019/05/21 [SQL Server] [xQuery] SQL Serverの動的管理ビューから、実行したSQL(とバインド変数)を取得する
  3. 2019/05/02 [SQL Server] SQL Serverで実行したSQLが暗黙の型変換が起こっているかどうかはどうやって調べたらよいのか
  4. 2019/04/23 [Oracle] [SQL Server] [PostgreSQL] [DB2] 色々なDBのバージョン確認方法とか論理名のつけ方とか
  5. 2019/04/15 [SQL Server] SQL Serverでの照合順序の確認方法