MySQLのサーバー、データベース、テーブル、カラムの文字コード設定とクライアントからのサーバー接続時の文字コード設定

MySQLでは、テーブルのカラム単位に文字コードを設定できますが、いちいちすべてのカラムに定義しなくてすむように、テーブル単位、データベース単位、サーバー単位にデフォルトの文字コードを設定できるようになっています。
　

１）サーバー単位の文字コード設定

　
・サーバー単位に文字コードと照合順序を設定できる。
・設定していない場合のデフォルトはlatin1。
・サーバー開始時のコマンドのオプションまたはオプションファイルで指定出来る。
・サーバー単位の文字コード設定は、データベース作成時に文字コードを指定しなかった場合にデータベースの文字コードとして設定されるだけで、それ以外には意味を持たない。
・サーバー単位の文字コード設定は、サーバー開始時に変更できる。
　
①サーバー開始のコマンドでの設定例

shell> mysqld --character-set-server=utf8 \
           --collation-server=utf8_unicode_ci

　
②オプションファイルでの設定例

〇/etc/my.cnf
　
[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci
　
※Linux環境のMySQLオプションファイルの読込順

/etc/my.cnf		Global options
/etc/mysql/my.cnf	Global options (as of MySQL 5.1.15)
SYSCONFDIR/my.cnf	Global options
$MYSQL_HOME/my.cnf	Server-specific options
defaults-extra-file	The file specified with --defaults-extra-file=path, 		if any
~/.my.cnf		User-specific options

　

２）データベース単位の設定

　
・データベース単位に文字コードを設定できる。
　
・データベース作成時に文字コードを指定
CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;
　
・既存のデータベースを変更する場合
ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;
　
・データベース作成時に文字コードを指定しなかった場合は、サーバー単位の設定が適用される。
　
・データベース単位の文字コード設定は、テーブル作成時に文字コードを指定しなかった場合に、そのテーブルの文字コードに指定される。
　さらに”LOAD DATA INFILE”実行時にもこのデータベース単位の文字コード設定が使用される。
　
上記以外にはこのデータベース単位の文字コード設定は意味を持たない。
　

３）テーブル単位の設定

　
・すべてのテーブルは、テーブル単位の文字コード設定を持つ。
　
・テーブル定義文

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

　
・テーブル定義変更文

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

　
・明示的に指定しなかった場合は、データベース単位の文字コード設定が適用される。
　
・各カラムの定義に文字コードが指定されていない場合は、このテーブル単位の文字コードが適用される。
　

４）カラム単位の設定

　
・CHAR, VARCHAR, TEXTのタイプのカラムでは、カラム単位に文字コードを指定出来る。

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

・カラム単位に文字コードが指定されていなかった場合は、テーブル単位の文字コード設定が適用される。

サーバー単位の文字コード設定（character_set_server、collation_serverシステム変数）、データベース単位の文字コード設定（character_set_database、collation_databaseシステム変数）とは別に下記３つのサーバー接続用のシステム変数があります。
　
①character_set_client
・クライアントから送られたクエリーの文字コード
　
②character_set_connection、collation_connection
・サーバーがクライアントから受け取ったクエリーをどの文字コードに変換して処理するか？
　
③character_set_results
・サーバーが処理した結果をどの文字コードでクライアントに送り返すか？
　
●上記３つのデフォルト値
　
①何も指定しない場合
　
特にサーバー接続時に明示的に指定していない場合は、コンパイル時のデフォルトであるlatin1に設定される。
　
②–default-character-set
　
mysqlコマンドでサーバー接続時に–default-character-setオプションを指定するとこの文字コードに設定される。
　
例）mysql -u user -p –default-character-set=utf8

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

　
③/etc/my.cnfファイルに設定
　
[mysql]
default-character-set=utf8
　

●実行例

　
テーブルの文字コード設定がutf8の場合
　
１）クライアントのTeraTermの文字設定がutf8の場合
　
①クエリーに日本語を含まない場合（クライアントから日本語情報を送信しない場合）
　
少なくてもcharacter_set_resultsをutf8にしないとクエリーの結果が文字化けします。

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> select name from markers limit 3;
+-----------+
| name      |
+-----------+
| Name      |
| ??????    |
| ????????? |
+-----------+

mysql> SET character_set_results = utf8;

mysql> select name from markers limit 3;
+-----------------------------+
| name                        |
+-----------------------------+
| Name                        |
| おとめ山公園                |
| 薬王院　ボタンの花          |
+-----------------------------+

　
②クエリーに日本語情報を含む場合
　
character_set_client、character_set_connectionもutf8にしないとクライアントが送信した日本語を認識できず、正しくクエリーが実行されません

mysql> select name from markers where name like "%公園" limit 3;
Empty set (0.01 sec)

mysql> SET character_set_client = utf8;
mysql> SET character_set_connection = utf8;

mysql> select name from markers where name like "%公園" limit 3;
+--------------------+
| name               |
+--------------------+
| おとめ山公園       |
| 哲学堂公園         |
| 甘泉園公園         |
+--------------------+

　
２）クライアントのTeraTermの文字設定がsjisの場合
　
①クエリーに日本語を含まない場合（クライアントから日本語情報を送信しない場合）
　
少なくてもcharacter_set_resultsをsjisにしないとクエリーの結果が文字化けします。

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> SET character_set_results = sjis;

mysql> select name from markers limit 3;
+--------------------+
| name               |
+--------------------+
| Name               |
| おとめ山公園       |
| 薬王院　ボタンの花 |
+--------------------+

　
②クエリーに日本語情報を含む場合
　
character_set_clientをsjis、character_set_connectionもutf8（テーブルの文字コードはutf8のため）にしないとクライアントが送信した日本語を認識できず、正しくクエリーが実行されません

mysql> select name from markers where name like "%公園" limit 3;
Empty set (0.01 sec)

mysql> SET character_set_client = sjis;

mysql> select name from markers where name like "%公園" limit 3;
Empty set (0.00 sec)

mysql> SET character_set_connection = utf8;

mysql> select name from markers where name like "%公園" limit 3;
+--------------+
| name         |
+--------------+
| おとめ山公園 |
| 哲学堂公園   |
| 甘泉園公園   |
+--------------+

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | sjis                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | sjis                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)