为什么MySQL处理与e相同？ [英] Why is MySQL treating é the same as e?

问题描述

我使用Django web应用程序将Unicode字符串存储在MySQL数据库中。我可以存储Unicode数据很好，但是当查询时，我发现é和 e 被视为是相同字符：

I'm storing Unicode strings in a MySQL database with a Django web application. I can store Unicode data fine, but when querying, I find that é and e are treated as if they were the same character:

In [1]: User.objects.filter(last_name='Jildén')
Out[1]: [<User: Anders Jildén>]

In [2]: User.objects.filter(last_name='Jilden')
Out[2]: [<User: Anders Jildén>]

这也是直接使用MySQL shell的情况：

This is also the case when using the MySQL shell directly:

mysql> select last_name from auth_user where last_name = 'Jildén';
+-----------+
| last_name |
+-----------+
| Jildén   |
+-----------+
1 row in set (0.00 sec)

mysql> select last_name from auth_user where last_name = 'Jilden';
+-----------+
| last_name |
+-----------+
| Jildén   |
+-----------+
1 row in set (0.01 sec)

这里是数据库字符集设置：

Here are the database charset settings:

mysql> SHOW variables LIKE '%character_set%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | latin1                                               |
| character_set_connection | latin1                                               |
| character_set_database   | utf8                                                 |
| character_set_filesystem | binary                                               |
| character_set_results    | latin1                                               |
| character_set_server     | latin1                                               |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /usr/local/Cellar/mysql/5.1.54/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+

这里是表格式：

CREATE TABLE `auth_user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(30) CHARACTER SET utf8 NOT NULL,
    `first_name` varchar(30) CHARACTER SET utf8 NOT NULL,
    `last_name` varchar(30) CHARACTER SET utf8 NOT NULL,
    `email` varchar(200) CHARACTER SET utf8 NOT NULL,
    `password` varchar(128) CHARACTER SET utf8 NOT NULL,
    `is_staff` tinyint(1) NOT NULL,
    `is_active` tinyint(1) NOT NULL,
    `is_superuser` tinyint(1) NOT NULL,
    `last_login` datetime NOT NULL,
    `date_joined` datetime NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=7952 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

这里是我通过Django的 DATABASES 设置：

and here are the options I'm passing via Django's DATABASES setting:

DATABASES = {
    'default': {
        # ...
        'OPTIONS': {
            'charset': 'utf8',
            'init_command': 'SET storage_engine=INNODB;',
        },
    },
}

请注意，整理到 utf8_bin ，无效果：

Note that I have tried setting the table collation to utf8_bin, with no effect:

mysql> alter table auth_user collate utf8_bin;

mysql> select last_name from auth_user where last_name = 'Jilden';
+-----------+
| last_name |
+-----------+
| Jildén   |
+-----------+
1 row in set (0.00 sec)

我如何让MySQL将这些视为不同的字符？

How can I get MySQL to treat these as different characters?

推荐答案

你改了表的排序规则，但不完全。在MySQL中，表中的每一列都有自己的字符集和排序规则。该表具有自己的字符集和排序规则，但这不会覆盖列排序规则;它仅确定对于您未指定排序规则的已添加新列，将使用什么排序规则。因此，您尚未更改您感兴趣的列的排序规则。

You were nearly there when you changed the table collation, but not quite. In MySQL, each column in a table has its own character set and collation. The table has its own character set and collation, but this does not override the column collations; it only determines what the collation will be for new columns that are added for which you don't specify the collation. So you haven't changed the collation of the column that you're interested in.

ALTER TABLE tablename MODIFY columnname
    varchar(???) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

这篇关于为什么MySQL处理与e相同？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！