ぱろっと・すたじお

技術メモなどをまったりと / my site : http://parrot-studio.com/

MySQLの「照合順序」ではまる

Ruby Rails MySQL

※本記事は自鯖Blogの転載です
※元記事は2013/05/29に書かれました

http://blog.parrot-studio.com/2013/05/mysql-collation/

最近、仕事が忙しすぎて、技術メモ的なものを書けてなかったので、
久々に書いておきます...φ(･ω･｀)

起こった問題と背景

昨年、ラグナロクオンライン（RO）のギルド戦（Gv）を戦略的支援するため、
状況を監視するシステムを作ったのですが、
最近、それのアーキテクチャを変えまして

先日の資料
http://parrot.hatenadiary.jp/entry/2013/07/18/005901

Gagnrath - ROGv : Forts Watching System
https://github.com/parrot-studio/gagnrath

サンプルサイト
http://ro.parrot-studio.com/rogvs

PadrinoからRails4に移行したことも大きいのですが、
MongoDBからMariaDB（事実上のMySQL）への移行も大きなジャンプです

先日の会では、NoSQLやRDBについての雑談というか議論もあったのですが、
今回はもうちょっと技術寄りな話で・・・

「Gagnrath」において、「ギルド名の比較」は頻繁におこなわれるため、
当然のように各テーブルの「guild_name」カラムにはIndexをつけてありました

基本的にはそれでOKだったのですが、
テストしていたところ、以下の問題が発生したのです

・「Parrot」というギルド名を指定したのに「Pａrrot」のデータもHITしてしまう（半角と全角で違う）

「いや、そんな名前をつける方がおかしいやろ」というのもわかるのですが、
ゲームの戦略上、このような名前の使い分けは普通に発生するのです

そもそも、全角と半角が同一視されるとか、
プログラマ的に気持ち悪くないですか(´･ω･)？ *1

MySQLの照合順序

というわけでここまでが前置きですが、
MySQLには「文字コード」の他に「照合順序」という概念があります

MySQL :: MySQL 5.1 リファレンスマニュアル :: 9.2 MySQLにおけるキャラクタセットおよび照合順序

つまり、文字コードとして「UTF-8(utf8)」を選んだとしても、
「文字列の比較」に関していくつかのパターンがある、ということです
（「文字列を格納する型」のみに存在します）

MySQL :: MySQL 5.1 リファレンスマニュアル :: 9.10.1 Unicode キャラクタセット

よく使われるのはこの辺でしょうか

  • utf8_general_ci
  • utf8_unicode_ci
  • utf8_bin


私の確認した範囲*2だと、デフォルトで「utf8_unicode_ci」になってました
（ただし、ActiveRecordが生成したテーブルなので、生でSQLを発行したらわかりません）

でまあ、早い話が「utf8_unicode_ci」を指定した場合、
大文字小文字を同一視するどころか、
半角の「a」と全角の「ａ」も同一視してしまうのですΣ(ﾟДﾟ)ｶﾞｰﾝ

これに気づかずにだいぶ悩みましたが、
解決策としては照合順序を「utf8_bin」に変えるか、
SQLで強制的にバイナリ比較するか、です

MySQL :: MySQL 5.1 リファレンスマニュアル :: 9.5.3 BINARY オペレータ

照合順序はカラム単位・テーブル単位・スキーマ単位で指定が可能です

MySQL :: MySQL 5.1 リファレンスマニュアル :: 9.3 デフォルトのキャラクタセットおよび照合順序の指定

当然ながら、文字型カラムに対するIndexもこの影響を受けるので、
先ほどのような問題が発生した、というわけです

なので、明示的に「ut8_bin」を指摘することで、
「a」と「ａ」は区別され、狙い通り動くようになります(`･ω･´)

Railsを前提にした解決策（の一つ）

・・・とまあ、ここまでは一般論です

問題は、今回のシステムはRailsで構築されており、
ActiveRecordにより抽象化されている、ということです

Gagnrathにおける、あるテーブルのmigration*3はこうなってます

class CreateForts < ActiveRecord::Migration
  def change
    create_table :forts do |t|
      t.integer  :situation_id, null: false
      t.string   :revision,     null: false, limit: 30
      t.string   :gvdate,       null: false, limit: 10
      t.string   :fort_group,   null: false, limit: 10
      t.string   :fort_code,    null: false, limit: 10
      t.string   :fort_name,    limit: 100
      t.string   :formal_name,  limit: 100
      t.string   :guild_name,   null: false, limit: 50
      t.datetime :update_time,  null: false
      t.timestamps
    end
    add_index :forts, :situation_id
    add_index :forts, :gvdate
    add_index :forts, :fort_group
    add_index :forts, [:gvdate, :fort_group]
    add_index :forts, :guild_name
    add_index :forts, [:gvdate, :guild_name]
    add_index :forts, [:revision, :fort_code], unique: true
  end
end

「migration」の仕組みは基本的にRDBMSに依存しないため、
MySQLの概念である「照合順序指定」は、
migrationで書くことができません *4

create_tableにオプションをつける手もあるのですが、
どちらにせよ「特定のRDBMS固有の概念をmigrationに含める」のは、
あまり筋が良くない手です(´･ω･`)

そこでどうしたかというと、database.ymlに書いてしまいました *5

development:
  adapter: mysql2
  encoding: utf8
  database: gagnrath_development
  pool: 5
  username: rogv
  password: hogepiyo
  socket: /tmp/mysql.sock
  collation: utf8_bin

ここは「RDBMSの設定を書く場所」なので、
MySQL固有の概念が含まれたとしても、
あまり問題になりません

今回の場合、「ギルド名」という概念はスキーマ全体に影響するため、
スキーマのデフォルトとして「utf8_bin」としても問題ありません *6

余談

ActiveRecord（のArel）は賢いので、
必要な場合に自動で「BINARY」をつけたSQLを発行します
（おそらくuniqueバリデーションの時？）

でも、全ての場合につけてくれるわけではないですし、
（特に仕事であれば）生SQLやRuby以外というケースも多いはずなので、
定義レベルでの対策は必要だと思います

*1: 個人の主観です　というか、例えば「検索機能」に提供する場合であれば、こちらの方が好都合な場合もありますが、プログラム側でロジカルに制御したい場合もあるでしょう

*2: Mac版MySQL5.1とCentOS版のMariaDB5.5

*3: [https://github.com/parrot-studio/gagnrath/blob/master/db/migrate/20130323024043_create_forts.rb]

*4: できるのかもしれませんが、見つかりませんでした　例えばこんな手とか [http://qa.atmarkit.co.jp/q/2041]

*5: [https://github.com/parrot-studio/gagnrath/blob/master/config/database.yml]

*6: 言うまでもありませんが、「問題あるか否か」は「システムの仕様」に基づいて決まるものであり、一概に「問題ない」わけではありません、念のため