トップ > MySQL

MySQL 5.5 の unicode collation で同一視される文字

MySQL

MySQL の collation について調べてたら、

今回の実験で、utf8_unicode_ciによる大文字-小文字や全角-半角の同一視に

関する動作はなんとなく分かりましたが、どの文字が同一視されるのかを記載した資料ってあるのだろうか?

http://d.hatena.ne.jp/end0tknr/20100613/1276427626

という記事を見かけたので調べてみました。

MySQL の マニュアルによると UCA というアルゴリズムを使用しているようです。

MySQL implements the xxx_unicode_ci collations according to the Unicode Collation Algorithm (UCA) described at http://www.unicode.org/reports/tr10/. The collation uses the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. Currently, the xxx_unicode_ci collations have only partial support for the Unicode Collation Algorithm. Some characters are not supported yet. Also, combining marks are not fully supported. This affects primarily Vietnamese, Yoruba, and some smaller languages such as Navajo.

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-sets.html

UCA はちゃんと読んでませんが、文字毎に weight という値が割り当てられていて、同じ weight であれば同じ文字とみなすという感じみたいです。

MySQL のソース中では、strings/ctype-uca.c で処理されています。このファイルから、どの文字が同一視されるかを調べてみました。

結果はこちら→ MySQL 5.5.11 unicode_ci で同一視される文字

これを作成するためのスクリプト→ https://gist.github.com/tmtm/922928

日本語の範囲で関係あるのは、英数字記号ひらがなカタカナくらいみたいです。

MySQL5.5 に groongaストレージエンジン入れてみた

groonga MySQL

groongaストレージエンジンは現在 MySQL 5.1 にしか対応してないようですが、MySQL 5.5.6-rc に入れてみました。

環境は CentOS 5.5 x86_64 です。

用意するもの

groongaのインストール

$ tar xpvfz .../groonga-1.0.2.tar.gz
$ ./configure --prefix=/usr/local/groonga
$ make
$ sudo make install

MySQLのバイナリのインストール

$ sudo -s
# useradd -r mysql
# cd /usr/local
# sudo tar xpvfz .../mysql-5.5.6-rc-linux2.6-x86_64.tar.gz
# sudo ln -s mysql-5.5.6-rc-linux2.6-x86_64 mysql
# cd mysql
# ./scripts/mysql_install_db
# chown -R mysql:mysql .

groongaストレージエンジンのインストール

コンパイルにMySQLのソースが必要です。ソースが展開されているだけでよくて、MySQLをコンパイルする必要はありません。

groonta-storage-engine-0.2 はそのままでは MySQL 5.5 に対応していないため以下のパッチを適用しました。
コンパイルが通るように適当に書き換えただけなので怪しいですが…。

--- ha_mroonga.cc.orig	2010-09-16 16:54:45.000000000 +0900
+++ ha_mroonga.cc	2010-09-29 13:24:06.000000000 +0900
@@ -22,9 +22,11 @@
 #endif
 
 #define MYSQL_SERVER 1
+#define DBUG_OFF 1
 
-#include <mysql_priv.h>
-#include <mysql/plugin.h>
+#include <sql_plugin.h>
+#include <sql_priv.h>
+#include <sql_show.h>
 #include <sql_select.h>
 #include <pthread.h>
 #include <sys/types.h>

$ tar xpvfz .../mysql-5.5.6-rc.tar.gz
$ tar xpvfz .../groonga-storage-engine-0.2.tar.gz
$ cd groonga-storage-engine-0.2
$ patch -p0 < .../groonga-storage-engin-0.2_for-mysql5.5.patch
$ PKG_CONFIG_PATH=/usr/local/groonga/lib/pkgconfig ./configure --with-mysql-source=../mysql-5.5.6-rc --with-mysql-config=/usr/local/mysql/bin/mysql_config
$ make
$ sudo make install

これで MySQL で次のようにすると groonga ストレージエンジンが使用できるようになります。

mysql> install plugin groonga soname 'ha_groonga.so';

注意

  • 上にも書きましたがパッチは適当に作ったものです。
  • MySQL の新しい charset utf8mb4 は groonga ストレージエンジンは使えないようです。utf8 ではちゃんと使用できました。

MySQL/Ruby 2.8.2

MySQL Ruby

MySQL/Ruby にバグがあったので 2.8.2 をリリースしました。

ダウンロードはこの辺から→ http://rubyforge.org/projects/mysql-ruby/

Mysql#insert_id が 32bit を超える場合に不正な値を返すというものです。

具体的には BIGINT AUTO_INCREMENT の場合に、自動的に 2147483648(UNSIGNED BIGINT の場合は 4294967296)以上の場合に、不正な値を返します。

mysql> create table t (id bigint auto_increment, unique(id));
mysql> alter table t auto_increment=2147483645;
m.query('insert into t values (0)')
m.insert_id #=> 2147483645
m.query('insert into t values (0)')
m.insert_id #=> 2147483646
m.query('insert into t values (0)')
m.insert_id #=> 2147483647
m.query('insert into t values (0)')
m.insert_id #=> -2147483648
m.query('insert into t values (0)')
m.insert_id #=> -2147483647

場合によっては致命的なので、アップグレードしといた方が良いと思います。

Ruby/MySQL 2.9

MySQL Ruby

Ruby から MySQL を使うための pure Ruby ライブラリ Ruby/MySQL 2.9 を公開しました。まだベータ版です。 http://github.com/tmtm/ruby-mysql/tree/2.9

前の Ruby/MySQL は 0.2.6 だったのですが、今回 2.9 とした理由は:

  • Cライブラリ版の MySQL/Ruby 2.8.x の後継。
  • 次は 3.0 にしたいという希望。

…という意味があります。

gem は gemcutter にあります。http://gemcutter.org/gems/ruby-mysql/versions/2.9.0
gemcutter が設定されて入れば次のコマンドでインストールできます。

# gem install ruby-mysql -v 2.9

対応する Ruby バージョンは 1.8.7 / 1.9.1 / 1.9.2、MySQL バージョンは 5.1.x です。

MySQL/Ruby 2.8.x との互換

以前のバージョンと異なり、MySQL/Ruby 2.8.x とほぼ互換があります。ただし以下の非互換があります。

Ruby 1.8.x で使用する場合、Mysql#escape_string は、マルチバイト文字の一部として特殊記号を含むマルチバイト文字セットに対して安全ではありません。シフトJIS(sjis, cp932)等は2バイト目に「\」を含むためこの制限にあてはまります。プログラム側で独自にエスケープ処理を行うか、ストアドプロシジャを使用してください。そうでないと SQL インジェクションの危険性があります。EUC-JP や UTF-8 は問題ありません。

いくつかのメソッドがありません: Mysql#debug, Mysql#change_user, Mysql#create_db, Mysql#drop_db, Mysql#dump_debug_info, Mysql#ssl_set, Mysql#reconnect

Mysql#options でサポートしているオプションは次のものだけです: Mysql::INIT_COMMAND, Mysql::OPT_CONNECT_TIMEOUT, Mysql::OPT_LOCAL_INFILE, Mysql::OPT_READ_TIMEOUT, Mysql::OPT_WRITE_TIMEOUT, Mysql::SET_CHARSET_NAME. これら以外を指定すると "option not implementted" という warning が標準エラー出力に出力されます。

Mysql#use_result は Mysql#store_result と同じです。つまりサーバーから一気に結果セットを読み込みます。

MySQL/Ruby 2.8.x からの改良点

Ruby 1.9.x の M17N に対応しています。mysqld へのクエリ文字列やプリペアドステートメントで与える値は mysqld との接続の文字コードに自動的に変換されます。mysqld からの結果文字列は接続文字コードとして取り出されます。

Mysql::Result, Mysql::Stmt が Enumerable を include しています。

ブロックなしの Mysql::Result#each, each_hash Mysql::Stmt#each, each_hash が Enumerator を返します。

Mysql#charset= で接続 charset を指定できます。

Mysql::Error だけでなく、エラー種別ごとにエラークラスが用意されてます。たとえば、構文エラーの場合は Mysql::ParseError など。これらのエラーは Mysql::Error の継承クラスです。

他に pure Ruby 版であることのメリットとして、Ruby のマルチスレッドが同時に動くというのがあります。あるスレッドで時間のかかるクエリを実行中に他のスレッドが動くことができます。

機能的な改良点ではありませんが、ライセンスは Ruby ライセンスです。Cライブラリ版の MySQL/Ruby は libmysqlclient をリンクするため、それを使用する Ruby スクリプトも GPL にするか、それが嫌なら MySQL の商用ライセンスを購入する必要がありました。Ruby/MySQL は libmysqlclient をリンクしないため、その制限はありません。

Ruby 1.9.x M17N 対応

MySQL はデータベース、テーブル、カラム毎にそれぞれ文字セットを持っています。また、サーバーとクライアントの間の接続に文字セットを持っています。接続とカラムとの間の文字セットは MySQL のサーバー側で自動的に変換されます。

Ruby/MySQL では MySQL 接続の文字セットを Mysql#charset= で指定できます。または、Mysql#connect 前に Mysql#options(Mysql::SET_CHARSET_NAME) で設定します。

Ruby 1.9.x では文字列はそれぞれエンコーディング(文字セット)を持っています。Ruby/MySQL で Ruby プログラムから MySQL サーバーに渡す文字列（クエリ文字列や、プリペアドステートメントのパラメータ）は自動的に接続に応じた文字セットに変換されます。

MySQL サーバーから返される文字列の Ruby のエンコーディングは接続の文字セットに対応したものになります。ただし、プリペアドステートメントでは、BINARY型とBIT型のデータは ASCII-8BIT エンコーディングで返ります。

例:

# -*- coding:utf-8 -*-
require 'mysql'
my = Mysql.connect(nil, 'username', 'password')
my.charset = 'cp932'          # 接続の文字セットは CP932
query = 'select "あいう"'     # UTF-8 のクエリ文字列
query.encoding                # => #<Encoding:UTF-8>
col, = my.query(query).fetch  # 取り出した値は接続と同じく CP932
col.encoding                  # => #<Encoding:Windows-31J>
col                           # => CP932 で "あいう"

MySQLユーザー会会2009冬

MySQL

MySQLユーザー会会2009冬に参加。
16時前に長野の会社を出て、19時前に六本木のGREEに到着。

19時に始まるはずだった池田さんのセッションだったんだけど、プロジェクタに出力できないというトラブルが発生。
事情によりUSBが使えないPCなので、他のPCにデータを移すこともままならなず、結局自分のPCとLANケーブルと直結して、FTPでアップロードしてもらい、USBメモリに移して他のPCに持っていって…ということをしてなんとか映すことができました。35分押し。

「MySQL全文検索ことはじめ」住商情報システム 池田徹郎さん

全文検索とは…から始まり、MySQLで実装する全文検索、Tritonn の紹介という流れでした。
Senna 自体開発が止まっていることとか、MySQL 5.1 への対応とかが気になっていたので質問してみました。
Senna は groonga に進化し、Tritonn も groonga に対応し、5.1 のプラガブルストレージエンジンになる予定とのことでした。

乾杯

時間が押したおかげでピザが届いてしまったので、ここで乾杯。何かしゃべった気がします。

「おくかずほ に訊く『MySQL』」サイボウズ・ラボ おくかずほさん

坂井さんがインタビュアーでおくさんに色々聞くという趣向でした。
MySQLの出会いとか、MySQLの好きなところや嫌いなところとか。

Q4M に興味があったので質問してその後も個人的に話をさせてもらいました。

「XtraDB for Performance」PERCONA 木下さん

懇親会中に飛び入りで、世界でもっともInnoDBに詳しい人である木下さんが、InnoDB を拡張した XtraDB について説明しました。

その後

一通り終了してもまだ10人くらい残っていて、@tokuhirom さんとか私とか @kentokushiba さんとかがネタを披露したりして、結局会場を出たのは AM3時頃。
その後は赤坂みすじ通りの店に移動して朝5時半くらいまで。ホテルとっておかなくて正解でした。

次回

この手のイベントを数ヶ月置きくらいにやりたいという話もありました。
個人的には夜中まで楽しめたからいいんですけど、そうじゃない参加者にとっては今回の3000円ってのはちょっと高いんじゃないかと思いました。やっぱり基本的に無料がいいんじゃないかなと思ったり。
主催者じゃないから主催者の苦労は本当にはわからないので好きなこと言ってるんですけど (^^;

主催者の id:sakaik さんの記事→ id:sakaik:20091219:kaikai_2009d_finished

Ruby/MySQL 3.0.2 alpha

Ruby MySQL

Ruby/MySQL 3.0.2 です。相変わらずアルファ版です。

インストール方法:

# sudo gem install tmtm-ruby-mysql --source http://gems.github.com

3.0.1 からの変更点。

Ruby 1.9 対応

これまでのでもちゃんと動くんじゃないかなぁ…と漠然と思っていたのですが、全然動いていないことが判明したので、修正しました。
テストコードを RSpec で書いてしまったので Ruby 1.9 でテストしてなかったのですが、Ruby会議2009 の IRC で、最近の RSpec は Ruby 1.9 でも動くということを教えてもらったので、テストしてみたら全然動いていなかったことが判明したという…。

Mysql#list_fields 追加

「SHOW COLUMNS」や「SHOW CREATE TABLE」でも同様の情報は取れるのですが、ビミョーに違うのであった方が良いかと思って追加しました。
ただし MySQL/Ruby 2.x との互換はありません。MySQL/Ruby 2.x では C API に合わせて、Mysql#list_fields はフィールド情報だけを持ち、レコードが空の Mysql::Result を返していたのですが、Ruby/MySQL 3.0 では単純に Mysql::Field の配列を返すようにしたためです。

今後

これで Ruby 1.9 でも普通に使えるようになったと思いますので、Ruby 1.9 が好きな人は使ってみてください。まあ Ruby/MySQL をベタに使う人はあまりいないと思うので、巷の O/R マッパーが対応しないとあまり関係ないのでしょうけど。
今後は、速度的にボトルネックになっている部分を高速化したりするかもしれません（C で書く？）。

Ruby/MySQL 3.0.1 alpha

Ruby MySQL

Ruby/MySQL 3.0.1 を作りました。相変わらずアルファ版です。

インストールは次のように。

# sudo gem install tmtm-ruby-mysql --source http://gems.github.com

3.0.0 からの変更点

  • Mysql#query で勝手にプリペアドステートメントを使うのをやめました。いろいろと複雑になってたので。ただし、クエリ文字列以外の引数がついている場合はプリペアドステートメントを使用します。
mysql.query("select 1,2,3")           # プリペアドステートメントではない
mysql.query("select ?,?,?", 1, 2, 3)  # プリペアドステートメント
  • Mysql::Result#fetch 等が返すレコードの値は、Ruby の適切な値に変換するようにしました。
mysql.query("select 123,'abc'").fetch  # => [123, "abc"]
  • Mysql::Statement#execute も Mysql::Result を返すようにしました。正確には Mysql::Result のサブクラスですが。それに伴い Mysql::Statement#each, #fetch 等は廃止しました。

Ruby/MySQL 3.0 全体については id:tmtms:20090322:1237719050 をどうぞ。

MySQL/Ruby 2.x でできるのに Ruby/MySQL 3.0 でできないこと

両方とも誰も使ってないですよね (^^;

[追記]

  • LOAD DATA LOCAL INFILE 命令は発行できません。
  • my.cnf ファイルを読み込む機能がありません。

それ以外はたいていは機能としては存在すると思います。MySQL/Ruby 2.x との互換が重要な場合には、require "mysql/compat" しておけば、ある程度はそれなりに動くんではないかと思います。

3.0.0 から4ヶ月も経ってる割にはこの程度… まあ、実質は数日程なのですが。今後はもうちょっと更新頻度をあげたいところです。
ということで、よろしければどうぞ。

table_cache, max_connections, open_files_limit の関係

MySQL

昔はマニュアルに書いてあったような気がしたけど、最近のマニュアルには見当たらないのでメモ。

mysqld が同時に使用可能なファイル数は open_files_limit というパラメータで指定します。ただし、mysqld は最低でも table_cache*2+max_connections+10 --- (a) は必要だと考えるので、open_files_limit が (a) よりも小さければ、黙って (a) の値まで大きくします。table_cache を2倍しているのは、MyISAM が1テーブルにつき2ファイル使用するためでしょう。10 を足しているのは標準入出力エラー出力と、ログファイル等の分でしょうか。

また、max_connections*5 --- (b) の方が (a) よりも大きければ、open_files_limit は (b) になります。

(a), (b) よりも現在のファイル記述子の制限値(getrlimit(2) の rlim_cur の値) --- (c) が大きければ、(c) が採用されます。

open_files_limit の最大値は 65535 です。また、setrlimit(2) で open_files_limit 分のファイル記述子が割り当てられない場合(おそらくrootで起動しなかった場合)は、(c) になります。そのため結果的に (a) よりも小さな値になってしまう場合があります。open_files_limit パラメータを指定しておらず (a) よりも小さい値になった場合は、max_connections と table_cache が次のように調整されます。

max_connections が open_files_limit-10-64*2 よりも大きい場合は、その値まで小さくなります。

table_cache が (open_files_limit-10-max_connections)/2 よりも大きい場合は、その値まで小さくなります。ただし 64 よりは小さくなりません。

この調整が行われると、エラーログに次のように出力されます。

Changed limits: max_open_files: XXX  max_connections: XXX  table_cache: XXX

open_files_limit パラメータを指定している場合は、エラーログに次のように出力されるだけです。

Could not increase number of max_open_files to more than XXX (request: XXX)

table_cache と max_connections の調整は行われません。この場合は稼働中にファイル記述子が足りなくなる可能性があるため、注意が必要です。

例

mysqld --max_connections=100 --table_cache=64
(c) が採用される。

max_connections 100
table_cache 64
open_files_limit 1024

mysqld --max_connections=1000 --table_cache=64
(b) が採用される。

max_connections 1000
table_cache 64
open_files_limit 5000

mysqld --max_connections=100 --table_cache=1000
(a) が採用される。

max_connections 100
table_cache 1000
open_files_limit 2110

mysqld --max_connections=15000 --table_cache=64
(b) 15000*5=75000 だが 65535 より大きいので 65535 になる

max_connections 1000
table_cache 64
open_files_limit 65535

mysqld --max_connections=100 --table_cache=40000
(a) 40000*2+100+10=80110 だが 65535 より大きいので 65535 になり、max_connections, table_cache の値も調整される。

[Warning] Changed limits: max_open_files: 65535  max_connections: 100  table_cache: 32712
max_connections 100
table_cache 32712
open_files_limit 65535

mysqld --max_connections=100 --table_cache=40000 --open_files_limit=10
上と同じだが open_files_limit が指定されているので、max_connections, table_cache の値は調整されない。

[Warning] Could not increase number of max_open_files to more than 65535 (request: 80110)
max_connections 100
table_cache 40000
open_files_limit 65535

Ruby/MySQL 3.0.0 alpha

Ruby MySQL

github に Ruby/MySQL 3.0.0 を置きました。

git にも gem にも慣れてないので試行錯誤でしたが、なんとか置けたようです。

github は gemspec を置いておけば自動的に gem を作ってくれるはずなのですが、罠に嵌まってもがいてました。

GitHub では *.gemspec ファイルのバージョン番号が更新されたときにのみ Gem を生成する。だから *.gemspec を最初に commit & push したときは、Gem が生成されない(バージョン番号が更新されているわけではないから)。まずは *.gemspec ファイルをバージョン 0.0.0 とかで commit & push し、そのあとバージョンを上げて commit & push し直す。

GitHub で gem を自動作成させるときの注意

gemspec のバージョンを変更して git push したらちゃんとできました。

次のようにすればインストールできるはずです。

# sudo gem install tmtm-ruby-mysql --source http://gems.github.com

…と言っておいてなんですが、Ruby/MySQL 3.0.0 はアルファ版なので、ヒトバシラー以外は使ってはいけません。

特徴等

Ruby/MySQL は Ruby から MySQL を使用するためのライブラリです。

  • MySQL/Ruby と異なり Ruby で書かれているのでコンパイルの必要はありません。
  • Ruby ライセンスです。libmysqlclient は使用していないので GPL ライセンスに縛られません。
  • Ruby 1.9 の M17N に対応しています。
  • Ruby/MySQL 0.x とも MySQL/Ruby 2.x とも互換はありません。
  • まともなドキュメントがありません (ぉ
  • MySQL/Ruby 2.x よりも遅いです。たぶん。

使用例

Mysql.connect("mysql://user:passwd@server:3306/dbname") do |my|
  my.query("select col1,col2 from tblname").each do |col1, col2|
    p col1, col2
  end
  my.query("insert into tblname (col1,col2) values (?,?)", 123, "abc")
end

接続

Mysql.new は Mysql オブジェクトを生成するだけでサーバーに接続はしません。Mysql.connect または Mysql#connect を使用してください。

Mysql.connect の引数には上記のようなURI 文字列の他に、URI オブジェクト、Hash 等を使用できます。

# URI文字列
Mysql.connect("mysql://user:passwd@server/dbname")

# URIオブジェクト
Mysql.connect URI.parse("mysql://user:passwd@server/dbname")

# Hash
Mysql.connect(:host=>"server", :user=>"user", :password=>"passwd", :db=>"dbname")

# 以前の形式
Mysql.connect("server", "user", "passwd", "dbname")

Mysql.new または Mysql.connect にブロックを渡すと、ブロックを抜ける時に自動的に接続を切断します。

クエリ

Mysql#query でクエリを発行します。クエリ文字列が "sel" で始まる場合はプリペアドステートメントとして実行します。（3.0.1で廃止）プリペアドステートメントクエリの結果は MySQL の型に応じた Ruby オブジェクトになります。以前とは異なり文字列だけとは限りません。

my.query("select 123,'abc'").fetch # => [123, "abc"]

また Mysql#query に引数が２つ以上ある場合もプリペアドステートメントとして実行します。

my.query("insert into tblname (col1,col2) values (?,?)", 123, "abc")

もちろん、プリペアドステートメントを明示して実行することもできます。

stmt = my.prepare("select ?,?")
stmt.execute 123, "abc"
stmt.fetch  # => [123, "abc"]

プリペアドステートメントを使用したくない場合は Mysql#simple_query を使用します。クエリの結果は文字列になります。(3.0.1で廃止)

my.simple_query("select 123,'abc'").fetch # => ["123", "abc"]

エラー

MySQL/Ruby 2.x では例外クラスはすべて Mysql::Error で、エラー種別を判定したい場合は、rescue した後、Mysql::Error#errno を見る必要がありました。Ruby/MySQL 3.0 ではエラーの種類毎に例外クラスが存在するので、rescue でエラーを振り分けできます。まあ、今までがひどかったのですけどね。

begin
  my.query("....")
rescue Mysql::DupEntry
  ...
rescue Mysql::ParseError
  ...
rescue Mysql::NoSuchTable
  ...
end

Charset

Ruby 1.9 では文字列の encoding/charset の自動変換を行います。

特に charset を指定しない場合は MySQL サーバー接続時にサーバーから得られた charset(mysqld の default-character-set パラメータ)を Mysql#charset に保持します。

charset を指定するには Mysql#charset= を使用します。

my.charset = "utf8"

クライアントからサーバーに送られるクエリやデータの文字列は、自動的に Mysql#charset に変換されます。また、サーバーからクライアントに送られる文字列は Mysql#charset に対応する Ruby のエンコーディング文字列として返されます。変換できない場合はエラーになります。

mysqld の default-character-set パラメータが latin1 の場合(これは mysqld のデフォルト値です)、次のスクリプトを実行すると Encoding::UndefinedConversionError 例外が発生します。

# -*- coding:utf-8 -*-
require "mysql"
Mysql.connect(...) do |my|
  p my.query("select 'あいう'").fetch
end

次のように charset を指定すればエラーになりません。なお、Mysql#charset= に指定できる文字列は Ruby のエンコーディング名ではなく、MySQL の charset 名なので "utf-8" ではなく "utf8" です。

# -*- coding:utf-8 -*-
require "mysql"
Mysql.connect(...) do |my|
  my.charset = "utf8"
  p my.query("select 'あいう'").fetch
end

サーバーから取り出される文字列はデータベース上の charset に関係なく、Mysql#charset に対応するエンコーディングの Ruby 文字列になります。

たとえば次のようなテーブルとレコードがある場合、

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `euc` char(10) CHARACTER SET eucjpms DEFAULT NULL,
  `sjis` char(10) CHARACTER SET cp932 DEFAULT NULL,
  `utf8` char(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> select euc,hex(euc),sjis,hex(sjis),utf8,hex(utf8) from t\G
*************************** 1. row ***************************
      euc: あいう
 hex(euc): A4A2A4A4A4A6
     sjis: あいう
hex(sjis): 82A082A282A4
     utf8: あいう
hex(utf8): E38182E38184E38186

それぞれのカラムは、異なる charset の文字列が格納されていますが、Ruby から次のように取り出した場合はすべて UTF-8 文字列になります。

# -*- coding:utf-8 -*-
require "mysql"
Mysql.connect(...) do |my|
  my.charset = "utf8"
  rec = my.query("select * from t").fetch
  rec.each{|c| p c, c.encoding}
end

結果:

"あいう"
#<Encoding:UTF-8>
"あいう"
#<Encoding:UTF-8>
"あいう"
#<Encoding:UTF-8>

なお、BINARY型や BLOB型のデータは charset によらず、そのままのバイト列で取り出せます。Ruby 文字列のエンコードは ASCII-8BIT になります。

おわりに

MySQL/Ruby と比べて遅かったり、非互換があったりしますが、Ruby 1.9 の M17N がちゃんと扱えるのはこれだけなので、Ruby 1.9 な人はこれを使ってみるのもいいと思います。
ただ、上にも書きましたが、まだアルファ版なので、今後非互換な変更が入る可能性もあります。
まあ、フツーの人は Rails とか O/Rマッパー経由で使うだろうから、あまり関係ないでしょうけどね。

MySQL 5.1 InnoDB が遅い

MySQL

Rubyist Magazine 25号が出ました。Ruby 1.9.1 についての有用な記事が盛りだくさんです。

「Ruby 1.9 で Web アプリを想定したベンチマークをとってみた」は「言語の速度がそのままアプリケーションの速度になるわけではない」ということについて、実際にベンチマークを測定して説明しています。

その中の「データベースを使ったベンチマーク」で使われてたベンチマークプログラムを自分でも試してみました。

測定環境: ThinkPad X61, Ubuntu 8.10, Ruby 1.8.7, MySQL 5.0.67

$ ruby -s blog-bench.rb -N=500
      user     system      total        real
  5.830000   0.640000   6.470000 ( 23.872301)

なぜか記事の結果よりも、Ruby の時間が多く、MySQL の時間が少ないです。

クエリに USE INDEX を追加するオプションをつけてみました。

$ ruby -s blog-bench.rb -N=500 -useindex
      user     system      total        real
  5.390000   0.650000   6.040000 ( 12.675880)

MySQL の使用時間は半分くらいになりました。

試しに MySQL を 5.1.31 にしてみました。…いつまでたっても終わらない…。

N=500 を N=100 にしたら 5.0.67 と同じくらいの時間で終わりました。

$ ruby -s blog-bench.rb -N=100
      user     system      total        real
  1.270000   0.130000   1.400000 ( 24.704081)
$ ruby -s blog-bench.rb -N=100 -useindex
      user     system      total        real
  1.140000   0.110000   1.250000 ( 24.831864)

500 から 100 にしたので、Ruby の時間はちゃんと 1/5 になったのですが、MySQL の時間は変わりません。つまり 5.0.67 と比べて 5倍くらい時間がかかってることになります。また、-useindex をつけても変化ありませんでした。

どうやら使用されているインデックスが異なるためのようです。

5.0.67:

mysql> explain select * from blog_comments where entry_id in (4994,4984,4974,4964,4954,4944,4934,4924,4914,4904) order by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_comments
         type: range
possible_keys: blog_comments_entry_id
          key: blog_comments_entry_id
      key_len: 4
          ref: NULL
         rows: 20
        Extra: Using where; Using filesort

5.1.31:

mysql> explain select * from blog_comments where entry_id in (4994,4984,4974,4964,4954,4944,4934,4924,4914,4904) order by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_comments
         type: index
possible_keys: blog_comments_entry_id
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10123
        Extra: Using where

これだと1万行もあるテーブルから10個のキーのレコードを抜き出すのにフルスキャンしてそうな…。

-useindex 時に、強制的に blog_comments_entry_id を使用するように変更してみたら、5.0.67 とほぼ同じになりました。

$ ruby -s blog-bench.rb -N=500 -useindex
      user     system      total        real
  5.510000   0.620000   6.130000 ( 12.852172)

ちなみに blog_comments を InnoDB から MyISAM に変更してみたら -useindex つけなくても速いです。

$ ruby -s blog-bench.rb -N=500
      user     system      total        real
  6.490000   0.710000   7.200000 ( 12.015570)

MySQL のオプティマイザの仕様変更なのかバグなのか InnoDB 特有の罠なのかはわかりませんが、データベースのチューニングは一筋縄ではいきませんな…。

MySQL/Ruby 2.8.1

Ruby MySQL

Ruby 1.9.1 がリリースされました。が、MySQL/Ruby は 1.9 に対応してませんでした。気づいてませんでした… orz。

とりあえず、Ruby 1.9.1 でもコンパイルと簡単なテストを通るようにしてみたものを、MySQL/Ruby 2.8.1 としてリリースしました。

ダウンロードはこの辺から。
http://rubyforge.org/frs/?group_id=4550

Ruby 1.9 の大きな特徴である M17N についてはまったく対応してません。なので、データベースから取り出した文字列は、全部 ASCII-8BIT になります…と思います…たぶん…。

Ruby 1.9.1 と MySQL 5.1 のリリースに合わせて、Ruby/MySQL 3.0 をリリースしようとこっそりたくらんでいたのですが、全然間に合ってません… (--;

予定しているのは MySQL/Ruby ではなく Ruby/MySQL です。ややこしいのですが (^^; MySQL/Ruby は MySQL 付属の libmysqlclient を使用した C 言語で書かれている Ruby の拡張ライブラリですが、Ruby/MySQL はすべて Ruby で記述されたライブラリです。つまりコンパイル不要です。

C ではなく Ruby で書いてあると速度が心配なのですが、Ruby 1.9 なのでなんとかなるんじゃないかと楽観してます。うまくいったら C版の MySQL/Ruby は 2.8.x で終了です。

今のところ次のような感じになる予定です。

  • Mysql.new の引数に URI 形式 "mysql://user:pass@hostname:port/db" を受け付ける。
  • Mysql.new にブロックを渡したら、ブロックから抜ける時に Mysql#close する。
  • mysqld の charset と ruby の encoding との間で文字列を自動変換する。
  • クエリは基本的にプリペアドステートメント。Mysql#query("select ?,?,?", 1, 2, 3) のような感じで記述できる。
  • 結果セットは Enumeratable
  • サーバエラーは、エラーの種類毎に独立したクラス（今はすべて Mysql::Error）。
  • libmysqlclient を使用しないので、GPL 汚染を気にしなくてよい（たぶん現状でも問題ないはずだけど…）。
  • たぶん、たくさんの非互換 (^^;

Ruby から MySQL を使っている人でも、直接 Mysql クラスを使っている人は少ないと思うので、どう変わろうと興味がない人ばかりかもしれませんけど。