明明表中没这条数据,竟然还能查出来?

数据库 其他数据库
品牌表使用的存储引擎ENGINE是InnoDB,为了保证表的事务性。字符集CHARSET用的utf8mb4,可以保存一些表情符号等特殊字符。校对规则COLLATE用的utf8_unicode_ci。

1.还原问题现场

有一天下午,有用户反馈说,它自定义的品牌:yoyo,一直都添加不成功。

我查了一下服务器的日志,并没有异常。

在我们的创建商品页面,用户可以选择已有品牌,也可以自己自定义新的品牌。

前端做了一个品牌的下来列表,为了方便用户查找,支持搜索。

用户可以输入关键字搜索品牌。

如果下拉框中出现了,则可以选择使用。

如果下拉框中没有数据,则在输入框中标识这个品牌是用户自定义的品牌。

然后通过创建商品接口,将该品牌添加到数据库当中。

现在的问题是yoyo这个品牌,用户自定义了,但不能保存到数据库当中。

这就非常奇怪了。

2 分析问题

为了查明这个问题,我先查询了数据库中的品牌表:

select * from brand where `name`='yoyo';

确实没有查出yoyo这个品牌。

但意外查出YOYO这个品牌。

图片图片

它是yoyo英文字母的大写。

奇怪,我们查小写的yoyo字符串,为什么会把大写的YOYO查出来了?

于是,我查了brand表的表结构。

CREATE TABLE `brand` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(30) NOT NULL COMMENT '品牌名称',
  `create_user_id` bigint NOT NULL COMMENT '创建人ID',
  `create_user_name` varchar(30) NOT NULL COMMENT '创建人名称',
  `create_time` datetime(3) DEFAULT NULL COMMENT '创建日期',
  `update_user_id` bigint DEFAULT NULL COMMENT '修改人ID',
  `update_user_name` varchar(30)  DEFAULT NULL COMMENT '修改人名称',
  `update_time` datetime(3) DEFAULT NULL COMMENT '修改时间',
  `is_del` tinyint(1) DEFAULT '0' COMMENT '是否删除 1:已删除 0:未删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci  COMMENT='品牌表';

品牌表使用的存储引擎ENGINE是InnoDB,为了保证表的事务性。

字符集CHARSET用的utf8mb4,可以保存一些表情符号等特殊字符。

校对规则COLLATE用的utf8_unicode_ci。

字符集是一组符号和编码的集合,而校对规则是用于比较字符集中字符的规则。

例如,utf8mb4字符集支持存储Unicode字符,而utf8mb4_0900_ai_ci校对规则定义了如何比较这些字符。

在MySQL中使用show collation指令,可以查看到所有COLLATE。

以utf8mb4为例,该编码所支持的所有COLLATE如下图所示。

图片图片

主要包含了三种:

  1. 以_ci结尾的。
  2. 以_bin结尾的。
  3. 以_cs结尾的。

ci是case insensitive的缩写,意思是大小写不敏感,即忽略大小写。

cs是case sensitive的缩写,意思是大小写敏感,即区分大小写。

还有一种是bin,它是将字符串中的每一个字符用二进制数据存储,区分大小写。

使用最多的是 utf8mb4_general_ci(默认的)和 utf8mb4_bin。

我们的brand表,使用的COLLATE是utf8mb4_general_ci,它不区分大小写。

难怪下面的这条sql:

select * from brand where `name`='yoyo';

数据库中明明没有小写的yoyo这条数据,但却能把大写的YOYO数据查出来。

3.如何解决问题?

知道原因了,就好办了。

第一个想到的是把brand表的COLLATE改成utf8mb4_bin不就搞定了?

这样确实可以非常快速解决问题。

但我仔细想了一下。

品牌这种基础数据,yoyo和YOYO正常情况下应该是同一个品牌,应该只有一个id,不区分大小写才是正确的做法。

如果brand表的COLLATE改成了utf8mb4_bin,区分大小写,不就会出现两个不同的id,这样品牌表不就会产生重复的数据,后面会导致商品也可能会重复。

如果后面商品也重复了,就会带来非常多的问题。

因此,我们要在brand表做好控制,不应该区分大小写,保证品牌不会重复。

既然修改brand表的COLLATE这个方案不行,那么,只能修改业务逻辑了。

目前有两种解决方案:

  • 前端搜索品牌时,不区分大小写。
  • 前端品牌下拉控件,改成分页的,搜索品牌的功能,改成调用后端接口实现。

方案1适合品牌数据量少的情况。

方案2适合品牌数据量多的情况。

我们的品牌数据,其实在不断增加,因此,决定使用方案2。

后端提供一个分页查询品牌的接口,并且支持不区分大小写的模糊搜索功能。

但这样还不能100%保证,品牌数据在brand表中不会重复。

还需要给name字段增加唯一索引。

这样改造之后,后面用户输入yoyo,但数据库中有YOYO,在品牌下拉列表中会显示YOYO,用户可以直接选择使用。

这样对用户的交互更友好一些。

这是一类问题,可以衍生一下。

有些属性值表也有类似的问题。

比如用户自定义属性值之后,如果业务逻辑中有通过属性id查询属性值集合,再拿这个属性值集合跟自定义属性值做判断的时候,就需要忽略大小写做判断了。

其实,在我们的实际工作中,这样的场景很多,赶紧排查一下代码,看看你有没有这个问题?

责任编辑:武晓燕 来源: 苏三说技术
相关推荐

2021-07-28 06:10:47

拖拽设计器 transmat

2009-09-24 09:45:23

Hibernate批量

2023-08-09 14:03:33

2013-08-26 14:18:12

SELinux

2013-08-06 10:00:52

IDC阿里云华为

2018-01-28 15:22:03

DockerKubernetes容器

2024-05-13 00:47:37

JSON对象数据

2023-03-05 22:11:20

删除文件磁盘

2024-03-04 07:37:40

MySQL记录锁

2020-08-14 08:19:25

Shell命令行数据

2020-03-05 09:51:20

内存分页映射

2021-04-01 05:40:53

分库分表数据库MySQL

2023-03-03 07:40:52

MySQLSQL命令

2021-12-27 09:59:57

SpringCanal 中间件

2024-09-27 08:44:43

2009-09-24 09:25:10

Hibernate批量

2020-09-02 07:05:56

手机支付

2020-08-26 14:45:34

SQL数据库数次

2017-08-22 10:49:28

DNA存储电影

2020-10-29 08:41:20

JavaNetty缓冲
点赞
收藏

51CTO技术栈公众号