SQLServerでサロゲートペアや結合文字列を扱う前に照合順序を確認しよう

SQL Server 2019 での話です。

Unicode の𠮷(つちよし), 𩸽(ほっけ)や 🍺(ビール), 👨‍👩‍👧‍👦(家族: 父母と女の子と男の子)のような絵文字等を扱う場合、データベースがサロゲートペアや結合文字列に対応している必要があります。

SQL Server の Unicode データ型は SQL Server 2005 から対応済みで、SQL Server 2019 では新しい UTF-8 対応の照合順序も加わり、より扱いやすくなってます。

公式サイトにも説明があります。

• 照合順序と Unicode のサポート - SQL Server | Microsoft Docs

しかし、この説明の中に補助文字に関する記述があり、SCA 照合順序なしで Unicode を扱う場合、結果として「 0 – 0xFFFF の範囲外の文字は無視されてしまう」事が示唆されています。

これが地味に影響する部分がクエリというか T-SQL の式に Unicode 文字列を使用する場合で、前述の範囲外の文字に該当する場合、その文字コードが除外された状態で式が評価される事があります。

つまり、照合順序の設定次第では「式の評価が異なるため、意図しない結果を得てしまう場合がある」という事です。

検証してみる

後述する作業は Azure Data Studio (または SSMS) から SQL Server 2019 に接続した状態で行いました。※Windows版 / Linux版 どちらも同じ結果でした。

(1) 検証用データベースの作成と照合順序の確認

検証用データベースを 2 種類用意します。

my_test_db	既定の照合順序のまま　※Japanese_CI_AS
my_xjis140_db	照合順序に Japanese_XJIS_140_CI_AS を指定

ここでは、次の SQL 文を実行してデータベースを作成します。

CREATE DATABASE my_test_db;
CREATE DATABASE my_xjis140_db COLLATE Japanese_XJIS_140_CI_AS;

照合順序の確認ではデータベースが Auto Closed のままだと情報を取得できません。
なので Azure Data Studio であれば、Manage や New Query のデータベース切替等で活性化します。

活性化が済んだら照合順序を確認します。

SELECT SERVERPROPERTY('Collation') as N'サーバの照合順序';
SELECT name, collation_name FROM sys.databases WHERE name LIKE 'my_%';

※ここで collation_name が NULL の場合、データベースが活性化してません。

(2) Unicode 文字の格納

テーブル変数に非Unicode データ型, Unicode データ型, UTF8 エンコード対応のデータ型を用意します。そこにサロゲートペアで表現される絵文字🍺と結合絵文字👨‍👩‍👧‍👦を格納し、それぞれの値を確認します。

DECLARE @a table (
    non_unicode varchar(40),
    unicode_utf16 nvarchar(40),
    unicode_utf8 varchar(40) COLLATE Japanese_XJIS_140_CI_AS_UTF8
);
INSERT INTO @a VALUES (N'🍺', N'🍺', N'🍺');
INSERT INTO @a VALUES (N'👨‍👩‍👧‍👦', N'👨‍👩‍👧‍👦', N'👨‍👩‍👧‍👦');
SELECT * FROM @a;
SELECT
    CAST(non_unicode AS varbinary),
    CAST(unicode_utf16 AS varbinary),
    CAST(unicode_utf8 AS varbinary)
FROM @a;

データベースに対してクエリを発行します。
※下画面の例は my_test_db で実行した結果です。

これは my_test_db, my_xjis140_db どちらで実行しても同じ結果になります。

varchar では文字が化けて ?? で埋められていますが、nvarchar と UTF8 エンコーディングを指定した varchar では、それぞれ UTF-16LE, UTF-8 で符号化された値が格納されます。

データの格納先としては、データベースの照合順序に関わらず、問題なく扱える事が確認できました。

(3) クエリの式で Unicode 文字列を使用

テーブル変数に絵文字を含む値を格納し、データを抽出してみます。

DECLARE @b table (
    name_utf16 nvarchar(40),
    name_utf8 varchar(40) COLLATE Japanese_XJIS_140_CI_AS_UTF8
);
INSERT INTO @b VALUES (N'超🍺ドライ', N'超🍺ドライ');
INSERT INTO @b VALUES (N'墨🍺🍺ラベル', N'墨🍺🍺ラベル');
INSERT INTO @b VALUES (N'最速🍺絞り', N'最速🍺絞り');
SELECT * FROM @b;
SELECT * FROM @b WHERE name_utf16 LIKE N'%🍺🍺%';
SELECT * FROM @b WHERE name_utf8 LIKE N'%🍺🍺%';

このクエリを実行したところ、my_test_db (照合順序: Japanese_CI_AS) と my_xjis140_db (照合順序: Japanese_XJIS_140_CI_AS) で異なる結果になりました。

my_xjis140_db では特に問題なく、意図した結果が得られました。

しかし、my_test_db では格納したデータが期待通りに抽出できません。
照合順序 Japanese_CI_AS に補助文字のサポートが含まれないからです。

....

式の評価では、サポート対象外な文字を除いての評価になるようです。
例えば、次のクエリを my_test_db で実行すると、墨🍺ラベルが返却されます。

DECLARE @c table (
    name_utf16 nvarchar(40)
);
INSERT INTO @c VALUES (N'超🍺ドライ');
INSERT INTO @c VALUES (N'中トロ🍣');
INSERT INTO @c VALUES (N'こはだ🍣');
INSERT INTO @c VALUES (N'墨🍺ラベル');
INSERT INTO @c VALUES (N'あじ🍣');
INSERT INTO @c VALUES (N'最速🍺絞り');
SELECT * FROM @c WHERE name_utf16 = N'墨🗻𩸽🍣𠮷🍺ラベル';

式の評価の際、墨🍺ラベルと墨🗻𩸽🍣𠮷🍺ラベルに含まれるサポート対象外の文字を省略すると、どちらも墨ラベルになります。
この状態で式を評価すると元の文字列が異なるのに「等しい」と評価されてしまいます。

影響は WHERE 句だけでなく、SELECT 句でも同様です。

DECLARE @d table (
    name_utf16 nvarchar(40)
);
INSERT INTO @d VALUES (N'超🍺ドライ');
INSERT INTO @d VALUES (N'中トロ🍣');
INSERT INTO @d VALUES (N'こはだ🍣');
INSERT INTO @d VALUES (N'墨🍺ラベル');
INSERT INTO @d VALUES (N'あじ🍣');
INSERT INTO @d VALUES (N'最速🍺絞り');
SELECT
    SUM(t.is_beer) as beer,
    SUM(t.is_sushi) as sushi
FROM (
    SELECT
        (CASE WHEN name_utf16 LIKE N'%🍺%' THEN 1 ELSE 0 END) as is_beer,
        (CASE WHEN name_utf16 LIKE N'%🍣%' THEN 1 ELSE 0 END) as is_sushi
    FROM @d
) as t

特定の絵文字を含むデータ数を集計するようなクエリでは誤った集計結果になります。

一応、データベースの照合順序が Japanese_CI_AS のままでも、式の評価時に _140 系等の補助文字をサポートする照合順序を指定すれば、一時的に回避できます。

    SELECT
        (CASE WHEN name_utf16 COLLATE Japanese_XJIS_140_CI_AS LIKE N'%🍺%' THEN 1 ELSE 0 END) as is_beer,
        (CASE WHEN name_utf16 COLLATE Japanese_XJIS_140_CI_AS LIKE N'%🍣%' THEN 1 ELSE 0 END) as is_sushi
    FROM @d

しかし、この方法は性能面では期待できない上、根本的な解決にはなってません。

そもそも、照合順序を Japanese_XJIS_140_CI_AS に設定して作成したデータベースの方は、問題なく意図した結果が得られています。

結論

SQLServerでサロゲートペアや結合文字列を扱う場合、データベース作成時に適切な照合順序を指定しましょう。

....

前述した公式サイトには、注意書きとして「SQL Server 2014 (12.x) 以降では、すべての新しい _140 照合順序で補助文字が自動的にサポートされます。」とあります。

ちなみに日本語の照合順序で指定可能な _140 系の一覧は次のクエリで取得できます。

SELECT * FROM fn_helpcollations() WHERE name LIKE 'Ja%' AND name LIKE '%_140%';

不要なトラブルを避ける意味でも、照合順序は指定するべきかと。