0%

MySQL Query_rewrite(查询重写)

MySQL从5.7.6开始支持Rewrite plugin插件,可以将符合条件的SQL语句进行改写。

安装插件

1
2
3
4
5
6
7
8
9
[root@t-luhx03-v-szzb share]# mysql -uroot -p < /usr/local/mysql/share/install_rewriter.sql
Enter password:

root@test 09:45: SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | ON |
+------------------+-------+

脚本会创建一个query_rewrite的数据库,其中包含一张rewrite_rules的表,用于定义并管理重写的规则

测试改写效果

插入规则

1
2
3
4
5
6
7
8
9
10
11
12
13
root@(none) 09:55:  insert into query_rewrite.rewrite_rules(pattern,replacement,pattern_database) values ("select * from tab1 where id=?","select * from tab1 where name='lu'","test");
Query OK, 1 row affected (0.01 sec)

root@(none) 10:02: select * from query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
id: 1
pattern: select * from tab1 where id=?
pattern_database: test
replacement: select * from tab1 where name='lu'
enabled: YES
message: NULL
pattern_digest: NULL
normalized_pattern: NULL

重新加载

1
root@(none) 10:02:  call query_rewrite.flush_rewrite_rules();

验证效果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
root@test 10:04:  select * from tab1;
+----+------------+------+
| id | name | comm |
+----+------------+------+
| 1 | lu | NULL |
| 2 | heng | NULL |
| 3 | xing | NULL |
| 4 | luhengxing | NULL |
+----+------------+------+
4 rows in set (0.00 sec)

root@test 10:04: select * from tab1 where id=2;
+----+------+------+
| id | name | comm |
+----+------+------+
| 1 | lu | NULL |
+----+------+------+
1 row in set, 1 warning (0.00 sec)

root@test 10:04: show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1105
Message: Query 'select * from tab1 where id=2' rewritten to 'select * from tab1 where name='lu'' by a query rewrite plugin
1 row in set (0.01 sec)

禁用规则

1
2
3
4
5
6
root@test 10:08:  UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

root@test 10:11: CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected (0.01 sec)

参考链接

  1. Using the Rewriter Query Rewrite Plugin