MySQLのCollationについて

質問

MySQLを使い始めたばかりものです。
SQLの学習のために下記ページのExample Databasesからworld database(InnoDB)をダウンロードし、インポートしました。
* http://dev.mysql.com/doc/index-other.html
* http://dev.mysql.com/doc/world-setup/en/index.html

そして、次のようなSQLを実行して countrycode列の値が 'JPN' の行が選択されることにびっくりしました。
mysql> select * from city where countrycode = 'jpn';

(1)Collationというものが関係することはわかったのですが、どうすればいいのかわからない状況です。
world database を PostgreSQL のようにデフォルトで大文字/小文字を区別して比較するようにしたいのですが、具体的にどのように設定したらよいのでしょうか？

(2)要件によるとは思いますが、データベースのデフォルトのCollationはcase-insensitiveで、必要に応じて列を case-sensitive にしたり、select文に collate句を付加したりしているような使われ方が、MySQLでは一般的なのでしょうか？
それとも、Collationにcase-sensitiveなものを指定してデータベースを作成し、必要に応じて列を case-insensitive 扱いするケースの方が圧倒的に多いのでしょうか？

■環境
* Linux Mint 17 Mate
* MySQL 5.5.43
以下の設定を my.cnf に追加
- 文字コード関連をutf8に設定
- lower_case_table_names = 1

よろしくお願いします。

回答No.1

anmochi · ベストアンサー

MySQLでは様々な言語（というかキャラクターセット）を取り扱う事ができるが、そのほとんどでデフォルトのCollationはアルファベットの大文字と小文字を区別しないようになっているね。キャラクターセットをUTF8にしてCollationを指定しない場合、Collationはutf8_general_ciというものになり、これはアルファベットの大文字と小文字を区別しない。キャラクターセットがUTF8の時に使用できるCollationは他にutf8_binとutf8_unicode_ciがある。お察しの通り大文字と小文字を区別するようにするためのCollationはutf8_binだ。utf8_unicode_ciは私は使った事無いが日本語のカタカナとヒラガナとかも含めてcase-insensitiveになるらしい。

(1)なので、Collationを指定する。Collationはテーブルカラムに対して行われるので
mysql> alter table <テーブル名> modify <カラム名> collate utf8_bin;
面倒であればmysqldumpでダンプを取った後、データベースのデフォルトCollationを変更するか作りなおしてからインポートするといいだろう。
mysql> alter database <データベース名> default character set utf8 collate utf8_bin;

(2)冒頭で説明した通りMySQLではほとんどのキャラクターセットのCollationデフォルト値がcase-insensitiveだ。が、多分ほとんどの人は知らずにciのまま使っていると思われる。問題が発生するまではciを使っていることすら意識していないのではないだろうか。
私個人の話になるが、私は（Key-Valueでいうところの）Value値となる項目（例えばブログ記事テーブルのようなものがあればそれのタイトル列や本文列）はci、ハッシュ値となるKey値はci、ID（Primary Key）となるKey値はcsとciを要件によって使い分けている。

> 以下の設定を my.cnf に追加
> - 文字コード関連をutf8に設定
> - lower_case_table_names = 1
このパラメータはファイル名に大文字小文字の区別がない（←正確には違う）Windowsで意味があるもので、Linuxでは不要というか逆に混乱の元になるので0にする方が良いだろう。MySQLはデータベース名はOSディレクトリ、テーブル名はOSファイルと密接に関わるのでこのようなパラメータがある。

大本のデフォルト値を操作したい場合、my.cnfの[mysqld]セクションに
collation-server = utf8_bin
collation-database = utf8_bin
collation-connection = utf8_bin
というパラメータを入れるといい。

お礼

anmochiさん
詳細で丁寧な解説ありがとうございます。

おかげさまで Collation についてだいぶ理解できるようになり、utf8_binで利用できるようになりました。
各テーブルの各カラムに対して個別に設定する必要があったのですね。
また、次のようにすればよいことも確認できました。
(1) "default charset=utf8 collate utf8_bin" でデータベースを作る。
(2) MySQLのサイトからダウンロードしてきたworldデータベースをインポートするためのsqlファイル(これ自体mysqldumpで出力したものだったのですね)の create table文での文字コードの指定を latin1 から utf8 に修正し、collate utf8_bin を追加する。
(3) 新規に作成したDBに対して、修正したsqlファイルを実行する。

> 多分ほとんどの人は知らずにciのまま使っていると思われる。問題が発生するまではciを使っていることすら意識していないのではないだろうか。
デフォルトの設定値が ci であることに驚き、MySQLユーザーの反応がどんなものか検索してみたのですが、想像していたよりもずっと冷静に受け止められているように感じたので、自分の感覚がおかしいのか？ と不安に思い質問させて頂きました。
おっしゃる通り、知らないで使ってる方も少なくないでしょうし、知ってる方はデータベース作成時に指定しておけば良いという感じなのでしょうね。
最初は混乱しましたが、今は「こういうRDBMSもあるんだな」という感覚に落ち着きました :)

lower_case_table_namesの件といいますか、テーブルの命名規則についてもちょっと疑問に思っていることがあるのですが、一旦頭の中を整理して改めて質問させて頂こうかと思っています。
また、その時はよろしくお願いします。

どうもありがとうございました。