MySQL日本語利用徹底入門

<Insert Picture Here>

MySQL 日本語利用徹底入門

日本オラクル株式会社 MySQL Global Business Unit
テクニカルアナリスト奥野幹也
2012 年 5 月 29 日
Copyright© 2012, Oracle. All rights reserved.

2

以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。ま
た、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはでき
ません。以下の事項は、マテリアルやコード、機能を提供することをコミットメント（確
約）するものではないため、購買決定を行う際の判断材料になさらないで下さい。オ
ラクル製品に関して記載されている機能の開発、リリースおよび時期については、弊
社の裁量により決定されます。

Oracle と Java は、 Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。



文字コードとは


文字コードは文字を符号化したもの。
● ASCII
● ７ビットで表現可能なラテン文字を中心とした文字コード
● a ---> 0x61, b ---> 0x62, c ---> 0x63
● JIS X 208
● Shift_JIS … 日本 ---> 0x93FA967B
● EUC-JP … 日本 ---> 0xC6FCCBDC
● UTF-8 … 日本 ---> 0xE697A5E69CAC

符号化の方式にはいくつか種類がある



MySQL と文字コード


MySQL の文字コードは結構複雑？

MySQL の文字コードの実装は
とても柔軟

柔軟さが増せば複雑さも増す・・・


MySQL で利用可能な日本語対応文字コード

文字コード名対応文字ストレージサイズ

sjis JIS X 0208:1997 1 〜 2 バイト

cp932 JIS X 0208:1997 + NEC 特殊文字 1 〜 2 バイト
・ IBM 拡張文字
ujis JIS X 0208:1997 1 〜 3 バイト

eucjpms JIS X 0208:1997 + NEC 特殊文字 1 〜 3 バイト
utf8 JIS X 0208:1997 + NEC 特殊文字 1 〜 3 バイト
utf8mb4 JIS X 0213:2004 1 〜 4 バイト


照合順序
文字の並び順を定義したもの。
mysql> set names utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'abc' = 'ABC';
+---------------+
| 'abc' = 'ABC' |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

mysql> set names utf8 collate utf8_bin;
ソートや比較の
mysql> select 'abc' = 'ABC';
結果に影響
+---------------+
| 'abc' = 'ABC' |
+---------------+
| 0 |
+---------------+


MySQL で使用可能な照合順序
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |

:
:

| eucjpms_japanese_ci | eucjpms | 97 | Yes | Yes | 1 |
| eucjpms_bin | eucjpms | 98 | | Yes | 1 |
+--------------------------+----------+-----+---------+----------+---------+
197 rows in set (0.01 sec)

照合順序は文字コードにマッチしている必要がある


文字コードはセッションごとに異なるかも。

UTF-8

セッション 1 クライアント 1

セッション 2 クライアント 2

Shift_JIS
MySQL サーバー


文字コードはテーブルごとに異なるかも。

Shift_JIS

テーブル
UTF-8

EUC_JP
テーブルテーブル

MySQL サーバー


文字コードはカラムごとに異なるかも。

UTF-8
カラム 1
Shift_JIS

カラム 2

カラム 3
binary

テーブル

MySQL サーバー


MySQL サーバー内の文字コード変換

④ データを ② クエリの実行
蓄える際のに利用する ① 送信する
文字コード文字コード SQL 文に対する
⑤ テーブル名や文字コード
カラム名に対する
文字コード

セッションクライアント
テーブル

MySQL サーバー
③ クエリの
実行結果に対する
⑥ ファイル名を文字コード
解決する際の
文字コード
ファイルシステム出展：エキスパートのための MySQL
［運用＋管理］トラブルシューティングガイド


文字コードを確認する
● SHOW [GLOBAL] VARIABLES LIKE 'char%';
(1)character_set_client
(2)character_set_connection
(3)character_set_results
(4)character_set_server/character_set_database
(5)character_set_system
(6)character_set_filesystem



テーブルの文字コード


テーブルの文字コード（１）
● カラムごとに文字コードを指定可能

CREATE TABLE t (
a VARCHAR(100) CHARACTER SET cp932,
b VARCHAR(100) CHARACTER SET eucjpms,
c VARCHAR(100) CHARACTER SET utf8,
:
);

超柔軟

テーブルの文字コード (2)
CREATE TABLE t (
c VARCHAR(100),
:
) CHARACTER SET utf8;

指定がない場合にはテーブルのデフォルト文字コードに


テーブルの文字コード (3)
CREATE TABLE t (
c VARCHAR(100),
:
);

テーブルの文字コードの指定がない場合には・・・
● データベースの文字コードがテーブルのデフォルト文字コードに

● テーブルのデフォルト文字コードがカラムの文字コードに


データベースの文字コード
CREATE DATABASE d1 CHARACTER SET utf8;
CREATE DATABASE d2;

指定がない場合には character_set_server に

SET GLOBAL character_set_server = utf8


文字コードのデフォルト値

characer_set_server

データベースの文字コード
（ character_set_database ）

テーブルのデフォルト文字コード

優先度が
カラムの文字コード最も高い


照合順序の設定方法
CREATE TABLE t (
a VARCHAR(100) CHARACTER SET utf8
COLLATE utf8_bin,
:
);

CREATE DATABASE d CHARACTER SET utf8
COLLATE utf8_bin;

SET GLOBAL collation_server = utf8_bin;


テーブルの文字コードの確認方法 (1)
mysql> show create table CityG
*************************** 1. row ***************************
Table: City
Create Table: CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1


mysql> show full fields in CityG
*************************** 1. row ***************************
中略
*************************** 2. row ***************************
Field: Name
Type: char(35)
Collation: latin1_swedish_ci
Null: NO
Key:
Default:
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 3. row ***************************
:


mysql> select table_schema, table_name, column_name,
collation_name from information_schema.columns where
collation_name not like 'latin%' and table_schema not
in ('mysql', 'information_schema',
'performance_schema');
Empty set (0.00 sec)



オプション


文字コード関係のオプション
● character_set_server … データベースの文字コードのデ
フォルトになる
● character_set_database … データベースの文字コード。
テーブルの文字コードのデフォルトになる。 CREATE
DATABASE 実行時に指定したものと同じ。
● character_set_connection … クライアントごとに設定可
能な文字コード。 SQL の評価時に適用される。
● character_set_client … クライアントから送信される文字
データの文字コード。
● character_set_results … クライアントへ送信される結果
に含まれる文字データの文字コード。
● character_set_system … テーブル名などの文字コード。
常に utf8 。
● character_set_filesystem … ファイルシステムへアクセ
スする際に用いられる文字コード。


照合順序関係のオプション
● collation_server … データベースの照合順序のデ
フォルトになる。
● collation_database … データベースの照合順序。
テーブルの照合順序のデフォルトになる。 CREATE
DATABASE 実行時に指定したものと同じ。
● collation_connection … クライアントごとに設定可
能な照合順序。 SQL の評価時に適用される。


skip_character_set_client_handshake
● クライアントが指定した文字コードを無視してサー
バーが指定した文字コードを使用
● C API や PHP 利用時に便利
● Connector/J では効かないので注意


default_character_set
● 古いオプションなのでサーバーでは利用しないよう
● MySQL 5.5 で廃止
● クライアントでは現在も利用中



ドライバ


C API
mysql = mysql_init(NULL);
if (!mysql_real_connect(mysql, NULL, "user",
"password", "db", 3306,
NULL, 0))
{
printf("Cannot connect: %s", mysql_error(mysql));
goto err;
};
if (mysql_set_character_set(mysql, "utf8"))
goto err;


PHP
● モジュールごとに指定法が異なる
● MySQL 拡張モジュール（ mysql ） … mysql_set_charset() ま
たは SET NAMES
● MySQL 改良型拡張モジュール（ mysqli ） …
mysqli_set_charset() または mysqli::set_charset() または
SET NAMES
● PDO … SET NAMES
<?php
$dbh = mysql_connect('hostname:3306', 'user', 'password');
if (!$dbh) {
die('Failed to connect MySQL Server: ' . mysql_error());
}
if(!mysql_set_charset('utf8', $dbh)) {
die('Failed to change charset.');
}


Perl
● mysql_enable_utf8 属性を指定して、 UTF-8 フラグ
が有効な文字列を使う。
● mysql_enable_utf8 属性を指定せず、 MySQL
Server から受け取った文字列を decode() 、 MySQL
Server へ渡す文字列を encode() する。

my $dbh = DBI->connect(
"DBI:mysql:database=db_name;host=hostname",
"user", "password",
{ RaiseError => 1, mysql_enable_utf8=>1 });


Java （ Connector/J ）
● 文字コードは characterEncoding プロパティで指定
● 指定がない場合には接続先の character_set_server により決
定。接続後 SET NAMES をドライバが実行。
● --skip-character-set-client-handshake は効かない
● characterEncoding が何であれ Java の文字列は
ucs2 に変換される。
Class.forName("com.mysql.jdbc.Driver");
Properties props = new Properties();
props.setProperty("user", "mikiya");
props.setProperty("password", "mikiya's password");
props.setProperty("characterEncoding", "UTF-8");
conn = DriverManager.getConnection(url, props);


Ruby
#!/usr/bin/ruby -Ks
:
my = Mysql.init()
my.options(Mysql::SET_CHARSET_NAME, 'cp932')
my.real_connect('127.0.0.1', 'user',
'password', 'db')
res = my.query('SELECT some_str_col FROM
some_tbl WHERE...');
res.each do |row|
puts row[0] # シフト JIS （ Cp932 ）で出力されま
す。
:
end



文字コード関係の
トラブル


文字化けの原因と対策
● 実際のデータと文字コードが一致していないと化け
る。
● 接続用の文字コードは問題ないか？
– アプリケーションが期待している文字コードとドライバの文
字コードは同じか？
● 端末の文字コードは問題ないか？
● テーブル内のデータは問題ないか？


LOAD DATA INFILE/SELECT … INTO OUTFILE
● LOAD DATA INFILE
● ファイルの文字コードが character_set_database になってい
ることを期待している
● SET character_set_database = charset_name
● テーブルの文字コードと同じ場合には binary を指定する
● mysqlimport を使う
● SELECT … INTO OUTFILE
● デフォルトでは文字コード変換しない
– LOAD DATA で読むときは binary を指定すると良い
● SELECT … INTO OUTOFILE 'file_name' CHARACTER SET
charset_name ...


latin1 で格納されている？
● セッションの文字コードもテーブルの文字コードも
latin1 の場合、文字コードの変換が起きないので、
文字化けも起きない。
● 照合順序が狂ってしまう (latin1_swedish_ci を用い
て 1 バイトずつ比較されてしまう )
● 本来の文字コードを指定して接続すると , 文字化け
してしまう


latin1 で格納されている？（つづき）
mysql> set names latin1;

mysql> create table t (a char(30) character set latin1);

mysql> insert into t values(' これはテストです。 ');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+-----------------------------+
| a |
+-----------------------------+
| これはテストです。 |
+-----------------------------+


5C 問題
Shift_JIS の２バイト目が 0x5C （￥）であることに起
因する問題。リテラルとして解釈すると 0x5C がエス
ケープ文字として扱われてしまう。

mysql> set names sjis; mysql> set names latin1;
Query OK, 0 rows Query OK, 0 rows
affected (0.00 sec) affected (0.00 sec)

mysql> select ' 表 abc'; mysql> select ' 表 abc';
+-------+ +------+
| 表 abc | | 病 bc |
+-------+ +------+
| 表 abc | | 病 bc |
+-------+ +------+
1 row in set (0.00 sec) 1 row in set (0.00 sec)


ラウンドトリップ変換
mysql> create table t (a char(1) character set eucjpms);

mysql> insert into t values(unhex('8FA2B7'));
Query OK, 1 row affected (0.01 sec)
mysql> select hex(a) from t;
+--------+
| hex(a) |
+--------+
| 8FA2B7 |
+--------+
mysql> select hex(convert(convert(a using cp932) using eucjpms)) from t;
+----------------------------------------------------+
| hex(convert(convert(a using cp932) using eucjpms)) |
+----------------------------------------------------+
| A1C1 |
+----------------------------------------------------+



まとめ


まとめ
● MySQL で利用可能な日本語対応文字コードの種類
● MySQL はカラムごと、セッションごとに文字コー
ドを指定可能
● 文字コードが異なる場合には自動変換
● 自動変換が起きないようにするのが理想
● 使用するプログラミング言語にあわせて、正しく文字コードを指
定する
● 迷ったらすべて utf8 で統一する
● SHOW コマンドや INFORMATION_SCHEMA で使
用中の文字コードを確認


46

宣伝
●
好評につき「エキスパートのための MySQL ［運
用＋管理］トラブルシューティングガイド」近日増
刷予定 !!
● 様々な角度から MySQL のトラブルを解析するのに役立つ書籍
です。ぜひ安心な DBA 生活のお供に一冊どうぞ。
● Contents
● 第 1 章 MySQL の概要
● 第 2 章開発時における問題
● 第 3 章 MySQL の状態を見る
● 第 4 章 DTrace
● 第 5 章運用中に起きる諸問題
● 第 6 章堅牢な運用を実現するために
● 第 7 章ソースコードのビルド


49


MySQL日本語利用徹底入門

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Viewers also liked

Viewers also liked (20)

Similar to MySQL日本語利用徹底入門

Similar to MySQL日本語利用徹底入門 (20)

More from Mikiya Okuno

More from Mikiya Okuno (20)

Recently uploaded

Recently uploaded (7)

MySQL日本語利用徹底入門