以下の内容はhttps://blog.tmtms.net/entry/202512-mysql-postgresql-collationより取得しました。

MySQL と PostgreSQL のコレーション

MySQL PostgreSQL

これは MySQL Advent Calendar 2025 の 22日目の記事です。

qiita.com

12/19 に MyNA 望年LT大会があって、そこで「MySQLとPostgreSQLのコレーション」という発表をした。

speakerdeck.com

今年は何回か似たようなネタで発表してて、

  • 2025-06-17 某クローズド勉強会
  • 2025-06-20 SmartHR LT大会
  • 2025-08-09 Nagano.rb
  • 2025-08-18 Software Design 9月号「データベースにおける文字コードの落とし穴」
  • 2025-12-13 NSEG + Nagano.rb
  • 2025-12-19 MyNA 望年LT大会

今回が今年最後。

NSEG + Nagano.rb の発表は 25分くらい掛かったんだけど、今回は制限時間5分以内ってことで削ってはみたんだけど、まあ5分じゃ終わらないよね。15分くらい掛かった。

Software Design の記事を書くに当たって PostgreSQL のコレーションについても調べてみたんで、MySQL と比較してみた。

コレーション

RDB で文字列をソートしたときの並び順は、文字コード順ではなくてコレーションによって決まる。

コレーションは CREATE TABLE 時にカラムごとに指定する。MySQL でも PostgreSQL でも可。

CREATE TABLE t (s VARCHAR COLLATE unicode)

MySQLの場合はテーブル単位でも指定可。そのテーブル内の文字列カラムのデフォルトのコレーションになる。

CREATE TABLE t (s VARCHAR(1000)) COLLATE utf8mb4_0900_as_cs

SELECT 時に動的に指定することも可能。

クエリ内の文字列や文字列カラムの後ろに COLLATE コレーション名 を指定する。

SELECT * FROM t ORDER BY s COLLATE unicode

ただし、この場合はたぶんインデックスは効かないので注意。

MySQLのコレーション

  • MySQL組み込みなので実行環境には依存しない
  • Unicode 9 ベース。最新は Unicode 17 なので結構古い
  • SHOW COLLATION で一覧できる
  • デフォルトは ai_ci で、アクセント記号や大文字小文字等を無視
  • サーバー、データベース、テーブル、カラムごとに設定できる
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 |
+----------------------+--------------------+

PostgreSQLのコレーション

  • デフォルトではOSのコレーションなので環境に依存
  • 外部ライブラリの libicu により Unicode のコレーションも選択可
  • 最新の Unicode 17 まで対応。新しい。
  • カラムごとに設定できる
  • デフォルトのコレーションは psql の \l で見れる
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 |

Locale Provider が libc の場合は OS に依存

Ubuntu で試したやつ

en_US.utf8 の場合:

  name  |         byte
--------+----------------------
 123    | \x313233
 １２３ | \xefbc91efbc92efbc93
 456    | \x343536
 ４５６ | \xefbc94efbc95efbc96
 aaa    | \x616161
 AAA    | \x414141
 abc    | \x616263
 ABC    | \x414243
 あいう | \xe38182e38184e38186
 日本語 | \xe697a5e69cace8aa9e

ja_JP.utf8 の場合: 文字コード順ぽいけどちょっと違う

  name  |         byte
--------+----------------------
 123    | \x313233
 456    | \x343536
 AAA    | \x414141
 ABC    | \x414243
 aaa    | \x616161
 abc    | \x616263
 １２３ | \xefbc91efbc92efbc93
 ４５６ | \xefbc94efbc95efbc96
 あいう | \xe38182e38184e38186
 日本語 | \xe697a5e69cace8aa9e

C の場合: 文字コード順

  name  |         byte
--------+----------------------
 123    | \x313233
 456    | \x343536
 AAA    | \x414141
 ABC    | \x414243
 aaa    | \x616161
 abc    | \x616263
 あいう | \xe38182e38184e38186
 日本語 | \xe697a5e69cace8aa9e
 １２３ | \xefbc91efbc92efbc93
 ４５６ | \xefbc94efbc95efbc96

Unicode のコレーション

aaa, AAA, abc, ABC の順に並ぶ仕組み

Unicode のコレーションの仕組みは、Unicode Collation Algorithm (UCA) にすべて書かれてる、けど長い。自分も全部は読めてない。

Unicode では文字ごとに weight という値を持っている。weight は3つの値からなる。Unicode のバージョンごとに値は異なる。この例は Unicode 16 のもの。

  • a : [.2380.0020.0002]
  • A : [.2380.0020.0008]
  • b : [.239A.0020.0002]
  • B : [.239A.0020.0008]
  • c : [.23B4.0020.0002]
  • C : [.23B4.0020.0008]

眺めてると、なんとなく１つめの値が基底文字で、３つめの値が大文字小文字を表しているっぽいことがわかる。

でも、この weight 値のままソートしても、aaa, abc, AAA, ABC の順になっちゃう。 実は Unicode の文字列のソートは、文字単位ではなく文字列全体でソートする。

文字列中の文字のweightの1番目の値、2番目の値、3番目の値をまとめて 0000 で連結した値をソートキーとする（1番目の値が 0000 の場合は無視される）。

a                 b                 c
[.2380.0020.0002] [.239A.0020.0002] [.23B4.0020.0002]
  a1   a2   a3      b1   b2   b3      c1   c2   c3
↓

2380 239A 23B4 0000 0020 0020 0020 0000 0002 0002 0002
a1   b1   c1   |    a2   b2   c2   |    a3   b3   c3

そうするとこのようにソートされる:

文字列 ソートキー
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

全角半角や丸囲みなど

大文字小文字だけでなく、全角半角や丸囲みも weight の３番目の値で表されてる。

たとえば 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

漢字と合字

文字の weight 値は、Default Unicode Collation Element Table (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

だいたい同じ値となって、「令和」と「㋿」は似たような位置に並ぶように出来てる。

MySQL でソートキーを得る

MySQLでは weight_string() を使ってソートキーを得られる:

mysql> SELECT weight_string('abc' COLLATE utf8mb4_0900_as_cs);
+------------------------------------------------------------------
| weight_string('abc' COLLATE utf8mb4_0900_as_cs)             
+------------------------------------------------------------------
| 0x1C471C601C7A00000020002000200000000200020002              
+------------------------------------------------------------------

PostgreSQL には同等の機能はないっぽい。

MySQLのコレーションの ai/as と ci/cs について

MySQL の Unicode コレーション名は ai_ci とか as_ci とか as_cs とかのサフィックスがついてるけど、これは次のような意味である。

  • ai_ci は weight の1つめの要素だけを使う
  • as_ci は weight の1つめと2つめの要素を使う
  • as_cs は weight のすべての要素を使う

つまりこれらは weight の何個目までの要素を使うかを示すものなので、ai_cs というのが存在しない理由もわかる。

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

ai_ci コレーションでソートキーを調べると、1つめの要素だけを使ってることがわかる。

mysql> select s,weight_string(s collate utf8mb4_0900_ai_ci) w from t;
+------+------------+
| s    | w          |
+------+------------+
| さ   | 0x3D65     |
| サ   | 0x3D65     |
| ざ   | 0x3D65     |
+------+------------+

as_ci コレーションのも同様に、1つめと2つめの要素を使う。

mysql> select s,weight_string(s collate utf8mb4_0900_as_ci) w from t;
+------+--------------------+
| s    | w                  |
+------+--------------------+
| さ   | 0x3D6500000020     |
| サ   | 0x3D6500000020     |
| ざ   | 0x3D65000000200037 |
+------+--------------------+

as_cs コレーションは全部の要素を使う。

mysql> select s,weight_string(s collate utf8mb4_0900_as_cs) w from t;
+------+--------------------------------+
| s    | w                              |
+------+--------------------------------+
| さ   | 0x3D65000000200000000E         |
| サ   | 0x3D650000002000000011         |
| ざ   | 0x3D650000002000370000000E0002 |
+------+--------------------------------+

MySQL の「㍻」と「㋿」

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                         |
+--------------------------------------------+

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)

ところが「令和」と「㋿」を比べるとソートキーが全然違う。

mysql> SELECT weight_string('令和');
+--------------------------------------------------+
| weight_string('令和')                            |
+--------------------------------------------------+
| 0xFB40CEE4FB40D48C                               |
+--------------------------------------------------+
mysql> SELECT weight_string('㋿');
+--------------------------------------------+
| weight_string('㋿')                         |
+--------------------------------------------+
| 0xFBC0B2FF                                 |
+--------------------------------------------+

これは Unicode 9 にはまだ「㋿」がなかったため。(「㋿」は Unicode 12.1 で入った) つまり MySQL はまだ平成。

同じ weight の文字

DUCET を見ると同じ weight の文字が結構あるのがわかる。

たとえば 0 と 〇(漢数字ゼロ):

0030  ; [.217D.0020.0002] # DIGIT ZERO
3007  ; [.217D.0020.0002] # IDEOGRAPHIC NUMBER ZERO

文字列の一致

数値では A <= B と A >= B が成り立つ場合は A = B になる。

文字列でも同じ考え方を適用すると、文字列のソート順が同じ場合は一致するということになる。

つまり文字列の一致はコレーションに依存するということ。

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 |
+-------------+

0 と 〇 は as_cs でも同じになる。

mysql> SET names utf8mb4 COLLATE utf8mb4_0900_as_cs;
mysql> SELECT '0'='〇';
+-----------+
| '0'='〇'  |
+-----------+
|         1 |
+-----------+

ところが PostgreSQL は同じ weight 値でも不一致になる。

postgres=# SELECT '0'='〇';
 ?column? 
----------
 f

PostgreSQL は weight 値が同じ場合はコードポイントで比較することになってるらしい。なので常に 0 < 〇 となる。

PostgreSQL でコレーションの作成

PostgreSQL では動的にコレーションを作成できる。MySQL はできない。PostgreSQL いいな。

MySQL と同じように weight 値が同じ場合は等しくなるというコレーションも作れる。

postgres=# CREATE COLLATION hoge (provider=icu, locale='und', deterministic=false);
CREATE COLLATION

postgres=# SELECT '0' COLLATE hoge = '〇' COLLATE hoge;
 ?column? 
----------
 t

deterministic=false の指定で weight 値が同じ場合にコードポイントによる比較をしないとなる。

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)

und-u は言語未指定 Unicode のことで、ks-level2 は weight の2番目までを使って3番目を無視する（MySQL の as_ci と同じ）。

おわり

PostgreSQL のコレーションは、

  • 最新の Unicode バージョンに対応してる
  • デフォルトで異なる文字が = で一致しない方が感覚にあってる
  • コレーションを動的に作れるのも良い

ということで MySQL よりも PostgreSQL の方がよさそう。



以上の内容はhttps://blog.tmtms.net/entry/202512-mysql-postgresql-collationより取得しました。
このページはhttp://font.textar.tv/のウェブフォントを使用してます
不具合報告/要望等はこちらへお願いします。
モバイルやる夫Viewer Ver0.14