文字列の並び順 / Unicode Collation

Unicode のコレーションについて。

HTML 版は https://tmtms.net/slides/20251213-nseg/

Transcript

1. 文字列の並び順 2025-12-13 NSEG #112 + Nagano.rb #21 とみたまさひろ 1
2. 自己紹介 • とみたまさひろ • • https://bsky.app/profile/tmtms.net https://blog.tmtms.net 2
3. MySQL徹底入門第5版 • 2025/6/16 発売 • 初版は2001年発売 • 1割くらい書きました ▪ 5章「ユーザー管理」 ▪ 11章「文字コードと日本語環境」 https://www.shoeisha.co.jp/book/detail/9784798189307 3
4. Software Design 9月号 • 2025/8/18発売 • 第2特集第2章「データベースにおける文字コードの落とし穴」 https://gihyo.jp/magazine/SD/archive/2025/202509 4
5. また文字コードの話をします Software Design の記事を書くために PostgreSQL についてちゃんと調べた 5
6. こんな回答したことありませんか？ 「このリストは何順で並んでるんですか？」 …コードを読んでみる… 「名前の文字コード順です」 SELECT 〜 ORDER BY name 6
7. 本当？ 7
8. 本当っぽい postgres=# SELECT name,name::bytea byte FROM t ORDER BY name; name | byte --------+---------------------- 123 | \x313233 abc | \x616263 あいう | \xe38182e38184e38186 日本語 | \xe697a5e69cace8aa9e (4 rows) 8
9. あれ？ 61 の方が 41 よりも先に…？ name | byte ------+---------- abc | \x616263 ABC | \x414243 9
10. あれれ？ 61 と 41 が交互に…？ name | byte ------+---------- aaa | \x616161 AAA | \x414141 abc | \x616263 ABC | \x414243 10
11. あれれれ？ name | byte --------+---------------------- 123 | \x313233 １２３ | \xefbc91efbc92efbc93 456 | \x343536 ４５６ | \xefbc94efbc95efbc96 name | byte ------+---------- ㋿ | \xe38bbf ← これはいい ㍽ | \xe38dbd ← 末尾 d ㍻ | \xe38dbb ← b ㍾ | \xe38dbe ← e ㍼ | \xe38dbc ← c 11
12. 何順なんだ？ 12
13. コレーション 日本語だと「照合順序」 13
14. コレーションの指定方法 • CREATE TABLE 時にカラムごとに指定する • MySQLの場合はテーブル単位でも指定可 • クエリ内の文字列や文字列カラムの後ろに COLLATE コレーション名 を指定する CREATE TABLE t (s VARCHAR COLLATE unicode) CREATE TABLE t (s VARCHAR(1000)) COLLATE utf8mb4_0900_as_cs SELECT * FROM t ORDER BY s COLLATE unicode 14
15. MySQLのコレーション • MySQL組み込み • 実行環境には依存しない • Unicode 9 ベース • 最新は Unicode 16 なので結構古い • SHOW COLLATION で一覧できる • 全部で286個 / utf8mb4 のコレーションは 89個 • デフォルトはアクセント記号や大文字小文字等を無視 15
16. MySQLのコレーション • データベース、テーブル、カラムごとに設定できる • 接続のコレーションは SHOW VARIABLES で見れる SHOW VARIABLES LIKE 'collation%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_0900_ai_ci | | collation_database | utf8mb4_0900_ai_ci | | collation_server | utf8mb4_0900_ai_ci | +----------------------+--------------------+ 16
17. PostgreSQLのコレーション • デフォルトではOSのコレーションなので環境に依存 • Unicode ベースのコレーションも選択可 • 外部ライブラリの libicu で対応 • 最新の Unicode 16 まで対応 • SELECT * FROM pg_collation で一覧できる • 全部で920個(環境による) • Unicode のコレーションは 817個 17
18. PostgreSQLのコレーション • カラムごとに設定できる • デフォルトのコレーションは psql の \l で見れる Locale Provider が libc の場合は OS に依存 postgres=# \l postgres List of databases -[ RECORD 1 ]-----+----------- Name | postgres Owner | postgres Encoding | UTF8 Locale Provider | libc ← これ Collate | en_US.utf8 ← これ Ctype | en_US.utf8 Locale | ICU Rules | Access privileges | 18
19. Ubuntu の場合 en_US.utf8 の場合: ja_JP.utf8 の場合: 文字コード順ぽいけどちょっと違う name | byte --------+---------------------- 123 | \x313233 １２３ | \xefbc91efbc92efbc93 456 | \x343536 ４５６ | \xefbc94efbc95efbc96 aaa | \x616161 AAA | \x414141 abc | \x616263 ABC | \x414243 あいう | \xe38182e38184e38186 日本語 | \xe697a5e69cace8aa9e name | byte --------+---------------------- 123 | \x313233 456 | \x343536 AAA | \x414141 ABC | \x414243 aaa | \x616161 abc | \x616263 １２３ | \xefbc91efbc92efbc93 ４５６ | \xefbc94efbc95efbc96 あいう | \xe38182e38184e38186 日本語 | \xe697a5e69cace8aa9e 19
20. C ロケールは単純に文字コード順 en_US.utf8 の場合: ja_JP.utf8 の場合: C の場合: 文字コード順 name | byte --------+---------------------- 123 | \x313233 １２３ | \xefbc91efbc92efbc93 456 | \x343536 ４５６ | \xefbc94efbc95efbc96 aaa | \x616161 AAA | \x414141 abc | \x616263 ABC | \x414243 あいう | \xe38182e38184e38186 日本語 | \xe697a5e69cace8aa9e name | byte --------+---------------------- 123 | \x313233 456 | \x343536 AAA | \x414141 ABC | \x414243 aaa | \x616161 abc | \x616263 １２３ | \xefbc91efbc92efbc93 ４５６ | \xefbc94efbc95efbc96 あいう | \xe38182e38184e38186 日本語 | \xe697a5e69cace8aa9e name | byte --------+---------------------- 123 | \x313233 456 | \x343536 AAA | \x414141 ABC | \x414243 aaa | \x616161 abc | \x616263 あいう | \xe38182e38184e38186 日本語 | \xe697a5e69cace8aa9e １２３ | \xefbc91efbc92efbc93 ４５６ | \xefbc94efbc95efbc96 20
21. Ruby の sort は文字コード順 PostgreSQL と Ruby ではソート順が異なる たぶん他の言語も 気をつけてないと画面によって並び順が違うなんてことも 21
22. Unicode のコレーション 22
23. Unicode Collation Algorithm (UCA) Unicode の照合順序のアルゴリズム https://unicode.org/reports/tr10/ 23
24. PostgreSQL で UCA を使いたい場合は unicode コレーションを使う SELECT s FROM t ORDER BY s COLLATE unicode 123 １２３ ①②③ 1234 １２３４ ①②③④ aaa AAA abc ABC 令和 ㋿ 平成 ㍻ 昭和 ㍼ 24
25. aaa, AAA, abc, ABC の順に並ぶ仕組み Unicode では文字ごとに weight という値を持っている weight は3つの値からなる Default Unicode Collation Element Table (DUCET) • a : [.2380.0020.0002] • A : [.2380.0020.0008] • b : [.239A.0020.0002] • B : [.239A.0020.0008] • c : [.23B4.0020.0002] • C : [.23B4.0020.0008] https://www.unicode.org/Public/UCA/16.0.0/allkeys.txt 25
26. 文字ごとではなく文字列全体のソートキーを求める 文字列中の文字のweightの1番目の値、2番目の値、 3番目の値をまとめて 0000 で連結する abc : [.2380.0020.0002] [.239A.0020.0002] [.23B4.0020.0002] ↓ 文字列のソートキー : 2380 239A 23B4 0000 0020 0020 0020 0000 0002 0002 0002 26
27. 文字列のソートキーでソートする 文字列 ソートキー aaa 2380 2380 2380 0000 0020 0020 0020 0000 0002 0002 0002 AAA 2380 2380 2380 0000 0020 0020 0020 0000 0008 0008 0008 abc 2380 239A 23B4 0000 0020 0020 0020 0000 0002 0002 0002 ABC 2380 239A 23B4 0000 0020 0020 0020 0000 0008 0008 0008 27
28. 123, １２３, ①②③, 1234 文字列 ソートキー 123 217E 217F 2180 0000 0020 0020 0020 0000 0002 0002 0002 １２３ 217E 217F 2180 0000 0020 0020 0020 0000 0003 0003 0003 ①②③ 217E 217F 2180 0000 0020 0020 0020 0000 0006 0006 0006 1234 217E 217F 2180 2181 0000 0020 0020 0020 0020 0000 0002 0002 0002 0002 28
29. 合字のソートも同様 漢字の weight は DUCET には載ってなくて計算で求められる • 令 : U+4EE4 → [.FB40.0020.0002][.CEE4.0000.0000] • 和 : U+548C → [.FB40.0020.0002][.D48C.0000.0000] 合字は DUCET に載ってる • ㋿ : [.FB40.0020.001C][.CEE4.0000.0000][.FB40.0020.001C][.D48C.0000.0000] ↓ 文字列 ソートキー 令和 FB40 CEE4 FB40 D48C 0000 0020 0000 0020 0000 0000 0002 0000 0002 0000 ㋿ FB40 CEE4 FB40 D48C 0000 0020 0000 0020 0000 0000 001C 0000 001C 0000 29
30. 濁点 / 半濁点 / 平仮名 / 片仮名 • さ : [.47EF.0020.000E] • サ : [.47EF.0020.0011] • ざ : [.47EF.0020.000E][.0000.0037.0002] weight の 1つめの 0000 は無視される 文字列 ソートキー さる 47EF 480F 0000 0020 0020 0000 000E 000E サル 47EF 480F 0000 0020 0020 0000 0011 0011 ざる 47EF 480F 0000 0020 0037 0020 0000 000E 0002 000E さん 47EF 4817 0000 0020 0020 0000 000E 000E 30
31. MySQL weight_string() MySQLでは weight_string() を使ってソートキーを得られる mysql> SELECT weight_string('abc' COLLATE utf8mb4_0900_as_cs); +------------------------------------------------------------------ | weight_string('abc' COLLATE utf8mb4_0900_as_cs) +------------------------------------------------------------------ | 0x1C471C601C7A00000020002000200000000200020002 +------------------------------------------------------------------ 31
32. MySQLの「㍻」 ai_ci では同じ mysql> SET names utf8mb4 COLLATE utf8mb4_0900_ai_ci; mysql> SELECT weight_string('平成'); +--------------------------------------------------+ | weight_string('平成') | +--------------------------------------------------+ | 0xFB40DE73FB40E210 | +--------------------------------------------------+ mysql> SELECT weight_string('㍻'); +--------------------------------------------+ | weight_string('㍻') | +--------------------------------------------+ | 0xFB40DE73FB40E210 | +--------------------------------------------+ 32
33. MySQLの「㍻」 as_cs でもだいたい同じ mysql> SET names utf8mb4 COLLATE utf8mb4_0900_as_cs; mysql> SELECT weight_string('平成'); +--------------------------------------------------+ | weight_string('平成') | +--------------------------------------------------+ | 0xFB40DE73FB40E210000000200020000000020002 | +--------------------------------------------------+ mysql> SELECT weight_string('㍻'); +--------------------------------------------+ | weight_string('㍻') | +--------------------------------------------+ | 0xFB40DE73FB40E2100000002000200000001C001C | +--------------------------------------------+ 1 row in set (0.000 sec) 33
34. MySQLの「㋿」 ソートキーが全然違う Unicode 9 にはまだ「㋿」がなかったため MySQL はまだ平成 mysql> SELECT weight_string('令和'); +--------------------------------------------------+ | weight_string('令和') | +--------------------------------------------------+ | 0xFB40CEE4FB40D48C | +--------------------------------------------------+ mysql> SELECT weight_string('㋿'); +--------------------------------------------+ | weight_string('㋿') | +--------------------------------------------+ | 0xFBC0B2FF | +--------------------------------------------+ 34
35. MySQLのコレーションの ai/as と ci/cs • ai_ci は weight の1つめの要素だけを使う • as_ci は weight の1つめと2つめの要素を使う • as_cs は weight のすべての要素を使う 35
36. Unicode 9 の DUCET より 「さ」「サ」「ざ」 3055 ; [.3D65.0020.000E] # HIRAGANA LETTER SA 30B5 ; [.3D65.0020.0011] # KATAKANA LETTER SA 3056 ; [.3D65.0020.000E][.0000.0037.0002] # HIRAGANA LETTER ZA 36
37. ai_ci 3055 ; [.3D65 ] # HIRAGANA LETTER SA 30B5 ; [.3D65 ] # KATAKANA LETTER SA 3056 ; [.3D65 ][.0000 ] # HIRAGANA LETTER ZA mysql> select s,weight_string(s collate utf8mb4_0900_ai_ci) w from t; +------+------------+ | s | w | +------+------------+ | さ | 0x3D65 | | サ | 0x3D65 | | ざ | 0x3D65 | +------+------------+ 37
38. as_ci 3055 ; [.3D65.0020 ] # HIRAGANA LETTER SA 30B5 ; [.3D65.0020 ] # KATAKANA LETTER SA 3056 ; [.3D65.0020 ][.0000.0037 ] # HIRAGANA LETTER ZA mysql> select s,weight_string(s collate utf8mb4_0900_as_ci) w from t; +------+--------------------+ | s | w | +------+--------------------+ | さ | 0x3D6500000020 | | サ | 0x3D6500000020 | | ざ | 0x3D65000000200037 | +------+--------------------+ 38
39. as_cs 3055 ; [.3D65.0020.000E] # HIRAGANA LETTER SA 30B5 ; [.3D65.0020.0011] # KATAKANA LETTER SA 3056 ; [.3D65.0020.000E][.0000.0037.0002] # HIRAGANA LETTER ZA mysql> select s,weight_string(s collate utf8mb4_0900_as_cs) w from t; +------+--------------------------------+ | s | w | +------+--------------------------------+ | さ | 0x3D65000000200000000E | | サ | 0x3D650000002000000011 | | ざ | 0x3D650000002000370000000E0002 | +------+--------------------------------+ 39
40. 異なる文字でも同じ weight 結構ある たとえば 0 と 〇 0030 ; [.217D.0020.0002] # DIGIT ZERO 3007 ; [.217D.0020.0002] # IDEOGRAPHIC NUMBER ZERO 40
41. 文字列の一致 41
42. 文字列の一致 数値では A <= B と A >= B が成り立つ場合は A = B 文字列でも A <= B と A >= B が成り立つ場合は A = B つまり文字列のソート順が同じ場合は一致 文字列の一致はコレーションに依存 42
43. MySQL mysql> SET names utf8mb4 COLLATE utf8mb4_0900_ai_ci; mysql> SELECT 'abc'='ABC'; +-------------+ | 'abc'='ABC' | +-------------+ | 1 | +-------------+ mysql> SET names utf8mb4 COLLATE utf8mb4_0900_as_cs; mysql> SELECT 'abc'='ABC'; +-------------+ | 'abc'='ABC' | +-------------+ | 0 | +-------------+ 43
44. MySQL mysql> SET names utf8mb4 COLLATE utf8mb4_0900_as_cs; mysql> SELECT '0'='〇'; +-----------+ | '0'='〇' | +-----------+ | 1 | +-----------+ 44
45. PostgreSQL 同じソート順でも不一致 PostgreSQL は Unicode でのソート順が同じ場合はコードポイントで比較する なので常に 0 < 〇 postgres=# SELECT '0'='〇'; ?column? ---------- f 45
46. コレーションの作成 PostgreSQL では動的にコレーションを作成可能 ソート順が同じ場合は等しくなるというコレーションも作れる deterministic=false: ソート順が同じ場合にコードポイントによる比較をしない postgres=# CREATE COLLATION hoge (provider=icu, locale='und', deterministic=false); CREATE COLLATION postgres=# SELECT '0' COLLATE hoge = '〇' COLLATE hoge; ?column? ---------- t 46
47. MySQLみたいに大文字小文字を区別しないコレーションも作れる und-u: 言語未指定Unicode ks-level2: weightの2番目までを使って3番目を無視 MySQL の as_ci と同じ postgres=# CREATE COLLATION ci (provider=icu, locale='und-u-ks-level2', deterministic=false); CREATE COLLATION postgres=# SELECT 'abc' COLLATE ci = 'ABC' COLLATE ci; ?column? ---------- t (1 row) 47
48. PostgreSQL のコレーションを調べてみて • MySQL よりも PostgreSQL の方が柔軟 • デフォルトで異なる文字が = で一致しない方が感覚にあってる • コレーションを動的に作れるのも良い 48
49. 日本語のコレーション 49
50. 日本語のコレーション • MySQL は utf8mb4_ja_0900_as_cs • PostgreSQL は ja-x-icu 50
51. 結構違う unicode ja-x-icu ORDER BY s COLLATE unicode 以 宇 安 於 永 ORDER BY s COLLATE "ja-x-icu" 安 以 宇 永 於 ja ロケールでは漢字はJISコード順！ JIS第１水準文字は音読みの順に並んでる https://github.com/unicode-org/cldr/blob/main/common/collation/ja.xml 51
52. 元号の合字は元号順に並ぶ！！ unicode ja-x-icu MySQLはこうはならない Unicode 9 の頃はこのルールはなかったので 令和 ㋿ 大正 ㍽ 平成 ㍻ 明治 ㍾ 昭和 ㍼ ㍾ ㍽ ㍼ ㍻ ㋿ 昭和 大正 平成 明治 令和 52
53. 長音記号 unicode ja-x-icu かー かあ かい かう きー きあ きい きう くー くあ くい くう かー かあ かい かう きあ きー きい きう くあ くい くー くう 前の文字の母音に従う！！！ やりすぎ感 53
54. ja のソート(JIS)の順番なんていつ使うの？ 国語辞書とか電話帳の並び順 54
55. Ruby で Unicode のソート 55
56. ICU ライブラリを使えばできる ffi-icu gem が簡単 % gem insttall ffi-icu 56
57. ICU::Collation::Collator#compare 文字列を UCA で比較 require 'ffi-icu' und_collator = ICU::Collation::Collator.new('und') # 言語未指定 ja_collator = ICU::Collation::Collator.new('ja') und_collator.compare('安', '以') #=> 1 ja_collator.compare('安', '以') #=> -1 57
58. ICU::Collation::Collator#collation_key(str) UCA ソートキーを得る # 標準入力から読み込んだ行を UCA でソートする require 'ffi-icu' collator = ICU::Collation::Collator.new(nil) # OSのロケール lines = ARGF.readlines puts lines.sort_by{collator.collation_key(it)} % LC_ALL=ja_JP.utf8 sort hoge.txt AAA ABC aaa abc きー きあ きい % LC_ALL=ja_JP.utf8 ruby unisort.rb hoge.txt aaa AAA abc ABC きあ きー きい 58
59. おわり • ユニコードのソートはかなり複雑 • RDBの コレーションが期待するものになってるか気にしよう • PostgreSQL の方が MySQL よりもよさそう • Ruby でユニコード規格のソートもできるよ ネコチャン絵文字 ©しかまつ https://note.com/shikamatsu/n/nd217dc0617db 59