MySQL严格字符串比较语义上正确的方法？

Question


**比较二进制方式**是**语义上不正确的**


例如，当您想要对不同编码的字符串进行严格字符串比较时，比较二进制方式就会出现错误。以下测试用例说明了原因:

在本例中，我想将城市字段中的字符串'北京'(带尾部空格)替换为字符串'北京111'，但保持字符串'北京'不变，所以我写下以下sql:

SELECT CASE WHEN BINARY `城市` = BINARY '北京 ' THEN '北京111' ELSE `城市` END
FROM `中文测试表1`
GROUP BY BINARY CASE WHEN BINARY `城市` = BINARY '北京 ' THEN '北京111' ELSE `城市` END

底层表定义和数据( session 编码设置为“utf8mb4”):

CREATE TABLE `中文测试表1` (
  `城市` varchar(50) CHARACTER SET gbk DEFAULT NULL,
  `销量` int(11) DEFAULT NULL
) ENGINE=InnoDB;

INSERT INTO `中文测试表1` VALUES ('杭州', '111');
INSERT INTO `中文测试表1` VALUES ('北京', '345');
INSERT INTO `中文测试表1` VALUES ('北京 ', '123');

实际发生的情况是，字符串 '北京 ' 没有被 '北京111' 替换，并且仍然保留结果集中的内容。

原因是，字符串文字'北京'是使用utf8mb4(由 session 决定)和字段中的字符串值'北京'编码的>城市是使用gbk编码的(这是由表定义决定的)，当它们转换成二进制时，每个字节并不相同，但是两个字符串在语义上确实是每个字符都相等(无论什么)使用底层编码方法)。

那么，在 MySQL 中严格比较字符串的语义上正确的方法是什么？

Answer 1

请参阅 TRIM() 函数来删除字符串开头/结尾的空格。

在 gbk 和 utf8mb4 之间进行转换会让您受到转换表的支配；您可能(或可能不会)获得所需的音译。

'北京' 是 utf8/utf8mb4 的十六进制 E58C97 E4BAAC
'北京' 是 utf8/utf8mb4 的十六进制 E58C97 E4BAAC 20 -- 如在查询中找到的
'北京' 是 GBK 的十六进制 B1B1 BEA9
'北京' 是 GBK 的十六进制 B1B1 BEA9 20 -- 如表中所示

当您说SELECT ... BINARY '北京'...时，字符串的编码基于连接，而不是列编码。所以是utf8mb4。

代替 ... WHEN BINARY 城市 = BINARY '北京 ' THEN ...，执行以下操作之一:

A 计划，让转换自动发生:... WHEN 城市 = '北京 ' THEN ...

B 计划，显式转换:... WHEN 城市 = CONVERT('北京 ' USING gbk) THEN ...

方案C，使用十六进制:... WHEN HEX(城市) = HEX(CONVERT('北京 ' USING gbk)) THEN ...

计划 D，更接近您的尝试:... WHEN BINARY 城市 = BINARY(CONVERT('北京 ' USING gbk)) THEN ...

还有其他方法，使用COLLATE utf8_bin、COLLATE gbk_bin等。