最近MySQLのVARCHARがデフォルトの設定で大文字と小文字を区別してくれていない事を知らずにバグを仕込んでしまったので軽く押さえてみた
検証
Dockerコンテナ
$ docker run -it --name mysql -e MYSQL_ROOT_PASSWORD=secret -d mysql:8.0 $ docker exec -it mysql bash
MySQLにログインしてテーブル作成
$ mysql -uroot -p $ CREATE DATABASE sample_db; $ CREATE TABLE sample_db.user (id int, name varchar(10));
nameを大文字でレコードを登録
$ INSERT INTO sample_db.user VALUES(1,'JHON');
WHEREでnameを指定して絞り込み
大文字
$ SELECT id,name FROM sample_db.user WHERE name = 'JHON'; +------+------+ | id | name | +------+------+ | 1 | JHON | +------+------+ 1 row in set (0.00 sec)
小文字
$ SELECT id,name FROM sample_db.user WHERE name = 'jhon'; +------+------+ | id | name | +------+------+ | 1 | JHON | +------+------+ 1 row in set (0.00 sec)
whereでの指定が小文字のjhonにも関わらずnameが大文字のJHONであるレコードがselectされる
なぜこんなことになるかドキュメントをひいてみる
文字セットとは、記号とエンコーディングのセットです。 照合順序とは、文字セット内の文字を比較するためのルールを集めたものです。 架空の文字セットを例にして、文字セットと照合順序の違いを見てみましょう。
非バイナリ文字列の場合 (
CHAR、VARCHAR、TEXT)、文字列検索では比較オペランドの照合順序が使用されます。 バイナリ文字列 (BINARY,VARBINARY,BLOB) の場合、比較ではオペランド内のバイトの数値が使用されます。つまり、アルファベット文字の場合、比較では大文字と小文字が区別されます。
デフォルトの文字セットおよび照合順序は
utf8mb4およびutf8mb4_0900_ai_ciであるため、非バイナリ文字列比較ではデフォルトで大文字と小文字が区別されません。 これは、_`col_name`_ LIKE 'a%'を使用して検索した場合、Aまたはaで始まるすべてのカラム値が取得されることを意味します。
かいつまんで言うとMySQLでは文字を扱うために文字セットと文字を比較するための照合順序(collate)の設定があってクエリを実行した際の結果はこの設定に左右されるらしいが、デフォルトでは大文字と小文字が区別されない設定になっているようだ。
ただし非バイナリ文字列比較とあるのでBINARY演算子によるCASTを試してみる。
CAST付きの絞り込み
小文字
SELECT id,name FROM sample_db.user WHERE BINARY name = 'jhon'; Empty set, 1 warning (0.00 sec)
大文字
SELECT id,name FROM sample_db.user WHERE BINARY name = 'JHON'; +------+------+ | id | name | +------+------+ | 1 | JHON | +------+------+ 1 row in set, 1 warning (0.00 sec)
今度は小文字ではヒットしなくなった
またクエリの実行時にcollateを指定する事も出来る
collateを使用した比較
まずは文字セットを指定したいcollateに対応したものに設定しなおす
SET character_set_connection=utf8mb4;
小文字
mysql> SELECT id,name FROM sample_db.user WHERE name = 'jhon' COLLATE utf8mb4_0900_bin; Empty set (0.01 sec)
大文字
mysql> SELECT id,name FROM sample_db.user WHERE name = 'JHON' COLLATE utf8mb4_0900_bin; +------+------+ | id | name | +------+------+ | 1 | JHON | +------+------+ 1 row in set (0.00 sec)
テーブルが使用するcollateの設定ごと変えてやればクエリごとに指定をしなくても良くなる
テーブルの設定
現在の設定の確認
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='sample_db' AND TABLE_NAME='user'; +--------------------+ | TABLE_COLLATION | +--------------------+ | utf8mb4_0900_ai_ci | +--------------------+ 1 row in set (0.00 sec)
collateの変更
ALTER TABLE sample_db.user COLLATE utf8mb4_0900_bin;
create table時にcollateを指定しても良い
CREATE TABLE sample_db.user (id int, name varchar(10)) COLLATE utf8mb4_0900_bin;
ただテーブルごとの設定もそれぞれに付与するのは手間だしそのデータベースを扱うサービス全体で大文字と小文字を区別して欲しいことも多いだろうから、その場合データベース単位で設定しまう方がラクだろう。
データベースの設定
ALTER DATABASE sample_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin;
CREATE DATABASE sample_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin;
最後に
今まで既存のアプリケーションの改修が多かったことも原因かもしれないがが、MySQLを触りだして数年たつのにこの事を知らずに結構衝撃だった。 幸いリリース前にチーム内の人が指摘してくれてことなきを得たが、調べても最初BINARYのキャストの話が多く出てきてピンと来ずcollateという馴染みの薄い単語覚えておかないと調べるのに少し不便だったので記録を残しておく
おまけ
文字セットの確認
SHOW VARIABLES LIKE "chara%"; +--------------------------+--------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | /usr/share/mysql-8.0/charsets/ | +--------------------------+--------------------------------+ 8 rows in set (0.01 sec)