mysql的校对规则引起的问题分析

问题是这样的： 一张test的表，字符集采用的latin1。 select to_id from test where to_id=’cn象_王’; +—————+ | to_id | +—————+ | cn陶_陶 | | cn象_王 | +—————+ 2 rows in set (0.00 sec) 取cn象_王的数据,居然把cn陶_陶的数据也取回来了。 这显然是不允许的。 查看它们的编码： (root@im_offlog1a:)[test]> select hex(‘cn陶_陶’); +—————-+ | hex(‘cn陶_陶’) | +—————-+ | 636ECCD55FCCD5 在MySQL数据库中，字符集和校对规则是两个非常重要的概念，它们决定了数据的存储方式以及比较时的行为。本问题中出现的异常情况是由于不正确的校对规则导致的。让我们深入探讨这个问题。 字符集（Character Set）定义了能被数据库识别和存储的一组字符，例如拉丁字符集（latin1）包含了西欧语言的基本字符。在这个例子中，表`test`使用了latin1字符集。 校对规则（Collation）则是字符集的一种特定排序和比较规则，它决定了字符之间的比较方式，是否区分大小写，以及如何处理特殊字符。不同的校对规则可能会对相同字符的比较结果产生影响。例如，`_ci`（Case Insensitive）表示不区分大小写，`_cs`（Case Sensitive）表示区分大小写，而`_bin`（Binary）则按照字节的二进制值进行比较，是最严格的校对规则。 在问题描述中，查询`select to_id from test where to_id=’cn象_王’;`返回了`'cn陶_陶'`的结果，这是因为当前使用的校对规则（latin1_swedish_ci）不区分某些字符的细微差异。尽管'cn陶_陶'和'cn象_王'在hex编码中不同，但在latin1_swedish_ci校对规则下，它们被认为是相等的。这是因为这种校对规则对某些字符的处理方式不是基于它们的二进制值，而是根据特定的语言规则。 为了验证这个问题，我们查看了字符的hex编码，发现'cn陶_陶'的编码是`636ECCD55FCCD5`，而'cn象_王'的编码是`636ECFF35FCDF5`，两者确实不相同。然而，当尝试手动将校对规则改为latin1_bin时，问题依然存在，这是因为仅改变连接、数据库或服务器级别的校对规则并不影响已存在的表的校对规则。 解决这个问题的关键在于理解MySQL选择表字符集和校对规则的规则：如果在创建表时指定了字符集但未指定校对规则，那么将使用该字符集的默认校对规则。在这种情况下，即使手动将全局或会话级的校对规则更改为latin1_bin，表的原有校对规则（latin1_swedish_ci）仍然有效。 要修复这个问题，有两种方法：一是重建表并指定`CHARACTER SET latin1 COLLATE latin1_bin`，这样表和其内容都会采用新的校对规则；二是使用`ALTER TABLE`语句，如`ALTER TABLE db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin`，将现有表的字符集和校对规则转换为latin1_bin。后者在不需要丢失数据的情况下提供了更便捷的解决方案。 为了避免类似的问题，建议在创建表时明确指定bin类型的校对规则，如latin1_bin，因为这种校对规则能够精确地比较字符，减少因字符比较错误导致的意外结果。同时，对于多语言环境，考虑使用支持更多语言的字符集，如utf8mb4，及其对应的二进制校对规则，以确保数据的正确存储和比较。