MySQLとPostgreSQLのコレーション / Collation of MySQL and PostgreSQL

HTML版はこちら https://tmtms.net/slides/20251219-myna/

Transcript

1. MySQLとPostgreSQLのコレーション 2025-12-19 MyNA 望年LT大会2025＠新宿 とみたまさひろ 1
2. 自己紹介 • とみたまさひろ • • • 長野県北部在住 • 得意技は Ruby と文字化け • ここ4年くらい仕事では PostgreSQL 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. コレーション 日本語だと「照合順序」 6
7. コレーションの指定方法 • 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 7
8. MySQLのコレーション • MySQL組み込み • 実行環境には依存しない • Unicode 9 ベース • 最新は Unicode 17 なので結構古い • SHOW COLLATION で一覧できる • 全部で286個 / utf8mb4 のコレーションは 89個 • デフォルトはアクセント記号や大文字小文字等を無視 8
9. 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 | +----------------------+--------------------+ 9
10. PostgreSQLのコレーション • デフォルトではOSのコレーションなので環境に依存 • Unicode ベースのコレーションも選択可 • 外部ライブラリの libicu で対応 • 最新の Unicode 17 まで対応 • SELECT * FROM pg_collation で一覧できる • 全部で917個(環境による) • Unicode のコレーションは 871個(環境による) 10
11. 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 | 11
12. 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 12
13. 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 13
14. Unicode のコレーション 14
15. Unicode Collation Algorithm (UCA) Unicode の照合順序のアルゴリズム https://unicode.org/reports/tr10/ 15
16. PostgreSQL で UCA を使いたい場合は unicode コレーションを使う SELECT s FROM t ORDER BY s COLLATE unicode 123 １２３ ①②③ 1234 １２３４ ①②③④ aaa AAA abc ABC 令和 ㋿ 平成 ㍻ 昭和 ㍼ 16
17. 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 17
18. 文字ごとではなく文字列全体のソートキーを求める 文字列中の文字の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 18
19. 文字列のソートキーでソートする 文字列 ソートキー 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 19
20. 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 20
21. 合字のソートも同様 漢字の 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 21
22. 濁点 / 半濁点 / 平仮名 / 片仮名 • さ : [.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 22
23. MySQL weight_string() MySQLでは weight_string() を使ってソートキーを得られる PostgreSQL には同等の機能はないっぽい mysql> SELECT weight_string('abc' COLLATE utf8mb4_0900_as_cs); +------------------------------------------------------------------ | weight_string('abc' COLLATE utf8mb4_0900_as_cs) +------------------------------------------------------------------ | 0x1C471C601C7A00000020002000200000000200020002 +------------------------------------------------------------------ 23
24. 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 | +--------------------------------------------+ 24
25. 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) 25
26. MySQLの「㋿」 ソートキーが全然違う Unicode 9 にはまだ「㋿」がなかったため(12.1 で入った) MySQL はまだ平成 mysql> SELECT weight_string('令和'); +--------------------------------------------------+ | weight_string('令和') | +--------------------------------------------------+ | 0xFB40CEE4FB40D48C | +--------------------------------------------------+ mysql> SELECT weight_string('㋿'); +--------------------------------------------+ | weight_string('㋿') | +--------------------------------------------+ | 0xFBC0B2FF | +--------------------------------------------+ 26
27. MySQLのコレーションの ai/as と ci/cs • ai_ci は weight の1つめの要素だけを使う • as_ci は weight の1つめと2つめの要素を使う • as_cs は weight のすべての要素を使う 27
28. 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 28
29. 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 | +------+------------+ 29
30. 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 | +------+--------------------+ 30
31. 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 | +------+--------------------------------+ 31
32. 異なる文字でも同じ weight 結構ある たとえば 0 と 〇 0030 ; [.217D.0020.0002] # DIGIT ZERO 3007 ; [.217D.0020.0002] # IDEOGRAPHIC NUMBER ZERO 32
33. 文字列の一致 33
34. 文字列の一致 数値では A <= B と A >= B が成り立つ場合は A = B 文字列でも A <= B と A >= B が成り立つ場合は A = B つまり文字列のソート順が同じ場合は一致 文字列の一致はコレーションに依存 34
35. 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 | +-------------+ 35
36. MySQL mysql> SET names utf8mb4 COLLATE utf8mb4_0900_as_cs; mysql> SELECT '0'='〇'; +-----------+ | '0'='〇' | +-----------+ | 1 | +-----------+ 36
37. PostgreSQL 同じ weight 値でも不一致 PostgreSQL は weight 値が同じ場合はコードポイントで比較する なので常に 0 < 〇 postgres=# SELECT '0'='〇'; ?column? ---------- f 37
38. コレーションの作成 PostgreSQL では動的にコレーションを作成可能 weight 値が同じ場合は等しくなるというコレーションも作れる deterministic=false: weight 値が同じ場合にコードポイントによる比較をしない postgres=# CREATE COLLATION hoge (provider=icu, locale='und', deterministic=false); CREATE COLLATION postgres=# SELECT '0' COLLATE hoge = '〇' COLLATE hoge; ?column? ---------- t 38
39. 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) 39
40. PostgreSQL のコレーションを調べてみて • 最新の Unicode バージョンに対応してる • デフォルトで異なる文字が = で一致しない方が感覚にあってる • コレーションを動的に作れるのも良い • MySQL よりも PostgreSQL の方がよさそう ネコチャン絵文字 ©しかまつ https://note.com/shikamatsu/n/nd217dc0617db 40