MySQL认为‘au，er’等于'auser‘

Question

我试图将一些表从一个MySQL数据库迁移到另一个数据库，但遇到了一个错误：

ERROR 1062 (23000) at line 108: Duplicate entry 'außer' for key 'PRIMARY'

我试图找出为什么，在目标数据库中，我运行

mysql> select 'außer' = 'auser';
+--------------------+
| 'außer' = 'auser'  |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.07 sec)

如您所见，MySQL认为两者是相同的，我检查了配置变量。

mysql> show variables like 'coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

mysql> show variables like 'character%';
+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| character_set_client     | utf8                                     |
| character_set_connection | utf8                                     |
| character_set_database   | utf8                                     |
| character_set_filesystem | binary                                   |
| character_set_results    | utf8                                     |
| character_set_server     | utf8                                     |
| character_set_system     | utf8                                     |
| character_sets_dir       | /rdsdbbin/mysql-5.5.8.R1/share/charsets/ |
+--------------------------+------------------------------------------+

然后，我返回原始数据库并尝试

mysql> select 'außer' = 'auser';
+--------------------+
| 'außer' = 'auser'  |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> show variables like 'coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> show variables like 'haracter%';
Empty set (0.00 sec)

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MySQL的原始版本为5.0.77，迁移目标为5.5.8。我不知道怎么会发生这种事。为什么他们比较字符串不同？我该如何解决这个问题？谢谢。

Answer 1

正如http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-sets.html中所概述的，这似乎是正确的行为：

utf8_general_ci对德语和法语也都是满意的，只不过“ss”等于“S”，而不是“ss”。如果这对于您的应用程序来说是可以接受的，那么您应该使用utf8_general_ci，因为它更快。否则，请使用utf8_unicode_ci，因为它更准确。