Query Rewrite pluginの一番ベーシックな使い方はこちら。
The Query Rewrite Plugins | MySQL Server Blog
MySQL 5.7.5-labsに$basedir/lib/plugin/install_rewriter_plugin.sqlを食わせてやれば取り敢えず有効になる。MySQL 5.7.5-m15にはこのプラグインは存在しないので注意。
$ mysql57 < install_rewriter_plugin.sql
mysql57> SELECT @@version;
+------------------------+
| @@version |
+------------------------+
| 5.7.5-labs-preview-log |
+------------------------+
1 row in set (0.00 sec)
mysql57> show plugins;
+----------------------------+--------+--------------------------+-------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+--------+--------------------------+-------------+---------+
..
| Rewriter | ACTIVE | QUERY REWRITE POST PARSE | rewriter.so | GPL |
+----------------------------+--------+--------------------------+-------------+---------+
43 rows in set (0.00 sec)
mysql57> show databases like 'query_rewrite';
+--------------------------+
| Database (query_rewrite) |
+--------------------------+
| query_rewrite |
+--------------------------+
1 row in set (0.00 sec)
mysql57> show tables;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules |
+-------------------------+
1 row in set (0.00 sec)
このquery_rewrite.rewrite_rulesにクエリー書き換えのルールを記述していく感じ。
記述したら、query_rewrite.flush_rewrite_rulesを呼んで更新してやる(FLUSH PRIVILEGESみたいな感じだ)
mysql57> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES ('INSERT INTO t1 VALUES (?)', 'INSERT INTO t2 VALUES (? + 1)');
Query OK, 1 row affected (0.01 sec)
mysql57> SELECT * FROM query_rewrite.rewrite_rules;
+---------------------------+------------------+-------------------------------+---------+---------+
| pattern | pattern_database | replacement | enabled | message |
+---------------------------+------------------+-------------------------------+---------+---------+
| INSERT INTO t1 VALUES (?) | NULL | INSERT INTO t2 VALUES (? + 1) | Y | NULL |
+---------------------------+------------------+-------------------------------+---------+---------+
1 row in set (0.00 sec)
mysql57> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.01 sec)
mysql57> SELECT * FROM query_rewrite.rewrite_rules;
+---------------------------+------------------+-------------------------------+---------+--------------------------------------------------+
| pattern | pattern_database | replacement | enabled | message |
+---------------------------+------------------+-------------------------------+---------+--------------------------------------------------+
| INSERT INTO t1 VALUES (?) | NULL | INSERT INTO t2 VALUES (? + 1) | N | Parse error in pattern: >>No database selected<< |
+---------------------------+------------------+-------------------------------+---------+--------------------------------------------------+
1 row in set (0.00 sec)
あれ、なんか怒られた。 "No database selected" なので、pattern_databaseがNULLなのがいけないのか…ってテーブル名を指定しない時はNULLでも大丈夫ぽい流れが冒頭のブログには書いてあった(´･ω･｀)
mysql57> UPDATE query_rewrite.rewrite_rules SET pattern_database= 'd1', enabled= 'Y';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql57> SELECT * FROM query_rewrite.rewrite_rules;
+---------------------------+------------------+-------------------------------+---------+--------------------------------------------------+
| pattern | pattern_database | replacement | enabled | message |
+---------------------------+------------------+-------------------------------+---------+--------------------------------------------------+
| INSERT INTO t1 VALUES (?) | d1 | INSERT INTO t2 VALUES (? + 1) | Y | Parse error in pattern: >>No database selected<< |
+---------------------------+------------------+-------------------------------+---------+--------------------------------------------------+
1 row in set (0.00 sec)
enabledは自分で'Y'に戻さないと、次にflush_rewrite_rulesを呼んでもそもそもFLUSHしようとしてくれなくなる(はまった)
mysql57> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)
mysql57> SELECT * FROM query_rewrite.rewrite_rules;
+---------------------------+------------------+-------------------------------+---------+---------------------------------+
| pattern | pattern_database | replacement | enabled | message |
+---------------------------+------------------+-------------------------------+---------+---------------------------------+
| INSERT INTO t1 VALUES (?) | d1 | INSERT INTO t2 VALUES (? + 1) | N | Pattern not a select statement. |
+---------------------------+------------------+-------------------------------+---------+---------------------------------+
1 row in set (0.00 sec)
(´･ω･｀) SELECTステートメントしか食ってくれないようなことを言っている。かなしい。
mysql57> DELETE FROM query_rewrite.rewrite_rules;
Query OK, 1 row affected (0.00 sec)
mysql57> INSERT INTO query_rewrite.rewrite_rules (pattern, pattern_database, replacement) VALUES ('SELECT * FROM t1', 'd1', 'SELECT * FROM t1 LIMIT 1');
Query OK, 1 row affected (0.00 sec)
mysql57> SELECT * FROM query_rewrite.rewrite_rules;
+------------------+------------------+--------------------------+---------+---------+
| pattern | pattern_database | replacement | enabled | message |
+------------------+------------------+--------------------------+---------+---------+
| SELECT * FROM t1 | d1 | SELECT * FROM t1 LIMIT 1 | Y | NULL |
+------------------+------------------+--------------------------+---------+---------+
1 row in set (0.00 sec)
mysql57> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.01 sec)
mysql57> SELECT * FROM query_rewrite.rewrite_rules;
+------------------+------------------+--------------------------+---------+---------+
| pattern | pattern_database | replacement | enabled | message |
+------------------+------------------+--------------------------+---------+---------+
| SELECT * FROM t1 | d1 | SELECT * FROM t1 LIMIT 1 | Y | NULL |
+------------------+------------------+--------------------------+---------+---------+
1 row in set (0.00 sec)
という訳で、d1.t1のテーブルを単純SELECTした時に"LIMIT 1"を押し込むようなやつを作って（まともに読み込まれたときは、messageはNULLのままで見た目変化がないっぽい）
mysql57> SELECT * FROM t1;
+------+
| num |
+------+
| 1 |
+------+
1 row in set, 1 warning (0.00 sec)
mysql57> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------+
| Note | 1105 | Query 'SELECT * FROM t1' rewritten to 'SELECT * FROM t1 LIMIT 1' by plugin: Rewriter. |
+-------+------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
おお、書き換えてくれたぽい。 クエリーキャッシュぽく、クエリーの文字列で判定しているだけぽいけど
mysql57> select * FROM t1;
+------+
| num |
+------+
| 1 |
+------+
1 row in set, 1 warning (0.00 sec)
mysql57> SELECT /* dummy */ * FROM t1;
+------+
| num |
+------+
| 1 |
+------+
1 row in set, 1 warning (0.00 sec)
mysql57> SELECT *
-> FROM t1;
+------+
| num |
+------+
| 1 |
+------+
1 row in set, 1 warning (0.00 sec)
mysql57> SELECT sql_cache * FROM t1;
+------+
| num |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
大文字小文字の吸収と、コメント, スペースや改行文字くらいは無視してくれるっぽい。SQL構文として有効なsql_cacheとか入るとさすがにダメ。簡単なプレースホルダー(?文字)は使えるけど、テーブル名の書き換えとかはできない。
SELECT以外の何かをゴニョゴニョしたかったり、テーブル名の書き換えをやりたかったら、Rewrite Pluginを自作することになるっぽい。
Write Yourself a Query Rewrite Plugin: Part 1 | MySQL Server Blog
取り敢えずrewrite_example(クエリーを全て小文字に書き換える)というのが付属しているので、
mysql57> INSTALL PLUGIN rewrite_example SONAME 'rewrite_example.so';
Query OK, 0 rows affected (0.01 sec)
mysql57> use d1
Database changed, 1 warning
mysql57> INSERT INTO t1 VALUES (100);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql57> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Query 'INSERT INTO t1 VALUES (100)' rewritten to 'insert into t1 values (100)' by plugin: rewrite_example. |
| Note | 1105 | Query 'SHOW WARNINGS' rewritten to 'show warnings' by plugin: rewrite_example. |
+-------+------+------------------------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
うむ、INSERTもSHOWも軒並み全部イケる。こっちはC++で文字列を書き換えてるだけだから結構融通が利く感じ。
Enjoy :)
