Django admin 保存unicode报错 <MySQL“incorrect string alue” error > 解决办法

来源：互联网发布：刘欢和孙楠知乎编辑：程序博客网时间：2024/06/05 18:19

如题，原因是创建数据库和表的时候默认字符集设置的问题，修改即可。

下面是批量修改数据库所有表的列的默认字符集 Python 脚本

#! /usr/bin/env pythonimport MySQLdbhost='localhost'password='your passwd'user='your user name'dbname='your database naem'db=MySQLdb.connect(host=host,user=user,passwd=password,db=dbname)cursor=db.cursor()cursor.execute("ALTER DATABASE `%s` CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'" % dbname)sql = "SELECT DISTINCT(table_name) FROM information_schema.columns WHERE table_schema = '%s'" % dbnamecursor.execute(sql)results=cursor.fetchall()for row in results:#print row[0]sql="ALTER TABLE %s convert to character set DEFAULT COLLATE DEFAULT " % (row[0])cursor.execute(sql)db.close()

python 运行即可。

MySQL 版本为 5.5,其他版本没有试。

打开MySQL 可以查看修改后的表的字符集

mysql> SELECT T.table_name, T.table_collation, CCSA.character_set_name FROM information_schema.`TABLES` T,    -> information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA    -> WHERE CCSA.collation_name = T.table_collation    -> AND T.table_schema = "databasename";+----------------------------+-----------------+--------------------+| table_name                 | table_collation | character_set_name |+----------------------------+-----------------+--------------------+| auth_group_permissions     | utf8_unicode_ci | utf8               || auth_permission            | utf8_unicode_ci | utf8               || auth_user                  | utf8_unicode_ci | utf8               || auth_user_groups           | utf8_unicode_ci | utf8               || auth_user_user_permissions | utf8_unicode_ci | utf8               || table_name1                | utf8_unicode_ci | utf8               || table_name2                | utf8_unicode_ci | utf8               || table_name3                | utf8_unicode_ci | utf8               || table_name4                | utf8_unicode_ci | utf8               || django_admin_log           | utf8_unicode_ci | utf8               || django_content_type        | utf8_unicode_ci | utf8               || django_session             | utf8_unicode_ci | utf8               |+----------------------------+-----------------+--------------------+14 rows in set (0.03 sec)

0 0