怎么使用Mysql的Query-rewrite

本篇內(nèi)容介紹了“怎么使用MySQL的Query-rewrite”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

創(chuàng)新互聯(lián)長期為近千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為三明企業(yè)提供專業(yè)的網(wǎng)站建設(shè)、成都做網(wǎng)站，三明網(wǎng)站改版等技術(shù)服務(wù)。擁有10余年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。

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 簡單練習(xí)

  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 去除類型轉(zhuǎn)換

  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”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！

分享標(biāo)題：怎么使用Mysql的Query-rewrite
URL地址：http://muchs.cn/article48/ippcep.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、App開發(fā)、品牌網(wǎng)站設(shè)計、微信小程序、網(wǎng)站導(dǎo)航、網(wǎng)頁設(shè)計公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

外貿(mào)網(wǎng)站建設(shè)