Illegal mix of collations (latin1_swedish_ci, implicit) and (utf8_general_ci, coer

After the project is deployed, test it. Well, there is not much problem with the data coming out ). Continue the test. After a new one is created, an error is reported to check the error.

 

I have finished reading java. SQL. sqlexception: Illegal mix of collations (latin1_swedish_ci, implicit)

 

and (utf8_general_ci,COERCIBLE) for operation '=' This is the error. Why? First time.

 

The head is big. The query document says: There are two character sets in the result set. I'm dizzy. How can this happen? Look at the table structure. Continue to check.

 

Use show variables like'character_set_%'; View 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 | +--------------------------+----------------------------+ Use show variables like'collation_%'; View the display+----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ It turns out this way. Haha, you know where the error is. The rest is easy to do.Solution: Run the following commands in sequence:set character_set_database =utf8;  set character_set_results =utf8;  set character_set_server =utf8;  Set character_set_system = utf8; -- UTF-8 is also supported here. Then execute: SET collation_server = utf8_general_ci SET collation_database = utf8_general_ci  After the execution, check whether all the databases, tables, and fields in MySQL are utf8. If not, change them so that they do not appear. The most stupid way is to reload the database. (Do not use this method)Final Solution:1.1 if it is a Windows version of MySQL, the system will prompt which encoding to use during installation.    If the setting is incorrect during installation, modify the my. ini file in the MySQL installation directory:    [mysql]     default-character-set=utf8     ...     # The default character set that will be used when a new schema or table is     # created and no character set is defined     default-character-set=utf8     After configuration, restart MySQL.1.2 For MySQL of Linux     Modify the MySQL configuration file so that the character set settings of the database and the server operating system are consistent.    VI/etc/My. CNF settings (if this file is not found, create one)    [mysqld]     datadir=/var/lib/mysql     socket=/var/lib/mysql/mysql.sock     default-Character-set = utf8 (add a key sentence to store the database with utf8 by default)    After modification, restart the database. (This setting takes effect for the newly created database table)     Use show variables like'character_set_%'Command to view the following content:+--------------------------+-----------------------------------------------------------------------+ | 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 | +--------------------------+-----------------------------------------------------------------------+     It is found that utf8 has been used for key projects, but this is not enough. It is also necessary to ensure that the client uses the utf8 character set for operations.    When logging on, use the following command: MySQL --default-character-set=utf8 -u root -p         Use show variables like again'character_set_%'Command to view, the result is:+--------------------------+-----------------------------------------------------------------------+ | 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/ | +--------------------------+-----------------------------------------------------------------------+     In this way, the commands sent by the client are all in the utf8 format. For example, if you create a database and a table, utf8 encoding is used by default, and you do not need to specify it again. (Again, this statement applies to the newly created database and table ).

In addition:

Method 3: record what you see on the Internet.

1. if the encoding for installing MySQL cannot be changed, many of our friends purchase a virtual host to create a website and do not have the right to change the encoding for installing MySQL. We can skip this step as long as the subsequent steps are correct, the same solution can solve the garbled problem.
2. Modify the database encoding. If the database encoding is incorrect, run the following command in phpMyAdmin: Alter database 'test' default Character Set utf8 collate utf8_bin.
The preceding command sets the encoding of the test database to utf8.
3. Modify the table encoding: alter table 'category 'default Character Set utf8 collate utf8_bin
The preceding command changes the category encoding of a table to utf8.
4. Modify the field encoding:
Alter table 'test' change 'dd' dd' varchar (45) Character Set utf8 collate utf8_bin not null
The preceding command is to encode the DD field in the test table to utf8.
5. If this is the case, you only need to check the page and modify the charset of the source file.
, // This is correct and there is no problem
6. In this case, modify the charset page.