regexp binary 匹配 可以区分大小写;
regexp 匹配 不会区分大小写;
时间 - 状态 - 响应时间(ms) - 来源->执行数据库,mysql_connect_id
------------------------------
sql
2020/07/02 17:23:52 - OK - 15307.5 - 10.10.10.11:20236->10.10.10.10:3306,mysql_connect_id=102783
------------------------------
select endpoint, id from endpoint WHERE lower(endpoint) regexp 'a8-apple-iphone-db*;
模糊匹配的时候,有时候确实需要忽略大小写,但是直接使用mysql的函数作用于字段(该字段有索引),则会导致索引失效,从而导致全表扫描的慢查询,影响很大。这时候应该使用 regexp binary 来进行模糊匹配。
###########################
测试案例如下:
##########################
igoodful@a8-apple-iphone-db01(oneservice) > show create table appleG
*************************** 1. row ***************************
Table: apple
Create Table: CREATE TABLE `apple` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '应用id自增主键',
`address` varchar(64) NOT NULL DEFAULT '' COMMENT '真实应用APP的ID',
`name` varchar(64) NOT NULL DEFAULT '' COMMENT '应用名称',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='应用信息表'
1 row in set (0.00 sec)
Fri Jul 3 10:42:23 2020
igoodful@a8-apple-iphone-db01(oneservice) > select * from apple;
+----+----------------------+------+
| id | address | name |
+----+----------------------+------+
| 1 | c3-dba-cloud-cc01.bj | 1 |
| 2 | C3-DBA-CLOUD-CC01.bj | 2 |
+----+----------------------+------+
2 rows in set (0.00 sec)
Fri Jul 3 10:42:44 2020
igoodful@a8-apple-iphone-db01(oneservice) > select * from apple where address regexp 'c3*';
+----+----------------------+------+
| id | address | name |
+----+----------------------+------+
| 1 | c3-dba-cloud-cc01.bj | 1 |
| 2 | C3-DBA-CLOUD-CC01.bj | 2 |
+----+----------------------+------+
2 rows in set (0.00 sec)
Fri Jul 3 10:42:52 2020
igoodful@a8-apple-iphone-db01(oneservice) > select * from apple where address regexp binary 'c3*';
+----+----------------------+------+
| id | address | name |
+----+----------------------+------+
| 1 | c3-dba-cloud-cc01.bj | 1 |
+----+----------------------+------+
1 row in set (0.01 sec)
Fri Jul 3 10:42:57 2020
igoodful@a8-apple-iphone-db01(oneservice) > select * from apple where address regexp 'c3-dba-cloud*';
+----+----------------------+------+
| id | address | name |
+----+----------------------+------+
| 1 | c3-dba-cloud-cc01.bj | 1 |
| 2 | C3-DBA-CLOUD-CC01.bj | 2 |
+----+----------------------+------+
2 rows in set (0.00 sec)
Fri Jul 3 10:44:20 2020
igoodful@a8-apple-iphone-db01(oneservice) >