MySQL“不正确的字符串值"在 Django 中保存 unicode 字符串时出错 [英] MySQL "incorrect string value" error when save unicode string in Django

问题描述

在尝试将 first_name、last_name 保存到 Django 的 auth_user 模型时，我收到了奇怪的错误消息.

I got strange error message when tried to save first_name, last_name to Django's auth_user model.

失败的例子

user = User.object.create_user(username, email, password)
user.first_name = u'Rytis'
user.last_name = u'Slatkevičius'
user.save()
>>> Incorrect string value: '\xC4\x8Dius' for column 'last_name' at row 104

user.first_name = u'Валерий'
user.last_name = u'Богданов'
user.save()
>>> Incorrect string value: '\xD0\x92\xD0\xB0\xD0\xBB...' for column 'first_name' at row 104

user.first_name = u'Krzysztof'
user.last_name = u'Szukiełojć'
user.save()
>>> Incorrect string value: '\xC5\x82oj\xC4\x87' for column 'last_name' at row 104

成功案例

user.first_name = u'Marcin'
user.last_name = u'Król'
user.save()
>>> SUCCEED

MySQL 设置

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| 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)

表格字符集和整理

表 auth_user 具有 utf-8 字符集和 utf8_general_ci 排序规则.

Table auth_user has utf-8 charset with utf8_general_ci collation.

UPDATE 命令的结果

使用 UPDATE 命令将上述值更新到 auth_user 表时没有引发任何错误.

It didn't raise any error when updating above values to auth_user table by using UPDATE command.

mysql> update auth_user set last_name='Slatkevičiusa' where id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select last_name from auth_user where id=100;
+---------------+
| last_name     |
+---------------+
| Slatkevi?iusa | 
+---------------+
1 row in set (0.00 sec)

PostgreSQL

当我在 Django 中切换数据库后端时，上面列出的失败值可以更新到 PostgreSQL 表中.很奇怪.

The failed values listed above can be updated into PostgreSQL table when I switched the database backend in Django. It's strange.

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
...
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 | 
...

但是来自 http://www.postgresql.org/docs/8.1/Interactive/multibyte.html，我发现了以下内容:

But from http://www.postgresql.org/docs/8.1/interactive/multibyte.html, I found the following:

Name Bytes/Char
UTF8 1-4

这是否意味着 unicode char 在 PostgreSQL 中有 4 个字节的 maxlen 而在 MySQL 中有 3 个字节导致上述错误?

Is it means unicode char has maxlen of 4 bytes in PostgreSQL but 3 bytes in MySQL which caused above error?

推荐答案

我刚刚想出了一种避免上述错误的方法.

I just figured out one method to avoid above errors.

保存到数据库

user.first_name = u'Rytis'.encode('unicode_escape')
user.last_name = u'Slatkevičius'.encode('unicode_escape')
user.save()
>>> SUCCEED

print user.last_name
>>> Slatkevi\u010dius
print user.last_name.decode('unicode_escape')
>>> Slatkevičius

这是将这样的字符串保存到 MySQL 表中并在渲染到模板以供显示之前对其进行解码的唯一方法吗?

Is this the only method to save strings like that into a MySQL table and decode it before rendering to templates for display?

这篇关于MySQL“不正确的字符串值"在 Django 中保存 unicode 字符串时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！