MySQL问题字符集问题

  • Z时代
  • 2024-01-10
  • 分类：综合

编程

 问题描述：执行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 = "㒰桂平";

 

 

 

 

以上是 MySQL问题字符集问题 的全部内容， 来源链接： utcz.com/z/517139.html

回到顶部