MYSQL character set ERROR: ERROR 1267 (HY000): Illegal mix of... solved

MYSQL character set ERROR: ERROR 1267 (HY000): Illegal mix... MYSQL Character Set: 1. mysql fields, tables, databases, database connections, database return results, and so on all have default character sets. The default character set can be set at the beginning of the table creation, and some can also be set by default. However, you may often forget to set character sets. When you connect to the database, return results, and store data, character sets may be different, so Character Set problems may occur. For example, it is common to store Chinese characters and read out garbled characters. How to view the character set of a database:

01mysql> show VARIABLES LIKE 'character_set_%' ;02+--------------------------+--------------------------------------+03| Variable_name            | Value                                |04+--------------------------+--------------------------------------+05| character_set_client     | latin1                               |06| character_set_connection | latin1                               |07| character_set_database   | utf8                                 |08| character_set_filesystem | binary                               |09| character_set_results    | latin1                               |10| character_set_server     | latin1                               |11| character_set_system     | utf8                                 |12| character_sets_dir       | /Apps/mysql5.1/share/mysql/charsets/ |13+--------------------------+--------------------------------------+1<span></span> mysql> SHOW VARIABLES LIKE 'collation_%';2+----------------------+-------------------+3| Variable_name        | Value             |4+----------------------+-------------------+5| collation_connection | latin1_swedish_ci |6| collation_database   | utf8_general_ci   |7| collation_server     | latin1_swedish_ci |8+----------------------+-------------------+93 rows in set (0.00 sec)

 

2. Problem: If the table is created using the latin character set by default, the field in the table is latin by default. If PHP is connected using the utf8 method, mysql will automatically save it as the latin character set. If it is queried, if the latin field contains Chinese characters by utf8 connection, an error similar to the following will be returned: first look at the character set of the table field:

1mysql> show create table t_search_str \G2*************************** 1. row ***************************3       Table: t_search_str4Create Table: CREATE TABLE `t_foo` (5  `str` varchar(100) CHARACTER SET latin1 NOT NULL DEFAULT '',6) ENGINE=MyISAM DEFAULT CHARSET=utf871 row in set (0.00 sec)

 

Obviously, the database character set is inconsistent with the field character set, which is the root cause. For example, in utf8 mode, the following error is returned:

Mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t_foo WHERE 'str' = 'test'; ERROR 1267 (HY000 ): illegal mix of collations (latin1_swedish_ci, IMPLICIT) and (utf8_general_ci, COERCIBLE) for operation '='

 

3. Solution: Set the character set and connection character of the field to a left Match and modify it. (In short, make sure the character set and connection character are consistent ):

1alter table t_foo change `str` `str` varchar(100) character set utf8 not null ;

 

Try again:

1 mysql> select count (*) AS ct FROM t_foo WHERE 'str' = 'test '; 2 + ---- + 3 | ct | 4 + ---- + 5 | 0 | 6 + ---- + 71 row in set (0.00 sec)

 

In this way, the fields are consistent with the character set of the database.