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 :)
