regexp

 

 

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 apple\G
*************************** 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) >