解决 Mysql Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

mysql根据中文条件查询时，报如标题的异常，其本质是：数据库出现了两种字符集。

 

SHOW VARIABLES LIKE 'character_set_%'; 

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8|

| character_set_connection | utf8|

| character_set_database | latin1 |

| character_set_results | utf8|

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /home/jh/mysql/share/mysql/charsets

 

 SHOW VARIABLES LIKE 'collation_%'; 

 | Variable_name | Value |

+----------------------+-------------------+

| collation_connection | utf8_swedish_ci |

| collation_database | latin1_swedish_ci |

| collation_server | latin1_swedish_ci |

 

解决办法：

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;

 

这里有个情况需要注意，最好同时查看一下表的字符编码，确保与库的编码一致，如果不一致可以通过下面的命令修改：

alter table `表名` charset=utf8;
alter table `表名` convert to character set utf8;