mysql 查询数据字段末尾存在空格，用“=“也会命中

最新推荐文章于 2024-04-22 16:27:49 发布

MakeGreatEffort

最新推荐文章于 2024-04-22 16:27:49 发布

阅读量1.6k

点赞数 2

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_36838191/article/details/120283005

版权

mysql 专栏收录该内容

35 篇文章 1 订阅

订阅专栏

本文探讨了MySQL在进行字符串等值比较时会忽略尾部空格的现象，这是由于其基于PADSPACE的校对规则。当使用'='进行查询时，即使查询条件带有不同数量的空格，也会匹配到所有包含相同字符序列的记录。为实现精确匹配，可以采用LIKE操作符或使用BINARY关键字进行二进制比较。这两种方法都不会忽略尾部空格，从而确保查询结果的准确性。

摘要由CSDN通过智能技术生成

一、现象

CREATE TABLE `user_info` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增ID',
`user_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '名字',

PRIMARY KEY (`id`),
KEY `idx_user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

INSERT INTO user_info(user_name) values('adu');
INSERT INTO user_info(user_name) values('adu '); #一个空格
INSERT INTO user_info(user_name) values('adu '); #四个空格

如上有个user_info表，我们分别插入'adu'、'adu '、'adu '三个用户。

但我们在查询时却遇到一个诡异的现象：

SELECT * FROM adu_user_info WHERE user_name = 'adu';
SELECT * FROM adu_user_info WHERE user_name = 'adu '; #一个空格
SELECT * FROM adu_user_info WHERE user_name = 'adu  '; #两个空格

无论查询中尾部带有几个空格，结果是一样的，都会命中'adu'、'adu '、'adu '三个用户。

二、原因

查询MySQL的官方文档（MySQL :: MySQL 5.7 Reference Manual :: 11.3.2 The CHAR and VARCHAR Types），原来MySQL的校对规则基于PAD SPACE，这就意味着CHAR、VARCHAR、TEXT等字符串的等值比较（“=”）会忽略掉尾部的空格。

而且这也符合SQL标准，无需设置也无法改变（参考：MySQL comparison operator, spaces - Stack Overflow）。

三、想要精确查询的解决方案

3.1 like

like不会忽略尾部的空格。

SELECT * FROM adu_user_info WHERE user_name LIKE 'adu';
SELECT * FROM adu_user_info WHERE user_name LIKE 'adu '; #一个空格
SELECT * FROM adu_user_info WHERE user_name LIKE 'adu    '; #四个空格

3.2 binary

binary不是函数，是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解成精确匹配。

SELECT * FROM adu_user_info WHERE user_name = BINARY 'adu';
SELECT * FROM adu_user_info WHERE user_name = BINARY 'adu '; #一个空格
SELECT * FROM adu_user_info WHERE user_name = BINARY 'adu    '; #四个空格

注：这里的BINARY关键字要放在“=”的后边，以便有效利用该字段的索引。