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

創(chuàng)新互聯(lián)長(zhǎng)期為近千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為三明企業(yè)提供專業(yè)的網(wǎng)站建設(shè)、成都做網(wǎng)站，三明網(wǎng)站改版等技術(shù)服務(wù)。擁有10余年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(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 簡(jiǎn)單練習(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)
思考：為什么不直接寫(xiě)？替換為‘？’ ，因?yàn)椋咳绻粏我?hào)包起來(lái)就失效了！

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 改字段名后兼容報(bào)錯(cuò)

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)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

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

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

廣告

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