1 启用或禁用
2 简单练习
3 去除类型转换
4 改字段名后兼容报错
网盘:http://pan.baidu.com/s/1qY2crTy
- /usr/local/mysql/bin/mysql -uroot -p`cat /etc/sqlpass ` -P3306 -h127.0.0.1 < $BASEDIR/share/install_rewriter.sql
- /usr/local/mysql/bin/mysql -uroot -p`cat /etc/sqlpass ` -P3306 -h127.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 简单练习
- 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 去除类型转换
- 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)
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 改字段名后兼容报错
-
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)
网盘:http://pan.baidu.com/s/1qY2crTy
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29096438/viewspace-2129879/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29096438/viewspace-2129879/