如何设置MySQL以正确处理Unicode变音符号？

Question

这是一个奇怪的难题，据我所知 utf8_bin 应该能保证每个重音符号被正确存储在数据库中，即不会发生奇怪的ASCII转换。因此，我有这样一张表：

DEFAULT CHARSET=utf8 COLLATE=utf8_bin

尽管我在MySQL中尝试比较/查询等Krąków和Kraków这样的条目，但它们被视为相同的字符串。

出于好奇，我还尝试了utf8_polish，并且MySQL声称对于波兰人来说，“a”和“ą”没有任何区别。

那么如何设置MySQL表，以便安全地存储Unicode字符串，而不会丢失重音符号等内容？

服务器：MySQL 5.5 + openSUSE 11.4，客户端：Windows 7 + MySQL Workbench 5.2。

更新--创建表

CREATE TABLE `Cities` (
  `city_Name` VARCHAR(145) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`city_Name`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

请注意，我无法为列设置不同的 utf8_bin，因为整个表都是 utf8_bin，所以实际上列的排序规则被重置为默认值。

- greenoldman

---

这是有意为之的。你并没有失去重音，只是在比较时非常宽容。等一下，我在找一个重复的…… - Pekka

好的，我找不到一个好的重复问题，并且懒得翻阅10页。一般的答案是，你需要使用utf8_bin排序规则来进行重音和大小写敏感的比较，可以将其作为表格的排序规则或在比较时使用COLLATE utf8_bin;。我不确定这是否是该问题的最终解决方案，或者是否有国家特定的排序规则是重音敏感的，但这是所有答案都建议的方法。 - Pekka

@Pekka웃，我在整个表中使用了utf8_bin，现在我也为特定列设置了utf8_bin。然而，在MySQL中，“a”仍然是“ą”。更新：实际上没有任何区别，因为如果有人对列使用默认值，则更改整个表的排序规则也会更改列的排序规则。 - greenoldman

您可以在列规范中指定排序规则，例如 columnname VARCHAR(100) CHARSET utf8 COLLATE utf8_bin NOT NULL。DEFAULT 设置是在创建时确定的，但在列已经创建后更改设置不会起作用。使用 SHOW CREATE TABLE tablename 命令查看现有列的字符集和排序规则。 - bobince

@ greenoldman 这个有文档记录吗？实际上，这听起来对我来说完全是错误的。有人能解释一下这种行为的理由吗？在这种情况下，自己发布答案可能是合理的，因为你必须改变到完全不同的字符集，这是不容忽视的。不过，你可能还需要调整问题和问题标题 ;) - Jonas Schäfer

Answer 1

本解决方案的所有功劳归功于 bobince，请为他在我的问题评论中点赞。

这个问题的解决方案有些奇怪，我敢说MySQL在这方面有问题。

假设我创建了一个使用utf8编码的表，并且没有对列进行任何操作。后来我意识到需要对字符进行严格比较，所以我将表和列的排序规则更改为utf8_bin。解决了吗？

不，现在MySQL看到这一点——表确实是utf8_bin，但列也是utf8_bin，这意味着该列使用表的默认排序规则。但是MySQL并没有意识到之前的默认值与当前的默认值不同。因此比较仍然无法工作。

所以你必须摆脱该列的默认值，选择某个超出“族”范围的外来值（在“utf8xxx”的情况下表示没有其他“utf8xxx”）。一旦它被摆脱，并且你看到条目没有在列排序规则中显示“default”，你可以设置utf8_bin，这现在被评估为默认值，但由于我们来自非默认排序规则，所以一切都按预期进行。

不要忘记在每个步骤中应用更改。

- greenoldman

Answer 2

MySQL的默认字符集和排序规则（适用于整个服务器，但可以在每个连接中更改）在创建表时应用。在创建表后更改默认值不会影响现有表。

字符集和排序规则是各个列的属性。它们可以从表范围的默认值设置，但确实属于列。

utf8的字符集应该足以正确表示所有欧洲语言。您绝对应该能够将“a”和“ą”存储为两个不同的字符。

utf8-bin的排序规则产生区分大小写和重音符号敏感的排序规则。

以下是文本值和排序规则行为之间差异的一些示例。我使用了三个样本字符串：“abcd”，“ĄBCD”和“ąbcd”。最后两个具有A-ogonek字母。

这个例子说明，在utf8字符表示和utf8_general_ci排序规则下，三个字符串都按照用户指定的方式显示，但它们相互比较相等。这在不区分a和ą的排序规则中是可以预期的。这是一个典型的不区分大小写的排序规则，其中所有变体字符都与没有任何变音符号的字符相等地排序。

SET NAMES 'utf8' COLLATE 'utf8_general_ci';
SELECT 'abcd', 'ąbcd' , 'abcd' < 'ąbcd',  'abcd' = 'ąbcd';
                               false            true  

这个例子展示了在不区分大小写的波兰语排序中，字母a排在ą之前。我不懂波兰语，但我猜波兰电话簿会把A和Ą分开。

SET NAMES 'utf8' COLLATE 'utf8_polish_ci';
SELECT 'abcd', 'ĄBCD' , 'ąbcd', 'abcd' < 'ĄBCD', 'abcd' < 'ąbcd' , 'ąbcd' = 'ĄBCD' 
                                      true             true              true

这个例子展示了使用utf8_bin排序规则时会发生什么。

SET NAMES 'utf8' COLLATE 'utf8_bin';
SELECT 'abcd', 'ĄBCD' , 'ąbcd', 'abcd' < 'ĄBCD', 'abcd' < 'ąbcd' , 'ąbcd' = 'ĄBCD' 
                                      true           true               false

这种情况有一件不太直观的事情需要注意。'abcd' < 'ĄBCD' 是true（而'abcd'与纯ASCII的'ABCD'是false）。如果您从语言学的角度考虑，那么这是一个奇怪的结果。这是因为两个A-ogonek字符在utf8中具有比所有abc和ABC字符都高的二进制值。因此：如果您在ORDER BY操作中使用utf8-bin排序规则，您将得到语言学上奇怪的结果。

您说'Krąków'和'Kraków'相等，这让您感到困惑。当使用utf8_general_ci排序规则时，它们确实相等。但是使用utf8_bin或utf8_polish_ci时，它们并不相等。根据MySQL中的波兰语支持，这个城市名称的这两种拼写是不同的。

在设计应用程序时，您需要梳理出语言上的工作方式。'Krąków'和'Kraków'是同一个地方吗？'Ąaron'和'Aaron'是同一个人吗？如果是这样，您需要使用utf8_general_ci。

您可以考虑像这样更改您展示的表：

  ALTER TABLE Cities
MODIFY COLUMN city_Name 
              VARCHAR(145)
              CHARACTER SET utf8 
              COLLATE utf8_general_ci

这将按照您的要求设置表中的列。

- O. Jones