Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation ‘=’

| username: breakyang

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] v7.1.1
[Reproduction Path] Using Mybatis, using where in (#{txIds}), the charset encoding of the JDBC connection string is UTF-8, the table definition is the default utf8mb4, and the collation is also the default utf8mb4_bin
[Encountered Problem] A query statement error:

### Cause: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

The direct cause of this problem is the incorrect use of Mybatis list dynamic parameters. However, there are a few doubts here:

  1. Why is the error message for operation ‘=’ when I am clearly using ‘in’?
  2. My table definition is the default utf8mb4, and the collation is also the default utf8mb4_bin, but the error message shows utf8_general_ci and utf8mb4_general_ci
    [Attachment: Screenshot/Log/Monitoring]
| username: xfworld | Original post link

Could you provide the structure and SQL statement?

It would be best if you could also provide the explain SQL.

| username: Kongdom | Original post link

Does this issue still occur after addressing the root cause?

| username: zhanggame1 | Original post link

Capture the actual executed SQL.

| username: Fly-bird | Original post link

Post the SQL statement and let’s take a look.

| username: 像风一样的男子 | Original post link

Could you please share the SQL?

| username: TiDBer_小阿飞 | Original post link

What is the query statement?

| username: tidb菜鸟一只 | Original post link

Could you provide the SQL and table creation statements? This is simply a character set conversion issue.

| username: breakyang | Original post link

I am ending this topic because our group’s testing has changed the database character set and collation of the test environment to utf8mb3. Thank you all for your hard work.

| username: Kongdom | Original post link

:yum: It turned out to be a misunderstanding, remember to mark the best answer yourself.

| username: zhanggame1 | Original post link

It turned out to be a misunderstanding.

| username: breakyang | Original post link

:sweat_smile::sweat_smile::sweat_smile:

| username: ShawnYan | Original post link

btw, from TiDB 7.2.0, it supports specifying the utf8mb3 character set

| username: Kongdom | Original post link

Reasonable and well-founded :+1: :+1: :+1:

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.