Illegal mix of collations latin1_swedish_ci,IMPLICIT and utf8_general_ci

在使用python storm的时候遇到如下错误：

  File "/usr/local/lib/python2.6/dist-packages/storm/database.py", line 371, in _check_disconnect
    return function(*args, **kwargs)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")

google了一下，有人说是字符集问题，解决方法如下。

1. 查看字符集设置

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+

2. 重设字符集

set character_set_database =utf8;
set character_set_results =utf8;
set character_set_server =utf8;
set character_set_system =utf8; --此处utf-8也可以

然后执行：

SET collation_server = utf8_general_ci
SET collation_database = utf8_general_ci

3. 修改mysql的配置文件

vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8

4. 更改已有数据库和表设置

//先修改数据库的字符集编码为：utf-8
alter database <dbname> character set utf8;

//修改该数据库下面的表的字符集编码为：utf-8
alter table <tablename> character set utf8;

//建库时设置字符集
CREATE DATABASE [DB NAME] CHARACTER SET utf8 COLLATE utf8_unicode_ci;

查出数据库级的字符集。
SHOW CREATE DATABASE db_name；

查出数据表的字符集。
SHOW CREATE TABLE table_name；
SHOW TABLE STATUS LIKE 'table_name'

查出数据列的字符集：
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;
SHOW CREATE TABLE table_name;