mysql h 字符集_#MySQL问题--字符集问题

问题描述：执行SQl语句

SQL：select * from searchdicts  where word = '㒰桂平';

异常：java.sql.SQLException: Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

原因：字符集问题，两表编码方式不一致。

查看字符集：

mysql> show variables like 'char%';

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

| Variable_name | Value |

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

| character_set_client | gbk |

| character_set_connection | gbk |

| character_set_database | gbk |

| character_set_filesystem | binary |

| character_set_results | gbk |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /Data/apps/mysql/share/charsets/ |

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

8 rows in set (0.01 sec)

解决：查询SQL过程中进行字符转换

设置字符集;set names 'gbk';

select * from searchdicts where word = '㒰桂平' collate utf8mb4_general_ci;

模式：

select * from table_a a left join table_b b on CONVERT(a.field_a USING utf8) COLLATE utf8_unicode_ci = b.field_b

备注：utf8mb4_general_ci字符集是包含gbk_chinese_ci，属于范围更广的字符集。

select * from searchdicts where word = '仝桂平';

select * from searchdicts where word = '㒰桂平';

影响：查询时间会受损。

select * from searchdicts where word = '孟卫平';

耗时：0.006s

select * from searchdicts where word = '孟卫平' collate utf8mb4_general_ci;

耗时：0.886s