怎么使用Mysql的Query-rewrite

本篇內容介紹了“怎么使用Mysql的Query-rewrite”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

1 啟用或禁用

1. /usr/local/mysql/bin/mysql -uroot -p`cat /etc/sqlpass ` -P3306 -h227.0.0.1 < $BASEDIR/share/install_rewriter.sql

2. /usr/local/mysql/bin/mysql -uroot -p`cat /etc/sqlpass ` -P3306 -h227.0.0.1 < $BASEDIR/share/uninstall_rewriter.sql 

3.  

4. mysql> show variables like 'rewrite_%';
   +------------------+-------+
   | Variable_name    | Value |
   +------------------+-------+
   | rewriter_enabled | ON    |
   | rewriter_verbose | 1     |
   +------------------+-------+

5. 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 簡單練習

1. mysql> insert into rewrite_rules(pattern,pattern_database,replacement) values('select ?','test','select ?+1');
   mysql> call flush_rewrite_rules();

2. mysql> select 1;
   +-----+
   | 1+1 |
   +-----+
   |   2 |
   +-----+
   

3 去除類型轉換

1. 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 改字段名后兼容報錯

1. 
   
   mysql> select * from tb where uid=1;
   ERROR 1054 (42S22): Unknown column 'uid' in 'where clause'
   

2. 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”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！