业务开发常见问题-MySQL 表字段值英文大小写问题

hello，大家好，今天我们来聊一下MySQL 表字段值英文大小写问题！
今天线上遇到了一个问题，再次记录下解决方案。

一、问题

问题是这样的。
有一张商品表，商品型号字段是 varchar类型的

create table t_product
(
    id          bigint auto_increment primary key comment '主键',
    name        varchar(100)                             not null comment '名称',
    type        varchar(60)                              not null comment '型号',
    create_by   varchar(64)                              null comment '创建人',
    create_time datetime(3) default CURRENT_TIMESTAMP(3) null comment '创建时间',
    update_by   varchar(64)                              null comment '修改人',
    update_time datetime(3) default CURRENT_TIMESTAMP(3) null on update CURRENT_TIMESTAMP(3) comment '修改时间',
    deleted     tinyint(1)  default 0                    not null comment '删除字段'
) comment '商品表';

一开始插入的数据是这样的

代码逻辑（简化代码，表达意思即可）：

// 方法传入参数：40*70cm
// 查询数据库select * from t_product where type = '40*70cm';
ProductEntity product = ProductMapper.selectByType("40*70cm");
// 比较 参数和 查询结果
if("40*70cm".equals(product.getType())){
// do something
}else {
	throw new BusinessException("未匹配到商品数据");
}

我想大家也都发现了，sql 的参数是40*70cm，而数据库是存的是40*70CM，这种情况，也是可以查询出数据的。但是 Java 代码 代码层面都不匹配了。

在 MySQL 中，字段值的大小写敏感性主要取决于字符集和排序规则（collation）的设置。默认情况下，MySQL 的字符串比较是不区分大小写的，因为大多数字符集的默认排序规则是大小写不敏感的。
那么如何解决这种问题呢？

二、解决方案

1、equalsIgnoreCase

将等值比较改写成equalsIgnoreCase就可以了。

// 方法传入参数：40*70cm
// 查询数据库select * from t_product where type = '40*70cm';
ProductEntity product = ProductMapper.selectByType("40*70cm");
// 比较 参数和 查询结果
if("40*70cm".equalsIgnoreCase(product.getType())){
// do something
}else {
	throw new BusinessException("未匹配到商品数据");
}

这种就需要我们考虑具体的业务场景了，是否可以采用该方法。
若业务对大小写敏感，则不能采用此方法。

2、修改数据库字段类型

将字段类型修改为BINARY或者VARBINARY
BINARY 类型会将字符串存储为二进制字节序列，从而使比较操作区分大小写。这是因为 BINARY 类型在存储和比较时不考虑字符集和排序规则，而是直接比较字节。

• 如果你希望字段为固定长度，可以使用 BINARY(n)，其中 n 是字节数。
• 如果你希望字段为可变长度，可以使用 VARBINARY(n)。

alter table t_product modify type varbinary(60) not null comment '型号';

修改后的再次执行查询 sql，就无法查出数据了。
但是这种方式也有缺点，比如：

• 使用 BINARY 或 VARBINARY 类型时，字段将不再是字符串类型，而是二进制类型。这意味着某些字符串函数可能无法直接使用，或者行为与字符串类型不同。
• 如果你需要保持字符串类型的功能，可以考虑使用 CHAR 或 VARCHAR 类型，并在查询中使用 BINARY 关键字来进行大小写敏感的比较。

先说第一点：

1. 字符函数：字符串函数如 LENGTH()、LEFT()、RIGHT()、SUBSTRING() 等在处理 BINARY 或 VARBINARY 数据时，仍然会返回正确的结果，但它们会基于字节数而非字符数进行操作。因此，对于多字节字符集，结果可能与预期不同。
2. 字符串比较：在 BINARY 或 VARBINARY 字段上，字符串比较是基于字节的，因此是大小写敏感的。这与字符字段的排序规则不同。
3. 字符串连接：使用 CONCAT() 等函数连接 BINARY 数据时，结果仍然是二进制数据。如果需要将结果转换为字符数据，可能需要显式转换。
4. 字符集转换：BINARY 和 VARBINARY 数据不直接支持字符集转换，因为它们不依赖字符集。因此，函数如 CONVERT() 和 CAST() 在转换为字符集时需要显式指定。
5. 排序和比较：排序操作在 BINARY 和 VARBINARY 字段上是基于字节的，因此不同于字符字段的排序，可能不符合特定语言的字母顺序。
6. 正则表达式和模式匹配：LIKE 和正则表达式匹配在 BINARY 数据上会严格区分大小写，因为它们处理的是字节序列。
7. 如果需要在 BINARY 或 VARBINARY 字段上使用这些函数并希望获得字符数据的行为，通常需要在查询中将数据转换回字符类型。例如，可以使用 CAST(your_column AS CHAR) 来处理。

再说第二点：
即使字段不是 BINARY 类型，你也可以在查询中使用 BINARY 关键字来强制区分大小写。例如：

SELECT * FROM t_product WHERE BINARY type = '40*70cm';

3、修改数据库字段字符集

1. 在创建表时指定排序规则：

create table t_product
(
    id          bigint auto_increment primary key comment '主键',
    name        varchar(100)                             not null comment '名称',
    type        varchar(60)                              not null comment '型号' COLLATE utf8mb4_general_ci,
    create_by   varchar(64)                              null comment '创建人',
    create_time datetime(3) default CURRENT_TIMESTAMP(3) null comment '创建时间',
    update_by   varchar(64)                              null comment '修改人',
    update_time datetime(3) default CURRENT_TIMESTAMP(3) null on update CURRENT_TIMESTAMP(3) comment '修改时间',
    deleted     tinyint(1)  default 0                    not null comment '删除字段'
) comment '商品表';

2. 如果表已经存在了，也可以修改字段属性：

ALTER TABLE t_product MODIFY type VARCHAR(60) COLLATE utf8mb4_general_ci not null comment '型号';

3. 若不想修改表结构，也可以只处理单个 sql 的查询

SELECT * FROM t_product WHERE type = '40*70cm' COLLATE utf8mb4_general_ci;

4. 或者查询时处理了大小写问题

SELECT * FROM t_product WHERE LOWER(type) = LOWER('40*70cm');

三、结语

好啦，以上就是今天为大家介绍的业务开发常见问题整理。你还有其他的解决方式吗？欢迎在评论区写出你的解决方案！！！！

同时也欢迎各位小伙伴关注我的微信公众号：小张的技术Talk