複数のテーブルのON UPDATE current_timestampなカラムの値を揃える方法を考える

タイトルで何を言ってるのか我ながら良くわからない。。

mysql56> SELECT * FROM t1 JOIN t2 USING(num);
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
| num | val   | created             | updated             | val    | created             | updated             |
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
|   1 | one   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | eins   | 2016-03-01 15:38:44 | 2016-03-01 16:40:29 |
|   2 | two   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | zwei   | 2016-03-01 15:38:44 | 2016-03-01 16:40:33 |
|   3 | three | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | drei   | 2016-03-01 15:38:44 | 2016-03-01 16:40:36 |
|   4 | four  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | vier   | 2016-03-01 15:38:44 | 2016-03-01 16:40:59 |
|   5 | five  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | funf   | 2016-03-01 15:38:44 | 2016-03-01 16:41:05 |
|   6 | six   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | sechs  | 2016-03-01 15:38:44 | 2016-03-01 16:41:11 |
|   7 | seven | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | sieben | 2016-03-01 15:38:44 | 2016-03-01 16:41:16 |
|   8 | eight | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | acht   | 2016-03-01 15:38:44 | 2016-03-01 16:41:27 |
|   9 | nine  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | neun   | 2016-03-01 15:38:44 | 2016-03-01 16:41:34 |
|  10 | ten   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | zehn   | 2016-03-01 15:38:44 | 2016-03-01 16:41:46 |
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
10 rows in set (0.00 sec)

t1.updated, t2.updatedはそれぞれDATETIME ON UPDATE CURRENT_TIMESTAMPなカラム。
やりたいことは、

mysql56> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:43:35 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:43:40 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.01 sec)

この時点でt1.updatedとt2.updatedを同じ時刻にすること。



1. リテラル渡す

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated', updated= '2016-03-01 16:44:05' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated', updated= '2016-03-01 16:44:05' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:44:05 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:44:05 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

はじめっからUPDATE ON current_timestampなんてつけないでほしかった。


2. 1ステートメントで更新する

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 JOIN t2 USING(num) SET t1.val= 'updated', t2.val= 'updated' WHERE t1.num = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:47:24 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:47:24 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

CURRENT_TIMESTAMP()はNOW()のシノニムなので、そのまま使うと「ステートメント開始時点の現在時刻」を返す。
という訳で1ステートメントなら開始時刻は1つに定まる。

どう考えても綺麗にJOINできないテーブルとかあるしINSERTやDELETEが混じったトランザクションで詰むので却下。


3. timestamp変数使う

さらに、SET TIMESTAMP ステートメントによって、NOW() で返された値は影響を受けますが、SYSDATE() で返された値は影響を受けません。つまり、バイナリログのタイムスタンプ設定は、SYSDATE() の呼び出しに影響しないことを意味します。タイムスタンプをゼロ以外の値に設定すると、後続の NOW() が起動されるたびに、その値が返されます。タイムスタンプをゼロに設定すると、この効果が取り消され、再度 NOW() が現在の日付と時間を返すようになります。

http://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_now

というわけでこうじゃ。

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET timestamp= @@timestamp;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:55:46 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:55:46 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql56> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET timestamp= 0;
Query OK, 0 rows affected (0.00 sec)

コミットした後は（前でもいいけど）timestamp変数を0に戻しておかないと、そのコネクションのNOW()がいつまでもSET timestampした時点の時刻を返すようになる。


…はじめっからUPDATE ON current_timestampなんてつけないでほしかった。

ORDER BY RAND()はしたくないけどそれなりにランダムな結果セットを返すいくつかの方法を考える

ORDER BY RAND()といえば、「結果セットをランダムにソートし、LIMITと組み合わせることでランダムに指定件数をピックアップしたかのように見える」黒魔術。

( ´-｀).oO(そういえばこれも ORDER BY FIELD と一緒で構文だと思っていた人がいたな。。


これもまあRAND()関数を使ってるだけなので、select_listに放り込めば何やってるかわかりやすい。

mysql56> SELECT num, val, RAND() AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 94164 | e8d2546088e6be7ff164964c7a07bdb3 | 0.000012977980089353379 |
|  4354 | 46d0671dd4117ea366031f87f3aa0093 |  0.00001926440747386255 |
| 11573 | 2d6304a207cd9469f776e651e81ed7f8 | 0.000023321248612665803 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.09 sec)

余談だけど、RAND()関数は引数にシード値を取れる（引数を取らない場合はテキトーにシード値が設定される）ので、ランダムっぽいけど再現可能な並び順を作り出すこともできる。

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 |
| 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 |
| 34076 | 6dcfff2b73388f6307994658463a9341 |  0.00004350300882337895 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.08 sec)

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 |
| 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 |
| 34076 | 6dcfff2b73388f6307994658463a9341 |  0.00004350300882337895 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

なおこの場合、「そのシードで何番目に生成された乱数か」がキモになるので、WHERE句評価後 *何番目に* その行がフェッチされるかでrand_valの値は変わる。WHERE句を評価した後に、取り出したレコードの順番にRAND()を計算してソートするからだ。

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 WHERE num > 30000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78089 | 67ebeaa4f6391a89d2b629860fff2c9d | 0.000006978400058092922 |
| 64076 | 6a3f8e5443504151a7306f2a13fae303 |  0.00004350300882337895 |
| 30140 | 8befb4efe8ce6cdf0e1a84974d452a9f |  0.00004702340815870409 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 WHERE num < 80000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 |
| 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 |
| 34076 | 6dcfff2b73388f6307994658463a9341 |  0.00004350300882337895 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.10 sec)

だから本当に再現可能なランダムっぽい何かをしたい場合、（あるなら）サロゲートキーを渡してゴニョる方がいい気がする。これなら常に「そのシードで1番目の乱数」を使うことになるので、「結果セットの先頭から何番目にあるか」が変わってもRAND()関数の戻す値は変わらない（はず） 並び順をズラす場合、サロゲートキーに何かしら足してやったりかけてやったりすればいい。

mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 |
| 51034 | 861c3ad6224d443664f925552d2255a1 |  0.00001504737885207625 |
| 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 WHERE num < 90000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 |
| 51034 | 861c3ad6224d443664f925552d2255a1 |  0.00001504737885207625 |
| 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 WHERE num > 10000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 |
| 51034 | 861c3ad6224d443664f925552d2255a1 |  0.00001504737885207625 |
| 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.08 sec)

mysql56> SELECT num, val, RAND(num * 2) AS rand_val FROM t1 WHERE num > 10000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 25517 | 362f278d9150aaf7894f586b5682de06 |  0.00001504737885207625 |
| 43266 | faeddbfcef4331221e71ed4186e0c65b | 0.000025210902118320486 |
| 61015 | 67f68835939b9fa291a4e417312b4ec1 |  0.00003538932654577245 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

さて本題。
ORDER BY RAND()はWHERE句でフィルターされた後の行全てに対してRAND()関数を適用し、その結果でソートするので、WHERE句でフィルターした後の行が多ければ多いほど重くなるし、WHERE句で十分フィルターが聞いてもUsing temporaryに落ちる。

昔からよく言われることではあるが、アプリケーション側で乱数を作ってWHERE句に指定するのはよくあるやり方だ。その場合、ORDER BY FIELD() はやっぱり役に立つかもしれない。
アプリケーション側と言いながらSQLでやってるのは気にしない。

mysql56> SELECT MAX(num) FROM t1 INTO @max_num;
Query OK, 1 row affected (0.01 sec)

mysql56> SET @r1 := CAST(@max_num * RAND() + 1 AS signed), @r2 := CAST(@max_num * RAND() + 1 AS signed), @r3 := CAST(@max_num * RAND() + 1 AS signed);
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num IN (@r1, @r2, @r3) ORDER BY FIELD(num, @r1, @r2, @r3);
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 68966 | 3ccf50b9d73ea11f758bd030e5ac593f |
| 51533 | 2f6a0826437abc6688b22dfd89d783c0 |
| 50767 | 8deda01d4df57c1d3cb6b1e4a0391fbe |
+-------+----------------------------------+
3 rows in set (0.01 sec)

実際問題、auto_increment（の値とは限らないけれど）の値が一貫して抜けがないことは期待できない（innodb_autoinc_lock_mode= 0だってDELETEすれば抜ける訳で、= 1ならトランザクションをロールバックしたりDuplicate Key Errorを起こすだけでautoincは進む）ので、こんな風に書くしかなかろうか。

mysql56> SELECT MAX(num) FROM t1 INTO @max_num;
Query OK, 1 row affected (0.00 sec)

mysql56> SET @r := @max_num * RAND() + 1;
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1;
+------+----------------------------------+
| num  | val                              |
+------+----------------------------------+
| 8564 | 621eb0b827c09dd1804e87bd74f79383 |
+------+----------------------------------+
1 row in set (0.00 sec)

mysql56> SET @r := @max_num * RAND() + 1;
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 67575 | fd85263468f2e1315a31116cf7b12a00 |
+-------+----------------------------------+
1 row in set (0.00 sec)

mysql56> SET @r := @max_num * RAND() + 1;
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 12181 | b75867b590e9a1a38ceaea9f8cb9cf45 |
+-------+----------------------------------+
1 row in set (0.00 sec)

INで仕留めた場合でもLIMIT 1で仕留めた場合でも、SQLのレイヤーでは重複（たとえばnum = 1が2回引っかかる）や空振り（その条件にマッチするレコードが1件もない）を検出しないので、それが嫌ならアプリケーション側で重複判定をする必要がある。それに、必要な件数が揃うまで複数回クエリーを投げる必要があるので、それが嫌な感じはもちろんする（とはいえ、速度的には大概の場合お釣りがくる）。あとはサロゲートキーが極端に偏ってるとこれは死ぬかも知れない。1, 2, 3, .., 1000の次が10万だったりすると、max_num * rand()を超えるレコードはかなりの高確率でnum= 10万だ。


もう一つ、WHERE RAND()という手法もある。`tail -f .. | perl -nle 'if (rand() < 0.01) {print}'` って話を聞いて思い付いただけだけれども。

mysql56> SELECT num, val FROM t1 WHERE rand() < 1/100 ORDER BY num DESC LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 99966 | 44d3377fd88bc32cd46acd38d716abd3 |
| 99899 | a9cfebcdb4e20ed975e82b7fd877693f |
| 99790 | 7aab6ba599620439ed28d3cee272c3af |
+-------+----------------------------------+
3 rows in set (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE rand() < 1/100 ORDER BY num DESC LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 99954 | 758f0bc5e561543556e9f4e0d23335cc |
| 99663 | 10ef53cc7b761466d851d05dda6b82e3 |
| 99650 | 02aecc1719dc308a7efac6064861cf93 |
+-------+----------------------------------+
3 rows in set (0.00 sec)

DESCにしてるのは趣味なのでASCでもいいかも知れない（けど、古いものからやるよりは新しいものから引いた方がバッファプール効率がいい） もうちょっと分散させたい場合はRAND()と比較する定数の値を小さくすれば良くて、1/100でLIMIT 3なら、期待値として直近300行をhandler_read_prevして、その中から3件が選ばれる。飽くまで1クエリーで1つのテーブルから取ってくるので、重複はない。が、確率で取ってくることになるのであまりタイトなサンプリングをすると空振り（LIMITで指定した件数が集まらない）はあり得る。あと、この形はサロゲートキーに依存せずインデックスさえあれば好きなインデックスでORDER BYできる。

mysql56> SHOW SESSION STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 312   |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

ほぼ期待値通り。 OORDEER BY RAND()とWHEREで3回引くケースはこんな感じだった。

mysql56> SHOW SESSION STATUS LIKE 'handler_read%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 1      |
| Handler_read_key      | 1      |
| Handler_read_last     | 0      |
| Handler_read_next     | 0      |
| Handler_read_prev     | 0      |
| Handler_read_rnd      | 3      |
| Handler_read_rnd_next | 200002 |
+-----------------------+--------+
7 rows in set (0.00 sec)

mysql56> SHOW SESSION STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 4     |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

直近のPRIMARY KEYはバッファプールに載ってる（INSERTされたままバッファプールに残ってる）率が高い気がするので、偏りが許せるなら実用的だと思う（し、本当に完全ランダムでなきゃいけないならRAND()関数とか使っちゃいけない気がする） これだけ件数が絞れてれば、FROM句に閉じ込めてORDER BY RAND()しても許せなくはないくらいのパフォーマンスのはず。パッと見、numでORDER BYされてるとは思えないような感じに仕上がる（かもしれない）

mysql56> SELECT * FROM (SELECT num, val FROM t1 WHERE rand() < 1/3000 ORDER BY num DESC LIMIT 3) AS dummy ORDER BY RAND();
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 96640 | 3076ef0ad4d1e7c6dec15fb4541b6997 |
| 95735 | 3b2016665210c18767dfe611b76ffbea |
| 97248 | 32d32773f19f2f421ebc2d41da4ef5a9 |
+-------+----------------------------------+
3 rows in set (0.00 sec)

mysql56> show profile cpu;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000046 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000005 | 0.000000 |   0.000000 |
| init                           | 0.000008 | 0.000000 |   0.000000 |
| checking query cache for query | 0.000348 | 0.000000 |   0.000000 |
| checking permissions           | 0.000016 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000195 | 0.000000 |   0.000000 |
| init                           | 0.000019 | 0.000000 |   0.000000 |
| System lock                    | 0.000018 | 0.000000 |   0.000000 |
| optimizing                     | 0.000004 | 0.000000 |   0.000000 |
| optimizing                     | 0.000019 | 0.000000 |   0.000000 |
| statistics                     | 0.000035 | 0.000000 |   0.000000 |
| preparing                      | 0.000019 | 0.000000 |   0.000000 |
| Sorting result                 | 0.000003 | 0.000000 |   0.000000 |
| statistics                     | 0.000003 | 0.000000 |   0.000000 |
| preparing                      | 0.000006 | 0.000000 |   0.000000 |
| Creating tmp table             | 0.000022 | 0.000000 |   0.000000 |
| Sorting result                 | 0.000004 | 0.000000 |   0.000000 |
| executing                      | 0.000014 | 0.000000 |   0.000000 |
| Sending data                   | 0.000012 | 0.000000 |   0.000000 |
| executing                      | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.001889 | 0.001999 |   0.000000 |
| Creating sort index            | 0.000045 | 0.000000 |   0.000000 |
| end                            | 0.000002 | 0.000000 |   0.000000 |
| removing tmp table             | 0.000025 | 0.000000 |   0.000000 |
| end                            | 0.000009 | 0.000000 |   0.000000 |
| query end                      | 0.000007 | 0.000000 |   0.000000 |
| closing tables                 | 0.000002 | 0.000000 |   0.000000 |
| removing tmp table             | 0.000004 | 0.000000 |   0.000000 |
| closing tables                 | 0.000010 | 0.000000 |   0.000000 |
| freeing items                  | 0.000176 | 0.001000 |   0.000000 |
| cleaning up                    | 0.000041 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+
31 rows in set, 1 warning (0.00 sec)

サロゲートキーへの依存度はmax_num * RAND()より小さいけど、なんかどこかに落とし穴がありそうだよなぁ。。

( ´-｀).oO(query_cache_type= DEMANDなんだけど、これ切れば300usくらい速くなるな。。


最後に、ランダムピックしたものをそもそもキャッシュする方法も考え付いた。そもそも元の行数が少なければ、ORDER BY RAND()でも戦えるんじゃないか、って話。
キャッシュは定期的に更新（というかスワップ）してやればいい。

mysql56> CREATE TABLE rand_cache AS SELECT num, val FROM t1 ORDER BY RAND() LIMIT 10000;
Query OK, 10000 rows affected (0.17 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql56> SELECT * FROM rand_cache ORDER BY RAND() LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
|  2533 | 4de754248c196c85ee4fbdcee89179bd |
| 78915 | d3272a819b09ced96c69e22f183cc88e |
| 16351 | dcb8e02b8527b08dbd8acb146bccc612 |
+-------+----------------------------------+
3 rows in set (0.00 sec)

mysql56> CREATE TABLE tmp_rand_cache AS SELECT num, val FROM t1 ORDER BY RAND() LIMIT 10000;
Query OK, 10000 rows affected (0.14 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql56> RENAME TABLE rand_cache TO old_rand_cache, tmp_rand_cache TO rand_cache;
Query OK, 0 rows affected (0.01 sec)

mysql56> DROP TABLE old_rand_cache;
Query OK, 0 rows affected (0.01 sec)

mysql56> SELECT * FROM rand_cache ORDER BY RAND() LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 98937 | 73a3320fa46a5e4fad268056af61cd42 |
| 54927 | a11d83c11ed8c95a32b3628a762cf41f |
| 38444 | d3e8129138f2c76dc6e4048281160fe0 |
+-------+----------------------------------+
3 rows in set (0.01 sec)

何も考えないORDER BY RAND()はORDER BY RAND()で、「空振りが無い（WHERE句でフィルターした結果がLIMIT未満でなければ）」、「重複がない」、「サロゲートキーに依存しない」というメリットもあるのだなぁと思った。そりゃあ使いたがる人多くてもわかるは。


【2020/05/13 16:01】

奥さん の ブコメ で(4年前に)教えてもらっていたもうちょっとスマートそうなやり方。

WHERE RAND() LIMIT するくらいなら、SELECT primary_key ORDER BY RAND() LIMIT 5した結果をセルフジョインするほうがいいんじゃ / top-N sortは最近のmysqlでは… https://t.co/VTcw6HQkgs

— Kazuho Oku (@kazuho) March 1, 2016

4年が経って手元の環境が既に5.6ではないけれど、吊るしのORDER BY RAND()に比べて10msくらい節約できるみたい（これは平均行サイズが大きくなれば大きくなるほど差も大きくなるはず）

ただしMySQL 8.0ではテンポラリーテーブルの方式が変わっている(TempTableストレージエンジン)ので5.7とそれ以前だと傾向は変わるかも知れないし変わらないかも知れない。


mysql80 88> SELECT num, val FROM t1 ORDER BY RAND() LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 88253 | a885315b8b17d78a1121b875bc0bc198 |
| 67289 | fde520a1553f284433c0140de0bab59a |
| 85943 | f324bc6faedca902976e58abd5d8a9d1 |
+-------+----------------------------------+
3 rows in set (0.06 sec)

mysql80 122> SELECT t1.num, t1.val FROM t1 JOIN (SELECT num FROM t1 ORDER BY RAND() LIMIT 3) AS _dummy ON t1.num = _dummy.num;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 63347 | 65b553cedfe36b1a168b7600ba146140 |
| 22114 | fb70c5b7a51b1a13deec0dcf7026459d |
|  2169 | bd0cc810b580b35884bd9df37c0e8b0f |
+-------+----------------------------------+
3 rows in set (0.06 sec)


mysql80 123> EXPLAIN SELECT num, val FROM t1 ORDER BY RAND() LIMIT 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99750 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql80 123> EXPLAIN SELECT t1.num, t1.val FROM t1 JOIN (SELECT num FROM t1 ORDER BY RAND() LIMIT 3) AS _dummy ON t1.num = _dummy.num;

+----+---------------------+------------+------------+--------+---------------+------+---------+------------+-------+----------+----------------------------------------------+
| id | select_type         | table      | partitions | type   | possible_keys | key  | key_len | ref        | rows  | filtered | Extra                                        |
+----+---------------------+------------+------------+--------+---------------+------+---------+------------+-------+----------+----------------------------------------------+
|  1 | PRIMARY             | <derived2> | NULL       | ALL    | NULL          | NULL | NULL    | NULL       |     3 |   100.00 | NULL                                         |
|  1 | PRIMARY             | t1         | NULL       | eq_ref | num           | num  | 8       | _dummy.num |     1 |   100.00 | NULL                                         |
|  2 | UNCACHEABLE DERIVED | t1         | NULL       | index  | NULL          | num  | 8       | NULL       | 99750 |   100.00 | Using index; Using temporary; Using filesort |
+----+---------------------+------------+------------+--------+---------------+------+---------+------------+-------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

$ mysqlslap -S /usr/mysql/8.0.20/data/mysql.sock -uroot  --query="SELECT num, val FROM d1.t1 ORDER BY RAND() LIMIT 3" --number-of-queries=1000
Benchmark
        Average number of seconds to run all queries: 52.038 seconds
        Minimum number of seconds to run all queries: 52.038 seconds
        Maximum number of seconds to run all queries: 52.038 seconds
        Number of clients running queries: 1
        Average number of queries per client: 1000

$ mysqlslap -S /usr/mysql/8.0.20/data/mysql.sock -uroot  --query="SELECT t1.num, t1.val FROM d1.t1 JOIN (SELECT num FROM d1.t1 ORDER BY RAND() LIMIT 3) AS _dummy ON t1.num = _dummy.num" --number-of-queries=1000
Benchmark
        Average number of seconds to run all queries: 42.276 seconds
        Minimum number of seconds to run all queries: 42.276 seconds
        Maximum number of seconds to run all queries: 42.276 seconds
        Number of clients running queries: 1
        Average number of queries per client: 1000


4年越しですがありがとうございます！

WHERE .. IN (..)のリストの順番でソートするORDER BY FIELDの仕組み

MySQLには`WHERE col IN (..) ORDER BY FILED(col, ..)`という書き方でINに並べた順番にソートしなおせるという知見がある。


こんなテーブルがあって、

mysql56> SELECT * FROM t1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
|   4 | four  |
|   5 | five  |
|   6 | six   |
|   7 | seven |
|   8 | eight |
|   9 | nine  |
|  10 | ten   |
+-----+-------+
10 rows in set (0.00 sec)

INにテキトーな値を並べてやっても、

mysql56> SELECT * FROM t1 WHERE num IN (7, 5, 3);
+-----+-------+
| num | val   |
+-----+-------+
|   3 | three |
|   5 | five  |
|   7 | seven |
+-----+-------+
3 rows in set (0.00 sec)

IN演算子に渡した順番には返ってこない、これがフツーの動作。

これを7 => 5 => 3の順番で返してほしいとか 業の深い 俺の知らないところで大変な何かを抱えている人もいたりするので、そんな時に使うのがORDER BY FIELD()

mysql56> SELECT * FROM t1 WHERE num in (7, 5, 3) ORDER BY FIELD(num, 7, 5, 3);
+-----+-------+
| num | val   |
+-----+-------+
|   7 | seven |
|   5 | five  |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)

初めて見た時はﾌｧｯ!? ってなったけど、クエリーをこう書き換えると、たぶんやってることが伝わる。

mysql56> SELECT *, FIELD(num, 7, 5, 3) AS sort_rank FROM t1 WHERE num in (7, 5, 3) ORDER BY sort_rank;
+-----+-------+-----------+
| num | val   | sort_rank |
+-----+-------+-----------+
|   7 | seven |         1 |
|   5 | five  |         2 |
|   3 | three |         3 |
+-----+-------+-----------+
3 rows in set (0.00 sec)

ORDER BY FIELDはORDER BY句のバリエーションじゃなくて、FILED関数の結果でORDER BYしている。

FIELD関数のドキュメントはこちら。第1引数に検索したい値、第2引数以降に検索元となるリストを与える感じ。
これが、numの値が(7, 5, 3)の何番目にあるかを整数で返すので、そこでソートできる。

http://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_field

で、FIELD関数は関数で、ORDER BY FIELDという構文じゃない。
つまり、やってることは「関数の演算結果でソート」だから、ORDER BY狙いのキーは動かない。

mysql56> explain SELECT * FROM t1 WHERE num in (7, 5, 3) ORDER BY FIELD(num, 7, 5, 3);
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | range | num           | num  | 8       | NULL |    3 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

基本的にWHERE .. IN ..でPRIMARY KEYをリテラルリストで数行～数十行取ってくる時くらいしか安全な使い道はなさそうな気がする（けど、そういうケースでは割と便利だったりする）
ご利用は計画的に。

MySQL 5.7.8から導入されたVersion Tokenとやらの動作

ドキュメントはこちら。
MySQL :: MySQL 5.7 Reference Manual :: 5.1.8.4 Version Tokens

サーバーの持ってるトークンとクライアントが持ってるトークンを比較して、一致しなければエラーにしてくれる仕組み。


取り敢えず何はなくともインストール。version_token.soはバンドルされてるので、ドキュメントの通りにINSTALL PLUGINとCREATE FUNCTIONを貼り付ければOK。

mysql> INSTALL PLUGIN version_tokens SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_set RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_show RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_edit RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_delete RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_lock_shared RETURNS INT SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_lock_exclusive RETURNS INT SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_unlock RETURNS INT SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'version_token_%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| version_tokens_session        |       |
| version_tokens_session_number | 0     |
+-------------------------------+-------+
2 rows in set (0.00 sec)

サーバー側のトークンを設定するにはUDFを使うので、剥き出しで使うにはSELECTステートメントを使う。サーバー側のトークンを設定したり読み出したりするにはSuper権限が必要。

mysql> SELECT version_tokens_set('MySQL=dolphin');
+-------------------------------------+
| version_tokens_set('MySQL=dolphin') |
+-------------------------------------+
| 1 version tokens set.               |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_show();
+-----------------------+
| version_tokens_show() |
+-----------------------+
| MySQL=dolphin;        |
+-----------------------+
1 row in set (0.00 sec)

クライアント側のトークンはSETステートメントで指定する…せめてConnector/Cにはmysql_optionsとか無いの？

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| yoku0825@%     |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT @@version_tokens_session;
+--------------------------+
| @@version_tokens_session |
+--------------------------+
| NULL                     |
+--------------------------+
1 row in set (0.00 sec)

mysql> SET version_tokens_session= 'MySQL=sealion!?';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_USER();
ERROR 3136 (42000): Version token mismatch for MySQL. Correct value dolphin

mysql> SELECT @@version_tokens_session;
ERROR 3136 (42000): Version token mismatch for MySQL. Correct value dolphin

*クライアント側のバージョントークンが指定されていない場合はバージョントークンの比較はされない*
クライアント側でバージョントークンが指定されていて、かつ、サーバーのバージョントークンと違う場合はError: 3136が返される。
権限とかそういうレベルではなく、ステートメントそのものが拒否される状態。

mysql> SET version_tokens_session= 'MySQL=dolphin';
ERROR 3136 (42000): Version token mismatch for MySQL. Correct value dolphin

バージョントークンを設定し直そうとしてもバージョントークンのチェックに引っかかってSETステートメントが転ける。おとなしく繋ぎ直す他にない。

mysql> SET version_tokens_session= 'MariaDB=sealion';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_USER();
ERROR 3137 (42000): Version token MariaDB not found.

存在しないトークンをセットした場合もステートメントが転ける。


最初にチラ見した時に「これ使えば追加要素認証（ユーザー名、パスワードの他にトークンを使う）もできる？」とか思ったけど、クライアント側のトークンをセットしない場合は比較されないし、期待されている値をエラーメッセージに埋め込んでしまうのでそういう用途には使えない。

mysql> SELECT version_tokens_set('MariaDB=sealion');
+---------------------------------------+
| version_tokens_set('MariaDB=sealion') |
+---------------------------------------+
| 1 version tokens set.                 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_show();
+-----------------------+
| version_tokens_show() |
+-----------------------+
| MariaDB=sealion;      |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_edit('MySQL=dolphin');
+--------------------------------------+
| version_tokens_edit('MySQL=dolphin') |
+--------------------------------------+
| 1 version tokens updated.            |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_show();
+--------------------------------+
| version_tokens_show()          |
+--------------------------------+
| MariaDB=sealion;MySQL=dolphin; |
+--------------------------------+
1 row in set (0.00 sec)

version_tokens_setだとトークンを追加はできなくて上書かれる。セミコロン区切りで'MySQL=dolphin;MariaDB=sealion'と渡してやるか、version_tokens_editで指定する（editといいつつINSERT .. ON DUPLICATE UPDATEと同じような動き）

mysql> SET version_tokens_session= 'MariaDB=sealion';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| yoku0825@%     |
+----------------+
1 row in set (0.00 sec)

mysql> SET version_tokens_session= 'MariaDB=sealion;sushi=beer';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_USER();
ERROR 3137 (42000): Version token sushi not found.

サーバー側に複数のトークンがセットされている場合、クライアントが申告したトークンが全てサーバーに含まれていればステートメントは成功する。要らないものを付けると失敗する。

ドキュメント の例を見る限り、read_onlyを指定できないマルチマスターな環境下でそれでも特定の粒度でマスターを分ける、みたいな感じに使うことを想定されているんだろうか。Group Replicationですかそうですかわかりますん。

ぱっと思い付いたのは、WEBサービスのAPIをバージョンごとに公開している場合なんかにAPIのコードにSET SESSION version_tokens_session= 'v1=true'みたいなのを入れておいて、v2が作られたらサーバー側のトークンを'v1=true;v2=true'にして、v1が消し去られるタイミングでversion_tokens_edit('v1=false')ってやってやると、API v1から来るクエリーだけを転けさせられるようになる、みたいな感じだろうか。

ただ、サーバー側のトークンは揮発性（MySQLの再起動で消える）ので、ちょっと微妙な感じはする。どう使えるだろう。


あとこのプラグインの面白いところは、これAudit PluginなのでAudit Pluginの書き方のサンプルとして面白かった。 このへん。

AnemometerというMySQLスローログ専用の可視化ツールの弱点と、その克服スクリプト

一部の人にしか知られていない Anemometer というMySQLのスローログ専用の可視化ツールがある。

box/Anemometer: Box SQL Slow Query Monitor


中身はpt-query-digestの テーブル出力機能 （サマライズした結果をMySQLのテーブルに保存する機能があるのだ）に依存していて、スローログの可視化というよりはpt-query-digestの可視化というのがたぶん正しい。

だけどこのやり方にはちょっと弱点があって、pt-query-digestはクエリーをサマライズする時に発生時間の情報を 「そのダイジェストが最初に現れた時間(ts_min)」と「そのダイジェストが最後に現れた時間(tx_max)」 という値にサマライズしてしまう。よく見る出力結果の中では"Time range"として表示されている。

# Query 14: 0.00 QPS, 0.00x concurrency, ID 0xAF17C328E1020443 at byte 10391443
# This item is included in the report because it matches --outliers.
# Scores: V/M = 13.70
# Time range: 2015-11-02 10:55:08 to 2015-12-18 15:20:14
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      13
# Exec time      0    163s      2s     51s     13s     29s     13s      9s
# Lock time      0     5ms   271us   445us   354us   424us    65us   301us
# Rows sent      9 544.87k   3.91k 104.73k  41.91k  97.04k  33.62k  46.68k
# Rows examine   0  12.00M 238.87k   3.04M 945.00k   1.46M 808.81k 725.01k
# Query size     0  24.29k   1.87k   1.87k   1.87k   1.86k    0.00   1.86k

コマンドラインからの出力結果を眺める分には便利なんだけど、グラフ化しようという時にこれはつらい。Anemometerはts_min(最初にスローログに現れた時刻）をグラフにプロットするので、今既にあるスローログをpt-query-digestで食わせてAnemometerで表示させると、チェックサムごとにts_minの時刻に1回だけスパイクしたようなグラフになってしまう。

これは、上手くない(´・ω・`)

pt-query-digest側でこの集約を無効化できればいいんだけど、オプションとしては存在せず、中に手を入れるにしても結構奥まったところにあっていじくりたくない。

定期的にpt-query-digestに--since, --until オプションを使って食わせてもいいんだけど、秒単位とは言わずとも分単位くらいでは見たい…となると、1日ぶんのログファイルを食わせるのに見るかどうかもわからないのに1440回pt-query-digestを起動しなければならない。それも嫌だ。


という訳で乱雑に書いたPerlスクリプト。Anemometer用にスローログを食ってくれるのでanemoeater（どや
my_script/anemoeater.pl at master · yoku0825/my_script
yoku0825/anemoeater

$ ./anemoeater.pl --docker path_to_slowlog

シンプル。--dockerを使わなくとも、Anemometerが既に構成されている環境があれば、--hostとか--userで指定してやればいい。--dockerに任せると構成済みの yoku0825/anemometer を起動して、スクリプトで1分ごとに丸めて送る。

パイプでpt-query-digestを呼ぶので結構遅い（11Mのスローログを食わせるのに、pt-query-digestで直接食わせると10秒ちょい、anemoeaterだと24パラにしても1分ちょい）

これで無事、過去のスローログをまとめて食わせても

ちゃんとジグザグしてくれるようになった。

2015/12/01 00:00～2016/01/01 23:59のログを8並列でぶち込むのはこんな感じで書きます。

$ ./anemoeater.pl --since 201512010000 --until 201601012359 --parallel 8 --docker path_to_slowlog
real    0m17.471s
user    1m47.847s
sys     0m11.117s

これでDockerでAnemometerの起動まで終わってるんだから、都度都度パースして突っ込めそう。しめしめ。


【2016/02/03 17:43】
yoku0825/anemoeater として別リポジトリーにしました。

MySQL Bug #79977 "utf8mb4_unicode_520_ci don't make sense for Japanese FTS" で言いたいこと

英語で書いてたら自分でもよくわからなくなってきたので。

MySQL Bugs: #79977: utf8mb4_unicode_520_ci don't make sense for Japanese FTS


* 本質的には MySQL Bugs: #76553: Sushi-Beer issue of MySQL with utf8mb4 の関連。
  * Unicode実装が不完全だから起こっている問題。


* 丸め問題

  * motherを意味する書き方に、はは（ひらがな）とハハ（カタカナ）とﾊﾊ（半角カタカナ）があって、
  * これらは（文字の形は違うけど）同じ音で同じ意味だから、大概の場合同じ文字として扱ってくれると嬉しい。


* 病院美容院問題

  * hospitalを意味する"びょういん"とheir salonを意味する"びよういん"
  * "ょ"と"よ"は文字の形は似てるけど、ほとんどの場合これは違う意味を持つから区別しないといけない。


* ハハパパ問題

  * Bug #76553でも説明されてるけど、ハハ（濁音なし）はmother、ババ（濁点）は"grand mother"、パパ（半濁点）は"daddy"を意味する。
  * これらは区別されないと困る。超困る。


* 最後に半角全角問題

  * "MySQL"と"ＭｙＳＱＬ"は同じに扱いたいよね。

* Hiragana-Katakanaと半角全角を区別しちゃうのは、不便だけど我慢できる。
* でも拗音促音と濁点半濁点を区別 *されない* のは（特に全文検索の側面で）機能要件を満たせない。LIKE演算子でもそうだけど。
  * 「びょういん」って検索して美容院が結果に含まれたら変でしょ？
* あと、これ、UNIQUE制約をぶっ壊す可能性があるのよね。
  * UNIQUE KEY(家族関係)って制約があって、俺には母親がいるからハハって入れて、俺に父親がいるからパパって入れようとすると、エラるよね？ （いわゆる kamipoさんのハハパパ問題 ）


表にまとめるとこんな感じ。ホントはutf8mb4_japanese_ciが欲しいところだよねって。

|                    | utf8mb4_bin | utf8mb4_general_ci | utf8mb4_unicode_ci | utf8mb4_unicode_520_ci|
|--------------------|-------------|--------------------|--------------------|-----------------------|
| Hiragana-Katakana  | cs (unkind) | cs (unkind)        | ci (good)          | ci(good)              |
| Youon              | cs (good)   | cs (good)          | ci (critical)      | ci(critical)          |
| Dakuten-Handakuten | cs (good)   | cs (good)          | ci (critical)      | ci(critical)          |
| Wide-Narrow        | cs (unkind) | cs (unkind)        | ci (good)          | ci(good)              |
| Sushi-Beer         | cs          | ci                 | ci                 | cs                    |

合わせて読みたい
* ActiveRecordでデフォルトの照合順序を変更する - Qiita
* utf8_unicode_ci に対する日本の開発者の見解 - かみぽわーる
* MySQL と寿司ビール問題 - かみぽわーる
* MySQL と Unicode Collation Algorithm (UCA) - かみぽわーる

MySQL 5.7.11でdefault_password_lifetimeのデフォルトが0になるらしい！

日々の覚書: MySQL 5.7.4で導入されたdefault_password_lifetimeがじわじわくる の公開以来大反響をいただいていた （ブクマ がすごいことになっていて、思わず ばぐれぽ にブクマのリンクを貼り付けたほど）default_password_lifetime が。

5.7.11でついにデフォルト0になる！！！ やった！！！ やったよ！！！

ドキュメントはもう"default: 0 (>= 5.7.11)"の記載になってる。


これで、

MySQL 5.7にアップデートしてから

360日後にやってくる

＿人人人人人人＿
＞　突然の死　＜
￣Y^Y^Y^Y^Y^Y^￣

はなくなりました！ （∩´∀｀）∩ﾜｰｲ


いいぞ○racle！
"Affects me"してくれたみなさんもありがとうございました。


( ´-｀).oO（さて、また自分が罠にならないように、あちこちのもの直さないとなー


【2015/01/14 13:59】
MySQL Bugs: #77277: default_password_lifetime should be set 0 as implicit default value

の最後の方で、Morgan Tocker（Oracle MySQL Community Manager改めOracle MySQL Product Manager）が他にも色々（default_password_filetime > 0だったらワーニング出そうぜ とか SYSスキーマでいつexpireされるか見るビュー作ろうぜ とか）出してくれてる。

もーがんのTwitterのプロフィール、Community managerからProduct managerに変わってるから、公になってるのよね。

— yoku0825 (@yoku0825) 2016, 1月 14

@morgo++