mariadbでUTF８のC2A0とutf8mb4_uca1400_ai_ci
概要
UTF8にはノンブレーキングスペース、HTMLの と同じような半角スペース、コードで言うとC2A0が存在し、通常の半角スペース20と区別されています。
※ 余談ですがvscodeでC2A0は協調表示されるで気づきました。もしそうじゃなかったら気づかずにもっとハマったかも。
これをカラムに入れた時の挙動が微妙だったので共有しておきます。
mariadb11からutf8mb4_uca1400_ai_ciというcollationsが導入され、私の環境ではそれがデフォルトで選択されています。以前のバージョンではcharacter_set_collations自体がないのですが、設定なしとutf8mb4_uca1400_ai_ciの時の挙動も違ったので合わせて書いておきます。
現象
character_set_collationsが空の場合とcharacter_set_collationsがutf8mb4=utf8mb4_uca1400_ai_ciの場合両方試しました。
どちらも共通して下記の特徴があります。
-
SELECT UNHEX('C2A0') = UNHEX('20');は0になる。 - カラムに
C2A0と20を入れてSELECT ... WHERE name = UNHEX('20');あるいは= UNHEX('C2A0')すると両方引っかかる。
そして、character_set_collationsがutf8mb4=utf8mb4_uca1400_ai_ciの場合のみ
- ユニーク制約が付いていた場合、制約引っかかってINSERTできません。
先に書いたようにSELECT UNHEX('C2A0') = UNHEX('20');が０になるにもかかわらずユニーク制約に引っかかるのが面白いですね。
↑良く考えたらcollationsはカラムの照合順序設定なのでそうなりますね。別物だけどカラム照合やオーダーの時だけそう扱うと言うことです。
あと、utf8mb4_uca1400_ai_ciは全角半角の区別もしないので全角スペースE38080も同じ扱いになります。
経緯
今回この話題に行き着いたきっかけですが、運用中のDBのダンプを新しいubuntuのサーバーのDBに流し込んだ時、下記のエラーが出ました。
ERROR 1180 (HY000) at line 1474: Got error 1 "Operation not permitted" during COMMIT
エラーで検索すると下記のサイトが見つかります。
原因は違うようですね。何かの理由でSQLが実行できなかった場合mysqldumpはとりあえずこれを出すようです。ひどい仕様ですね。もう少し具体的出せ。マジで。
文字コードの設定の違いに気づいて、設定を合わせてdumpを流し込んだらエラーなく流し込めたので何か変な文字が入ってるんだろう、というところまで想像できました。
mysqldumpはデフォルトで一つのINSERTで複数の行を入れるので、対象の行を見ても文字列が多すぎてどの文字が問題なのか特定できません。--skip-extended-insertをつけると1行ずつ出すのですが容量がデカくなり、時間もかかるため、テキストエディタでINSERT文を全部分けたSQLを作り、流し込んだらUNIQUE制約に引っかかっていることが判明し解決できました。
トラブルは大体StackOverFlowに解決してもらってますが、今回の立役者はC2A0を強調表示したvscodeですね。そして戦犯はmysqldump（のエラー文）。
環境
最後に手元にあって色々試した環境を参考までに載せておきます。
運用中のサーバー(CentOS 8.2)
mariadb 10.3.17-MariaDB-log MariaDB Server
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+--------+
character_set_collations自体がありません。
ローカル（Mac 14.5）
mariadb 11.3.2-MariaDB Homebrew
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| character_set_client | utf8mb3 |
| character_set_collations | |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
+--------------------------+---------+
character_set_collationsは空になっています。もしかしたら10から11にアップグレードしたので空になっているのかもしれません。
新サーバー(Ubuntu 24.04 LTS)
mariadb 11.4.2-MariaDB-ubu2404-log
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%';
+--------------------------+-------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------+
| character_set_client | utf8mb3 |
| character_set_collations | utf8mb4=utf8mb4_uca1400_ai_ci |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
+--------------------------+-------------------------------+
Discussion