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の書き方のサンプルとして面白かった。 このへん。