ある日のこと
社内のHさんから質問が。
外注さんが、文字列である建物名の完全一致検索するときのSQLで ｀... where building like ? ...｀ って書いてきたので 「 ｀=｀ じゃないのは何故？」って聞いたら↓を意図したと回答がありました。 https://dev.mysql.com/doc/refman/5.6/ja/string-comparison-functions.html ぶっちゃけ、初めてこの仕様を知ったんですが、ご意見いただけませんでしょうか？
ここでいうこの仕様とは以下のこと
SQL 標準では、LIKE は文字ごとに一致を実行するため、= 比較演算子とは異なる結果が生成される可能性があります。 特に、末尾の空白は重要です。ただし、= 演算子を使って実行される CHAR や VARCHAR の比較には当てはまりません。
マジか？イコールは空白無視するんかーい！？
本当なのか？
じゃあ、テーブル作ってWHERE句で確認してみるか。
テーブルを作って、データを入れます。
CREATE TABLE `aaa_utfmb4` ( `id` int(11) NOT NULL, `a` varchar(10) DEFAULT NULL, `b` char(10) DEFAULT NULL, `c` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- aとbカラムにそれぞれ文字列'a 'と'b 'と'c 'をぶっこむ insert into aaa_utfmb4(id,a,b,c) value(1,'a ','b ','c '); -- 全行(1行ですが) select * from aaa_utfmb4; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec)
イコールで空白無しで検索してみます。
-- ='a'(スペースなし)でvarcharのほうを見る select * from aaa_utfmb4 where a = 'a'; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec) -- ='b'(スペースなし)でcharのほうを見る select * from aaa_utfmb4 where b = 'b'; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec) -- ='c'(スペースなし)でtextのほうを見る select * from aaa_utfmb4 where c = 'c'; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec)
本当だ！イコールだと空白無視されてる！
MySQL気持ち悪い(何をいまさら)
LIKEで空白無しで検索してみます。
-- LIKE'a'(スペースなし)でvarcharのほうを見る select * from aaa_utfmb4 where a like 'a'; Empty set (0.00 sec) -- LIKE'b'(スペースなし)でcharのほうを見る select * from aaa_utfmb4 where b like 'b'; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec) -- LIKE'c'(スペースなし)でtextのほうを見る select * from aaa_utfmb4 where c like 'c'; Empty set (0.00 sec)
なにこれ？ charの時は空白無視して見えちゃってるやん！ 可変長の場合はLIKEで回避できるのか～。
大文字でも試してみます。
/* 全部大文字で見てみると？ */ -- ='a'(スペースなし)でvarcharのほうを見る select * from aaa_utfmb4 where a = 'A'; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec) -- ='b'(スペースなし)でcharのほうを見る select * from aaa_utfmb4 where b = 'B'; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec) -- ='c'(スペースなし)でtextのほうを見る select * from aaa_utfmb4 where c = 'C'; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec)
-- LIKE'a'(スペースなし)でvarcharのほうを見る select * from aaa_utfmb4 where a like 'A'; Empty set (0.00 sec) -- LIKE'b'(スペースなし)でcharのほうを見る select * from aaa_utfmb4 where b like 'B'; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec) -- LIKE'c'(スペースなし)でtextのほうを見る select * from aaa_utfmb4 where c like 'C'; Empty set (0.00 sec)
まあ、大文字小文字はデフォでは判別しないから同じよね。
※上記の検証はMySQL5.7でやりましたが、MariaDB10.2と10.3、MySQL5.6でも同じでした。 ※また、latin1でも同じでした。
ドキュメントは間違ってないけど・・・
char＋空白＋LIKEの場合の話抜けてると思うんですが、気のせいですかね？
(charの空白は文字として扱ってるんです！って話なら＝の時の挙動が納得いかない)
上記についてmita2さんからコメントいただき、納得。
charは固定長で、スペースはカラム長に満たない部分を埋める文字として扱われます。"b<スペース>"をINSERTしても、実際SELECTすると"b"として返るので、仕様に齟齬はない認識。
あー、そういうことか。納得！感謝です。
つまり、INSERTしたときにCHARでは末尾の空白がTRIMされて取得されるんですね。
mysql> select CHAR_LENGTH(a),CHAR_LENGTH(b),CHAR_LENGTH(c) from aaa_utfmb4; +----------------+----------------+----------------+ | CHAR_LENGTH(a) | CHAR_LENGTH(b) | CHAR_LENGTH(c) | +----------------+----------------+----------------+ | 10 | 1 | 9 | +----------------+----------------+----------------+ 1 row in set (0.00 sec)
charでは埋めてある空白を文字として見ないようにしている様子。
また、yoku0825さんからも
それのためにMariaDBにはNO PAD Collationがいつの頃からか実装されて、MySQLも8.0で（というかUCA 9.0.0から？）実装されたようです？ [https://jira.mariadb.org/plugins/servlet/mobile#issue/MDEV-9711] [https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html]
というコメントをいただき、MySQL8で試してみることに。
$ mysql -u xxxx -pxxxxxxx test mysql: [Warning] Using a password on the command line interface can be insecure. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1935090 Server version: 8.0.15 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> -- ='a'(スペースなし)でvarcharのほうを見る mysql> select * from aaa_utfmb4 where a = 'a'; Empty set (0.00 sec) mysql> -- ='b'(スペースなし)でcharのほうを見る mysql> select * from aaa_utfmb4 where b = 'b'; +----+------------+------+-----------+ | id | a | b | c | +----+------------+------+-----------+ | 1 | a | b | c | +----+------------+------+-----------+ 1 row in set (0.00 sec) mysql> -- ='c'(スペースなし)でtextのほうを見る mysql> select * from aaa_utfmb4 where c = 'c'; Empty set (0.00 sec)
本当だ！varcharとtext出てない！ charが出るのはさっきのmita2さんの話で分かったし、これですべてのもやもやが解決！
回避方法は
BINARY演算子使う方法がこの記事にあって、こんなんあるんや～！とも思ったが、まあTRIMやREPLACEで抜いて検索するのもありだし、インデックス効かないの嫌ならそういうデータは洗い替えちゃったり、そもそも入れる段階で無くそうぜ！って感じ。
あとは、バージョン上げればNO PAD collationが効いてくれるというので、気持ち悪ければバージョン上げようぜ！ってことですね。
ちなみに、パフォーマンスって差異あるのかしら？
LIKEの挙動がもしMySQLでも 文字ごとに一致を実行する のであれば少し遅いような気もするけど・・・。
