MySQL
MySQL
日本語処理完全解説
日本語処理完全解説
住商情報システム株式会社 住商情報システム株式会社 プラットフォームソリューション事業部門 プラットフォームソリューション事業部門 IT IT基盤ソリューション事業部基盤ソリューション事業部 オープンソースシステム部 オープンソースシステム部 玉川 玉川 修一修一2 2
今日の内容
今日の内容
MySQLMySQL概要概要 日本語処理対策日本語処理対策 – –問題点問題点 – –原因原因 – –解決策解決策MySQL
4 4
MySQL
MySQL
と
と
は
は
-- オープンソースのデータベースオープンソースのデータベース スウェーデン発スウェーデン発 オープンソースのオープンソースのRDBMSRDBMS 11日あたりの平均ダウンロード数日あたりの平均ダウンロード数55万万 全世界で全世界で10100000万万インストレーションインストレーション ( (20062006年年44月現在月現在)) MySQL
MySQL
バージョン
バージョン
-- 4.04.0以下と以下と4.14.1以上では日本語の取り扱いが異なる以上では日本語の取り扱いが異なる 5.1(beta)5.1(beta) 5.0(GA)5.0(GA) 4.1(GA)4.1(GA) 4.0(old)4.0(old) 3.23(old)3.23(old) ※6 6
日本語処理の留意点
日本語処理の留意点
-- いろいろありますいろいろあります 1. 1. 文字化け対策基礎文字化け対策基礎 2. 2. クライアントライブラリクライアントライブラリ 3. 3. ((株株))問題問題 4. 4. UnicodeUnicode変換変換ルール問題ルール問題 5. 5. ラウンドトリップラウンドトリップ変換問題変換問題 6. 6. JavaJavaのキャラクターセットのキャラクターセット 7. 7. 日本語日本語メタデータメタデータ 8. 8. 日本語全文検索日本語全文検索1.
8 8 FAQFAQ 特に特に ver.4.1ver.4.1のリリース以降のリリース以降 他のマルチバイトユーザーも経験他のマルチバイトユーザーも経験
“
“
MySQL
MySQL
で文字化けしてしまいます!
で文字化けしてしまいます!
”
”
文字化けの例
文字化けの例
-- 4.14.1ををインストールはしてみたもののインストールはしてみたものの……
mysql> CREATE TABLE t1(a CHAR(1))
mysql> CREATE TABLE t1(a CHAR(1))
> DEFAULT CHARACTER SET = sjis;
> DEFAULT CHARACTER SET = sjis;
Query OK, 0 rows affected (0.09 sec)
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t1 VALUES('
mysql> INSERT INTO t1 VALUES('ああ');');
Query OK, 1 row affected, 1 warning (0.05 sec)
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> SELECT * FROM t1;
mysql> SELECT * FROM t1;
+ +------++ | a | | a | + +------++ | ? | | ? | + +------++
1 row in set (0.00 sec)
1 row in set (0.00 sec)
10 10
MySQL
MySQL
で日本語使える
で日本語使える
?
?
-- はい、ちゃんと使えます。はい、ちゃんと使えます。 ほとんどの場合が「キャラクターセット」のほとんどの場合が「キャラクターセット」の 設定ミスが原因 設定ミスが原因 正しい設定を行えば、簡単に解決できる正しい設定を行えば、簡単に解決できる 場合が多数 場合が多数問題を理解する為に
問題を理解する為に
-- まずは基礎から解説まずは基礎から解説 キャラクターセットとは?キャラクターセットとは? 主なキャラクターセット主なキャラクターセット MySQLMySQL 4.04.0以下以下 とと MySQL 4.1MySQL 4.1以上以上のの 違い
12 12
MySQL
MySQL
のキャラクターセットとは?
のキャラクターセットとは?
-- 文字集合文字集合 + + 文字エンコーディング文字エンコーディング 文字集合文字集合 – –どんな文字が使えるかどんな文字が使えるか 文字エンコーディング文字エンコーディング – –どうやって文字を表現するかというルールどうやって文字を表現するかというルール sjis sjis エンコーディングで「あ」という文字はエンコーディングで「あ」という文字は 「「0x82A00x82A0」と表現」と表現
ujis ujis エンコーディングで「あ」という文字はエンコーディングで「あ」という文字は 「「0x0xA4AA4A22」と表現」と表現
MySQLMySQLのキャラクターセットのキャラクターセット – –文字集合と文字エンコーディングの組み合わせ文字集合と文字エンコーディングの組み合わせ 文字コード文字コード – –あるキャラクターセットにおける文字の値あるキャラクターセットにおける文字の値
現在使える
現在使える
主なキャラクターセット
主なキャラクターセット
-- 日本語が使えるのは日本語が使えるのは66種類種類
ujisujisの文字集合に加え、の文字集合に加え、 NECNEC特殊文字等の外字が使用可能特殊文字等の外字が使用可能
文字コード体系は文字コード体系はujisujisとほぼ同等とほぼ同等 eucjpms eucjpms MySQLMySQLのデフォルトキャラクターセットのデフォルトキャラクターセット latin1 latin1 各国言語が使用可能な国際文字集合各国言語が使用可能な国際文字集合 Ver. 4.1Ver. 4.1以降で使用可能以降で使用可能 ucs2 ucs2 各国言語が使用可能な国際文字集合各国言語が使用可能な国際文字集合 Ver. 4.1Ver. 4.1以降で使用可能以降で使用可能 utf8 utf8
sjissjisの文字集合に加え、の文字集合に加え、NECNEC特殊文字等の外字が使用可能特殊文字等の外字が使用可能
文字コード体系は文字コード体系はsjissjisとほぼ同等とほぼ同等 cp932 cp932 主に主にUnix/LinuxUnix/Linux向け向け sjissjisの文字集合に加えて、補助漢字等が使用可能の文字集合に加えて、補助漢字等が使用可能 ujis ujis 主に主にWindowsWindows向け向け
ASCIIASCII文字、ひらがな、カタカナ、文字、ひらがな、カタカナ、JISJIS第一、第二水準第一、第二水準 漢字等が使用可能
漢字等が使用可能
sjis sjis
14 14
MySQL 4.0
MySQL 4.0
以下の
以下の
処理
処理
-- 文字コードの変換はなし文字コードの変換はなし クライアントクライアント ⇒⇒ サーバーサーバー – –文字コードをそのまま格納文字コードをそのまま格納 サーバーサーバー ⇒⇒ クライアントクライアント – –文字コードをそのまま表示文字コードをそのまま表示 クライアント サーバー あ (0x82A0) あ (0x82A0)MySQL 4.0
MySQL 4.0
以下の
以下の
処理
処理
-- latin1latin1でも日本語でも日本語OKOK キャラクターセットにかかわらず、文字キャラクターセットにかかわらず、文字 コードをそのまま送受信 コードをそのまま送受信 キャラクターセットがデフォルトのキャラクターセットがデフォルトのlatin1latin1 でも日本語の利用は(見かけ上 でも日本語の利用は(見かけ上**)可能)可能 だった だった キャラクターセットキャラクターセットを変換する機能はないを変換する機能はない * *文字列長の認識や、ソート順序に影響有り文字列長の認識や、ソート順序に影響有り16 16
MySQL 4.
MySQL 4.
1
1
以上の
以上の
処理
処理
-- 機能が強化され高度になりました機能が強化され高度になりました テーブル(カラム)キャラクターセットのテーブル(カラム)キャラクターセットの 概念 概念 文字コード変換機能文字コード変換機能 + + UnicodeUnicodeサポートサポート 文字コードの自動変換機能文字コードの自動変換機能 キャラクターセット変数の追加キャラクターセット変数の追加MySQL 4.1
MySQL 4.1
以上
以上
の処理
の処理
-- テーブルキャラクターセットが指定できますテーブルキャラクターセットが指定できます
CREATE TABLE `table1` (
CREATE TABLE `table1` (
`column1` char(8) default NULL
`column1` char(8) default NULL
) ENGINE=
) ENGINE=MyISAMMyISAM
DEFAULT CHARSET=sjis
18 18
MySQL 4.1
MySQL 4.1
の処理
の処理
-- UnicodeUnicodeを介して変換できますを介して変換できます sjis ujis latin1 Unicode (ucs2) sjis ujis utf8 utf8 latin1 … …MySQL 4.1
MySQL 4.1
の
の
処理
処理
-- 自動変換が行われます自動変換が行われます クライアント サーバー sjis あ (0x82A0) ujis あ (0xA4A2) 自動変換20 20
MySQL 4.1
MySQL 4.1
の処理
の処理
-- 変数が増えました変数が増えました CREATE TABLE CREATE TABLE のデフォルトキャラクタセットのデフォルトキャラクタセット character_set_database character_set_database サーバー サーバー CREATE DATABASE CREATE DATABASE のデフォルトのデフォルト キャラクタセット キャラクタセット character_set_server character_set_server 返された結果を表示するキャラクタセット 返された結果を表示するキャラクタセット character_set_results character_set_results テーブルアクセスのないサーバ処理の テーブルアクセスのないサーバ処理の キャラクタセット キャラクタセット character_set_connection character_set_connection クライアント クライアント クライアントから入力された文字の クライアントから入力された文字の キャラクタセット キャラクタセット character_set_client character_set_client クライアントプログラム起動時の --default-character-set オプションが影響 mysqld起動時の --default-character-set オプションが影響文字化け発生のメカニズム
文字化け発生のメカニズム
-- こんなイメージですこんなイメージです((4.1 <4.1 <)) クライアント (Windowsマシン) サーバー 「あ」をInsert (0x82A0) サーバーdefault character set = sjis
クライアントキャラクタセット変数 character_set_client = latin1 character_set_connection = latin1 character_set_result = latin1 入力された文は ’0x82’と’0xA0’の 2文字だな 送られてきた文字は latin1の’0x82’と’0xA0’ か…。テーブルがsjis なので、sjisに変換だ! 今から送る文字は latin1の’0x82’と’0xA0’ だぞ! 文字化け発生 あ -> ??
22 22
文字化け発生の条件(
文字化け発生の条件(
4.1 <
4.1 <
)
)
-- ポイントは変換と文字集合ポイントは変換と文字集合 文字コードの変換が発生文字コードの変換が発生 UnicodeUnicodeととのマッピングが定義されていないのマッピングが定義されていない – –文字集合の範囲外文字集合の範囲外の文字を使用の文字を使用 – –変換元と変換先の文字集合の違い変換元と変換先の文字集合の違い対策
対策
(
(
4.1 <
4.1 <
)
)
-- ポイントはポイントは変換の回避変換の回避 文字コードの変換を回避文字コードの変換を回避 – –クライアントクライアント//サーバー間でキャラクターセットサーバー間でキャラクターセット を統一 を統一 変換元キャラクターセットと変換先キャラ変換元キャラクターセットと変換先キャラ クターセットの互換性を理解して使用 クターセットの互換性を理解して使用24 24
対策
対策
(
(
設定例
設定例
)
)
-- この様に設定すればこの様に設定すればOKOK mysqlmysqlクライアントを使う限りではクライアントを使う限りではmy.cnfmy.cnf ファイルで設定可能
ファイルで設定可能
[mysql
[mysqldd]] default
default--charactercharacter--set = sjisset = sjis
[mysql]
[mysql]
default
2.
26 26
問題はクライアントライブラリ
問題はクライアントライブラリ
-- キャラクターセットは常にキャラクターセットは常にlatin1latin1 my.cnfmy.cnfでは設定済みでは設定済み mysqlmysqlクライアントでは問題なしクライアントでは問題なし PHPPHP等のプログラムでは文字化け等のプログラムでは文字化け 問題はバイナリ配布のクライアント問題はバイナリ配布のクライアント ライブラリ ライブラリ キャラクターセットはキャラクターセットはlatinlatin11でコンパイルでコンパイル my.cnfmy.cnfファイルの設定ファイルの設定は影響しないは影響しない対策
対策
-- 方法は方法は33通り通り ソースからコンパイルソースからコンパイル ./configure./configure ----withwith--charset=charset=character_set_namecharacter_set_name
接続直後に接続直後にSET NAMES SET NAMES
character_set_name
character_set_nameの実行の実行
mysqldmysqld起動オプション起動オプション
----skipskip--charactercharacter--setset--clientclient--handshakehandshake (
28 28
対策
対策
-- この様に設定すればこの様に設定すればOKOK(例)(例) my.cnf my.cnf ファイルファイル [mysqld] [mysqld] defaultdefault--charactercharacter--set = sjisset = sjis skip
skip--charactercharacter--setset--clientclient--handshakehandshake
[mysql]
[mysql]
default
3.(
30 30
文字化けの例
文字化けの例
-- ㈱㈱が文字化け!が文字化け!mysql> CREATE TABLE `table1` (`column1` char(8))
mysql> CREATE TABLE `table1` (`column1` char(8))
--> ENGINE=MyISAM> ENGINE=MyISAM
--> DEFAULT CHARSET=utf8;> DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.12 sec)
Query OK, 0 rows affected (0.12 sec)
mysql> insert into table1 values('
mysql> insert into table1 values('㈱㈱');');
Query OK, 1 row affected, 1 warning (0.00 sec)
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from table1;
mysql> select * from table1;
+ +---++ | column1 | | column1 | + +---++ | ? | | ? | + +---++
「
「
(
(
株
株
)
)
」問題
」問題
-- 機種依存文字の文字化け機種依存文字の文字化け WindowsWindows機種依存文字が化けする問題機種依存文字が化けする問題 – –NEC特殊文字NEC特殊文字 – –IBMIBM拡張文字拡張文字 ––NEC選定NEC選定IBMIBM拡張文字拡張文字
WindowsWindowsのシフトのシフトJISJISととMySQLMySQLののsjissjisのの 文字集合の違いが原因
32 32
イメージ図
イメージ図
-- 変換マッピングルールがないのです変換マッピングルールがないのです ㈱ sjisの 文字集合sjis ucs2 utf8
マッピングなし マッピングあり マッピングあり 文字化け ? sjisではカバー されない 文字集合 ?
“
“
シフト
シフト
JIS
JIS
”
”
と
と
“
“
sjis
sjis
”
”
-- 実は違うんです実は違うんです
MySQLMySQLのの””sjissjis””ははIANAIANAのの””Shift_JISShift_JIS””
WinWinddowsowsのの””シフトシフトJISJIS””ははWindows Code Windows Code Page 932
Page 932(通称(通称cp932cp932))
IANAIANAではではWindows31Windows31--JJに相当に相当
cp932/Windows31cp932/Windows31--JJの文字集合をカの文字集合をカ バーするキャラクターセットが必要 バーするキャラクターセットが必要 ※ ※解決策は後ほど解決策は後ほど……
34 34
4.
Unicode
Unicode
変換
変換
ルール
ルール
問題
問題
-- 変換ルールも異なる変換ルールも異なる””シフトシフトJISJIS”” この世には異なるシフトこの世には異なるシフトJISJISが存在が存在 それぞれ一部の文字についてそれぞれ一部の文字についてUnicodeUnicode への変換 への変換ルールルールが異なるが異なる 変換が異なる文字変換が異なる文字の例の例 \∼‖−¢£¬ \∼‖−¢£¬36 36
Unicode
Unicode
変換
変換
ルール
ルール
問題
問題
-- UnicodeUnicodeに変換すると違う文字に!に変換すると違う文字に!0x8191
¢
(全角)sjis
0x8191
¢
(全角) その他ucs2
0x00A2¢
(半角) 変換の結果 全角が半角に! 0xFFE0¢
(全角) 変換後も 全角のままsjis
※ ※解決策は後ほど解決策は後ほど……5.
38 38
ラウンドトリップ問題
ラウンドトリップ問題
-- ディレクトリパスがディレクトリパスが変変!?!?ラウンドトリップ問題
ラウンドトリップ問題
-- こんなイメージですこんなイメージです0x005C
¥
ucs2
sjis
0x5C
¥
異なる複数の 文字が一つの 文字に変換0x815F
\
戻す時はどちらか 選ばないといけない 結果として片方の文字が 消えてしまうsjis
0x5C
¥
0x815F
\
40 40
解決策
解決策
-- cp932cp932でで33つまとめてつまとめて全て解決全て解決 新キャラクターセット新キャラクターセットcp932cp932を実装を実装 33つの問題すべて解決つの問題すべて解決 – –㈱問題㈱問題 – –UnicodeUnicode変換ルール問題変換ルール問題 – –ラウンドトリップ問題ラウンドトリップ問題 ちなみにこれ、ちなみにこれ、SCSSCSがやりましたがやりましたcp932
cp932
とは
とは
sjissjisの拡張の拡張 WindowsWindows機種依存文字をカバー機種依存文字をカバー – –((株株))問題を解決問題を解決 WindowsWindows方式の方式のUnicodeUnicode変換変換
–
–UnicodeUnicode変換ルール問題を解決変換ルール問題を解決 –
42 42
sjis
sjis
と
と
cp932
cp932
-- 文字集合が違います文字集合が違います jisx0201 jisx0208 jisx0201 jisx0208 ㈱Ⅰ①㍻℡ 纊褜鍈 纊褜鍈 %&¥ abc 123 アイウ あいう アイウ 亜井宇 sjis cp932 NEC特殊文字 IBM拡張文字 NEC選定IBM拡張文字0x8191
¢
sjis
0x5C
¥
ucs2
cp932
sjis
sjis
と
と
cp932
cp932
-- 変換ルールも違います変換ルールも違います0x815F
\
0xFFE0
¢
0x005C
¥
0xFF3C
\
44 44
sjis
sjis
と
と
cp932
cp932
-- ユーザー定義領域もカバーユーザー定義領域もカバーeucjpms
eucjpms
-- ujisujisでで((株株))を使いたい場合にを使いたい場合に ujisujisの拡張の拡張 cp932cp932互換互換 ujisujisででWindowsWindows機種依存文字を格納機種依存文字を格納するする 場合に使用 場合に使用 5.05.0以上で使用可能以上で使用可能 詳細は日本 詳細は日本MySQLMySQLパートナ会の記事参照パートナ会の記事参照 「
46 46
対処方法
対処方法
-- sjis/ujissjis/ujisよりもよりもcp932/eucjpmscp932/eucjpmsをを
my.cnf
my.cnf ファイルファイル [mysqld]
[mysqld] default
default--charactercharacter--set = cp932set = cp932 skip
skip--charactercharacter--setset--clientclient--handshakehandshake [
[mysqlmysql]] default
default--charactercharacter--set = cp932set = cp932
※
※cp932: 4.1,5.0cp932: 4.1,5.0 ※
6.Java
48 48
Java
Java
と
と
MySQL
MySQL
のキャラクターセット
のキャラクターセット
-- Connector/JConnector/Jでマッピングでマッピング キャラクターセットの名称はキャラクターセットの名称はJavaJavaとと MySQL MySQLで異なるで異なる ConnectorConnector/J/Jでマッピングでマッピング 例) 例) (
(MySQLMySQL) cp932 = (Java) MS932) cp932 = (Java) MS932 (MySQL) cp932 = (Java) Windows
cp932
cp932
使用時の問題点
使用時の問題点
-- ConnectorConnector/J/Jががcpcp932932に完全対応していなかったに完全対応していなかった WindowsWindows--31J(MS932)31J(MS932)を使用してもを使用しても(株(株))をを 表示できない 表示できない!?!? INSERTは出来るがINSERTは出来るがSELECTSELECTはエラーにはエラーに
JVM MySQL Connector/J cp932 cp932 MS932 MS932 cp932 cp932 WINDOWS31 WINDOWS31--JJ cp932 cp932 未定義 未定義 cp932 cp932 未定義 未定義 cp932 cp932 MS932 MS932 cp932 cp932 WINDOWS31 WINDOWS31--JJ
50 50
eucjpms
eucjpms
使用時の問題点
使用時の問題点
-- eucjpmseucjpmsととJavaJavaのキャラクターセットは非完全互換のキャラクターセットは非完全互換
eucjpmseucjpmsは互換性のないは互換性のないEUC_JPEUC_JPにに マッピング マッピング ((株株))が文字化けが文字化け JVM MySQL Connector/J eucjpms eucjpms EUC_JP EUC_JP eucjpms eucjpms EUC_JP
EUC_JPEUC_JP_Solaris eucjpmseucjpms
EUC_JP_Solaris
eucjpms
eucjpms
EUC_JP_Solaris
EUC_JP_Solaris
EUC_JP_Solaris
-- better solution (better solution (≠≠best solution)best solution)
eucjpmseucjpmsと完全互換のわけではないと完全互換のわけではない 互換互換性のない文字性のない文字 ― ― ‖‖ −− ¢¢ ££ ¬¬ ¦¦ このようなこのような特殊記号のみ影響特殊記号のみ影響但し、但し、((株株)) は使用可能 は使用可能 実は実はここれもれもSCSSCSが実装しましたが実装しました
52 52
対策
対策
-- JavaJavaでのまとめでのまとめ cp932cp932にはにはWINDOWSWINDOWS--31J / MS932を使用31J / MS932を使用 eucjpmseucjpmsにはにはEUC_JP_SolarisEUC_JP_Solarisを使用を使用
– –互換性のない文字について留意すること互換性のない文字について留意すること ujis EUC_JP eucjpms EUC_JP_Solaris sjis SJIS utf8 UTF-8 cp932 MS932 cp932 WINDOWS-31J MySQL側キャラクターセット Java側キャラクターセット (Connector/J ver. 3.1.9以上)
7.
54 54
日本語テーブル名問題
日本語テーブル名問題
データベース名データベース名 = = ディレクトリ名ディレクトリ名 テーブル名テーブル名 = = ファイル名ファイル名 44.1.1以降、テーブル名、データベース名以降、テーブル名、データベース名 等のメタデータは 等のメタデータはutf8utf8で保存で保存 データベース名やテーブル名に日本語データベース名やテーブル名に日本語 を使用した場合に問題あり( を使用した場合に問題あり(bug#3906bug#3906))55 55
日本語テーブル名問題
日本語テーブル名問題
Utf8で保存された名前を 使用してファイルを作成し、 シフトJISで表示している為56 56
日本語テーブル名問題
日本語テーブル名問題
@+Unicode コードポイントで表現 Ver. 5.1.6.にて修正対処方法
対処方法
-- 日本語メタデータの使用は控えましょう日本語メタデータの使用は控えましょう 55.1.1を使用するまでは、日本語メタデータを使用するまでは、日本語メタデータ を使用しない方が安全 を使用しない方が安全58 58
8.
MySQL
MySQL
で日本語全文検索
で日本語全文検索
-- 機能はあるが分かち書きが必要機能はあるが分かち書きが必要 MyISAMMyISAMストレージエンジンでは全文検ストレージエンジンでは全文検 索用インデックスをサポート 索用インデックスをサポート 但し日本語但し日本語のような言語には未対応のような言語には未対応 別途分かち書きを行ってやる必要有り別途分かち書きを行ってやる必要有り60 60
対処方法
対処方法
-- MySQLMySQL++SennaSenna**という選択肢という選択肢
組み込み型の全文検索エンジン組み込み型の全文検索エンジン MySQLMySQLに組み込み可に組み込み可 SQLSQLのみで全文検索が可能にのみで全文検索が可能に * *未来検索ブラジルが開発した、オープンソースソフトウェア未来検索ブラジルが開発した、オープンソースソフトウェア
MySQL
MySQL
+
+
Senna
Senna
-- 詳細はこちらで詳細はこちらで
OpenOpen Source PavilionSource Pavilion
–
–MySQL + MySQL + SennaSennaを紹介を紹介
ブースブース
–
62 62
まとめ
まとめ
まとめ
まとめ
-- MySQLMySQLでの日本語処理対策での日本語処理対策 5.1まで原則使用しない 日本語メタデータ 1. cp932に対してMS932/Windows-31Jを使用 2. Eucjpmsに対してEUC_JP_Solarisを使用 JAVA ラウンドトリップ問題 Unicode変換ルール問題 キャラクターセットcp932の使用 (株)問題 1. --skip-client-character-set-handshake 2. SET NAMES キャラクターセット名 3. ./configure –with-charset=キャラクターセット名 クライアントライブラリ キャラクターセットオプションの設定 基本的な文字化け 対策 対策 課題 課題64 64