検索時に半角スペースなどが無視される場合がある

事象

画面で検索した際、MySQLに格納されている半角スペースが無視される場合がある。
加えて、ある画面の検索では無視されるのに、別画面の検索では無視されない。

原因

以下のいずれかに該当しているため

• 利用している照合順序が異なるため
• 検索条件が完全一致 or 部分一致の違いがあったため

解説

MySQLは、文字列の後ろにある半角スペースなどを比較から無視する機能が存在する。

MySQL 照合には、PAD SPACE または NO PAD の値を持つ pad 属性があります:
ほとんどの MySQL 照合には、PAD SPACE のパッド属性があります。
(中略)
・PAD SPACE 照合の場合、末尾のスペースは比較では重要ではありません。
　文字列は末尾のスペースに関係なく比較されます。
・NO PAD 照合順序では、他の文字と同様に、比較で末尾のスペースが重要として扱われます。

上記の通り、利用している照合順序によっては、無視されない。
例えば、utf8mb4の照合順序の場合、MySQL8.0のデフォルトエンコードutf8mb4_0900_ai_ciは無視されない(NO PAD)が、MySQL5.7のデフォルト照合順序ベースにしたい場合に用いるutf8mb4_general_ciの場合は無視される(PAD SPACE)。
[実行したSQL]

SELECT COLLATION_NAME, PAD_ATTRIBUTE 
FROM INFORMATION_SCHEMA.COLLATIONS 
WHERE COLLATION_NAME = 'utf8mb4_0900_ai_ci' 
OR COLLATION_NAME = 'utf8mb4_general_ci'; 

[SQLの結果]

COLLATION_NAME　　　　　PAD_ATTRIBUTE
utf8mb4_0900_ai_ci     NO PAD
utf8mb4_general_ci     PAD SPACE

また、PAD SPACEが有効な照合順序であっても、likeまでには適応されない。

このコンテキストの「比較」には、LIKE パターン一致演算子は含まれていません。
この演算子の末尾の空白は、照合に関係なく重要です。

上記引用元

dev.mysql.com

補記事項

PAD SPACEとは

ChAR型における文字列を埋める際の文字の事を指す。

CHAR 値は格納されると、指定された長さになるように右側がスペースで埋められます。

スペースは半角スペース(U+0020)を指す。
(確認してみたが、これを全角スペースなど別の文字にすることはできなさそう)
検索結果の挙動は以下の様になる。
[実行したSQL]

create table Test(id integer key auto_increment, title varchar(8)) COLLATE utf8mb4_general_ci;
insert into Test(title) values ("hoge"); -- 半角スペース無
insert into Test(title) values ("hoge "); -- 半角スペース1個
insert into Test(title) values ("hoge  "); -- 半角スペース2個
insert into Test(title) values ("hoge　"); -- 全角スペース1個
insert into Test(title) values ("hoge 　"); -- 半角スペース1個、全角スペース1個
select * from Test where title = 'hoge';

[検索結果]
全角スペースを含むと、検索結果からは除外された。

id	title
1	hoge
2	hoge 
3	hoge  

どうしても比較演算子で無視したくない場合

BINARY演算子を付加する

SELECT * 
FROM tbl1
WHERE BINARY col1 = 'hoge  ';

CHAR型の場合の追加ルール

char型を利用しているカラムについては、以下のルールも配慮する必要がある。

CHAR 値は格納されると、指定された長さになるように右側がスペースで埋められます。
PAD_CHAR_TO_FULL_LENGTH SQL モードが有効になっていないかぎり、CHAR 値が取り出されるときに、末尾のスペースが削除されます。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 11.3.2 CHAR および VARCHAR 型
但し、PAD_CHAR_TO_FULL_LENGTH自体は、現在は非推奨パラメータである。

MySQL 8.0.13 では、PAD_CHAR_TO_FULL_LENGTH は非推奨です。
MySQL の将来のバージョンで削除されることが予想されます。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.11 サーバー SQL モード

MySQL徹底入門 第4版 MySQL 8.0対応

MySQL即効クエリチューニング【電子書籍】[ yoku0825 ]