この記事でわかること

文字セット、照合順序の定義
照合順序の命名規則
照合パット属性の定義
SQLステートメントでのCOLLATEの使用
COLLATE 句の優先順位
文字セットと照合順序の互換性
式での照合の強制性

1. 背景

業務でtableのjoin時のカラム値の比較で照合順序が異なる(utf8mb4_generalと
utf8mb4_0900_ai_ci)ことによるエラーが起きました
さっぱり分からないので照合順序についてざっと理解しようと思います。

こちらのMySQLのDocumentを参考にします。

2. 一般の文字セットおよび照合順序

文字セット

記号とエンコーディングの組合せの集合を文字セットと言います
例）
4文字のアルファベットがあるとします。
A, B, a, b
↓
各文字に数字を付けます
A = 0, B = 1, a = 2, b = 3
文字Aは記号、数字0はA用のエンコーディングといいます

照合順序

文字セット内の文字を比較するためのルールの集合のことです。

例1） AとBの2つの文字列の値を比較する

AとBの2つの文字列の値を比較するとします。
これを行う最も簡単な方法は、エンコーディング（文字に割り当てられた数値）を確認することです
0 (A の場合)、1 (B の場合)。
0は1より小さいため、AはBより小さいと言います。
今ここで行なったのは、文字セットに対する照合順序の適用です。
この場合の照合順序は「エンコーディングの比較」の1つだけになります。
これは可能な照合順序のうちでもっとも単純なものであり、バイナリ照合順序と呼ばれています。

例2）大文字と小文字を区別しない

大文字と小文字を区別しない照合順序について説明します。
少なくとも2つのルールがあります。
(1) 小文字の a および b を A および B と同等の文字として扱う
(2) エンコーディングを比較
これは大文字と小文字を区別しない照合順序と呼ばれます。

その他のルール

アクセント (「アクセント」はドイツ語Öのように文字に添付されたマーク)
複数文字マッピング (ドイツ語照合のいずれかでÖ = OE となるルールなど)

3. MySQLの環境構築

環境構築

環境構築はこちらを参考にしました

.
├── docker-compose.yml
└── mysql
    └── Dockerfile

docker-mysql/docker-compose.yml

# versionは3系が最新版で、versionによって書き方が異なる
version: "3"
services:
  mysql:
    build: ./mysql    #Dockerfileからビルドすることを示す
    image: original_mysql # イメージの名前
    environment:
      - MYSQL_ROOT_PASSWORD=root   #コンテナ内のMySQLを起動する際のパスワードを設定

dockerhub mysqlのdoc
docker-composeファイルのdoc

docker-mysql/mysql/Dockerfile

#使うDockerイメージ
FROM mysql

#ポートを開ける
EXPOSE 3306

#docker runに実行される
CMD ["mysqld"]

起動

# イメージのビルド
$ docker-compose build

# コンテナの作成
$ Docker-Compose up -d

# 起動したコンテナにログイン
$ docker exec -it docker-mysql-mysql-1 /bin/sh

# コンテ内でMySQLを起動
sh-4.4# mysql -u root -proot -h 127.0.0.1

4. MySQLでの文字セットと照合順序

MySQLでは以下が可能です。

さまざまな文字セットを使用して文字列を格納
さまざまな照合順序を使用して文字列を比較
文字セットまたは照合順序が異なる文字列を、同じサーバー、同じデータベース、または同じテーブル内にも混在させる
どのレベルでも文字セットと照合順序を指定

MySQLでサポートされている複数のUnicode文字セット

Unicodeとは

世界の様々な言語、書式、記号に、番号を割り当てて定義した標準の(文字コード Character set )のことです

文字コードとは

コンピューターに文字(文字、数字、区切り記号、空白文字などを含む)を理解させるための符号化システムのことです

使用可能な文字セットとそのデフォルトの照合順序を表示する

SHOW CHARACTER SET

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

INFORMATION_SCHEMA CHARACTER_SETS テーブル

利用できる文字セットに関する情報を提供します。

mysql> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                     | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5               | big5_chinese_ci      | Big5 Traditional Chinese        |      2 |
| dec8               | dec8_swedish_ci      | DEC West European               |      1 |
| cp850              | cp850_general_ci     | DOS West European               |      1 |
| hp8                | hp8_english_ci       | HP West European                |      1 |
| koi8r              | koi8r_general_ci     | KOI8-R Relcom Russian           |      1 |
| latin1             | latin1_swedish_ci    | cp1252 West European            |      1 |
| latin2             | latin2_general_ci    | ISO 8859-2 Central European     |      1 |
| swe7               | swe7_swedish_ci      | 7bit Swedish                    |      1 |
| ascii              | ascii_general_ci     | US ASCII                        |      1 |
| ujis               | ujis_japanese_ci     | EUC-JP Japanese                 |      3 |
| sjis               | sjis_japanese_ci     | Shift-JIS Japanese              |      2 |
| hebrew             | hebrew_general_ci    | ISO 8859-8 Hebrew               |      1 |
| tis620             | tis620_thai_ci       | TIS620 Thai                     |      1 |
| euckr              | euckr_korean_ci      | EUC-KR Korean                   |      2 |
| koi8u              | koi8u_general_ci     | KOI8-U Ukrainian                |      1 |
| gb2312             | gb2312_chinese_ci    | GB2312 Simplified Chinese       |      2 |
| greek              | greek_general_ci     | ISO 8859-7 Greek                |      1 |
| cp1250             | cp1250_general_ci    | Windows Central European        |      1 |
| gbk                | gbk_chinese_ci       | GBK Simplified Chinese          |      2 |
| latin5             | latin5_turkish_ci    | ISO 8859-9 Turkish              |      1 |
| armscii8           | armscii8_general_ci  | ARMSCII-8 Armenian              |      1 |
| utf8mb3            | utf8mb3_general_ci   | UTF-8 Unicode                   |      3 |
| ucs2               | ucs2_general_ci      | UCS-2 Unicode                   |      2 |
| cp866              | cp866_general_ci     | DOS Russian                     |      1 |
| keybcs2            | keybcs2_general_ci   | DOS Kamenicky Czech-Slovak      |      1 |
| macce              | macce_general_ci     | Mac Central European            |      1 |
| macroman           | macroman_general_ci  | Mac West European               |      1 |
| cp852              | cp852_general_ci     | DOS Central European            |      1 |
| latin7             | latin7_general_ci    | ISO 8859-13 Baltic              |      1 |
| cp1251             | cp1251_general_ci    | Windows Cyrillic                |      1 |
| utf16              | utf16_general_ci     | UTF-16 Unicode                  |      4 |
| utf16le            | utf16le_general_ci   | UTF-16LE Unicode                |      4 |
| cp1256             | cp1256_general_ci    | Windows Arabic                  |      1 |
| cp1257             | cp1257_general_ci    | Windows Baltic                  |      1 |
| utf32              | utf32_general_ci     | UTF-32 Unicode                  |      4 |
| binary             | binary               | Binary pseudo charset           |      1 |
| geostd8            | geostd8_general_ci   | GEOSTD8 Georgian                |      1 |
| cp932              | cp932_japanese_ci    | SJIS for Windows Japanese       |      2 |
| eucjpms            | eucjpms_japanese_ci  | UJIS for Windows Japanese       |      3 |
| gb18030            | gb18030_chinese_ci   | China National Standard GB18030 |      4 |
| utf8mb4            | utf8mb4_0900_ai_ci   | UTF-8 Unicode                   |      4 |
+--------------------+----------------------+---------------------------------+--------+
41 rows in set (0.00 sec)

例）utf8mb4のデフォルトの照合順序はutf8mb4_0900_ai_ciです
utf8mb4: Unicode文字セットのUTF-8エンコーディングです
0900:Unicodeのバージョン 9.00を示しています
文字ごとに1~4バイトを使用します

照合順序名の命名規則

照合順序名は、関連付けられている文字セットの名前で始まり、通常は、他の照合順序特性を示す1つ以上の接尾辞(照合サフィックス)が続きます
例） utf8mb4_general_ci
文字セットはutf8mb4で大文字小文字を区別しない(ci:Case Insensitive)照合順序を示します

照合サフィックスの例

サフィックス	意味
_ai(Accent Insensitive)	アクセントを区別しない
_as(Accent Sensitive)	アクセントを区別する
_ci(Case Insensitive)	大文字小文字を区別しない
_cs(Case Sensitive)	大文字小文字を区別しない
_cs(Case Sensitive)	大文字小文字を区別しない
_ks(Kana Sensitive)	カナを区別する
_bin(Binary)	数値バイト値で区別する

照合の命名規則
 バイナリ照合順序と_bin 照合順序

照合パッド属性

文字列の末尾のスペースは他の文字と同様に扱われます

例）　文字セットがutf8mb4の照合順序と照合パッド属性

mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE
    ->        FROM INFORMATION_SCHEMA.COLLATIONS
    ->        WHERE CHARACTER_SET_NAME = 'utf8mb4';
+----------------------------+---------------+
| COLLATION_NAME             | PAD_ATTRIBUTE |
+----------------------------+---------------+
| utf8mb4_general_ci         | PAD SPACE     |
| utf8mb4_bin                | PAD SPACE     |
| utf8mb4_unicode_ci         | PAD SPACE     |
| utf8mb4_icelandic_ci       | PAD SPACE     |
| utf8mb4_latvian_ci         | PAD SPACE     |
| utf8mb4_romanian_ci        | PAD SPACE     |
| utf8mb4_slovenian_ci       | PAD SPACE     |
| utf8mb4_polish_ci          | PAD SPACE     |
| utf8mb4_estonian_ci        | PAD SPACE     |
| utf8mb4_spanish_ci         | PAD SPACE     |
| utf8mb4_swedish_ci         | PAD SPACE     |
| utf8mb4_turkish_ci         | PAD SPACE     |
| utf8mb4_czech_ci           | PAD SPACE     |
| utf8mb4_danish_ci          | PAD SPACE     |
| utf8mb4_lithuanian_ci      | PAD SPACE     |
| utf8mb4_slovak_ci          | PAD SPACE     |
| utf8mb4_spanish2_ci        | PAD SPACE     |
| utf8mb4_roman_ci           | PAD SPACE     |
| utf8mb4_persian_ci         | PAD SPACE     |
| utf8mb4_esperanto_ci       | PAD SPACE     |
| utf8mb4_hungarian_ci       | PAD SPACE     |
| utf8mb4_sinhala_ci         | PAD SPACE     |
| utf8mb4_german2_ci         | PAD SPACE     |
| utf8mb4_croatian_ci        | PAD SPACE     |
| utf8mb4_unicode_520_ci     | PAD SPACE     |
| utf8mb4_vietnamese_ci      | PAD SPACE     |
| utf8mb4_0900_ai_ci         | NO PAD        |
| utf8mb4_de_pb_0900_ai_ci   | NO PAD        |
| utf8mb4_is_0900_ai_ci      | NO PAD        |
| utf8mb4_lv_0900_ai_ci      | NO PAD        |
| utf8mb4_ro_0900_ai_ci      | NO PAD        |
| utf8mb4_sl_0900_ai_ci      | NO PAD        |
| utf8mb4_pl_0900_ai_ci      | NO PAD        |
| utf8mb4_et_0900_ai_ci      | NO PAD        |
| utf8mb4_es_0900_ai_ci      | NO PAD        |
| utf8mb4_sv_0900_ai_ci      | NO PAD        |
| utf8mb4_tr_0900_ai_ci      | NO PAD        |
| utf8mb4_cs_0900_ai_ci      | NO PAD        |
| utf8mb4_da_0900_ai_ci      | NO PAD        |
| utf8mb4_lt_0900_ai_ci      | NO PAD        |
| utf8mb4_sk_0900_ai_ci      | NO PAD        |
| utf8mb4_es_trad_0900_ai_ci | NO PAD        |
| utf8mb4_la_0900_ai_ci      | NO PAD        |
| utf8mb4_eo_0900_ai_ci      | NO PAD        |
| utf8mb4_hu_0900_ai_ci      | NO PAD        |
| utf8mb4_hr_0900_ai_ci      | NO PAD        |
| utf8mb4_vi_0900_ai_ci      | NO PAD        |
| utf8mb4_0900_as_cs         | NO PAD        |
| utf8mb4_de_pb_0900_as_cs   | NO PAD        |
| utf8mb4_is_0900_as_cs      | NO PAD        |
| utf8mb4_lv_0900_as_cs      | NO PAD        |
| utf8mb4_ro_0900_as_cs      | NO PAD        |
| utf8mb4_sl_0900_as_cs      | NO PAD        |
| utf8mb4_pl_0900_as_cs      | NO PAD        |
| utf8mb4_et_0900_as_cs      | NO PAD        |
| utf8mb4_es_0900_as_cs      | NO PAD        |
| utf8mb4_sv_0900_as_cs      | NO PAD        |
| utf8mb4_tr_0900_as_cs      | NO PAD        |
| utf8mb4_cs_0900_as_cs      | NO PAD        |
| utf8mb4_da_0900_as_cs      | NO PAD        |
| utf8mb4_lt_0900_as_cs      | NO PAD        |
| utf8mb4_sk_0900_as_cs      | NO PAD        |
| utf8mb4_es_trad_0900_as_cs | NO PAD        |
| utf8mb4_la_0900_as_cs      | NO PAD        |
| utf8mb4_eo_0900_as_cs      | NO PAD        |
| utf8mb4_hu_0900_as_cs      | NO PAD        |
| utf8mb4_hr_0900_as_cs      | NO PAD        |
| utf8mb4_vi_0900_as_cs      | NO PAD        |
| utf8mb4_ja_0900_as_cs      | NO PAD        |
| utf8mb4_ja_0900_as_cs_ks   | NO PAD        |
| utf8mb4_0900_as_ci         | NO PAD        |
| utf8mb4_ru_0900_ai_ci      | NO PAD        |
| utf8mb4_ru_0900_as_cs      | NO PAD        |
| utf8mb4_zh_0900_as_cs      | NO PAD        |
| utf8mb4_0900_bin           | NO PAD        |
| utf8mb4_nb_0900_ai_ci      | NO PAD        |
| utf8mb4_nb_0900_as_cs      | NO PAD        |
| utf8mb4_nn_0900_ai_ci      | NO PAD        |
| utf8mb4_nn_0900_as_cs      | NO PAD        |
| utf8mb4_sr_latn_0900_ai_ci | NO PAD        |
| utf8mb4_sr_latn_0900_as_cs | NO PAD        |
| utf8mb4_bs_0900_ai_ci      | NO PAD        |
| utf8mb4_bs_0900_as_cs      | NO PAD        |
| utf8mb4_bg_0900_ai_ci      | NO PAD        |
| utf8mb4_bg_0900_as_cs      | NO PAD        |
| utf8mb4_gl_0900_ai_ci      | NO PAD        |
| utf8mb4_gl_0900_as_cs      | NO PAD        |
| utf8mb4_mn_cyrl_0900_ai_ci | NO PAD        |
| utf8mb4_mn_cyrl_0900_as_cs | NO PAD        |
+----------------------------+---------------+
89 rows in set (0.01 sec)

dbを作って検証します

mysql> create database mydb;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mydb;
Database changed

utf8mb4_binの照合順序を持つカラムは照合パッド属性がPAD SPACEなので末尾にspaceがあっても同じ文字列とみなします。
先頭にスペースがある場合は別の文字列と認識されます。

mysql> CREATE TABLE t1 (c CHAR(10) COLLATE utf8mb4_bin);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES('a');'
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t1 WHERE c = 'a ';
+------+
| c    |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE c = ' a';
Empty set (0.00 sec)

照合パッド属性がNO PADである持つ非バイナリ文字列値 (CHAR、VARCHAR および TEXT) 、たとえば、'a'と'a 'は、同じ文字列ではなく異なる文字列として比較されます。

mysql> CREATE TABLE t2 (c CHAR(10) COLLATE utf8mb4_0900_bin);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t2 VALUES('a');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t2 WHERE c = 'a ';
Empty set (0.00 sec)

SQLステートメントでのCOLLATEの使用

COLLATE句では、比較に対するデフォルト照合順序が何であれ、照合順序をオーバーライドできます。

COLLATE 句の優先順位

COLLATE 句は|| より上位です。
以下は同じです。

x || y COLLATE z
x || (y COLLATE z)

文字セットと照合順序の互換性

mysql> SELECT _latin1 'x' COLLATE latin2_bin;
ERROR 1253 (42000): COLLATION 'latin2_bin' is not valid
for CHARACTER SET 'latin1'

文字セットLatin1の文字 'x' を文字セットLatin2のバイナリ照合順序で比較するように求めています。
latin2_bin 照合順序は latin1 文字セットに対して有効ではないのでエラーになります。

式での照合の強制性

SELECT x FROM T WHERE x = 'Y';

カラム x と文字列リテラル'Y'の比較を実行します
照合順序が異なる場合、比較で x の照合順序または'Y'の照合順序どちらが優先されるのかということになります。
MySQL では、一方のアイテムの照合を他方のアイテムの照合に強制できるかどうかをチェックします。 MySQL は次のように強制性値を割り当てます。

アイテム	強制性値
明示的な COLLATE 句	0
照合順序の異なる2つの文字列を連結	1
カラムまたはストアドルーチンのパラメータまたはローカル変数	2
「システム定数」 (USER() または VERSION() などの関数で返される文字列)	3
リテラル	4
数値または時間値	5
NULLまたはNULLから派生した式	6

ストアドルーチンとは、サーバーに格納できる一連のSQL文のことです

MySQLは、次のルールとともに強制性値を使用して、あいまいさを解決します。

強制性値がもっとも低い照合順序を使用します
両方の側で強制性値が同じ場合
- 両方の側がUnicodeであるか、両方の側がUnicodeではない場合はエラーになります
- どちらかの側にUnicode文字セットがあり、もう一方の側にUnicode以外の文字セットがある場合
  - Unicode文字セットの側が優先され、Unicode以外の側に自動文字セット変換が適用されます
  - たとえば、次のステートメントはエラーを返しません
    SELECT CONCAT(utf8mb4_column, latin1_column) FROM t1;
    utf8mb4 の文字セットと utf8mb4_column と同じ照合順序を持つ結果を返します。
    latin1_column の値は、連結する前に自動的に utf8mb4 に変換されます。
- 同じ文字セットのオペランドだが、_bin照合順序と _ciまたは_cs照合順序が混在したオペランドを使用した演算の場合
  - _bin照合順序が使用されます

COERCIBILITY() 関数で文字列式の強制性値を算出できます。

mysql> SELECT COERCIBILITY(_utf8'A' COLLATE utf8_bin);
        -> 0
mysql> SELECT COERCIBILITY(VERSION());
        -> 3
mysql> SELECT COERCIBILITY('A');
        -> 4
mysql> SELECT COERCIBILITY(1000);
        -> 5
mysql> SELECT COERCIBILITY(NULL);
        -> 6

その他

次回の記事で深掘りしようと思います。

5. join時にutf8mb4_0900_ai_ciとutf8mb4_general_ciの比較でエラーになった理由の個人的推測

今回は明示的なCOLLATE句を使っていません。
両方の側で強制性値が同じです。
同じ文字セットでどちらも_bin照合順序でないためかなと思っています。

6. 参考

7.最後に

SQLのことはさっぱりわかっていないのでひとつずつ勉強していきたいです

本記事を読んで頂き、ありがとうございました。
いいねいただけると記事執筆の励みになりますので、参考になったと思われた方は是非よろしくお願い致します🙏