MySQL - 模糊搜索 LIKE BINARY 和 LIKE

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: MySQL - 模糊搜索 LIKE BINARY 和 LIKE

MySQL 模糊搜索

MySQL 模糊搜索: LIKE BINARY 是区分大小写的; LIKE 是否区分大小写,取决于表的 COLLATE,如果 COLLATE=utf8_bin 即二进制形式就是区分大小写的,否则是不区分的。

在 ORM 中过滤的时候会写 xxx.objects.filter(name__contains=‘sxn’) 和 xxx.objects.filter(name__icontains=‘sxn’) 转成的 SQL分别是: LIKE BINARY 和 LIKE


(drf_poc_env) root@robert-Ubuntu:/media/sf_WorkSpace/drf_poc# python manage.py shell
Python 2.7.12 (default, Nov 12 2018, 14:36:49) 
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> 
>>> from app01.models import UserInfo
>>> 
>>> UserInfo.objects.filter(username__contains='SXN')  ----用的是  LIKE BINARY
DEBUG 2020-04-01 11:01:07,836 4942 140546619713280 utils 90 Line (0.001) 
SELECT `app01_userinfo`.`id`, `app01_userinfo`.`user_type`, `app01_userinfo`.`username`, `app01_userinfo`.`password` 
FROM `app01_userinfo` 
WHERE `app01_userinfo`.`username` LIKE BINARY '%SXN%' LIMIT 21; args=(u'%SXN%',)
<QuerySet []>
>>> 
>>> 
>>> 
>>> UserInfo.objects.filter(username__icontains='SXN')  ----用的是  LIKE 
DEBUG 2020-04-01 11:01:25,360 4942 140546619713280 utils 90 Line (0.001) 
SELECT `app01_userinfo`.`id`, `app01_userinfo`.`user_type`, `app01_userinfo`.`username`, `app01_userinfo`.`password` 
FROM `app01_userinfo` 
WHERE `app01_userinfo`.`username` LIKE '%SXN%' LIMIT 21; args=(u'%SXN%',)
<QuerySet [<UserInfo: UserInfo object>]>
>>> 
>>> 
mysql> show create table app01_userinfo \G     -----注意表的 COLLATE 用是默认值,并不是COLLATE=utf8_bin 
*************************** 1. row ***************************
       Table: app01_userinfo
Create Table: CREATE TABLE `app01_userinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_type` int(11) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql> 
mysql> 
mysql> select * from app01_userinfo where username like binary '%sxn%';
+----+-----------+----------+----------+
| id | user_type | username | password |
+----+-----------+----------+----------+
|  2 |         2 | sxn      | 655      |
+----+-----------+----------+----------+
1 row in set (0.00 sec)
mysql> 
mysql> select * from app01_userinfo where username like binary '%SXN%';
Empty set (0.00 sec)
mysql> 
mysql> 
mysql> 
mysql> select * from app01_userinfo where username like '%sxn%';
+----+-----------+----------+----------+
| id | user_type | username | password |
+----+-----------+----------+----------+
|  2 |         2 | sxn      | 655      |
+----+-----------+----------+----------+
1 row in set (0.00 sec)
mysql> 
mysql> select * from app01_userinfo where username like '%SXN%';
+----+-----------+----------+----------+
| id | user_type | username | password |
+----+-----------+----------+----------+
|  2 |         2 | sxn      | 655      |
+----+-----------+----------+----------+
1 row in set (0.00 sec)
mysql> 
mysql> show create table db_work_order \G      --------- 注意表的  COLLATE=utf8_bin 
*************************** 1. row ***************************
       Table: db_work_order
Create Table: CREATE TABLE `db_work_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_num` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '工单编号',
  `order_type` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '工单类型',
  ......
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `update_time` datetime(6) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uqi_ordernum` (`order_num`),
  KEY `created_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1578 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据库类别的工单表'
1 row in set (0.05 sec)
mysql> 
mysql>   ------可以看到是区分大小写的------
mysql> select id, order_num  from db_work_order where order_num LIKE '%DB_change%' limit 3;
Empty set (0.01 sec)
mysql> 
mysql> select id, order_num  from db_work_order where order_num LIKE '%db_change%' limit 3;
+-----+-----------------------+
| id  | order_num             |
+-----+-----------------------+
| 392 | db_change202003030106 |
| 335 | db_change202003040029 |
|  67 | db_change202003040078 |
+-----+-----------------------+
3 rows in set (0.00 sec)
mysql> 
是不是和环境变量也有关系?这个待确定
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql>

SQL WHERE 中的空格


mysql> show create table db_work_order \G      --------- 注意表的  COLLATE=utf8_bin 
*************************** 1. row ***************************
       Table: db_work_order
Create Table: CREATE TABLE `db_work_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_num` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '工单编号',
  `order_type` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '工单类型',
  ......
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `update_time` datetime(6) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uqi_ordernum` (`order_num`),
  KEY `created_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1578 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据库类别的工单表'
mysql>
mysql> select order_num, order_type, length(order_type) from db_work_order where order_type = 'cds_change_bak    ' limit 3;
+----------------------------+----------------+--------------------+
| order_num                  | order_type     | length(order_type) |
+----------------------------+----------------+--------------------+
| cds_change_bak202003050001 | cds_change_bak |                 14 |
| cds_change_bak202003050002 | cds_change_bak |                 14 |
| cds_change_bak202003060001 | cds_change_bak |                 14 |
+----------------------------+----------------+--------------------+
3 rows in set (0.00 sec)
mysql>

因为 order_type 的类型是 varchar 所以在 WHERE 中会忽略 'cds_change_bak ’ 中的空格,因为在varcahr 在存储的时候就会把后缀的空格给截断,注意前导空格不会忽略。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
SQL 关系型数据库 MySQL
MySQL数据库,可以使用二进制日志(binary log)进行时间点恢复
对于MySQL数据库,可以使用二进制日志(binary log)进行时间点恢复。二进制日志是MySQL中记录所有数据库更改操作的日志文件。要进行时间点恢复,您需要执行以下步骤: 1. 确保MySQL配置文件中启用了二进制日志功能。在配置文件(通常是my.cnf或my.ini)中找到以下行,并确保没有被注释掉: Copy code log_bin = /path/to/binary/log/file 2. 在需要进行恢复的时间点之前创建一个数据库备份。这将作为恢复的基准。 3. 找到您要恢复到的时间点的二进制日志文件和位置。可以通过执行以下命令来查看当前的二进制日志文件和位
931 1
|
关系型数据库 MySQL
Mysql基础第十二天,用正则表达式进行搜索
Mysql基础第十二天,用正则表达式进行搜索
108 0
Mysql基础第十二天,用正则表达式进行搜索
|
SQL 关系型数据库 MySQL
总结 vue3 的一些知识点:MySQL LIKE 子句
总结 vue3 的一些知识点:MySQL LIKE 子句
|
7月前
|
自然语言处理 搜索推荐 关系型数据库
MySQL实现文档全文搜索,分词匹配多段落重排展示,知识库搜索原理分享
本文介绍了在文档管理系统中实现高效全文搜索的方案。为解决原有ES搜索引擎私有化部署复杂、运维成本高的问题,我们转而使用MySQL实现搜索功能。通过对用户输入预处理、数据库模糊匹配、结果分段与关键字标红等步骤,实现了精准且高效的搜索效果。目前方案适用于中小企业,未来将根据需求优化并可能重新引入专业搜索引擎以提升性能。
291 5
|
8月前
|
SQL 关系型数据库 MySQL
MySQL 中的全文索引:强大的文本搜索利器
MySQL 的全文索引是一种用于快速搜索大量文本数据的特殊索引。它通过对文本内容进行分析(如分词、去除停用词等)并构建倒排索引,实现高效查找。创建全文索引使用 `CREATE FULLTEXT INDEX`,搜索时使用 `MATCH AGAINST` 语句。适用于 `CHAR`、`VARCHAR`、`TEXT` 等字段,但需注意性能影响和正确使用搜索语法。
242 22
|
关系型数据库 MySQL 数据库
在 MySQL 中使用 LIKE
【8月更文挑战第12天】
1476 1
|
11月前
|
搜索推荐 关系型数据库 MySQL
mysql like查询优化
通过合理的索引设计、使用全文索引、优化查询结构以及考虑分片和分区表,可以显著提高MySQL中 `LIKE`查询的性能。针对不同的应用场景选择合适的优化策略,能够有效地提升数据库查询效率,减少查询时间。希望这些方法和技巧能帮助您优化MySQL数据库中的模糊查询。
1148 4
|
搜索推荐 关系型数据库 MySQL
MySQL 模糊查询新纪元:超越 LIKE+% 的高效探索
在数据库的日常操作中,模糊查询是一项不可或缺的功能,它允许我们根据不完全匹配的关键字来检索数据。传统上,MySQL 使用 LIKE 关键字配合 % 通配符来实现这一功能,虽然灵活但性能上往往不尽如人意,尤其是在处理大型数据集时。今天,我们将一起探索几种超越 LIKE+% 的模糊查询技术,以提升查询效率与用户体验。
664 2
|
关系型数据库 MySQL
MySQL 保姆级教程(七):用正则表达式进行搜索
MySQL 保姆级教程(七):用正则表达式进行搜索
|
SQL 运维 关系型数据库

推荐镜像

更多