テーブルカラムの照合順序(collation)設定で発生した問題とその対応

アソビューでバックエンドエンジニアをしている山野です。

今回は過去に発生したMySQLに関連した問題から学んだ点について書きたいと思います。

発生した問題の概要

新たに導入したある機能で一部のゲストに限りエラーが発生する事象が発生しました。
調査を行ったところ原因は2点ありました。

あるテーブルの文字列型カラムにおいて、本来文字列の末尾にスペースを許可していないにも関わらず、一部のデータで不正なスペース文字が含まれていたこと
該当の機能が末尾にスペースが含まない状態で検索処理を行っているにも関わらず、前述の不正にスペースを含んだデータも抽出してしまうこと。

原因の詳細

2つの原因のうち、前者は過去の機能仕様の考慮漏れで既に修正済みであったことと、
後者の根本原因は分かっていなかったものの、エラーの発生は限定的であることが分かったため、
不正にスペースが入ってしまったデータ補正を行うことを優先し、スペースが含まれているデータの抽出を行うため下のようなSQLで検索を行いました。

SELECT * FROM users
WHERE users.name = CONCAT(LTRIM(RTRIM(users.name)), ' ');

しかしながら、結果はスペースが含まれていない正常なデータまで抽出され、この時点で問題がアプリケーションの処理ではなくDB側の設定が関係していることに気づきました。

そして改めて調べ直した結果、
テーブルカラムの照合順序(collation)の設定が関係していることが分かりました。

照合順序(collation)は端的に言うと文字列の比較やソートの方法を定義するルールのことで、代表的なものは以下の通りです。

■ utf8mb4_general_ci
- 大文字小文字の区別をしない
- アクセント付文字(フランス語のアクサン(É)・ドイツ語のウムラウト(ä)など)無視

■ utf8mb4_unicode_ci
- 大文字小文字の区別をしない
- アクセント付文字を区別する

■ utf8mb4_bin
- バイト単位での厳密な比較
- 大文字小文字の区別をしない
- アクセント付文字も区別する

また各照合順序(collation)にはパッド属性(pad attribute)と呼ばれる 文字列の末尾の空白の比較方法を決定する属性が存在しており、デフォルトで「PAD SPACE」、または「NO PAD」どちらかが設定されています。

■ PAD SPACE
文字列を末尾のスペースに関係なく比較する
(->末尾のスペースの存在を無視して比較する)

■ NO PAD 
文字列の末尾のスペースも比較対象する

(詳細はMySQLの公式ドキュメントをご参照ください)

問題となったカラムを調べたところ、
照合順序(collation)が「utf8_general_ci」、パッド属性(pad attribute)が「PAD SPACE」となっていました。

対象データ抽出のために対応したこと

対象データの抽出にあたって、該当テーブルの照合順序(collation)の定義を変更することは既存処理への影響が大きいため、抽出用SELECT文の実行時に対象のカラムのみ照合順序(collation)一時的に強制変換する方法で行いました。

SELECT * FROM users
WHERE CONVERT(users.name USING UTF8MB4) COLLATE utf8mb4_0900_bin = CONCAT(LTRIM(RTRIM(users.name)), ' ');

考慮した点は以下です。

CONVERT関数を利用し、パッド属性(pad attribute):「NO PAD」が設定されている照合順序(collation)を利用できる文字セット(char set)に強制変換する。
COLLATEを利用し、パッド属性(pad attribute):「NO PAD」が設定されている照合順序(collation)に強制変換する

1についてはカラムの照合順序(collation)によっては不要ですが、
今回対象となったカラムの文字セット(char set)では、「NO PAD」が設定されている照合順序(collation)が利用できなかったため、文字セット(char set)の変換も実施しました。