Resolve illegal mix of collations (latin1_swedish_ci,implicit) and (utf8_general_ci,coer

After the deployment of the project, test, eh, the data came out not much trouble (dark sigh). Continue to test, a little new finished, error, see what wrong

Once you've seen Java.sql.SQLException:Illegal mix of collations (latin1_swedish_ci,implicit)

and (utf8_general_ci,coercible) for operation ' = ' Is this error, and for what reason, first encountered.

The head is big. Go to the documentation: there are two sets of characters in the result set. I faint, how can this be, look at the table structure, a kind of ah. Go ahead and check it out.

code is as follows

copy code

Show VARIABLES like ' character_set_% '; Check the display +--------------------------+----------------------------+ | 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 |

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

Then use show VARIABLES like ' collation_% '; Check the display

The code is as follows	Copy Code
+----------------------+-------------------+ | variable_name | Value | +----------------------+-------------------+ | collation_connection | Utf8_swedish_ci | | Collation_database | Latin1_swedish_ci | | Collation_server | Latin1_swedish_ci | +----------------------+-------------------+

I know what's wrong and what's left is good.

Workaround:

Execute sequentially:

The code is as follows	Copy Code
Set Character_set_database =utf8; Set Character_set_results =utf8; Set Character_set_server =utf8; Set Character_set_system =utf8; --Here utf-8 can also Then execute: SET Collation_server = Utf8_general_ci SET collation_database = Utf8_general_ci

After the execution, please check the MySQL under each database, table, fields are UTF8, is not changed to come, this way will not appear

The dumbest way to do this is to reload the database. (generally do not use this method AH)

Final Solution:

1.1 If it is a version of MySQL in Windows, then the system will be prompted to use which encoding when installed.

If Setup is wrong, modify the My.ini file under the MySQL installation directory:

The code is as follows	Copy Code
[MySQL] Default-character-set=utf8 ... # The default character set that'll be used when a new schema or table is # created and no character set is defined Default-character-set=utf8

When configured, restart MySQL.

1.2 If Linux version of MySQL

Modify the MySQL configuration file so that the database is consistent with the character set of the server's operating system.

VI/ETC/MY.CNF settings (create 1 if no file is found)

The code is as follows	Copy Code
[Mysqld] Datadir=/var/lib/mysql Socket=/var/lib/mysql/mysql.sock Default-character-set=utf8

(added key sentence, make database default to UTF8 storage)

Of course, after you modify it, restart the database. (This setting does not work for a newly created database table)

The code is as follows

Copy Code

Use show VARIABLES like ' character_set_% '; command to see the following: +--------------------------+-----------------------------------------------------------------------+ | variable_name | Value | +--------------------------+-----------------------------------------------------------------------+ | character_set_client | utf8| | character_set_connection | utf8| | |character_set_database |utf8 | | Character_set_filesystem | binary | | Character_set_results | utf8| | Character_set_server | UTF8 | | Character_set_system | UTF8 | | Character_sets_dir | /home/jh/mysql/share/mysql/charsets | +--------------------------+-----------------------------------------------------------------------+

found that the key project has been used UTF8, but this is not enough, but also to ensure that the client is also used UTF8 character set to operate.

When you log in, use the following command: MySQL--default-character-set=utf8-u root-p

Again with show VARIABLES like ' character_set_% '; command view, the result becomes:

code is as follows

copy code

+--------------------------+-------------------------------------------- ---------------------------+ | variable_name | value | +--------------------------+-----------------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir |/home/jh/mysql/share/mysql/charsets/|

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

This ensures that the client-issued commands are based on the UTF8 format, such as database and tables, and by default they are encoded in UTF8 without having to be specified again. (Once again, it works for a new database and table).

Other than that:

The third way: see on the net, first record.

1. If the installation of MySQL code can not be changed, many friends are the purchase of virtual host website, no right to change the installation of MySQL code, this can be skipped, because as long as the back of the step is correct, the same can solve the garbled problem
2. Modify the database encoding, if the database encoding is incorrect: You can execute the following command in phpMyAdmin: ALTER database ' test ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin
The above command is to set the test database encoding to UTF8
3. Modify the encoding of the table:

The code is as follows	Copy Code
ALTER TABLE ' category ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin

The above command is to change the encoding of a table category to UTF8
4. Modify the encoding of the field:

The code is as follows	Copy Code
ALTER TABLE ' test ' change ' dd ' DD ' VARCHAR (a) CHARACTER SET UTF8 COLLATE utf8_bin not NULL

The above command is to change the field encoding of DD in the test table to UTF8
5. If this situation is easy to solve, just check the page, modify the source file CharSet can be
,//This right is no problem.
6. This situation is also to modify the page charset can