在mysql查询中修复非法混合排列错误的教程

这是一个简短的教程，介绍了如何在mysql中修复一个错误 非法混合排列的问题。

当你在使用选择、插入、更新和存储过程查询时，如果为数据库/表/列定义了encoding 和collation ，而数据以不同的encoding/collation 发送，就会出现这个错误。

例如，encoding 和collation 表和列为UTF8，而插入的数据为希腊语编码值。

解决方案是在数据库的所有表和mysql系统的所有列中保持相同的编码和排序。

让我们来看看这个错误重现的一个例子

在我的应用程序中，其中一个表在插入数据时出现了错误。

我的数据库、表和列的定义是

charset : utf8 collation: utf8\_general\_ci

我有一个雇员表，有以下字段和数据插入其中

I used the below query with data 

select * from employee where name ='Test 🐟 user'

In the above WHERE condition, check the name with some Unicode mix characters.

I got an  error in the application logs
ERROR http-nio-80-exec-36 [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_gene
ral_ci,COERCIBLE) for operation '='




There are multiple ways we can fix this error 
## Solution and Fix for Illegal mix of collations 

Here is the sequence of steps and approaches to solve an error
-  use alter to modify column encoding and collate


First, check are existing options for encoding.

show variables WHERE variable_name like "col%";


Output


If columns are giving an error change column encoding and collate using `alter` query

Here is a query to change collation and encoding for column

ALTER TABLE .employee MODIFY COLUMN name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;



You can change character `utf8mb4` and collation to  `utf8mb4_unicode_ci`. This is not fixed, you can change it as per your character Unicode values.

Changing encoding and collation at the table using the below command

ALTER TABLE employee CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

To change the database encoding and collation

ALTER DATABASE empdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci



- Always use the latest database driver

if this error comes in java or any application, Always use the latest drivers.

Here is a database connection to change the collation.

SET collation_connection = 'utf8_general_ci'



- Global change a MySQL instance


This applies to all databases in the MySQL instance.

updated in mysql.conf file
```mysql
[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

接下来，重启mysql实例以使其生效。

结论

当你处理不同字符集的数据时，非法混合排序是在应用程序级别或mysql客户端发生的错误。

了解了修复这个错误的方法。