本文主要是介绍Query Rewrite Plugin 安装与使用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
适用范围
设置SQL语句匹配规则，REWRITE PLUGIN 自动改写匹配的语句
MySQL8.0.12 前版本，只指定select
MySQL8.0.12+版本支持： SELECT, INSERT, REPLACE, UPDATE , DELETE语句
问题概述
问题原因
解决方案
1、 Rewriter Query Rewrite Plugin 安装
需要在安装软件包中的share目录找到脚本 install_rewriter.sql ，执行时会自动安装 rewriter.so
mysql@s2:/home/db/mysql/product/share$ ls -l rewri
-rw-r–r-- 1 mysql mysql 2216 Dec 17 2022 install_rewriter.sql
-rw-r–r-- 1 mysql mysql 1248 Dec 17 2022 uninstall_rewriter.sql
直接执行脚本install_rewriter.sql
root@localhost 16:07:59 [(none)]>source install_rewriter.sql
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.34 sec)
Query OK, 0 rows affected (0.69 sec)
Query OK, 0 rows affected (0.02 sec)
查看rewrite插件，已经加载
root@localhost 16:08:05 [(none)]>show plugins ;
…
| Rewriter | ACTIVE | AUDIT | rewriter.so | GPL |
±---------------------------------±---------±-------------------±---------------------±--------+
50 rows in set (0.00 sec)
查看rewriter_enabled状态，自动为on
root@localhost 16:08:08 [(none)]>show variables like ‘%rewrite%’ ;
±------------------------------------------------------±------+
| Variable_name | Value |
±------------------------------------------------------±------+
| rewriter_enabled | ON |
| rewriter_enabled_for_threads_without_privilege_checks | ON |
| rewriter_verbose | 1 |
±------------------------------------------------------±------+
3 rows in set (0.00 sec)
同时自动创建query_rewrite库，及规则表rewrite_rules
root@localhost 16:08:16 [(none)]>show databases ;
±-------------------+
| Database |
±-------------------+
| query_rewrite |
±-------------------+
root@localhost 16:32:29 [query_rewrite]>show tables ;
±------------------------+
| Tables_in_query_rewrite |
±------------------------+
| rewrite_rules |
±------------------------+
添加改下规则
select ? 替换 为 select ? + 1
root@localhost 16:09:10 [(none)]>select 1 ;
±–+
| 1 |
±–+
| 1 |
±–+
1 row in set (0.00 sec)
root@localhost 16:09:23 [(none)]>INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
-> VALUES(‘SELECT ?’, ‘SELECT ? + 1’);
Query OK, 1 row affected (0.02 sec)
root@localhost 16:09:27 [(none)]>select 1 ;
±–+
| 1 |
±–+
| 1 |
±–+
1 row in set (0.00 sec)
发现不没有生效，需要把改写规则加载到共享内存中，执行flush_rewrite_rules();
root@localhost 16:09:31 [(none)]>SELECT * FROM query_rewrite.rewrite_rules \G ;
*************************** 1. row ***************************
id: 1
pattern: SELECT ?
pattern_database: NULL
replacement: SELECT ? + 1
enabled: YES
message: NULL
pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)
ERROR:
No query specified
root@localhost 16:10:01 [(none)]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.03 sec)
root@localhost 16:10:12 [(none)]>select 1 ;
±------+
| 1 + 1 |
±------+
| 2 |
±------+
1 row in set, 1 warning (0.00 sec)
再次执行select 1 ,发现结果变成2 ，说明改写规则生效。
root@localhost 16:32:34 [query_rewrite]>select * from rewrite_rules ;
±—±---------±-----------------±-------------±--------±--------±-----------------------------------------------------------------±-------------------+
| id | pattern | pattern_database | replacement | enabled | message | pattern_digest | normalized_pattern |
±—±---------±-----------------±-------------±--------±--------±-----------------------------------------------------------------±-------------------+
| 1 | SELECT ? | NULL | SELECT ? + 1 | YES | NULL | d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae | select ? |
±—±---------±-----------------±-------------±--------±--------±-----------------------------------------------------------------±-------------------+
规则生效后 normalized_pattern和normalized_pattern 被更新。
当有错误发生时，错误信息会写入字段message。统计刷新 Rewriter_reload_error 为on
root@localhost 16:43:27 [query_rewrite]>show status like ‘rewriter_reload_error’ ;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| Rewriter_reload_error | OFF |
±----------------------±------+
匹配语句的长度受max_digest_length 限制
这篇关于Query Rewrite Plugin 安装与使用的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
