We found Illegal mix of collations (latin1_swedish_ci, IMPLICIT) and (utf8_general_ci, COER error prompt when using java and mysql Databases today. Let's look at the solution below.
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 '=' is this 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.
The Code is as follows: |
Copy code |
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/assets/mysql/share/mysql/charsets | |
+ -------------------------- + ---------------------------- +
Use show variables like 'collation _ % '.
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 | + ---------------------- + ------------------- + |
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:
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; -- 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:
The Code is as follows: |
Copy code |
[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)
The Code is as follows: |
Copy code |
[Mysqld] Datadir =/var/lib/mysql Socket =/var/lib/mysql. sock Default-character-set = utf8 |
(The key sentence is added so that the database is stored in utf8 by default)
After modification, restart the database. (This setting takes effect for the newly created database table)
The Code is as follows: |
Copy code |
Run the 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/assets/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.
Use the following command to log on: mysql -- default-character-set = utf8-u root-p
Run the show variables like 'character _ set _ % 'command again:
The 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/assets/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:
The Code is as follows: |
Copy code |
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:
The Code is as follows: |
Copy code |
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.