はじめに
イタンジ株式会社の磯谷です。不動産賃貸仲介業向けのSaaSであるノマドクラウドの開発をしています。
ノマドクラウドではRailsとMySQLを使用して開発を行っています。その中で、VARCHAR型のカラムに格納されている値の、比較時の末尾の空白の取り扱いについてつまづいたので、今回はその点について書いていこうと思います。
MySQLの環境は以下です。
- MySQL 8.0
- 文字セット
utf8mb4 - 照合順序
utf8mb4_general_ci
つまづいたこと
あるテーブルについてVARCHAR型のカラムに格納されている値を用いてレコードを検索するという要件がありました。このとき、そのテーブルには該当のVARCHAR型のカラムに、末尾の空白の有無のみが異なる値、例えばaという値と、a という値を格納したレコードがそれぞれ存在しており、またその2つのレコードを区別する必要がありました。
以下のようなコードによってその検索を行おうと思っていたのですが、該当のVARCHAR型のカラムの値の末尾の空白を区別せずにaという値を格納したレコードを取得してしまっており、それら空白の有無のみが異なる値を持つレコードの区別につまづいてしまいました。
Model.find_by(column_name: 'a ') # SELECT `table_name`.* FROM `table_name` WHERE `table_name`.`column_name` = 'a ' LIMIT 1 # => #<Model:0x00... # ... # column_name: "a", # ...>
区別されなかった理由
この、末尾の空白が区別されない点についてはMySQLのこちらのドキュメントの最後に記載されています。 dev.mysql.com
非バイナリ文字列であるCHAR、VARCHAR、TEXT値の比較時に末尾の空白が意味を持つかどうかは、照合順序のPAD属性によって決まるようです。
PAD属性はPAD SPACEとNO PADのどちらかをとり、それぞれ以下のような挙動になります。
PAD SPACE: 文字列比較の際に末尾の空白の有無、数は区別しないNO PAD: 文字列比較の際に末尾の空白の有無、数は区別する
各照合順序のPAD属性はinformation_schema.collationsを参照して確認することができます。
MySQL :: MySQL 8.0 Reference Manual :: 12.8.5 The binary Collation Compared to _bin Collations
mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE FROM information_schema.collations WHERE COLLATION_NAME = 'utf8mb4_general_ci'; +--------------------+---------------+ | COLLATION_NAME | PAD_ATTRIBUTE | +--------------------+---------------+ | utf8mb4_general_ci | PAD SPACE | +--------------------+---------------+ 1 row in set (0.00 sec)
今回私の環境では照合順序がutf8mb4_general_ciでしたので、PAD属性はPAD SPACEであり、そのために文字列末尾の空白は区別されずに比較されていました。
改めて手元で実際に確認してみても、区別されていない挙動を確認することができました。
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT 'a' = 'a '; +------------+ | 'a' = 'a ' | +------------+ | 1 | +------------+ 1 row in set (0.00 sec)
解決した方法
VARCHAR型のカラムの値の末尾の空白を区別するという要件を満たすために、以下のようにコード側で発行されるクエリを変更するようにしました。
Model.find_by('BINARY column_name = ?', 'a ') # SELECT `table_name`.* FROM `table_name` WHERE (BINARY column_name = 'a ') LIMIT 1 # => #<Model:0x00... # ... # column_name: "a ", # ...>
BINARY演算子を使用することでバイト単位で比較することができ、これにより末尾の空白も区別されるようになります。
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT BINARY 'a' = 'a '; +-------------------+ | BINARY 'a' = 'a ' | +-------------------+ | 0 | +-------------------+ 1 row in set, 1 warning (0.00 sec)
今回は短期的に必要になる要件と実装であったためにコード側での対応を行いましたが、このような場合では照合順序の再考も選択肢に上がるかと思います。
ちなみにMySQL 8.0において文字セットutf8mb4のデフォルトの照合順序はutf8mb4_0900_ai_ciであり、PAD属性はNO PADですので末尾の空白は区別されます。
MySQL :: MySQL 8.0 Reference Manual :: 12.2 Character Sets and Collations in MySQL
mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE FROM information_schema.collations WHERE COLLATION_NAME = 'utf8mb4_0900_ai_ci'; +--------------------+---------------+ | COLLATION_NAME | PAD_ATTRIBUTE | +--------------------+---------------+ | utf8mb4_0900_ai_ci | NO PAD | +--------------------+---------------+ 1 row in set (0.02 sec) mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT 'a' = 'a '; +------------+ | 'a' = 'a ' | +------------+ | 0 | +------------+ 1 row in set (0.00 sec)
おわりに
以上、VARCHAR型のカラムに格納されている値の、比較時の末尾の空白の取り扱いについてまとめてみました。 今回はそのほか該当のカラムのユニーク制約であったり、絵文字の比較など、手元で検証しながらMySQLの挙動について少し理解を進めることができました。
この記事の内容がご覧の方の一助になれば幸いです。
