事象
画面で検索した際、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 パターン一致演算子は含まれていません。
この演算子の末尾の空白は、照合に関係なく重要です。
補記事項
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 ");
insert into Test(title) values ("hoge ");
insert into Test(title) values ("hoge ");
insert into Test(title) values ("hoge ");
select * from Test where title = 'hoge';
[検索結果]
全角スペースを含むと、検索結果からは除外された。
id title
1 hoge
2 hoge
3 hoge
どうしても比較演算子で無視したくない場合
BINARY演算子を付加する
SELECT *
FROM tbl1
WHERE BINARY col1 = 'hoge ';