java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_c

比眉伴天荒 2023-03-12 09:19 52阅读 0赞

## java.sql.SQLException: Illegal mix of collations (latin1\_swedish\_ci,IMPLICIT) and (utf8mb4\_general\_ci,COERCIBLE) for operation ‘=’ ##

在使用mybatis向数据库中插入数据时报此错误信息，通过报错信息可以看到是字符编码混淆。

<insert id="addEmp" parameterType="com.mytest.mybatis.entity.Employee">
        insert into tbl_employee ( last_name, email, gender, age) values(#{ lastName},#{ email},#{ gender},#{ age})
    </insert>

### 解决办法 ###

1、修改表的编码

ALTER TABLE tbl_employee(表名) CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

2、查看表的编码信息

show variables like 'character_set_%';
    # 正常情况
    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
    # 如果不相同则使用如下命令修改
    set character_set_client = 'utf8'
    set character_set_connection = 'utf8'
    set character_set_database = 'utf8'
    set character_set_results = 'utf8'
    set character_set_server = 'utf8'

3、查看collation排序规则

show variables like 'collation_%';
    # 正常情况
    collation_connection utf8_general_ci
    collation_database utf8_general_ci
    collation_server utf8_general_ci
    # 如果修改则使用如下命令
    set collation_database='utf8_general_ci';
    SET collation_connection = 'utf8_general_ci'; 
    SET collation_server = 'utf8_general_ci'; 
    或
    SET collation_database = 'latin1_swedish_ci'; 
    SET collation_connection = 'latin1_swedish_ci'; 
    SET collation_server = 'latin1_swedish_ci';

进行修改后可以正常向数据库插入数据。  
参考：https://blog.csdn.net/weixin\_43564627/article/details/94638842

java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_c

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=

相关【MySQL】报错1267 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPL

相关 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation

相关 java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_c

相关报错：1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT)

相关解决Illegal mix of collations (utf8eneral_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operatio

相关 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '

相关 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE)

相关 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=

相关解决Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COER