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

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

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

#! /usr/bin/env python
import MySQLdb
host='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'" % dbname
cursor.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)