Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to use Mysql's Query-rewrite". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1 enable or disable

/ 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 simple exercises

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 removal of type conversion

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)

Think about it: why not just write? Replace with'?' Because? If it is wrapped in single quotation marks, it will be invalid!

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 compatible error reporting after changing field name

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)

That's all for "how to use Mysql's Query-rewrite". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.