コピペコードで快適生活

明日使えるソースを自分のために

MySQLの文字セットと照合順序

MySQL

MySQLでは、データベース、テーブル、カラムのそれぞれに対して文字セットと照合順序を設定できる。

文字セット

  • 用語
    • 文字セットとは、文字と符号の対応付けたセット。
    • 文字コードとは、文字セットの符号をビット列に変換する方式。
    • 例:
      • Unicodeは文字セット。対応する文字コードはUTF8やUTF16など複数ある。
      • JIS X 0208は文字セット。対応する文字コードはISO-2022-JPやEUC-JP、Shift-JISなど。
    • MySQLの文脈で語られる文字セットは、正確には文字コードっぽい。
-- MySQLで設定可能な文字セットの一覧
show character set;

-- MySQLの設定
-- 出力結果の変数の意味は下記
show variables like '%char%';

-- DBの設定を確認する
show create database #{db_name};

-- 出力例
-- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `my-db` /*!40100 DEFAULT CHARACTER SET utf8 */;
-- /*!32312 xxxx */ は MySQLの拡張コメント。MySQL 3.23.12 以降のサーバーでのみ実行の意味

-- テーブルの設定を確認する
show crate table #{table_name};
  • 文字セットに関する変数
    • character_set_client:
      • クライアントから送られるステートメントはcharacter_set_clientの文字セットとして扱う
    • character_set_connection:
      • クライアントから受け取ったステートメントは、character_set_clientからcharacter_set_connectionの文字セットに変換される。
    • character_set_results:
      • サーバがクライアントに返すときはcharacter_set_resultsの文字セットに変換される
    • character_set_database:
      • デフォルトデータベースで使用される文字セット
      • デフォルトデータベース＝USE文で指定したデータベースのこと
    • character_set_server:
      • デフォルトの文字セット
    • character_set_filesystem:
    • character_set_system:
    • character_sets_dir:
      • 文字セットを扱うために必要なファイルがあるディレクトリ

照合順序

  • 値を並び替えするときや比較するときのルール
    • 大文字と小文字を区別するかどうかなど
    • 照合順序が異なるカラムで比較するとエラーする(比較ルールが異なるため)
  • 文字セットごとに照合順序が用意されている
  • 例:
    • cp932_japanese_ci
      • cp932: 文字コード
      • japanese: 言語名。マルチリンガルの場合はgeneralやunicode。
      • ci: 比較法。
        • _ci: 大文字と小文字が区別されない
        • _cs: 大文字と小文字が区別される
        • _bin: バイナリ
    • utf8_general_ci
      • アルファベットの大文字小文字は区別しない。他は全て区別。
    • utf8_unicode_ci
      • 大文字小文字/全角半角を区別しない。
-- UTF8で使える照合順序の確認
show collation where charset = 'utf8';

-- DB単位での確認&変更
SELECT @@character_set_database, @@collation_database;
ALTER DATABASE COLLATE 'utf8_general_ci';

-- テーブル単位での確認&変更
SHOW TABLE STATUS FROM #{TABLE_NAME};
ALTER TABLE #{TABLE_NAME} COLLATE 'utf8_general_ci';

/etc/my.cnfの設定

# MySQLサーバの設定
[mysqld]
character-set-server = utf8
collation-server     = utf8_general_ci

# mysqldump用の設定
[mysqldump]
default-character-set = utf8

# MySQLクライアントの設定
# [client]でもOK
# 
# default-character-setの値に以下の変数が従属する
# - character_set_client
# - character_set_connection
# - character_set_results
[mysql]
default-character-set = utf8

参考