MySQL“不正确的字符串值”在Django中保存unicode字符串时出错 [英] MySQL "incorrect string value" error when save unicode string in Django
问题描述
失败的示例
$ b我尝试将first_name，last_name保存到Django的auth_user模型时出现了奇怪的错误消息$ b
user = User.object.create_user（用户名，电子邮件，密码）
user.first_name = u'Rytis'
用户。 last_name =u'Slatkevičius'
user.save（）
>>>字符串值不正确：第104行列'last_name'的'\xC4\x8Dius'
user.first_name =u'Валерий'
user.last_name =u'Богданов'
user.save（）
>>>字符串值不正确：第104行的列first_name的'\xD0\x92\xD0\xB0\xD0\xBB ...'
user.first_name = u'Krzysztof '
user.last_name =u'Szukiełojć'
user.save（）
>>>字符串值不正确：第104行列'last_name'的'\xC5\x82oj\xC4\x87'
成功例子
user.first_name = u'Marcin'
user.last_name =u'Król'
user.save（）
>>> SUCCEED
MySQL设置
mysql>显示变量，如char％;
+ -------------------------- + ------------------ ---------- +
| Variable_name |价值|
+ -------------------------- + ------------------ ---------- +
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem |二进制|
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | / usr / share / mysql / charsets / |
+ -------------------------- + ------------------ ---------- +
8行（0.00秒）
表字符集和排序规则
表auth_user具有utf8_general_ci排序规则的utf-8字符集。
UPDATE命令的结果
使用UPDATE命令将上述值更新为auth_user表时，没有引发任何错误。 / p>
mysql>更新auth_user set last_name ='Slatkevičiusa'其中id = 1;
查询确定，1行受影响，1警告（0.00秒）
匹配的行：1已更改：1警告：0
mysql>从auth_user中选择last_name，其中id = 100;
+ --------------- +
| last_name |
+ --------------- +
| Slatkevi？iusa |
+ --------------- +
1行（0.00秒）
PostgreSQL
上面列出的失败的值可以更新为PostgreSQL表，当我切换数据库后端Django。这很奇怪。
mysql> SHOW CHARACTER SET;
+ ---------- + ----------------------------- + ---- ----------------- + -------- +
|字符集|说明|默认排序规则| Maxlen |
+ ---------- + ----------------------------- + ---- ----------------- + -------- +
...
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
...
但是从 http://www.postgresql.org/docs/8.1/interactive/multibyte.html ，我发现如下： p>
名称字节/字符
UTF8 1-4
这是否意味着unicode char在PostgreSQL中具有4个字节的maxlen，而MySQL中的3个字节导致上述错误？
我只是想出了一种避免上述错误的方法。
保存到数据库
user.first_name = u'Rytis'.encode（'unicode_escape'）
user.last_name =u'Slatkevičius'.encode 'unicode_escape'）
user.save（）
>>> SUCCEED
打印user.last_name
>>> slatkevi\\\čius
print user.last_name.decode（'unicode_escape'）
>>> Slatkevičius
这是将这样的字符串保存到MySQL表中的唯一方法，并在渲染之前对其进行解码显示模板？
I got strange error message when tried to save first_name, last_name to Django's auth_user model.
Failed examples
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
Succeed examples
user.first_name = u'Marcin'
user.last_name = u'Król'
user.save()
>>> SUCCEED
MySQL settings
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)
Table charset and collation
Table auth_user has utf-8 charset with utf8_general_ci collation.
Results of UPDATE command
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
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 |
...
But from http://www.postgresql.org/docs/8.1/interactive/multibyte.html, I found the following:
Name Bytes/Char
UTF8 1-4
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.
Save to database
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
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屋！
