Re: MySQL の NOW() と SYSDATE()

自分は全然気にしたことなかったんだけど、MySQL の NOW()SYSDATE() は異なるらしい。

sakaik.hateblo.jp

MySQL 8.0 のマニュアル (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_sysdate) にも確かにちゃんと書かれてる。

SYSDATE() returns the time at which it executes. This differs from the behavior for NOW(), which returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.)

Google翻訳:

SYSDATE()は、それが実行された時刻を返します。 これは、ステートメントの実行が開始された時間を示す一定の時間を返すNOW()の動作とは異なります。 (ストアドファンクションまたはトリガー内で、NOW()は、ファンクションまたはトリガーステートメントが実行を開始した時刻を返します。)

複数行を返すクエリで試してみると、行ごとに値が異なる。

mysql> select now(), sysdate(), sleep(1) from (values row(1), row(2), row(3), row(4)) t;
+---------------------+---------------------+----------+
| now()               | sysdate()           | sleep(1) |
+---------------------+---------------------+----------+
| 2020-07-12 19:14:05 | 2020-07-12 19:14:05 |        0 |
| 2020-07-12 19:14:05 | 2020-07-12 19:14:06 |        0 |
| 2020-07-12 19:14:05 | 2020-07-12 19:14:07 |        0 |
| 2020-07-12 19:14:05 | 2020-07-12 19:14:08 |        0 |
+---------------------+---------------------+----------+
4 rows in set (4.00 sec)

へー、面白い。複数行を処理するようなクエリでは SYSDATE() は使わないほうが良さそうだな。

しかし、この挙動はいつからなんだろう。昔は NOW()SYSDATE() は同じだったはず。

4.1 の日本語マニュアル (https://downloads.mysql.com/docs/refman-4.1-ja.a4.pdf)には

SYSDATE() は NOW() のシノニム

と書かれている。ソースコード上も lex.h に1行だけ。

  { "SYSDATE",        SYM(NOW_SYM)},

5.0 のマニュアル (https://downloads.mysql.com/docs/refman-5.0-en.a4.pdf) に次のようにあった。

As of MySQL 5.0.12, SYSDATE() returns the time at which it executes. This differs from the behaviorfor NOW(), which returns a constant time that indicates the time at which the statement beganto execute. (Within a stored function or trigger, NOW() returns the time at which the function ortriggering statement began to execute.)

As of MySQL 5.0.13, the SYSDATE() function is no longer equivalent to NOW(). Implications are that SYSDATE() is not replication-safe because it is not affected by SET TIMESTAMP statements in thebinary log and is nondeterministic. To avoid this, you can start the server with the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW().

へー、5.0 からなのか。最近だった。自分は知らないわけだ。

どうやら 5.0.12 か 5.0.13 のどちらかかららしい。どっちやねん。

「5.0 の途中から!?」と一瞬思ったけど、5.0 の GA は 5.0.15 なので、パッチレベルで振る舞いを変えるようなことはさすがに無かったようだ(昔はよかった)。

そして非互換なので、--sysdate-is-now オプションで振る舞いを変えることができるようだ(昔はよかった)。なお、このオプションは 8.0 でも存在する。

MySQL 8.0.20 を --sysdate-is-now つきで起動してさっきのクエリを試すと

mysql> select now(), sysdate(), sleep(1) from (values row(1), row(2), row(3), row(4)) t;
+---------------------+---------------------+----------+
| now()               | sysdate()           | sleep(1) |
+---------------------+---------------------+----------+
| 2020-07-12 20:11:15 | 2020-07-12 20:11:15 |        0 |
| 2020-07-12 20:11:15 | 2020-07-12 20:11:15 |        0 |
| 2020-07-12 20:11:15 | 2020-07-12 20:11:15 |        0 |
| 2020-07-12 20:11:15 | 2020-07-12 20:11:15 |        0 |
+---------------------+---------------------+----------+
4 rows in set (4.00 sec)

NOW() と同じになった。

これは mysqld 起動時のオプションだけど、システム変数ではないので SHOW VARIABLES では出てこない。たまにこういうのがある。 システム変数ではないので SET PERSIST でも設定できないけど、my.cnf に書くとちゃんと有効になる。

「MySQL徹底入門 第4版」が出るよ

「MySQL徹底入門 第4版」が 7/6 に発売される。🎉

www.shoeisha.co.jp

電子書籍は翔泳社の直販がDRMフリー(たぶん)だからオススメ。

著者用見本誌も届いたので、さすがにこれからやっぱり発売できませんでした!ってことにはならないと思う。

長かった。 本当は去年出る予定だったんだが、なんやかんやで今年になった(よくある)。

第3版が出たのが 2011年だから、実に9年ぶり!

偶然にも第3版と同じ 544ページなんだけど、1ページ辺りの文字数は増えているので情報密度は増しているはず(そしてその分価格も上がってる)。 (ただしくは552ページらしい。)

「MySQL徹底入門」は第n版が出るたびに、毎回ほとんどを書き下ろしてるし、複数人で書いてるんだけど、書いてる人も入れ替わるし担当する章も変わる面白い本。

自分は、今回は第5章「ユーザー管理」、第10章「データベースプログラミング」のRubyの節、第11章「文字コードと日本語環境」、第14章「逆引きMySQL辞典」のうちの2項目を書いた。 全部で544ページあるうちの、52ページ分。あれ? 1/10 にも満たないのか…。結構書いたような気がしたんだけど。というかみんな書きすぎなんだよ!

前の版からそうなんだけど、「MySQL徹底入門」は MySQL の入門書であって、SQL や RDB の入門書ではないということに注意。

一般的な SQL の話はほとんどないかわりに、MySQL固有の情報はふんだんに書かれている

また、今回は「MySQL 8.0 対応」という副題がついている。これを見て「MySQL 8.0 の新機能についても触れられてるんだなー」とか思っちゃいけない。 この本の中では、MySQL 8.0 よりも前のバージョンのことはほとんど考慮されていない。この本は MySQL 5.6 や 5.7 を使ってる人にはほとんど役に立たないだろう(言いすぎな気もする)。

まあでもみんな 8.0 を使ってるだろうから問題ないよね。


これを機に歴史を振り返ってみる。

初版 2001/1/1 384ページ 3200円

www.seshop.com

Amazon だと 2000/12/1 になってるな。

どっちが正しいんだろう。まあその辺で出たんだろう、きっと。

今手元に本が無いからわからないんだけど、自分は3割くらいは書いたような気がする。

2000年だからたぶん MySQL 3.23 あたりが対象と思われる。

2000年3月に「MySQLユーザ会」が発足したと思ったらわりとすぐに執筆依頼が来たという記憶がある。

詳しい経緯は忘れたけどメーリングリストで執筆者を募って「MySQLユーザ会」として執筆することになったんだと思う。

執筆者は「MySQLユーザ会」で、監修がソフトエージェンシーさんと自分だった。個人の名前として自分しか載らなかったのは良くなかったので、第2版からは全員の名前を並べるようになった。

なお、表紙が鳥なのは、MySQLユーザ会の略称である MyNA から、myna が英語で九官鳥などの鳥を表す単語だったから。

自分の名前が載った本が本屋に並んでるのを見るのは嬉しかった。

当時3歳の娘と本屋に行って本を見せて、

私「ほら、これはちちの本なんだよ」
娘「ふーん、なんで本屋に置いてあるの? 娘ちゃんの本はおうちにあるよ」
私「いや、ちちが書いた本なんだよ」
娘「へー、こんな綺麗な字が書けるんだね」

という会話をしたのも懐かしい思い出。

第2版 2006/1/23 416ページ 3480円

www.seshop.com

初版から5年後。 表紙にも書かれてるけど、MySQL 4.1 & 5.0 対応。

執筆者は、遠藤 俊裕、坂井 恵、館山 聖司、鶴長 鎮一、とみた まさひろ、班石 悦夫、松信 嘉範 (敬称略)。あいうえお順みたい。

メールによると、編集さんから 2003/6 に改訂しないかという話があったっぽい。その後のメールはロストしててよくわからない…。

MySQL 5.0 の GA版は 2005/10/19 リリースの 5.0.15 だから、基本は 4.1 で書いて、5.0 の目玉機能だけ追記したとかなのかなぁ。これも今手元に無いからわからない。

自分は1割くらいは書いたような気がする。

第3版 2011/8/25 544ページ 3480円

www.seshop.com

さらに5年後。MySQL 5.5 対応。

執筆者は、遠藤 俊裕、坂井 恵、館山 聖司、鶴長 鎮一、とみた まさひろ、班石 悦夫、松信 嘉範 (敬称略)。お、第2版とまったく同じだった。

2010/7 頃に編集さんから第3版を出さないかという話があった。

その後、章構成を変更しないで、5.5 対応だけするという方針になったっぽい。

ゆるゆると話が進んで、目次案が確定したのが 2011/2 頃。

レプリケーションの章が新たに追加されて、それを自分が書いたらしい。

Subversion とメーリングリストを使ってたっぽい。

自分は 5% くらいは書いたような気がする。 版を重ねる毎に順調に執筆割合が減ってきている。

第4版 2020/7/6 552ページ 3800円

www.shoeisha.co.jp

そして9年ぶりに第4版。

執筆者は、yoku0825、坂井 恵、鶴長 鎮一、とみたまさひろ、深町 日出海、福山 裕大、班石 悦夫、山﨑 由章(敬称略)。結構変わった。

8.0 対応。これまでに 5.6 とか 5.7 とかあったから、やっぱり途中にもうひとつ出てても良かったような感じ。

最初に話が出たのが 2018/10 頃。 2018/12 にみんなで集まってキックオフ。

今なら Subversion じゃなくて Git だろうということで、GitLab を使うことに。GitHub はプライベートリポジトリは有料だったので。

メーリングリストじゃなくてチャットにしようということで、Mattermost サーバーを立てた。今思えば Slack にしておいた方が良かったかもしれない。 Slack は無料プランだと過去のログが消えてしまうと思ってたので。実は管理者権限ならログを取り出せるらしい。

原稿の形式は Markdown で。

原稿が書けたら GitLab でマージリクエストを発行して、誰かがレビューしたらマージする…みたいな運用をしてたんだけど、これはやめといた方がよかったかもしれない。 というか、途中でやめて、書いたらどんどん master に突っ込むようにした。 みんなが Git や GitLab の使い方に慣れてるわけではなかったので。

Mattermost も章ごとにチャンネルを分けた方がよかったかも。一つのチャンネルでやってたんだけど、流れが速くなると見なきゃいけないメッセージが埋もれちゃうので。 Mattermost の設定がヘボくて(私のせいだ)、日本語で過去のメッセージを検索できなかったのも何気にいたかったかも。

自分の執筆は1割くらい。少し増えた。

2019/5 原稿締切、2019/8 頃出版… というのが当初の予定だったんだけど、実際には 2020/7 になった。 まあ予定は未定。ちゃんと出たんだからヨシッ!

mrubyudf : C を書かずに Ruby だけで MySQL の UDF を作る

昨日はmrubyでMySQLのUDFを作ってみたんだけど、関数毎にCのプログラムを書くのがめんどくさいので、簡単なツールを作ってみた。

github.com

詳しくは README 参照。

fib.rb と fib.spec をこんな感じで作っておいて、

fib.rb

LONG_LONG_MAX = 9223372036854775807

def fib(n)
  b = 1
  c = 0
  n.times do
    a, b = b, c
    c = a + b
    raise 'Overflow' if c > LONG_LONG_MAX
  end
  c
end

fib.spec

MrubyUdf.function do |f|
  f.name = 'fib'           # 関数名は fib
  f.return_type = Integer  # 戻り値は Integer
  f.arguments = [          # 引数は一つで型は Integer
    Integer
  ]
end
% mrubyudf fib.spec

とやれば fib.so が出来上がるはず。 コンパイルオプションとかは自分の環境決め打ちなのでうまくいかないかもしれない。

% sudo cp fib.so $(mysql_config --plugindir)
% mysql -uroot
mysql> create function fib returns int soname 'fib.so';
mysql> select fib(10);
+---------+
| fib(10) |
+---------+
|      55 |
+---------+

ところで、mruby のインスタンスは関数を使うクエリが発行されたタイミングで生成されて、クエリ終了時に破棄される。

つまり、クエリの実行中はグローバル変数が生存しているので、こんな感じにすると、

rownum.rb

$n = 0
def rownum()
  $n += 1
end

rownum.spec

MrubyUdf.function do |f|
  f.name = 'rownum'
  f.return_type = Integer
  f.arguments = []
end

クエリ結果に行番号をつけることができたりもする。面白いかも。

mysql> create function rownum returns int soname 'rownum.so';
mysql> select rownum(),character_set_name from information_schema.character_sets;
+----------+--------------------+
| rownum() | CHARACTER_SET_NAME |
+----------+--------------------+
|        1 | big5               |
|        2 | dec8               |
|        3 | cp850              |
|        4 | hp8                |
|        5 | koi8r              |
|        6 | latin1             |
|        7 | latin2             |
|        8 | swe7               |
|        9 | ascii              |
|       10 | ujis               |
|       11 | sjis               |
|       12 | hebrew             |
|       13 | tis620             |
|       14 | euckr              |
|       15 | koi8u              |
|       16 | gb2312             |
|       17 | greek              |
|       18 | cp1250             |
|       19 | gbk                |
|       20 | latin5             |
|       21 | armscii8           |
|       22 | utf8               |
|       23 | ucs2               |
|       24 | cp866              |
|       25 | keybcs2            |
|       26 | macce              |
|       27 | macroman           |
|       28 | cp852              |
|       29 | latin7             |
|       30 | cp1251             |
|       31 | utf16              |
|       32 | utf16le            |
|       33 | cp1256             |
|       34 | cp1257             |
|       35 | utf32              |
|       36 | binary             |
|       37 | geostd8            |
|       38 | cp932              |
|       39 | eucjpms            |
|       40 | gb18030            |
|       41 | utf8mb4            |
+----------+--------------------+

しかし、ツールを作ってはみたものの別に作りたい UDF は特に無いんだよな…。

RubyでMySQLのUDFを作る

昨日はCでMySQLのUDFを作ってみたんだけど、今日はRubyで作ってみる。Rubyと言ってもmrubyだけど。

mrubyは5年ぶりくらいに使ってみたんだけど、相変わらずドキュメントが少なくてなかなかつらい…。

まず mruby のビルド。MySQLのUDFは共有ライブラリにしないといけないので、パッチをあてて make。

% git clone git@github.com:mruby/mruby.git
% cd mruby
% patch -p1 < /path/to/mruby-shared.patch
% make

mruby-shared.patch はこれ

diff --git a/build_config.rb b/build_config.rb
index 254a28ce..310191e3 100644
--- a/build_config.rb
+++ b/build_config.rb
@@ -35,6 +35,10 @@ MRuby::Build.new do |conf|
   #   cc.compile_options = %Q[%{flags} -MMD -o "%{outfile}" -c "%{infile}"]
   # end
 
+  conf.cc do |cc|
+    cc.flags = '-fPIC'
+  end
+
   # mrbc settings
   # conf.mrbc do |mrbc|
   #   mrbc.compile_options = "-g -B%{funcname} -o-" # The -g option is required for line numbers
@@ -59,6 +63,11 @@ MRuby::Build.new do |conf|
   #   archiver.archive_options = 'rs "%{outfile}" %{objs}'
   # end
 
+  conf.archiver do |archiver|
+    archiver.command = 'gcc'
+    archiver.archive_options = '-shared -o %{outfile} %{objs}'
+  end
+
   # Parser generator settings
   # conf.yacc do |yacc|
   #   yacc.command = ENV['YACC'] || 'bison'
diff --git a/lib/mruby/build.rb b/lib/mruby/build.rb
index 8154b2b1..80f0b782 100644
--- a/lib/mruby/build.rb
+++ b/lib/mruby/build.rb
@@ -61,7 +61,7 @@ module MRuby
         if ENV['OS'] == 'Windows_NT'
           @exts = Exts.new('.o', '.exe', '.a')
         else
-          @exts = Exts.new('.o', '', '.a')
+          @exts = Exts.new('.o', '', '.so')
         end
 
         build_dir = build_dir || ENV['MRUBY_BUILD_DIR'] || "#{MRUBY_ROOT}/build"

関数のメインのRubyスクリプト fib.rb

LONG_LONG_MAX = 9223372036854775807

def fib(n)
  b = 1
  c = 0
  n.times do
    a, b = b, c
    c = a + b
    raise 'Overflow' if c > LONG_LONG_MAX
  end
  c
end

一応ちゃんと動くか確認

% /path/to/mruby/bin/mruby -r./fib.rb -e 'p fib(10)'
55
% /path/to/mruby/bin/mruby -r./fib.rb -e 'p fib(100)'
trace (most recent call last):
    [2] -e:1
    [1] -e:6:in fib
-e:9:in fib: Overflow (RuntimeError)

mrubyバイトコードにコンパイル。

% /path/to/mruby/bin/mrbc -Bfib_mrb fib.rb

バイトコードが埋め込まれた fib.c が出来る。内容はこんなの。

/* dumped in little endian order.
   use `mrbc -E` option for big endian CPU. */
#include <stdint.h>
#ifdef __cplusplus
extern const uint8_t fib_mrb[];
#endif
const uint8_t
#if defined __GNUC__
__attribute__((aligned(4)))
#elif defined _MSC_VER
__declspec(align(4))
#endif
fib_mrb[] = {
0x45,0x54,0x49,0x52,0x30,0x30,0x30,0x36,0x64,0x45,0x00,0x00,0x01,0x5f,0x4d,0x41,
0x54,0x5a,0x30,0x30,0x30,0x30,0x49,0x52,0x45,0x50,0x00,0x00,0x01,0x0e,0x30,0x30,
0x30,0x32,0x00,0x00,0x00,0x96,0x00,0x01,0x00,0x03,0x00,0x01,0x00,0x00,0x00,0x14,
0x02,0x01,0x00,0x1c,0x01,0x00,0x61,0x01,0x56,0x02,0x00,0x5d,0x01,0x01,0x0e,0x01,
0x01,0x37,0x01,0x67,0x00,0x00,0x00,0x01,0x01,0x00,0x13,0x39,0x32,0x32,0x33,0x33,
0x37,0x32,0x30,0x33,0x36,0x38,0x35,0x34,0x37,0x37,0x35,0x38,0x30,0x37,0x00,0x00,
0x00,0x02,0x00,0x0d,0x4c,0x4f,0x4e,0x47,0x5f,0x4c,0x4f,0x4e,0x47,0x5f,0x4d,0x41,
0x58,0x00,0x00,0x03,0x66,0x69,0x62,0x00,0x00,0x00,0x00,0x72,0x00,0x05,0x00,0x07,
0x00,0x01,0x00,0x00,0x00,0x14,0x00,0x00,0x33,0x04,0x00,0x00,0x07,0x03,0x06,0x04,
0x01,0x05,0x01,0x55,0x06,0x00,0x2f,0x05,0x00,0x00,0x37,0x04,0x00,0x00,0x00,0x00,
0x00,0x00,0x00,0x01,0x00,0x05,0x74,0x69,0x6d,0x65,0x73,0x00,0x00,0x00,0x01,0x31,
0x00,0x03,0x00,0x06,0x00,0x00,0x00,0x00,0x00,0x3d,0x00,0x00,0x33,0x00,0x00,0x00,
0x1f,0x03,0x03,0x00,0x1f,0x04,0x04,0x00,0x01,0x02,0x03,0x20,0x04,0x03,0x00,0x01,
0x03,0x02,0x1f,0x04,0x03,0x00,0x3b,0x03,0x20,0x03,0x04,0x00,0x1f,0x03,0x04,0x00,
0x1b,0x04,0x00,0x44,0x03,0x23,0x03,0x00,0x39,0x10,0x03,0x4f,0x04,0x00,0x2e,0x03,
0x01,0x01,0x21,0x00,0x3b,0x0f,0x03,0x37,0x03,0x00,0x00,0x00,0x01,0x00,0x00,0x08,
0x4f,0x76,0x65,0x72,0x66,0x6c,0x6f,0x77,0x00,0x00,0x00,0x02,0x00,0x0d,0x4c,0x4f,
0x4e,0x47,0x5f,0x4c,0x4f,0x4e,0x47,0x5f,0x4d,0x41,0x58,0x00,0x00,0x05,0x72,0x61,
0x69,0x73,0x65,0x00,0x4c,0x56,0x41,0x52,0x00,0x00,0x00,0x33,0x00,0x00,0x00,0x05,
0x00,0x01,0x6e,0x00,0x01,0x26,0x00,0x01,0x62,0x00,0x01,0x63,0x00,0x01,0x61,0x00,
0x00,0x00,0x01,0x00,0x01,0x00,0x02,0x00,0x02,0x00,0x03,0x00,0x03,0x00,0x04,0x00,
0x01,0x00,0x01,0x00,0x04,0x00,0x02,0x45,0x4e,0x44,0x00,0x00,0x00,0x00,0x08,
};

MySQLのUDFからfibメソッドを実行するためのファイル fib_udf.c を作成。

#include <stdio.h>
#include <string.h>
#include "mruby.h"
#include "mruby/string.h"
#include "mruby/irep.h"
#include "mysql.h"

extern const uint8_t fib_mrb[];

bool fib_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
  if (args->arg_count != 1) {  // 引数が1個じゃなければエラー
    strcpy(message, "fib() requires one argument");
    return true;
  }
  args->arg_type[0] = INT_RESULT; // 引数は整数に自動変換する

  mrb_state *mrb = mrb_open();
  if (!mrb) {  // mruby 初期化でエラーになった
    strcpy(message, "mrb_open() error");
    return true;
  }
  initid->ptr = (void *)mrb;
  mrb_load_irep(mrb, fib_mrb);
  if (mrb->exc) {  // mrb 実行でエラーになった
    // 例外オブジェクトを inspect
    mrb_value s = mrb_funcall(mrb, mrb_obj_value(mrb->exc), "inspect", 0);
    // C文字列化
    const char *cs = mrb_string_value_cstr(mrb, &s);
    // MySQLのエラーメッセージに設定
    strncpy(message, cs, MYSQL_ERRMSG_SIZE);
    // 標準エラー出力にも書き出し
    fprintf(stderr, "%s\n", cs);
    return true;
  }
  return false;
}

void fib_deinit(UDF_INIT *initid)
{
  mrb_state *mrb = (mrb_state *)initid->ptr;
  if (mrb) {
    mrb_close(mrb);
  }
}

long long fib(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
  mrb_state *mrb = (mrb_state *)initid->ptr;
  long long n = *((long long *)args->args[0]);
  // トップレベルの fib メソッド呼び出し
  mrb_value ret = mrb_funcall(mrb, mrb_top_self(mrb), "fib", 1, mrb_fixnum_value(n));
  if (mrb->exc) { // 例外が発生した
    // 例外オブジェクトを inspect
    mrb_value s = mrb_funcall(mrb, mrb_obj_value(mrb->exc), "inspect", 0);
    // 標準エラー出力に書き出し
    fprintf(stderr, "fib(%lld): %s\n", n, mrb_string_value_cstr(mrb, &s));
    // 戻り値は NULL
    *is_null = true;
    return 0;
  }
  // Integerオブジェクトを long long に変換して返す
  return mrb_fixnum(ret);
}

コンパイル。

LD_RUN_PATH=/path/to/mruby/build/host/lib gcc -shared -fPIC -I /usr/local/mysql/include -I /path/to/mruby/include fib_udf.c fib.c -L /path/to/mruby/build/host/lib -lmruby -lm -o fib.so

配置。既に同じ名前のファイルを使ってた場合は、mysql で drop function fib してからじゃないと、mysqld が落ちるので注意。

% sudo cp fib.so /usr/local/mysql/lib/plugin/

使う。

% mysql -uroot
mysql> create function fib returns integer soname 'fib.so';
Query OK, 0 rows affected (0.03 sec)
mysql> select fib(10);
+---------+
| fib(10) |
+---------+
|      55 |
+---------+
1 row in set (0.04 sec)

mysql> select fib(100);
+----------+
| fib(100) |
+----------+
|     NULL |
+----------+
1 row in set (0.03 sec)

オーバーフローが起きた場合はエラーログに出力される。

fib(100): Overflow (RuntimeError)

続く

MySQLのユーザー定義関数(UDF)

MySQLのユーザー定義関数(UDF)は大昔に作った記憶があるけど、最近作ってなかったので試しに作ってみたメモ。

関数の中身はなんでも良かったんだけど、フィボナッチ数を求める fib()を作ってみた。

ちゃんと知りたい場合は、マニュアルとかサンプルプログラムを見ましょう。

% gcc -shared -I /usr/local/mysql/include fib.c -o fib.so
% sudo cp fib.so /usr/local/mysql/lib/plugin/
% /usr/local/mysql/bin/mysql -uroot
mysql> create function fib returns integer soname 'fib.so';
Query OK, 0 rows affected (0.02 sec)
mysql> select n, fib(n) from (values row(1),row(2),row(3),row(4),row(5),row(6),row(7),row(8),row(9),row(10)) as n(n);
+----+--------+
| n  | fib(n) |
+----+--------+
|  1 |      1 |
|  2 |      1 |
|  3 |      2 |
|  4 |      3 |
|  5 |      5 |
|  6 |      8 |
|  7 |     13 |
|  8 |     21 |
|  9 |     34 |
| 10 |     55 |
+----+--------+
10 rows in set (0.01 sec)
// UDFの詳細はマニュアルとサンプルプログラムを参照
// https://dev.mysql.com/doc/refman/8.0/en/adding-functions.html
// https://github.com/mysql/mysql-server/blob/8.0/sql/udf_example.cc
//
// コンパイル方法
// gcc -shared -I /usr/local/mysql/include fib.c -o fib.so
// 作成された so ファイルを plugin-dir ディレクトリに置く
// 使用時:
// create function fib returns integer soname 'fib.so';
// select fib(10);
// 関数が不要になった場合は破棄できる:
// drop function fib;

#include <string.h>
#include "mysql.h"
#include "mysql/udf_registration_types.h"

// 初期化
// 関数を使用するクエリの実行前に呼ばれる
// initid  : 戻り値
// args    : 引数
// message : エラーメッセージ用バッファ。MYSQL_ERRMSG_SIZE バイト(8.0 では 512)
// 戻り値は、成功時には false、エラー時には true (変なの)
// 関数の戻り値は CREATE FUNCTION で指定される
bool fib_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
  initid->maybe_null = true;   // 戻り値がNULLになり得る場合はtrue
  initid->decimals = 0;        // 戻り値が浮動小数点数の場合に小数点以下の桁数を指定
  initid->max_length = 21;     // 戻り値の最大文字数/最大桁数
  initid->ptr = NULL;          // この関数用に確保したメモリのポインタ等
  if (args->arg_count != 1) {  // 引数が1個じゃなければエラー
    strcpy(message, "fib() requires one argument");
    return true;
  }
  if (args->maybe_null[0]) { // 引数にNULLは許容しない
    strcpy(message, "fib() requires not NULL");
    return true;
  }
  if (args->arg_type[0] != INT_RESULT) { // 引数が整数でなければエラー
    strcpy(message, "fib() requires an integer argument");
    return true;
  }
  // 上のようにチェックする他に MySQL に自動変換させることもできる
  //args->arg_type[0] = INT_RESULT;

  // おまけ
  // args->attribues[0] と args->attribute_lengths[0] で 0番目の引数に与えた名前がわかる
  // fib(123) の場合は "123", fib(hoge) の場合は "hoge"

  return false;
}

// 終了
// initid->ptr にメモリを確保していた場合はここで解放する
void fib_deinit(UDF_INIT *initid)
{
}

// 実行
// initid  : ptr を使う場合以外は使用しない
// args    : 引数
// is_null : 戻り値がNULLになる場合 true を設定
// error   : エラー時に true を設定。ひとつのクエリ内ではそれ以降は評価されずにNULLが返る
long long fib(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
  long long n;
  n = *((long long *)args->args[0]);

  long long i, a, b, c;
  for (i = 0, b = 1, c = 0; i < n; i++) {
    a = b;
    b = c;
    c = a + b;
    if (c <= 0) {  // オーバーフロー時は NULL を返す
      *is_null = true;
      return 0;
    }
  }
  return c;
}

続く

MySQL 8.0.19 のオフセットつき日時リテラル

MySQL 8.0.19 のリリースノート にこんなのがあって、

MySQL now supports datetime literals with time zone offsets, such as '2019-12-11 10:40:30-05:00', '2003-04-14 03:30:00+10:00', and '2020-01-01 15:35:45+05:30'; these offsets are respected but not stored when inserting such values into TIMESTAMP and DATETIME columns; that is, offsets are not displayed when retrieving the values.

一瞬 DATETIME 型にタイムゾーンが来たか! と喜んだんだけど、よく読んだら違った。

日時リテラルにオフセットを指定できるようになっただけだった。

日本時間のサーバーで +09:00 を指定しても時刻はそのまま。当たり前。

mysql> SELECT CONVERT('2020-01-28 00:00:00+09:00', DATETIME);
+------------------------------------------------+
| CONVERT('2020-01-28 00:00:00+09:00', DATETIME) |
+------------------------------------------------+
| 2020-01-28 00:00:00                            |
+------------------------------------------------+

UTC(+00:00)を指定すると、日本時間に変換される。

mysql> SELECT CONVERT('2020-01-28 00:00:00+00:00', DATETIME);
+------------------------------------------------+
| CONVERT('2020-01-28 00:00:00+00:00', DATETIME) |
+------------------------------------------------+
| 2020-01-28 09:00:00                            |
+------------------------------------------------+

正確にはサーバーの時間じゃなくて、接続のタイムゾーンに依存する。デフォルトの time_zoneSYSTEM だからサーバーの時間になっていただけ。

接続のタイムゾーンを UTC にすると、UTC時間に変換される。

mysql> SET time_zone='+00:00';

mysql> SELECT CONVERT('2020-01-28 00:00:00+09:00', DATETIME);
+------------------------------------------------+
| CONVERT('2020-01-28 00:00:00+09:00', DATETIME) |
+------------------------------------------------+
| 2020-01-27 15:00:00                            |
+------------------------------------------------+

mysql> SELECT CONVERT('2020-01-28 00:00:00+00:00', DATETIME);
+------------------------------------------------+
| CONVERT('2020-01-28 00:00:00+00:00', DATETIME) |
+------------------------------------------------+
| 2020-01-28 00:00:00                            |
+------------------------------------------------+

DATETIME 型はタイムゾーンを保持しないので、格納したデータは接続のタイムゾーンを変更しても何も変わらない。残念。TIMESTAMP の方は変わる。

mysql> CREATE TABLE x (ts TIMESTAMP, dt DATETIME);

mysql> SET time_zone='+09:00';
mysql> INSERT INTO x (ts, dt) VALUES
    -> ('2020-01-28 00:00:00+09:00', '2020-01-28 00:00:00+09:00'),
    -> ('2020-01-28 00:00:00+00:00', '2020-01-28 00:00:00+00:00');
mysql> SELECT * FROM x;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2020-01-28 00:00:00 | 2020-01-28 00:00:00 |
| 2020-01-28 09:00:00 | 2020-01-28 09:00:00 |
+---------------------+---------------------+
mysql> SET time_zone='+00:00';
mysql> SELECT * FROM x;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2020-01-27 15:00:00 | 2020-01-28 00:00:00 |
| 2020-01-28 00:00:00 | 2020-01-28 09:00:00 |
+---------------------+---------------------+

おまけ: MySQL 8.0 での TIMESTAMP と DATETIME の比較

昔は TIMESTAMP はレコードの作成時刻や更新時刻を自動設定できるのに DATETIME ではできなかったんだけど、今は DATETIME でもできる。

TIMESTAMP DATETIME
上限 2038-01-19 03:14:07+00:00 9999-12-31 23:59:59+00:00
レコード作成時時刻を自動設定 可(デフォルトはオフ) 可(デフォルトはオフ)
レコードの更新時刻を自動設定 可(デフォルトはオフ) 可(デフォルトはオフ)
接続のタイムゾーンに時刻を変換 不可

ということで、DATETIME が時刻変換してくれるようになれば、TIMESTAMP を捨てされるのになーって感じ。

MySQL 8.0.19 で追加された TABLE と VALUES

1/13 に MySQL 8.0.19 がリリースされたんで、リリースノート を眺めてると気になるのがいくつか。

Important Change: MySQL now supports explicit table clauses and table value constructors according to the SQL standard. These have now been implemented, respectively, as the TABLE statement and the VALUES statement

TABLE と VALUES ステートメントが追加されたみたい。 さすがMySQL! パッチリリースで新しい構文を突っ込んでくるッ! そこにシビれる!あこがれるゥ!

TABLE

TABLE は、テーブルの中身全部を取り出す SELECT のようなものらしい。

TABLE tSELECT * FROM t と同じ意味らしい。WHERE や JOIN は使えない(ORDER BY と LIMIT はある)。

mysql> SELECT * FROM t;
+------+-------+
| id   | value |
+------+-------+
|    1 | abc   |
|    2 | xyz   |
+------+-------+
2 rows in set (0.01 sec)

mysql> TABLE t;
+------+-------+
| id   | value |
+------+-------+
|    1 | abc   |
|    2 | xyz   |
+------+-------+
2 rows in set (0.00 sec)

誰得なのかわからん…。サブクエリ中の SELECT を簡単に書けるようになるくらい?

VALUES

VALUES はレコードセットをリテラルで書けるらしい。 カラム名は column_N になる。

mysql> VALUES ROW(1,2,3), ROW(3,2,1);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
|        3 |        2 |        1 |
+----------+----------+----------+
2 rows in set (0.00 sec)

ROW が余計な気がする。PostgreSQL は ROW が要らなくて、INSERT の VALUES 句と同じように書けるっぽい。

VALUES にも ORDER BY と LIMIT はある。

mysql> VALUES ROW(1,2,3), ROW(3,2,1) ORDER BY column_2 LIMIT 1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        3 |        2 |        1 |
+----------+----------+----------+
1 row in set (0.00 sec)

けど、ORDER BY を指定しない LIMIT は効かないみたい。バグなのかな…。

mysql> VALUES ROW(1,2,3), ROW(3,2,1) LIMIT 1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
|        3 |        2 |        1 |
+----------+----------+----------+
2 rows in set (0.00 sec)

VALUES の方は嬉しいことがあるかもしれない。集計系のクエリで結果テーブルに表れない項目のレコードを足したりとか。

mysql> SELECT name, COUNT(*) cnt FROM t GROUP BY name;
+---------+-----+
| name    | cnt |
+---------+-----+
| 製品A   |   2 |
| 製品C   |   1 |
| 製品E   |   3 |
+---------+-----+
3 rows in set (0.00 sec)

mysql> SELECT name, IFNULL(cnt,0) AS cnt
 FROM (SELECT name, count(*) AS cnt FROM t GROUP BY name) AS a
 RIGHT JOIN (VALUES ROW('製品A'), ROW('製品B'), ROW('製品C'), ROW('製品D'), ROW('製品E')) AS b(name)
 USING (name);
+---------+-----+
| name    | cnt |
+---------+-----+
| 製品A   |   2 |
| 製品C   |   1 |
| 製品E   |   3 |
| 製品B   |   0 |
| 製品D   |   0 |
+---------+-----+
5 rows in set (0.00 sec)

しかし名前が TABLE と VALUES なのか…。動詞じゃないことに違和感。標準SQLらしいけど。

MySQL Parameters を拡張した

これは「MySQL アドベントカレンダー 2019」と「富士通クラウドテクノロジーズ アドベントカレンダー 2019」の4日目の記事です。

qiita.com

qiita.com

MySQL の各バージョン間のパラメーターの違いを比較できる MySQL Parameter というページを去年公開したんですが、それが何故か割と好評だったみたいで今年の4月に MySQL Community Contributor Award Program 2019 というのにノミネートされたりもしました。

blogs.oracle.com

それを独自ドメイン化したり、いろいろ追加したりしたので、それについて書きます。

独自ドメイン化

今まで GitHub Pages のデフォルトのドメイン https://tmtm.github.io/mysql-params で公開していたのですが、手持ちのドメインのサブドメインの https://mysql-params.tmtms.net に変更してみました。 サブドメイン名をリポジトリ名と同じにしてみましたが、別にその必要はありません。

まず mysql-params.tmtms.net レコードを CNAME tmtm.github.io で登録します。

tmtms.net ゾーンはニフクラDNSで管理しているので、ニフクラのコンパネから tmtms.net のゾーン管理で mysql-params レコードを作ります。簡単。

f:id:tmtms:20191123211349p:plain

次に GitHub のリポジトリで、「Settings」の下の方に「GitHub Pages」があるので、そこにドメイン名を設定するだけです。お手軽。

f:id:tmtms:20191123204255p:plain

今までの https://tmtm.github.io/mysql-params にアクセスすると、ちゃんと https://mysql-params.tmtms.net にリダイレクトされます。 相対パスやクエリパラメータも保ったままリダイレクトされるので便利。

HTTPS もチェックボックスをチェックするだけ。簡単。チェックできるようになるまで時間が掛かりますが。

いろいろ追加

今まで mysqld のパラメーターだけにしか対応していなかったのですが、mysql コマンドにも対応しました。

mysqld https://mysql-params.tmtms.net/mysqld/?vers=5.0.96,5.1.72,5.5.62,5.6.46,5.7.28,8.0.18

mysql https://mysql-params.tmtms.net/mysql/?vers=5.0.96,5.1.72,5.5.62,5.6.46,5.7.28,8.0.18

あと、Status, Charset, Collation, Privilege にも対応してみました。 これらは項目の有無がわかるだけですが。

Status https://mysql-params.tmtms.net/status/?vers=5.0.96,5.1.72,5.5.62,5.6.46,5.7.28,8.0.18

Charset https://mysql-params.tmtms.net/charset/?vers=5.0.96,5.1.72,5.5.62,5.6.46,5.7.28,8.0.18

Collation https://mysql-params.tmtms.net/collation/?vers=5.0.96,5.1.72,5.5.62,5.6.46,5.7.28,8.0.18

Privilege https://mysql-params.tmtms.net/privilege/?vers=5.0.96,5.1.72,5.5.62,5.6.46,5.7.28,8.0.18

https://mysql-params.tmtms.net/ にアクセスするとメニューを表示するようになってますが、

f:id:tmtms:20191124113607p:plain

過去のURLとの互換のため、https://tmtm.github.io/mysql-params?vers=8.0.11,8.0.18&diff=true のようにクエリパラメータがあったら mysqld のページにリダイレクトするようにしてあります。

mysqld と mysql のパラメータは、/usr/local/mysql にインストールした後に --no-defaults --help -v オプションで実行した出力から得ています。

Status, Charset, Collation は SHOW GLOBAL STATUS, SHOW CHARSET, SHOW COLLATION の結果です。

Privilege は DESC mysql.user のカラム名に _priv がついてるものを取り出して、変換しています(Create_tmp_table -> CREATE TEMPORARY TABLE など)。 PROXY 権限は mysql.user テーブルには載ってないので、mysql.proxies_priv テーブルの有無を見てるような感じです。

権限は結構増えてるような印象があったのですが、5.0 の頃と比べても意外とそんなに増えてなかったですね。

f:id:tmtms:20191130150808p:plain

見た目にはこだわりないのでCSSは最低限でデザインがアレですが、よろしければご利用ください。

[追記] 2020-01-28

MySQL 8.0 で動的パラメータがかなり増えてました。それも反映したので今見たらもっと多いです。 動的パラメータは GRANT ALL でユーザー作って、それを SHOW GRANTS した結果から取り出してます。

MySQLと「令和」その2

MySQLでは異なる字が等しいと見なされることがあるということを書きました。

tmtms.hatenablog.com

この動きはMySQLが独自に変なことをしているわけではなく、Unicodeの規則に従っています。

MySQL 8.0 のデフォルトのCollationは Unicode 9.0.0Unicode Collation Algorithm(UCA) に従っています。

文字にはそれぞれ Weight という値が設定されていてソートに使用されています。この値が同じなら等しい文字とみなされます。

Collation

utf8mb4 のデフォルトの Collation は utf8mb4_0900_ai_ci という名前ですが、これは次のような意味です。

名前の要素 意味
utf8mb4 charset名
0900 Unicodeバージョン9.0.0
ai アクセントの違いを無視 (Accent Insensitive)
ci 大文字小文字の違いを無視 (Case Insensitive)

ai は「a」と「á」が等しくなります。日本語の場合は「」「」「」が等しくなります。 ai ではなく as であれば異なる文字として扱われます。

ci は「A」と「a」が等しくなります。日本語の場合は「」「」が等しくなります。 ci ではなく cs であれば異なる文字として扱われます。

utf8mb4 の Collation は次のようなものがあります。

  • utf8mb4_0900_ai_ci

    • アクセント/大文字小文字の違いを無視。
  • utf8mb4_0900_as_ci

    • アクセントが異なれば異なる文字。大文字小文字の違いは無視。
  • utf8mb4_0900_as_cs

    • アクセント/大文字小文字が異なれば異なる文字。
  • utf8mb4_bin

    • UnicodeのCollationを無視。すべて異なる文字。

以上を踏まえて、この前の記事であげた例を見てみます。

「令」と「令」

令和」(U+4EE4 U+548C)と「令和」(U+F9A8 U+548C)が等しいと見なされるやつです。

Collationによって次のように評価されます。

Collation =
utf8mb4_0900_ai_ci
utf8mb4_0900_as_ci
utf8mb4_0900_as_cs
utf8mb4_bin

」(U+F9A8)のWeightは DUCET(https://www.unicode.org/Public/UCA/9.0.0/allkeys.txt) に次のように定義されています。

F9A8  ; [.FB40.0020.0002][.CEE4.0000.0000] # CJK COMPATIBILITY IDEOGRAPH-F9A8

」(U+4EE4)は定義されていませんが、http://www.unicode.org/reports/tr10/tr10-34.html#Implicit_Weights に計算方法が載っています。 これに従って計算すると、次のようになります。

[.FB40.0020.0002][.CEE4.0000.0000]

」(U+4EE4)と「」(U+F9A8)のWeightは同じ値になるので、utf8mb4_bin 以外は等しいと評価されます。

合字

」と「平成」が等しいと見なされるやつです。 別に元号だけに限らず、「」と「サンチーム」も同じです。

Collationによって次のように評価されます。

Collation =
utf8mb4_0900_ai_ci
utf8mb4_0900_as_ci
utf8mb4_0900_as_cs
utf8mb4_bin

」のWeightは DUCET(https://www.unicode.org/Public/UCA/9.0.0/allkeys.txt) に次のように定義されています。

337B  ; [.FB40.0020.001C][.DE73.0000.0000][.FB40.0020.001C][.E210.0000.0000] # SQUARE ERA NAME HEISEI

」と「」は定義されてませんが「」と同じく計算すると次のようになります。「」と似てますがちょっと異なります。

平成 [.FB40.0020.0002][.DE73.0000.0000][.FB40.0020.0002][.E210.0000.0000]

Unicode では大文字小文字を区別しない場合(ci)は [ ] の中の3番目の数字を無視して評価することになってます。

そうすると次のようにまったく同じ値となります。

㍻   [.FB40.0020][.DE73.0000][.FB40.0020][.E210.0000]
平成 [.FB40.0020][.DE73.0000][.FB40.0020][.E210.0000]

ということで、ci の Collation では「㍻=平成」となるのでした。

異体字セレクタ

「令和」(U+4EE4 U+548C)と「令󠄂和」(U+4EE4 U+E0102 U+548C) が等しいと見なされるやつです。

Collationによって次のように評価されます。

Collation =
utf8mb4_0900_ai_ci
utf8mb4_0900_as_ci
utf8mb4_0900_as_cs
utf8mb4_bin

U+E0102 は DUCET に次のように定義されています。

E0102 ; [.0000.0000.0000] # VARIATION SELECTOR-19

UCA ではすべてが 0 の値は無視することになってるので、異体字セレクタの U+E0102 は無視され、utf8mb4_bin 以外は等しいと評価されます。

LIKE

上のように = での評価は UCA に従ってることで説明できるのですが、LIKE での評価はよくわかりませんでした。

LIKE ではすべての Collation で偽になります。

Collation LIKE
utf8mb4_0900_ai_ci
utf8mb4_0900_as_ci
utf8mb4_0900_as_cs
utf8mb4_bin

MySQL のマニュアルには次のように書いてあります。

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

MySQL 5.6 の日本語マニュアルでは:

SQL 標準では、LIKE は文字ごとに一致を実行するため、= 比較演算子とは異なる結果が生成される可能性があります。

文字数(コードポイント数)が異なる合字や異体字セレクタでは =LIKE が異なるのはまあわかるんですが、 「」(U+4EE4)と「」(U+F9A8)は同じ1文字なのに LIKE では一致しなくなるのが謎…。

(って書いておけば誰かがソース読んで解説してくれるんじゃないかと期待)

MySQLと「令和」

新元号が「令和」に決まったことなので、MySQLでの扱いについての話を。

普通の文字

「令」も「和」もJIS第一水準に含まれている基本的な文字なので普通に日本語が使用できるcharsetで使用できます。

mysql> create table t (
  utf8mb4 varchar(255) charset utf8mb4,
  utf8mb3 varchar(255) charset utf8mb3,
  utf16 varchar(255) charset utf16,
  utf32 varchar(255) charset utf32,
  cp932 varchar(255) charset cp932,
  eucjpms varchar(255) charset eucjpms,
  sjis varchar(255) charset sjis,
  ujis varchar(255) charset ujis
);

mysql> insert into t values ('令和', '令和', '令和', '令和', '令和', '令和', '令和', '令和');

mysql> select * from t\G
*************************** 1. row ***************************
utf8mb4: 令和
utf8mb3: 令和
  utf16: 令和
  utf32: 令和
  cp932: 令和
eucjpms: 令和
   sjis: 令和
   ujis: 令和

mysql> select hex(utf8mb4), hex(utf8mb3), hex(utf16), hex(utf32), hex(cp932), hex(eucjpms), hex(sjis), hex(ujis) from t\G
*************************** 1. row ***************************
hex(utf8mb4): E4BBA4E5928C
hex(utf8mb3): E4BBA4E5928C
  hex(utf16): 4EE4548C
  hex(utf32): 00004EE40000548C
  hex(cp932): 97DF9861
hex(eucjpms): CEE1CFC2
   hex(sjis): 97DF9861
   hex(ujis): CEE1CFC2

なんの問題もないですね。

「令」と「令」

ところで、ユニコードには「令」と見た目が同じ字が他にもあると話題になりました。 普通の「令」は U+4EE4(UTF-8 で E4BBA4)、もうひとつの「令」は U+F9A8(UTF-8 で EFA6A8)です。

mysql> set @4ee4=_utf8mb4 0xE4BBA4E5928C, @f9a8=_utf8mb4 0xEFA6A8E5928C;

mysql> select @4ee4, @f9a8;
+--------+--------+
| @4ee4  | @f9a8  |
+--------+--------+
| 令和   | 令和   |
+--------+--------+

見た目が同じなのにコードが違うから何かトラブルが起きるんじゃないかと言われたりしてますが、実際にはこのような文字は他にもあるので、今まで問題になったことがないんならそんなに問題にならないんじゃないですかね。

ところで MySQL では、この二文字は = で比較すると一致として扱われます。LIKE では不一致です。

mysql> select @4ee4, @f9a8, @4ee4=@f9a8, @4ee4 LIKE @f9a8;
+--------+--------+-------------+------------------+
| @4ee4  | @f9a8  | @4ee4=@f9a8 | @4ee4 LIKE @f9a8 |
+--------+--------+-------------+------------------+
| 令和   | 令和   |           1 |                0 |
+--------+--------+-------------+------------------+

理由は調べてませんが、たぶん = は正規化して比較するけど LIKE は正規化しないで比較するみたいな感じなんじゃないかと思います(字面的には =LIKE が逆のような印象で面白い)。

異体字セレクタ

もうひとつ「令」と言えば異体字ですよね。ユニコードでは異体字セレクタというコードを後ろにつけることで字体を選択することができます。

環境によりますが「U+4EE4 U+E0102」の文字「令󠄂」は下が「マ」のように見えるコードです。

異体字セレクタを使用した場合も、= で一致し LIKE では不一致となります。

mysql> set @4ee4_e0102=_utf8mb4 0xE4BBA4F3A08482E5928C;

mysql> select @4ee4, @4ee4_e0102, @4ee4=@4ee4_e0102, @4ee4 LIKE @4ee4_e0102;
+--------+-------------+-------------------+------------------------+
| @4ee4  | @4ee4_e0102 | @4ee4=@4ee4_e0102 | @4ee4 LIKE @4ee4_e0102 |
+--------+-------------+-------------------+------------------------+
| 令和   | 令󠄂和        |                 1 |                      0 |
+--------+-------------+-------------------+------------------------+

合字

そして元号と言えば合字ですよね。

「明治」「大正」「昭和」「平成」には、それぞれ1文字で表せる「㍾」「㍽」「㍼」「㍻」という文字があります。 「令和」にも「㋿」(U+32FF)という文字が割り当てられています(見えないかもしれないけどOSやフォントが対応すればちゃんと見えるようになるはず)。

合字も正規化して比較されるので「平成」と「㍻」は一致するのですが、「令和」とU+32FFは一致しません。

mysql> select '明治'='㍾', '大正'='㍽', '昭和'='㍼', '平成'='㍻', '令和'='㋿'\G
*************************** 1. row ***************************
'明治'='㍾': 1
'大正'='㍽': 1
'昭和'='㍼': 1
'平成'='㍻': 1
'令和'='㋿': 0

U+32FF が「令和」の合字となるのはUnicodeのバージョン12.1からです。Unicode 12.1はどうやら日本の新元号に対応した合字を含めるためだけに作られるバージョンらしいです。迷惑な話ですね。2019/5/7 にリリースされるようです。

Unicode 12.1までは U+32FF は何の文字でもないコードです。

MySQL 8.0 のUnicodeのバージョンは9.0なので対応してないのですね。MySQL がいつUnicode 12.1に対応するのかはわかりませんが。


という感じですが、まあ、実際にはそんなに問題になることはないんじゃないですかね。

[追記] 続きを書きました https://tmtms.hatenablog.com/entry/201904/mysql-reiwa2

そろそろMySQLのutf8について一言いっとくか

MySQLのutf8 charsetは、やれ「罠」だの「絵文字が入らなくて使えない」だの「utf8という名前はutf8mb4の別名にすべき」だの、散々な言われようでディスられてかわいそうな charset なんだけど、というか主に私がそう言ってる気もするんだけど、そろそろ utf8mb3 のエイリアスとしての utf8 は消え去ろうとしてるみたいなので、ここでちょっと勝手にフォローしておく。

UTF-8 エンコーディングの RFC は RFC3629で、ここで UTF-8 は最大4バイトと書かれている。 しかし、この RFC3629 の前のRFC2279では6バイトだった。 RFC3629 の日付は 2003/11 なので、つまり 2003/11 よりも前は UTF-8 の1文字のバイト数は最大6バイトだったのだ(少なくともRFC上では)。

MySQL が Unicode に対応したのはバージョン 4.1 からで、開発版4.1.0のリリースは 2003/04/03、正式版4.1.7のリリースは 2004/10/23 だった。すくなくとも開発版リリース時点では UTF-8 の理論的な最大長は6バイトだった。 理論的に最大6バイトと言っても、当時に実際にそんなに文字が割り当てられてるわけはなく、UTF-8対応しようとした時に6バイトに対応するのは無駄であるし、それよりも小さいバイト数に対応しようとするのは自然だと思う。

Unicode は当初2バイトにすべての文字を納めるという計画だったし、UTF-8は1文字が可変長なのでそのままではプログラムで扱いづらいため、内部的には2バイトの固定長で扱われることが多かった(たぶん今でも多いと思う)。 2バイトで表現できる範囲(U+0000〜U+FFFF)をUTF-8で表すと最大3バイトになる。UTF-8の最大バイト数を3バイトにするというのは当時は妥当な判断だったと思う。

たとえば Windows が UTF-8で4バイトになる文字(U+10000〜の文字)に対応したのは、2006年リリースの Windows Vista からだ。 Windows Vista が出るまでは世間で4バイトUTF-8文字なんて実質使えなかったのである。

utf8が最大3バイトなのは妥当だったとして、MySQL が4バイトUTF-8に対応したのは 5.5 (2010/12 リリース)で、Windows Vista の4年後なので、まあちょっと遅い気がしないでもない。

なお、utf8mb4 に対応した現在でも、テーブル名やカラム名には4バイト文字は使えない。絵文字テーブル名や絵文字カラム名を使いたかったみなさん、残念でした。

ところで、先般リリースされたMySQL 8.0 では utf8 を使用するととうとう warning が出るようになるまで地位が下がってきた。 将来 utf8 は utf8mb3 のエイリアスではなく utf8mb4 のエイリアスになるらしい。

mysql> create table t (hoge varchar(10)) charset utf8;
Query OK, 0 rows affected, 1 warning (0.22 sec)

Warning (Code 3719): 'utf8' is currently an alias for the character set UTF8MB3, 
which will be replaced by UTF8MB4 in a future release. Please consider using 
UTF8MB4 in order to be unambiguous.

ところが、utf8mb3 でテーブル作っても、show create table すると utf8 で出力しやがるんですよ。

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `hoge` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

なので、mysqldump してロードしてみると、

% sudo mysqldump -uroot test t > /tmp/dump.sql
% sudo mysql -uroot --show-warnings test < /tmp/dump.sql
Warning (Code 3719): 'utf8' is currently an alias for the character set UTF8MB3, 
which will be replaced by UTF8MB4 in a future release. Please consider using 
UTF8MB4 in order to be unambiguous.

ちょ、おま… ダンプしてロードするだけで warning て…。

まだ中途半端なようですな…。

MySQLのsjisとcp932の違い

今さらですけど、自分でもちゃんと把握してなかったので調べてみました。

MySQLのCharsetのうちシフトJIS系のものはsjisとcp932の二つあります。

どちらもコードの範囲は次のように同じです。

1バイト文字 0x00-0x7F, 0xA1-0xDF
2バイト文字の1バイト目 0x81-0x9F, 0xE0-0xFC
2バイト文字の2バイト目 0x40-0x7E, 0x80-0xFC

違いは文字集合です。1バイト文字はどちらも同じ(ASCII + JIS X 0201 カナ)ですが、2バイト文字はsjisはJIS X 0208 で、cp932はWindows-31Jです。

sjisに含まれていない文字

cp932はsjisよりも文字が多く、丸囲み数字(「①」「②」「③」等)、ローマ数字(「Ⅰ」「Ⅱ」「Ⅲ」等)、組文字(「㍉」「㌍」「㍻」等)、その他「彅」「髙」等の JIS X 0208 には入ってない文字が含まれています。

同じコードで異なる文字

上記のように基本的にはsjisに文字を追加したものがcp932なのですが、同じコードで異なる文字が割り当てられているものがあります。

「\」「~」「∥」「-」「¢」「£」「¬」の7文字です。

sjisだけまたはcp932だけを使用して処理している場合には、特に何も問題にならないのですが、charsetを変換する場合に問題になります。

sjisの「~」はcp932には存在しないし、cp932の「~」はsjisには存在しません。つまりsjisとcp932の相互に変換しようとしたときに問題になります。なおutf8mb4には両方とも存在するので、utf8mb4に変換することはできます。

どうしてこんなことになったのか興味がある人はWikipediaの日本語環境でのUnicodeの諸問題を見ましょう。

利用者定義領域(外字領域)

cp932にはsjisには無かった利用者定義領域(外字領域)が1880文字分あります(0xF040〜F9FC)。

MySQLではsjis,cp932のコードの範囲であれば文字が割り当てられていないコードの文字も使用することができますが、他のcharsetに変換することはできません。

Unicodeにも利用者定義領域と同様の私用領域が定められていて、このうちの1880文字がcp932の利用者定義領域とマッピングされています。

つまりcp932の0xF040〜F9FCの文字はutf8mb4には変換できますが、sjisの同じ領域の文字は変換することができません。


ということで、以上をMySQL上で確かめてみます。

まず sjis, cp932 の範囲の文字を含んだファイルを作ります。今回はRubyで次のように作りました。

((0x81..0x9f).to_a + (0xe0..0xfc).to_a).each do |c1|
  ((0x40..0x7e).to_a + (0x80..0xfc).to_a).each do |c2|
    code = format "%02X%02X", c1, c2
    s = [c1, c2].pack('C*').b
    puts [code, s].join("\t")
  end
end

これを sjis.rb として保存して、次のように実行すると sjis.txt が作られます。

% ruby sjis.rb > /tmp/sjis.txt

MySQLでsjisとcp932それぞれのテーブルを作り、sjis.txtをロードします。

mysql> create table sjis (code varchar(255) ascii primary key, c varchar(255)) charset sjis;
mysql> create table cp932 (code varchar(255) ascii primary key, c varchar(255)) charset cp932;
mysql> load data local infile '/tmp/sjis.txt' into table sjis charset sjis;
mysql> load data local infile '/tmp/sjis.txt' into table cp932 charset cp932;

sjis と cp932 をそれぞれ utf8mb4 に変換して、違うコードになった文字を出力します。

mysql> select sjis.code, sjis.c sjis, cp932.c cp932 from sjis join cp932 using (code) where hex(convert(sjis.c using utf8mb4))!=hex(convert(cp932.c using utf8mb4));
+------+------+-------+
| code | sjis | cp932 |
+------+------+-------+
| 815F | \    | \    |
| 8160 | 〜   | ~    |
| 8161 | ‖    | ∥     |
| 817C | −    | -    |
| 8191 | ¢    | ¢    |
| 8192 | £    | £    |
| 81CA | ¬    | ¬    |
| 8740 | ?    | ①     |
| 8741 | ?    | ②     |
| 8742 | ?    | ③     |
| 8743 | ?    | ④     |
...

出力結果は長いので https://gist.github.com/tmtm/c0d325ae74e43740f7ffa3d0dccb0bb4 に置いておきました。

815F,8160等のようにsjisとcp932の両方に文字があるものは、sjisとcp932の相互に変換できない文字です。utf8mb4には対応する文字が存在しているので、utf8mb4に変換することはできています。

?となってるのは、utf8mb4に変換することができなかった文字です。コードに文字が割り当てられていないことを意味しています。

MySQL Innovation Day Tokyo で MySQL 8 の文字コードについて話した

MySQL Innovation Day Tokyo に参加して10分ほど喋ってきました。

ひさびさに巨大サキラちゃん登場。本人曰く5年間ほど倉庫に隠れてたそうです。

f:id:tmtms:20180523175014j:plain

昼ごはんは今半のすき焼弁当でした。豪華!

f:id:tmtms:20180523121113j:plain f:id:tmtms:20180523121135j:plain

会場の様子。百数十人で満員でした f:id:tmtms:20180523152737j:plain

以下わたしの発表内容。スライドはこちら https://tmtm.github.io/mysql-innovation-tokyo/


MySQL恒例「RCとはいったい…」案件

utf8の指定でwarningが出るようになった

mysql> set names utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 3719): 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

今は utf8 は utf8mb3 の別名だけどどうやら将来 utf8mb4 の別名になるらしく、そのためのwarningらしいんだけど、RC版では出力されなかったのがGA版で出力されるようになったのはいいんだろうか。

なお、utf8mb3 を使えという割には、参照時には utf8 と表示されるのがイマイチ。show create table の結果をそのまま使うと warning が出るという…。

mysql> create table xx (id char(10)) charset utf8mb3;
Query OK, 0 rows affected (0.08 sec)

mysql> show create table xx\G
*************************** 1. row ***************************
       Table: xx
Create Table: CREATE TABLE `xx` (
  `id` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> CREATE TABLE `xx` (
    ->   `id` char(10) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.19 sec)

Warning (Code 3719): 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

mysqlshのプロンプトが派手になった

プロンプトの「MySQL」の文字色がロゴの色と同じ。凝りすぎでは…。

f:id:tmtms:20180524223644p:plain

パラメータ比較

MySQLのバージョン間のパラメータを比較できるページを作りました。よろしければどうぞ。

https://tmtm.github.io/mysql-params/?vers=5.7.22,8.0.11&diff=true

RC版とGA版の比較はこちら。RCとはいったい…。

https://tmtm.github.io/mysql-params/?vers=8.0.4,8.0.11&diff=true

文字コードまわり

5.7ではデフォルトのcharsetはlatin1(ISO8859-1)だったのが8.0ではutf8mb4(Unicode)になった。

デフォルトのままで日本語や絵文字が使えるようになったのはよい。

utf8mb4 charset のcollation(照合規則)のうち日本語環境で使われそうなものは次の通り:

  • utf8mb4_bin
  • utf8mb4_general_ci (5.7 デフォルト)
  • utf8mb4_unicode_ci
  • utf8mb4_unicode_520_ci
  • utf8mb4_0900_ai_ci (8.0 デフォルト)
  • utf8mb4_0900_as_ci
  • utf8mb4_0900_as_cs
  • utf8mb4_ja_0900_as_cs
  • utf8mb4_ja_0900_as_cs_ks

強調されてる下の5つが8.0で増えたもの。

utf8mb4の次の文字の意味:

bin コードのまま
general MySQL独自規則
unicode Unicode 4.0.0
unicode_0520 Unicode 5.2.0
0900 Unicode 9.0.0
ja_0900 Unicode 9.0.0 + 日本語

その次の文字の意味:

ai Accent Insensitive アクセント違いは同じ文字
as Accent Sensitive アクセント違いは異なる文字
ci Case Insensitive 大文字小文字は同じ文字
cs Case Sensitive 大文字小文字は異なる文字
ks Kana Sensitive カタカナひらがなは異なる文字

デフォルトCollationの違い

  • 5.7: utf8mb4_general_ci

    • Aa, ,
    • 🍣🍺
  • 8.0: utf8mb4_0900_ai_ci

    • Aa, ,
    • 🍣🍺

Charset=utf8mb4 を指定しただけでは 5.7 と 8.0 で動きが異なるので注意

新しいCollationたち

utf8mb4_0900_ai_ci

  • アクセントの違いを区別しない

    • ははぱぱばば
  • 大文字小文字を区別しない

    • びょういんびよういん
    • いろはイロハ
    • 株式会社

ci/cs でカタカナや組文字も影響うけるらしい。

utf8mb4_0900_as_ci

  • アクセントの違いを区別する

    • ははぱぱばば
  • 大文字小文字を区別しない

    • びょういんびよういん
    • いろはイロハ
    • 株式会社

utf8mb4_0900_as_cs

  • アクセントの違いを区別する

    • ははぱぱばば
  • 大文字小文字を区別する

    • びょういんびよういん
    • いろはイロハ
    • 株式会社

(時間の都合で発表では以下は省略した)

全部の文字が区別されるんなら utf8mb4_bin と何が違う? 合成文字の扱いが異なる。

「が」と「か」+「゛」の比較

utf8mb4_0900_as_cs では一致:

mysql> set @a='が' collate utf8mb4_0900_as_cs;
mysql> set @b=concat('か', _utf8mb4 0xE38299 collate utf8mb4_0900_as_cs);
mysql> select @a,@b,hex(@a),hex(@b),@a=@b;
+------+--------+---------+--------------+-------+
| @a   | @b     | hex(@a) | hex(@b)      | @a=@b |
+------+--------+---------+--------------+-------+
| が   | が   | E3818C  | E3818BE38299 |     1 |
+------+--------+---------+--------------+-------+

utf8mb4_bin では不一致:

mysql> set @a='が' collate utf8mb4_bin;
mysql> set @b=concat('か', _utf8mb4 0xE38299 collate utf8mb4_bin);
mysql> select @a,@b,hex(@a),hex(@b),@a=@b;
+------+--------+---------+--------------+-------+
| @a   | @b     | hex(@a) | hex(@b)      | @a=@b |
+------+--------+---------+--------------+-------+
| が   | が   | E3818C  | E3818BE38299 |     0 |
+------+--------+---------+--------------+-------+

異体字セレクタも同様。

utf8mb4_ja_0900_as_cs

  • アクセントの違いを区別する

    • ははぱぱばば
  • 大文字小文字を区別する

    • びょういんびよういん
    • 株式会社
  • カタカナひらがなを区別しない

    • いろはイロハ

utf8mb4_ja_0900_as_cs_ks

  • アクセントの違いを区別する

    • ははぱぱばば
  • 大文字小文字を区別する

    • びょういんびよういん
    • 株式会社
  • カタカナひらがなを区別する

    • いろはイロハ

全部の文字が区別されるなら utf8mb4_0900_as_cs と同じ??

utf8mb4_0900 と utf8mb4_ja_0900 の違い

ja_0900 ではJISコード順にソートされる。

mysql> select hex(s),s from ja order by s;
+--------+------+
| hex(s) | s    |
+--------+------+
| E4BA9C | 亜   |
| E4BC8A | 伊   |
| E99BA8 | 雨   |
| E6A084 | 栄   |
| E5A5A5 | 奥   |
+--------+------+

長音記号は前の文字の母音に依存する。

mysql> select s from ja order by s;
+--------+
| s      |
+--------+
| あー   |
| ああ   |
| あい   |
| いあ   |
| いー   |
| いい   |
| うあ   |
| うい   |
| うー   |
+--------+

凝りすぎ!? でもこれはMySQL固有の特殊な実装じゃなくて、ちゃんとUnicodeの規約通り。 日本語辞書順ともいうらしい。

でも自分はつかわないかなー。

utf8mb4_0900_as_csが一番無難かも。 合成文字等の Unicode の特殊な事情を考慮しなくていいのであれば utf8mb4_binでもいいかも。たぶんその方が速いんじゃないかと。

昔からMySQLを使ってる人は大文字小文字を区別しない方がうれしいかもしれない。その場合は utf8mb4*ci の中から選択する感じで。

ちゃんと各collationの特徴を知って適切なものを使いましょう。


サキラちゃんと。

f:id:tmtms:20180523175456j:plain

MySQLパラメータ比較

Vue.js の勉強をしようと思ってMySQLのバージョン間のパラメータを比較できるページを作ってみました。

MySQL Parameters

やってることは、あらかじめ mysqld --no-defaults -v --help の出力からパラメータの名前と値を JSON にしておいて、それを表示しているだけです。

環境によってデフォルト値が動的に変わるようなパラメータもあるのですべて信用できるわけではないですけど、まあ参考くらいにはなるかなと。

自分が 5.7 と 8.0 の比較を見てみて気づいたのは、

  • basedir のデフォルト値が mysqld の実行パスから動的に生成されるようになった。

  • date-format, datetime-format なんてパラメータが今まであったの知らなかった。

  • へー query-cache まわりはパラメータ自体なくなったのか…。

とかとか。

本当はこわいMySQLプロトコル

11/28 に Haskell で MySQL の Xプロトコルを実装したという話が聴ける Club MySQL というイベントがあったので参加してきました。

clubmysql.connpass.com

MySQLのプロトコルの話ということで、平日の夜とは言え東京で参加者9人(発表者含む)というマニアックな集まりでした。

自分も1年前に Ruby で MySQL Xプロトコルを実装していたのですが、このツイートを最後に中断していたのでした。

今回話を聞いて、無理に謎条件式文字列をパースするんじゃなくて、処理系で書きやすいように書けばいいんだという方式に目からウロコでした。

もしかしたらまたRubyでの実装を再開するかもしれません。

で、このイベントで私も喋ってきました。本当は前座で話す予定だったんですが、到着が遅れてしまったので合間の休憩時間で話すことになりました。スイマセン。

www.slideshare.net

以下がしゃべった内容です。


MySQLの参照系のクエリのパケットはこんな感じです。クエリの応答としてフィールド数と、フィールド情報と、レコードデータが返されます。

f:id:tmtms:20171130224159p:plain

更新系のクエリはもっと簡単です。クエリの応答として更新の結果情報が返されます。

f:id:tmtms:20171130224203p:plain

クエリをパースするのはサーバーなので、クライアントはクエリが参照か更新かは知りません。

クライアントはクエリの応答のパケットでクエリが参照だったか更新だったかを知ります。

ところで、LOAD DATA LOCAL INFILE という特殊なクエリがあって、これはローカルにあるファイルをテーブルにロードします。

が、クエリをパースするのはサーバーなので、クエリ中のファイル名もサーバーから返してもらいます。

f:id:tmtms:20171130224205p:plain

ということは、クライアントから送ったクエリで指定したファイル名とは異なるファイル名がサーバーから指定されても、クライアントはそれを信じてサーバーから指定されたファイルのデータを送ってしまいます。

当日は、クライアントとサーバーの間のプロキシとして動くようなテキトーに作ったプログラムを使って、LOAD DATA LOCAL INFILE のときだけファイル名を差し替えるデモをしました。

f:id:tmtms:20171130224208p:plain

この場合は、テーブルに登録されたデータを見れば自分が意図したものではないことがわかります。

クエリをパースするのはサーバー側ということは、INSERT, UPDATE 等の更新系のクエリを発行した時に LOAD DATA LOCAL INFILE だと偽ってクライアントにファイルを送らせることもできるわけです。

f:id:tmtms:20171130224211p:plain

この場合はテーブルに登録されるのはクライアントが発行したクエリ通りのデータなので、プロキシによってファイルの内容が取られてしまったことはわかりません。

こわい!

対策としては、

  • 基本的に信頼できない MySQL サーバーには繋がない。

  • サーバーは信頼できるんだけど、途中のネットワークが信頼できない場合はSSL接続を使う。

  • クライアントライブラリで LOAD DATA LOCAL INFILE を使わないというフラグを設定する。

mysql コマンドの場合は --local-infile=false を指定できます。この場合はサーバーからファイルを送るように指示されてもクライアントライブラリが不正なパケットとして振る舞います。