如何使用Mysql的Query-rewrite
|
本篇内容介绍了“怎么使用Mysql的Query-rewrite”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 1 启用或禁用 /usr/local/mysql/bin/mysql -uroot -p`cat /etc/sqlpass ` -P3306 -h227.0.0.1 < $BASEDIR/share/install_rewriter.sql /usr/local/mysql/bin/mysql -uroot -p`cat /etc/sqlpass ` -P3306 -h227.0.0.1 < $BASEDIR/share/uninstall_rewriter.sql mysql> show variables like 'rewrite_%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | rewriter_enabled | ON | | rewriter_verbose | 1 | +------------------+-------+ mysql> use query_rewrite; mysql> show tables; +-------------------------+ | Tables_in_query_rewrite | +-------------------------+ | rewrite_rules | +-------------------------+ 1 row in set (0.00 sec) [mysqld] rewriter_enabled=ON 2 简单练习 mysql> insert into rewrite_rules(pattern,pattern_database,replacement) values('select ?','test','select ?+1'); mysql> call flush_rewrite_rules(); mysql> select 1; +-----+ | 1+1 | +-----+ | 2 | +-----+ 3 去除类型转换 mysql> show create table tb\G; *************************** 1. row *************************** Table: tb Create Table: CREATE TABLE `tb` ( `id` int(10) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> select * from tb; +------+----------+ | id | name | +------+----------+ | 1 | 12345678 | | 1 | 12345671 | | 1 | 12345672 | | 1 | 12345673 | | 1 | 12345674 | | 1 | 12345675 | | 1 | 12345676 | | 1 | 12345677 | | 1 | 12345679 | +------+----------+ mysql> explain select * from tb where name=12345677; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb | NULL | ALL | idx_name | NULL | NULL | NULL | 9 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ mysql> insert into rewrite_rules(pattern,pattern_database,replacement) values("select * from tb where name=?","test","select * from tb where name = cast(? as char character set utf8)"); Query OK, 1 row affected (0.08 sec) 思考：为什么不直接写？替换为‘？’ ，因为？如果被单引号包起来就失效了！ mysql> call flush_rewrite_rules(); Query OK, 0 rows affected (0.09 sec) mysql> select * from tb where name=12345678; mysql> show warnings; +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1105 | Query 'select * from tb where name=12345678' rewritten to 'select * from tb where name = cast(12345678 as char character set utf8)' by a query rewrite plugin | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 4 改字段名后兼容报错 mysql> select * from tb where uid=1; ERROR 1054 (42S22): Unknown column 'uid' in 'where clause' mysql> insert into rewrite_rules(pattern,pattern_database,replacement) values("select * from tb where uid=?","test","select * from tb where id=?"); Query OK, 1 row affected (0.06 sec) mysql> call flush_rewrite_rules(); Query OK, 0 rows affected (0.06 sec) “怎么使用Mysql的Query-rewrite”的内容就介绍到这里了，感谢大家的阅读。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
