mysql.exeでUTF-8を使う

拙著『Webアプリケーション』では、Windowsのコマンドプロンプトでmysql.exeを対話的に使うときは、「mysql -uユーザ名 -pパスワード --default-character-set=cp932」のようにして、文字コードをcp932にすることを推奨しています。理由は2つあります。

  1. コマンドプロンプトでUTF-8を利用するのが面倒(特にWindows XPで)
  2. MySQL 5.5以前のmysql.exeにはUTF-8に関するバグがある

第1の問題は完全には解決されていません。日本語環境のコマンドプロンプトのデフォルトの文字コードはcp932ですが、プロパティでフォントをMS ゴシックに変更してから「chcp 65001」とすると、UTF-8で日本語を利用できます(変更してからプロパティを開いてはいけません)。面倒です。Windows XPではさらに作業が必要だったのですが、XP自体がそろそろ終わりなのでまあいいでしょう。(MS ゴシック以外のフォントを使いたい場合は先に一手間必要です。「コマンドプロンプト フォント 変更」などで探してください。)

第2の問題はMySQL 5.6で解決されました(XAMPPなら1.8.3以降)。

というわけで、前準備(フォントの変更)がバッドノウハウではありますが、p.100の脚註11とp.188の「WindowsのコマンドプロンプトではCP932(Windows-31J)しか使えない」という記述は昔話になりました。

コマンドプロンプトでUTF-8を使っている様子を以下に掲載します(設定ファイルはデフォルトのまま、「--default-character-set=utf8」や「SET NAMES utf8;」は無くても大丈夫です)。

C:\>chcp 65001
Active code page: 65001

C:\>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 77
Server version: 5.6.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8                           |
| character_set_connection | utf8                           |
| character_set_database   | latin1                         |
| character_set_filesystem | binary                         |
| character_set_results    | utf8                           |
| character_set_server     | latin1                         |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

mysql> CREATE TABLE t (name TEXT) CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT t(name) VALUES ('안녕하세요');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t;
+-----------------+
| name            |
+-----------------+
| 안녕하세요      |
+-----------------+
1 row in set (0.00 sec)

きれいに表示されないという問題が残っていますね。

『Webアプリケーション構築入門』のウェブアプリをMavenで管理する方法

拙著『Webアプリケーション構築入門』では、NetBeansやEclipseといったIDEの上でウェブアプリを開発する方法を紹介しています。IDEを使うので、ビルドプロセスは自動化されているわけですが、必要なライブラリの導入は手動ですし、機械的なテストは導入してもいません(紙面の限られた入門書であるため)。このあたりの欠点を補うためには、いろんなことを学ばなければなりませんが、ビルドやテスト、依存関係管理などを自動化するツールであるMavenを導入することは、その第一歩としてふさわしいものでしょう。

ここでは拙著のウェブアプリをMavenで管理する方法を紹介します。

NetBeansで新しいプロジェクト(Maven→Webアプリケーション)を作成し、Mavenのためのファイルpom.xmlの、<dependencies>から</dependencies>の間に、以下の要素を追記します。

<dependency>
  <groupId>commons-lang</groupId>
  <artifactId>commons-lang</artifactId>
  <version>2.3</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.20</version>
</dependency>

これは、p. 86の作業「Apache Commons Lang」の導入と、8.2.2項(p. 131)の作業「MySQL JDBC ドライバの追加」に代わるものです。この記述があれば、ライブラリを追加する作業は不要になります。

必要な作業はこれだけです。あとは本書の通りにサーブレットやJSPを作ってください。

Twitter時代の文字の数え方

正確には、「Unicode 3.1時代の文字の数え方」なのでしょうが、Unicodeの最新バージョンが6.0の今、それではぱっとしないので。

はじめに

一言で言えば、「𠮷野家」は3文字なのか4文字なのか、という話です。これはもちろん3文字で、Twitterの1回のつぶやきに46個含められます。しかし、4文字だとみなされるケースがたくさんあるのです。

30過ぎて数も数えられない、なんてことになるとは思っていなかったのですが、文字を数えるのはけっこう難しいです。とはいえ、Twitterがこれだけ普及しているのに、「難しい」と言って済ますわけにもいかないので、ちょっとまとめておきましょう(拙著『Webアプリケーション構築入門』で使ったもの+αで)。

注意:ちゃんとやるためには、Unicode正規化の話とか、もしかしたらIVSの話とか、いろいろやらなければいけないのかもしれませんが、ここでは話を少し単純にしています。

まとめのまとめ

Unicodeの登録文字数が少ない時代に作られたシステムが、登録文字数の多い時代に対応できていないという問題があります。例えば「𠮷」(U+20BB7)はもちろん1文字ですが、UTF-8・UTF-16ともに、想定を超える4バイトで表されるため、2文字とみなされることがあります(UTF-8のナイーブな想定は3バイト、UTF-16のそれは2バイト)。

𠮷
コードポイント U+20BB7 U+91CE U+5BB6
UTF-8 F0 A0 AE B7 E9 87 8E E5 AE B6
4バイト 3バイト 3バイト
UTF-16 D842 DFB7 91CE 5BB6
4バイト 2バイト 2バイト

対策方法は以下の通りです。

  • HTML5のフォーム検証:あきらめる
  • JavaScript:注意が必要(ふつうの方法ではダメ)
  • PHP:問題なし
  • Java:注意が必要(ふつうの方法ではダメ)
  • MySQL:5.1以前ではあきらめる。5.5以降ではutf8mb4を使う
  • Perl 5.?:問題なし(コメントでの指摘)
  • C#:注意が必要(ふつうの方法ではダメ。正規表現も要注意)
  • Visual Basic:注意が必要(ふつうの方法ではダメ。正規表現も要注意)
  • ASP.NET:注意が必要(ふつうの方法ではダメ。正規表現も要注意)

PerlやPHPは問題ないとは言っても、MySQLを使うようなアプリケーションでは問題が起こることに注意してください。

この結論に至る理由を、少し詳しく説明します。


クライアント側

HTML5

HTML5のフォームでは入力されたデータの検証ができるようになっているのですが、この機能を使って文字数をチェックするのは、やめたほうがよさそうです。

maxlength属性

HTML5のフォームでは、input要素やtextarea要素において、入力できる文字数をmaxlength属性で指定できるようになっています。たとえば、次のようなinput要素では、3文字までしか入力できません。

<input type="text" maxlength="3" />

ところが、ブラウザによっては、ここに「𠮷野家」という3文字を入力できません。そもそもこの3文字を正しく表示できないブラウザもあるようなので、手元のWindows上のブラウザで調べてみました(環境に依ります。たとえば、MacのSafariでは表示が○になります)。

ブラウザ 表示 入力
Chrome 12.0.742.100
Safari 5.0.5 ×
Firefox 21.0 ×
IE 8 ×
IE 9 ×
IE 10
Opera 12.15 × ×

入力「×」のブラウザでは、「𠮷」が2文字とみなされるため、2文字目まで、つまり「𠮷野」までしか入力できません。

Mozillaの文書には、Unicode code pointsで数えると書いてあるので、そのうち改善されるのかもしれませんが、現時点ではTwitterのために「maxlength="140"」を使うことはできません。

pattern属性

Firefox 21とChrome 27、IE 10、Opera 12.15は、「pattern=".{0,3}"」(任意の文字からなる0から3文字)のような正規表現を使った検証にも対応していますが、やはり「𠮷野家」は4文字とみなされてしまいます。


JavaScript

追記:javascript – でBMP以外のUnicode文字をきちんと扱う(404 Blog Not Found)

JavaScriptでは、文字列strの長さをstr.lengthで取得できることになっていますが、残念ながらこの方法では、上記のすべてのブラウザで「𠮷野家」は4文字とみなされてしまいます。

かつて、Twitterのウェブサイトではこの方法で文字数を数えていたと思われます。APIでならつぶやける文字列が、ウェブサイトからでは文字数超過でつぶやけなかったのです。Twitterに報告して直してもらいましたが(参考(https://support.twitter.com/tickets/1279115)リンク切れ)、「𠮷野家」を4文字とみなすクライアントはまだたくさんあるでしょう(手元のHootSuiteとついっぷるはそうでした)。

ちなみに、「.{0,3}」という正規表現で検証することもできません。「𠮷野家」はやはり4文字だとみなされます。

次のような関数を使えば、JavaScriptでも文字列の長さを正しく測ることができます(Stringオブジェクトをそのまま拡張する方法が、「サロゲート・ペアに対応した文字列操作関数を書いてみた」で紹介されています)。

function strlen(str) {
  var i = 0, len = str.length, result = 0;
  while (i < len) {
    result++;
    var x = str.charCodeAt(i++);
    if (0xD800 <= x && x < 0xDC00) i++;
  }
  return result;
}

この方法で数えた文字数が140以下なら、Twitterでつぶやくことができるでしょう(サーバ側も自分で作るときは、サーバ側でのチェックも必要です)。


サーバ側

PHP

mb_strlen()

関数mb_strlen()で正しく長さを測れます。ただし、クライアントから送信されたデータを処理する際には、関数mb_check_encoding()を使って、文字以外の不正なものがないことをまず確認するといいでしょう(拙著『Webアプリケーション構築入門』でも触れました)。

正規表現

長さがある範囲にあることをチェックするだけなら、徳丸浩『体系的に学ぶ 安全なWebアプリケーションの作り方』(ソフトバンククリエイティブ, 2011)で紹介されている方法が洒落ています。

if (preg_match('/\A\P{Cc}{1, 140}\z/u', $str) == 1) {
  // OK

Java

codePointCount()

Javaでは、文字列strの長さをstr.length()で取得できることになっていますが、残念ながらこの方法では「𠮷野家」は4文字とみなされてしまいます(JavaScriptの場合と同じです)。

拙著でも紹介したように、長さを正しく測りたい場合は、「str.codePointCount(0, str.length())」としなければなりません。ただし、クライアントから送信されたデータを処理する際には、条件「str.indexOf(0xFFFD) < 0」をチェックして、文字以外の不正なものがないことをまず確認するといいでしょう。

正規表現

長さがある範囲にあることをチェックするだけなら、徳丸浩『体系的に学ぶ 安全なWebアプリケーションの作り方』(ソフトバンククリエイティブ, 2011)で紹介されている方法が洒落ています(PHPの場合と同じです)。

if (str.matches("\\P{Cc}{1,140}")) {
  // OK

MySQL

MySQL 5.1以前

UTF-8で4バイトになるような文字を文字として扱うようにはなっていないので、文字列はbinaryとして保管し、文字列の長さを関数char_length()で測ったりするのはあきらめましょう(PHPやJavaなど、MySQLの外で測るのが簡単)。n文字まで格納したいなら、VARCHAR(4n)にしなければなりませんが、使う文字によってはn文字より多く格納できてしまうのが困りものです(全部ASCIIなら4n文字)。

MySQL 5.5以降

UTF-8で4バイトになるような文字を文字として扱うためのutf8mb4が導入されたので、これを使うのが簡単です。文字列の長さは関数char_length()で正しく測れます。n文字まで格納したいときも、VARCHAR(n)で大丈夫です。


おわりに

「𠮷野家」は単なる例です。「𠮷野家」でも「吉野家」を出してくるGoogleは偉いとは思いますが、拙著でも述べたように、それでも「吉野家」と書くべきだと私は思います。しかし、ここで述べたような話は、絵文字が大量に導入されたUnicode 6.0の普及が進めば、深刻な問題になるかもしれません。Javaが虐げられる理由が増えなければいいのですが。

想定外(あるいは無視された想定)の津波が原子力発電所を襲うほどの深刻度ではありませんが、ITの世界では、過去に2000年問題がありましたし、現在もこういう問題が発生していますし、将来もおそらく似た問題は起こるのでしょう(例えば2038年問題とか)。

先祖を笑えるようにはならず、先祖からは笑われ続けるのでしょうか。

文字を数えるのはけっこう難しいです。


追記

Perl

Perlは問題ないということをコメントで教えてもらいました。しかし、まさにここで紹介した問題のせいでコメントを壊してしまったので、本文にコードを追記します。

$ perl -v
This is perl 5, version 14, subversion 0 (v5.14.0) built for darwin-2level

$ perl -Mutf8 -E 'say length(𠮷野家)'
3

.NET Framework 4.0

追記

.NET Framework 4.0で文字列の長さを知りたい時は、StringInfo.LengthInTextElementsを使います。String.Lengthではダメです。PHPやJavaでは有効だった正規表現による長さの検査が使えないことにも注意してください。参考:田丸健三郎『UnicodeによるJIS X0213実装入門』(日経BPソフトプレス, 2008)

C#

String str = "𠮷野家";
System.Console.WriteLine(str.Length);
//4

StringInfo si = new StringInfo("𠮷野家");
System.Console.WriteLine(si.LengthInTextElements);
//3

Regex pattern = new Regex("^\\P{Cc}{4}$");
System.Console.WriteLine(pattern.IsMatch("𠮷野家"));
//True(「4文字」にマッチ)

Visual Basic

Dim str As String = "𠮷野家"
Console.WriteLine(str.Length)
'4

Dim si As New StringInfo("𠮷野家")
Console.WriteLine(si.LengthInTextElements)
'3

Dim pattern As New Regex("^\P{Cc}{4}$")
Console.WriteLine(pattern.IsMatch("𠮷野家"))
'True(「4文字」にマッチ)

ASP.NET

拙著『Microsoft Visual Web Developer 2008 Express Edition入門』で紹介した、正規表現による検証コントロールであるRegularExpressionで「.{0,3}」のような正規表現を設定しても、文字列の長さを正しく検証することはできません。「𠮷野家」は4文字とみなされてしまうからです。

PHPやJavaで有効だった「\P{Cc}{0,3}」という正規表現は機能しないようです(機能したとしても、上述のC#やVisual Basicの結果を見ると役には立たなそうですが)。

そこで、CustomValidatorを使うことになります。しかし、MSDNで公開されている「方法 : ASP.NET サーバー コントロールをカスタム関数で検証する」はダメです。文字列の長さを正しく測れません。正しく検証するためには、次のようなコードを使うといいでしょう。


StringInfo si = new StringInfo(args.Value);
args.IsValid = si.LengthInTextElements <= 3; [/csharp] クライアント側で処理したいときも、MSDNの方法はダメで、次のようなJavaScriptが必要になるでしょう(関数strlen()の定義は上述)。

function validateLength(oSrc, args) {
    args.IsValid = (strlen(args.Value) <= 3);
}

MySQLのテンプレ設定ファイルの使い方

拙著『Webアプリケーション構築入門』は入門書なので、インデックスを張ってEXPLAIN文で調べるくらいのことは書いてありますが、データベースのパフォーマンスチューニングについての本格的な説明は書いありません。p.113のコラムで、MySQLの設定ファイルのテンプレートと参考文献を紹介している程度です。

MySQLのチューニングは、設定ファイルmy.cnfで行うのが基本ですが、設定項目がたくさんあるのでどこをどう変えていいのかよくわかりません。そういう場合のために、MySQLにはあらかじめ設定ファイルが用意されています(拙著ではUbuntu版とMac版のみ。XAMPP 1.8.1でも用意されています)。デフォルトと違う部分だけをまとめると、リンク先の表のようになります。

MySQL Variables

設定ファイルmy.cnfを適当なもので置き換えて使えばいいのですが、そのままだとはまるので、回避法を書いておきます。2点あります。

  • InnoDBログファイルの消去
  • MySQLの実行ユーザの確認

InnoDBログファイルの消去

InnoDBのログファイルのサイズは設定ファイルごとに違います。その対応をせずにMySQLサーバを再起動すると、InnoDBが使えなくなってしまいます。この問題を回避するために、以下の手順で一度ログファイルを消去してからMySQLサーバを再起動してください。

まず、MySQLに接続し、「SET GLOBAL innodb_fast_shutdown=0;」としてログをテーブルスペースに反映させます。

次に、シェルから以下のコマンドを実行し、ログファイルを消去します。

sudo service mysql stop
sudo chmod 777 /var/lib/mysql
sudo rm /var/lib/mysql/ib_logfile*
sudo chmod 700 /var/lib/mysql

MySQLの実行ユーザの確認

用意されている設定ファイルには、デフォルトのものを除いて、MySQLデーモンの実行ユーザが記載されておらず、そのままではデーモンを起動させられません。この問題を回避するために、使いたい設定ファイルをもとからあるmy.cnfと置き換えてから、設定ファイルの「[mysqld]」の下に、以下のようにユーザを記載します。

[mysqld]
user = mysql

sudo service mysql start」(あるいは「start mysql」)としてMySQLを起動してからMySQLに接続し、「show engines;」とした結果にInnoDBが含まれていれば成功です。

寛容なMySQLを非寛容にすること(その3)

結論:MySQLのsql_modeには、ONLY_FULL_GROUP_BYを入れておく

ユーザを管理するテーブルを考える。ユーザはa, bという2つの属性を持つ。テーブルはこんな感じ。

CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a INT,
b INT,
INDEX (a),
INDEX (b)
);
INSERT users (a,b) VALUES (1,1);
INSERT users (a,b) VALUES (1,2);
INSERT users (a,b) VALUES (1,3);
SELECT * FROM users;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    2 |
|  3 |    1 |    3 |
+----+------+------+

属性aが1の人数は、次のように求められる。

SELECT count(*)
FROM users
WHERE a=1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+

何かの事情でaの値も取得したいとき、安易に次のように書いてしまうかもしれない。

SELECT a,count(*)
FROM users
WHERE a=1;
+------+----------+
| a    | count(*) |
+------+----------+
|    1 |        3 |
+------+----------+

上のSELECT文は、手元のMySQL 5.0.84と5.1.37-1ubuntu5, 5.4.3-beta-communityでは動作する。しかし、MySQL 5.0.45-logでは次のようなエラーになる。

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

リファレンスマニュアルの3.3.4.8. Counting Rowsには、上のようなSELECT文は可能だという記述があるのだが、うまくいかない環境もある(「SET sql_mode='';」としてもだめ)。

次のように書けば、どの環境でも動く。一般的に、集約関数があるときは、SELECTするものはGROUP BYの対象になっていなければならない(最初のSELECT文を実行できたのは、MySQLの独自の拡張のため)。

SELECT a,count(*)
FROM users
WHERE a=1
GROUP BY a;

SELECT a,count(*) FROM users GROUP BY a HAVING a=1;」と書いてもいいが、これを素朴実行したらさすがに遅いだろう(オプティマイザが上の形に変換してくれるとも思えない)。

このようなバージョンによる振る舞いのぶれを無くすためには、「SET sql_mode='ONLY_FULL_GROUP_BY';」として、SQLモードを変更し、MySQLの独自の拡張を無効にすればよい。こうすることによって、以下のSELECT文はすべてのバージョンでエラーになる。(ちなみに、現在のSQLモードは「SELECT @@sql_mode;」でわかる。)

SET sql_mode='ONLY_FULL_GROUP_BY';
SELECT a,count(*)
FROM users
WHERE a=1;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

そもそも、曖昧なSELECT文を実行させるような独自拡張に何の意義があるのだろうか。リファレンスマニュアルの12.16.3 MySQL Handling of GROUP BYには、You can use this feature to get better performance by avoiding unnecessary column sorting and groupingとあるから、パフォーマンスがよくなる可能性はある。本稿の例ではaの値は1しかないため、「GROUP BY a」をしなくて済むならそれに越したことはない。しかし、これはわざと作った例であり、実際にこういうことがうれしいという状況にあるときは、何かおかしなことをしているという自覚があるべきだろう(先に書いた「何かの事情」とはそういうことだ)。

それよりも、何か間違ったことをしているのに、MySQLの寛容さのためにその発見が遅れることがこわい。そういうことにならないための安全策として、普段から、曖昧なSELECT文を許容しないようにMySQLを設定しておくといいだろう。具体的には、my.cnfあるいはmy.iniの[mysqld]のセクションに、「sql-mode="ONLY_FULL_GROUP_BY"」と書いておけばよい。

もっと非寛容にしたい向きは、リファレンスマニュアルの5.1.7. Server SQL Modesから、それらしいものを見繕ってくればいい。TRADITIONAL(STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USERをまとめたもの)あたりから試すのがおすすめか。

お約束ですが、こういう話を基本から学びたいという方には、拙著『Webアプリケーション構築入門 実践!Webページ制作からマッシュアップまで 』(森北出版, 2011)がおすすめです。