Django 字符集与 MySQL 的怪异

发布于 2024-07-25 13:25:00 字数 322 浏览 5 评论 0原文

我看到

OperationalError (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")

我的一些变量看起来像是 UTF8 字符串

'name': 'p\xc7\x9d\xca\x87\xc9\x9f\xc4\xb1\xc9\xa5s Badge'

这是配置问题吗？如果是这样，我该如何解决？我想用 Unicode 来处理一切（我想）。

原文

I'm seeing

OperationalError (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")

It looks like some of my variables are UTF8 strings

'name': 'p\xc7\x9d\xca\x87\xc9\x9f\xc4\xb1\xc9\xa5s Badge'

Is this a configuration issue? If so, how can i solve it? I'd like to handle everything in Unicode (I think).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花心好男孩 2024-08-01 13:25:00

您可以通过 shell 更改表编码：

$ manage.py shell
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute('SHOW TABLES')
>>> results=[]
>>> for row in cursor.fetchall(): results.append(row)
>>> for row in results: cursor.execute('ALTER TABLE %s CONVERT TO CHARACTER SET utf8 COLLATE     utf8_general_ci;' % (row[0]))

https://mayan.readthedocs .org/en/v0.13/faq/index.html

You can change the table encoding via the shell:

$ manage.py shell
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute('SHOW TABLES')
>>> results=[]
>>> for row in cursor.fetchall(): results.append(row)
>>> for row in results: cursor.execute('ALTER TABLE %s CONVERT TO CHARACTER SET utf8 COLLATE     utf8_general_ci;' % (row[0]))

https://mayan.readthedocs.org/en/v0.13/faq/index.html

回复收藏 0 原文

吻安 2024-08-01 13:25:00

您的数据库似乎默认为 latin1_swedish_ci，因此无法接受所有 utf8 字符。您需要更改 MySQL 数据库表的配置以使用 utf8_general_ci。关于此的一篇很好的博文（带有工具的链接）可以在找到MySQL 性能博客

回复收藏 0 原文

雄赳赳气昂昂 2024-08-01 13:25:00

您将在 django 设置文件中添加选项，如下所示：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {'charset': 'utf8mb4'},
        'NAME': 'sarpanchDb',
        'USER': 'root',
        'PASSWORD': 'tiger',
        'HOST': 'localhost',
        'PORT': '',
    },
}

您还需要在 /etc/mysql/my.cnf 文件中进行更改

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

然后重新启动 mysql 服务

sudo service mysql restart

使用以下查询交叉检查它是否有效

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR 
Variable_name LIKE 'collation%';

您应该得到以下输出

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

You will add OPTIONS in django settings file like below:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {'charset': 'utf8mb4'},
        'NAME': 'sarpanchDb',
        'USER': 'root',
        'PASSWORD': 'tiger',
        'HOST': 'localhost',
        'PORT': '',
    },
}

Also you will need to do change in /etc/mysql/my.cnf file

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

Then restart mysql service

sudo service mysql restart

Cross check it worked or not using following query

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR 
Variable_name LIKE 'collation%';

You should get following output

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

回复收藏 0 原文

~没有更多了~