黄兵的个人博客

1366, "Incorrect string value: '\\xE9\\xBB\\x84\\xE5\\x85\\xB5' for column 'fullname' at row 1"

最近使用SQLALchemy的时候，出现了如下错误：

sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (_mysql_exceptions.OperationalError) (1366, "Incorrect string value: '\\xE9\\xBB\\x84\\xE5\\x85\\xB5' for column 'fullname' at row 1") [SQL: 'INSERT INTO users (email, username, fullname, address, province_region_id, city_region_id, role_id, password_hash, confirmed, name, location, about_me, member_since, last_seen, avatar_hash) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: ('huang_9119596@163.com', 'huang_huang118', '黄兵', '上海路大美盛城', '9370', '9394', None, 'pbkdf2:sha256:50000$MK1ryXVu$6451e3c17d506e04b2074cf3dd5d5ea36b82f34efdc8e8d036616f9518993866', 0, None, None, None, datetime.datetime(2018, 7, 18, 2, 20, 59, 779650), datetime.datetime(2018, 7, 18, 2, 20, 59, 779650), '039465ee32ad51ab0433e25e845c7193')] (Background on this error at: http://sqlalche.me/e/e3q8)

出现这个问题的原因是：

数据库编码问题。

解决方案：

首先查看一下字段的编码：

show full columns from users;

这里出错的是users表，所以需要看一下users表。

这里出问题的是fullname字段，其他字段也都会存储中文，所以都需要改变。

执行如下语句：

alter table users change fullname fullname varchar(64) character set utf8 collate utf8_unicode_ci  null ;
alter table users change address address varchar(64) character set utf8 collate utf8_unicode_ci  null ;
alter table users change name name varchar(64) character set utf8 collate utf8_unicode_ci  null ;
alter table users change username username varchar(64) character set utf8 collate utf8_unicode_ci  null ;
alter table users change about_me about_me text character set utf8 collate utf8_unicode_ci  null ;

之后再次查看一下编码：

show full columns from users;

如果遇到外键，报如下错误：

Cannot change column 'xxx': used in a foreign key constraint 'xxxx' of table 'xxxx'

可以参考这篇文章：Cannot change column 'b_name': used in a foreign key constraint 'material_product_ibfk_1' of table 'Material_Development.material_product'

参考资料：mysql 错误 SQL Error: 1366: Incorrect string value: "\xE8\xAF\xA6\xE7\xBB\x86…" for column "address" a

黄兵个人博客原创。

转载请注明出处：黄兵个人博客 - 1366, "Incorrect string value: '\\xE9\\xBB\\x84\\xE5\\x85\\xB5' for column 'fullname' at row 1"