MySQL Hint: illegal mix of collations for operation ' UNION '

The statement is simple

The code is as follows

Copy Code

<?php $connection =mysql_connect ("localhost", "root", "password"); Connect and select a database server mysql_select_db ("Test", $connection); $query = "SELECT * from users where name= ' Hello '"; $result =mysql_query ($query, $connection); while ($row =mysql_fetch_array ($result)) { echo "User id:". $row [0]. " <br> "; can also write $row["user_id"] echo "Username:". $row [1]. " <br> "; can also write $row["user_name"] } ?>

Run Results prompt

Error:illegal mix of collations (gbk_chinese_ci,implicit) and (latin1_swedish_ci,coercible) for operation ' = '

It means: the operator "=" compares the character set inconsistency, one is gbk_chinese_ci, the other is latin1_swedish_ci.

First, view the MySQL my.cnf, confirmed as UTF8

The code is as follows

Copy Code

Mysql> Show variables like "%char%"; +--------------------------+-------------------------------------+ | variable_name | Value | +--------------------------+-------------------------------------+ | character_set_client | UTF8 | | character_set_connection | UTF8 | | Character_set_database | Latin1 | | Character_set_filesystem | binary | | Character_set_results | UTF8 | | Character_set_server | UTF8 | | Character_set_system | UTF8 | | Character_sets_dir | D:/mysql Server 5.0/share/charsets/| +--------------------------+-------------------------------------+ 8 rows in Set (0.00 sec) Mysql> Show variables like "%coll%"; +----------------------+-------------------+ | variable_name | Value | +----------------------+-------------------+ | collation_connection | Utf8_general_ci | | Collation_database | Latin1_swedish_ci | | Collation_server | Utf8_general_ci | +----------------------+-------------------+ 3 Rows in Set (0.00 sec)

Then change all encodings in the database to uniform:

The code is as follows	Copy Code
Mysql> set Character_set_database=utf8; Query OK, 0 rows Affected (0.00 sec) Mysql> Set collation_database= utf8_general_ci

Re-view character set and data table field encoding characters are consistent:

Summary

Utf8_bin
Utf8_general_ci
Utf8_unicode_ci

Utf8_bin and Utf8_general_ci can

Utf8_bin and Utf8_unicode_ci can

Utf8_general_ci and Utf8_unicode_ci not allowed