文字列の並び順 / String Collation

2025-08-09 nagano.rb #19 で発表したスライドの前半部分

1. 文字列の並び順 2025-08-09 nagano.rb （…で発表したやつの前半） とみたまさひろ 1
2. 自己紹介 • とみたまさひろ • • • 最近アイコン変えた https://bsky.app/profile/tmtms.net https://blog.tmtms.net 2
3. MySQL徹底入門第5版 • 2025/6/16 発売！ • 初版は2001年発売 • 1割くらい書きました • 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. また文字コードの話をします 見どころは下にいるネコです 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. macOSのソートはアホで困る % cat a.txt 千葉県千葉市 千葉県市川市 千葉県柏市 東京都北区 東京都千代田区 東京都大田区 東京都江戸川区 東京都江東区 東京都港区 % sort a.txt 千葉県柏市 東京都北区 東京都港区 千葉県千葉市 千葉県市川市 東京都大田区 東京都江東区 東京都千代田区 東京都江戸川区 https://blog.tmtms.net/entry/202412-macos-sort 22
23. どうやら直ったらしい 「【朗報】macOS 15.4でsortコマンドのソート順がまともに修正されました！ 〜 マーズと マーキュリーの間にジュピターが割り込んでいた理由 #grep - Qiita」 https://qiita.com/ko1nksm/items/ce20e643bd83a72850b2 23
24. Unicode のコレーション 24
25. Unicode Collation Algorithm (UCA) Unicode の照合順序のアルゴリズム https://unicode.org/reports/tr10/ 25
26. 完全版は58ページあるけどとりあずここまで！ 続きは Software Design で！ ネコチャン絵文字 ©しかまつ https://note.com/shikamatsu/n/nd217dc0617db 26