mysql查询报错了：Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Question

问题描述：

Error querying database. Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
The error may exist in class path resource [mapper/QuestionMethodMapper.xml]
The error may involve com.wekj.monkey.mapper.QuestionMethodMapper.selectByQuestion-Inline
The error occurred while setting parameters
SQL: select id, question, industry_ids, industry_names, method_id, method_name, flag, features, gmt_create, gmt_modified, status from question_method where question = ?
Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

sql语句：

  <select id="selectByQuestion" resultMap="BaseResultMap" parameterType="String" >
    select
    <include refid="Base_Column_List" />
    from question_method
    where question =#{question,jdbcType=VARCHAR}
  </select>

建表语句

CREATE TABLE `question_method` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `question` varchar(256) COLLATE utf8mb4_bin NOT NULL COMMENT '买家问题',
  `industry_ids` varchar(128) COLLATE utf8mb4_bin NOT NULL COMMENT '行业列表ID',
  `industry_names` varchar(256) COLLATE utf8mb4_bin NOT NULL COMMENT '行业列表名字',
  `method_id` bigint(11) NOT NULL COMMENT '问法ID',
  `method_name` varchar(4096) COLLATE utf8mb4_bin NOT NULL COMMENT '问法名字',
  `flag` bigint(20) DEFAULT '0' COMMENT '按位打标签',
  `features` varchar(10240) COLLATE utf8mb4_bin DEFAULT '' COMMENT '通用扩展存储',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `status` int(11) NOT NULL DEFAULT '0' COMMENT ' 是否删除(0:否,1:是)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=521 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT COMMENT='问题对应问法表';

求解：不知道为什么会在生产环境上报错，本地postman请求不会报错的，传的参数是

Answer 1

1. 基本是库编码和表编码不一致造成，以及jdbc字符版本驱动问题，像带有表情字段存储做好做法是进行base_encode64 存储。