日報(2024-01-13) mariadb で table の collation を create table のタイミングで指定する

背景

ORM が嫌いで、何かあった時を思うといまいち怖くてどうしても信用できないので、JSON 型を好んで使ってます

こんな感じで

CREATE TABLE IF NOT EXISTS tablename (
	ID       VARCHAR(16) PRIMARY KEY, 
	Attr     JSON,
	Sys      JSON,
	CHECK (JSON_VALID(Attr) and JSON_VALID(Sys))
)

アプリで使うデータ一切を Attr にまとめて、生成や更新の日時とかを Sys にまとめて使ってます

ある時、LeR という ID で登録しようとしたら

id already exist

あれれと思って検索しても LeR はないのですが LER という ID はありました、というかなんで LeR で検索してるのに LER が表示されての？あ、case insensitive か！

とりあえず database のデフォルトを見てみると

show create database tablename
    -> ;
+--------------+-----------------------------------------------------------------------------------------------------+
| Database  | Create Database                                                                                     |
+--------------+-----------------------------------------------------------------------------------------------------+
| tablename | CREATE DATABASE `tablename` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ |
+--------------+-----------------------------------------------------------------------------------------------------+

utf8mb4 はググると詳しく説明してくれているサイトがたくさんでてくるのですが、歴史的な経緯があってややこしい名前をしているようですがようするに utf8ですよね で、utf8mb4_general_ci が COLLATE で、語尾の _ci が case insensitive を示してます。正解ですね

ちなみに table の collation を確認すると

SHOW FULL COLUMNS FROM tablename;
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| ID    | varchar(16) | utf8mb4_general_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
| Attr  | longtext    | utf8mb4_bin        | YES  |     | NULL    |       | select,insert,update,references |         |
| Sys   | longtext    | utf8mb4_bin        | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+

ID が case insensitive な utf8mb4_general_ci なのですが JSON の Attr と　Sys は　case sensitive な utf8mb4_bin なんですね

create table で collation を変える

collation を指定して database を作り直すのは巻き添えが多すぎてちょっと大事なのですが、alter table で変更しなきゃいけない程でもない開発中なので、collation に utf8mb4_bin を指定して table を作り直します

CREATE TABLE IF NOT EXISTS tablename (
	ID       VARCHAR(16) PRIMARY KEY, 
	Attr     JSON,
	Sys      JSON,
	CHECK (JSON_VALID(Attr) and JSON_VALID(Sys))
)  COLLATE utf8mb4_bin

で、collation を確認すると

+-------+-------------+-------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation   | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+-------------+------+-----+---------+-------+---------------------------------+---------+
| ID    | varchar(16) | utf8mb4_bin | NO   | PRI | NULL    |       | select,insert,update,references |         |
| Attr  | longtext    | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references |         |
| Sys   | longtext    | utf8mb4_bin | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+-------------+------+-----+---------+-------+---------------------------------+---------+

case sensitive になりました

collation

さて、collation ですが英語苦手な私は DB の文脈以外では見たことがない言葉なのですが、DB の文脈だとよく照合順序 みたいな訳語を見るのですが元々どういうニュアンスでどういう所で使ってた言葉なんだろと好奇心をもったのですが、辞書を引くと「照合、校合」とかっこいいんだけど意味のわからない言葉でよくわかりません

こういう時に英英辞典はわかりやすい言葉で説明してくれていることが多く ケンブリッジ (opens new window) の米語辞典だと

to collect and arrange in correct order the sheets of a document

つまり正しい順序で並べるって意味なんですね

参考文献

• MySQLで大文字小文字を区別しないのを直す (opens new window) collation の詳しい話し
• MySQLで照合順序を確認する方法。 (opens new window) collation を確認する script
• Case Sensitive collation in MySQL (opens new window)
• How to specify sqlite database collation (opens new window)
• How to do a case sensitive search in WHERE clause? (opens new window)
• Supported Character Sets and Collations (opens new window)
• Case Sensitivity in String Searches (opens new window)
• collate (opens new window)

---