平武建站

怎么使用Mysql的Query-rewrite

本篇内容介绍了“怎么使用MySQL的Query-rewrite”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

创新互联长期为近千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为三明企业提供专业的网站建设、成都做网站，三明网站改版等技术服务。拥有10余年丰富建站经验和众多成功案例,为您定制开发。

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”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！


新闻名称：怎么使用Mysql的Query-rewrite
文章来源：http://pwwzsj.com/article/ippcep.html

其他资讯