2019-06-13-sqlserver-varchar-nvarchar.md
code:2019-06-13-sqlserver-varchar-nvarchar.md
---
layout: post
title: "SQL Serverでnvarchar(x)のカラムにx文字入らないのはなぜ"
description: ""
category:
---
## あらすじ
SQL Serverにおける文字列型の長さは、nvarcharクラスを使っていればバイト数に関するあれこれは
SQL Server側で吸収してくれて、特に気にする必要ないと思ってたが違った。
## 環境
`sql
SELECT SERVERPROPERTY('productversion') as VERSION;
`
|VERSION|
|:---|
|14.0.1000.169|
## 事象
`sql
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 文字としてカウントされます。
このあたりを気を付けた方が良いっぽい。
## 実際に確認してみる
### テーブル作成
`sql
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;
`
出来上がったテーブルがこちら。
`sql
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文の典型的な形は以下。
`sql
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と共に取得した。
`sql
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の仕様:
この関数では、式を表すために必要なバイト数が返されます。
## どの文字がやばいのか
やばいのはこの辺。一文字ずつ適当に例を上げてみる。
- 𠌂 U+20302
- 𪟟 U+2A7DF
- 𫝃 U+2B743
- 𫭟 U+2BB5F
- 丸 U+2F801
あとこの辺。
- 😀 U+1F600
- 🚀 U+1F680
- 🤐 U+1F910
- 🌀 U+1F300
サロゲートペアになっていないこの辺は大丈夫。歴史的経緯がわからないけどCJK統合漢字拡張Aはセーフ。
- あ U+3042
- ア U+30A2
- 㑀 U+3440
- 光 U+5149
- ァ U+FF67
## 参考