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

Question

The **compare-on-binary way** is **NOT semantically-correct**

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

在本例中，我希望将字段'北京 '中的字符串城市替换为string '北京111'，但将字符串'北京'保持不变，因此我编写了以下sql：

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

底层表定义和数据(会话编码设置为“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 (由会话决定)编码，来自字段城市的字符串值'北京 '使用gbk (由表定义决定)编码，并且当它们转换为二进制时，它们并不是每个字节相同，但是两个字符串在语义上实际上每个字符都是相等的(不管使用什么底层编码方法)。

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

Answer 1

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

gbk和utf8mb4之间的转换使您任由转换表支配；您可能(或不可能)获得所需的音译。

'北京'是用于utf8 8/utf8mb4的HEX E58C97 E4BAAC

'北京 '是用于utf8 8/utf8mb4的HEX E58C97 E4BAAC 20 --如查询中所示

'北京'是gbk的HEX B1B1 BEA9

'北京 '是gbk的HEX B1B1 BEA9 20 --如表所示

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

不要使用... WHEN BINARY 城市 = BINARY '北京 ' THEN ...，而要执行以下操作之一：

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

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

C计划，使用HEX：... WHEN HEX(城市) = HEX(CONVERT('北京 ' USING gbk)) THEN ...

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

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