Mysql字符编码导致的错误：Illegal mix of collations (latin1_swedish_ci) and (utf8_general_ci)

最新推荐文章于 2025-03-18 20:30:00 发布

原创最新推荐文章于 2025-03-18 20:30:00 发布 · 645 阅读

1 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

3 篇文章

订阅专栏

本文档描述了在排查BUG时遇到的MySQL字符集问题，错误为ER_CANT_AGGREGATE_2COLLATIONS，原因是latin1_swedish_ci和utf8_general_ci之间的不匹配。作者提供了检查MySQL字符集的SQL语句，并展示了一个shell脚本，用于显示数据库信息和字符集设置。脚本中还包含了如何修改数据库默认字符集的建议。最后，运行脚本的结果证实了字符集的查看和修改过程。

简介

今天在排查BUG的时候，dev环境和production环境代码一致，sql语句也没什么问题，
查看日志发现出现了Mysql字符集的问题

Error: ER_CANT_AGGREGATE_2COLLATIONS: 
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'

查看Mysql字符集

show variables like ‘%character%’;
show variables like ‘collation%’;
查看数据库
show create database ${DB_NAME}

EOF简介

我自己的理解为执行/bin/mysql 连接命令前给的语句信息，连接上后Mysql后，马上执行SQL语句，执行完后，EOF把mysql-client的整个进程都退出了

编写脚本

直接写一个shell脚本吧

#! /bin/bash

DB_HOST=XXX
DB_PORT=XXX
DB_USER=XXX
DB_PASSWORD=XXX
DB_NAME=XXX

/usr/local/bin/mysql -h${DB_HOST} -p${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} <<EOF 2>/dev/null
-- show databases;
-- use ${DB_NAME};
-- show variables like '%character%';
-- show variables like 'collation%';
-- show charset;
show create database ${DB_NAME}
EOF

# -e 允许后面的字符进行转义
echo -e  "\n
if you want to change mysql charsets:\n 
please execute: \n
  alter database ${DB_NAME} default character set utf8; \n"

/usr/local/bin/mysql -h${DB_HOST} -p${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME}

运行结果

./sky_prd_mysql.sh
Database	Create Database
aiov_db	CREATE DATABASE `XXX` /*!40100 DEFAULT CHARACTER SET utf8 */


if you want to change mysql charsets:

please execute: 
  alter database XXX default character set utf8;

#mysql: [Warning] Using a password on the command line interface can be insecure.
#Reading table information for completion of table and column names
#You can turn off this feature to get a quicker startup with -A

#Welcome to the MySQL monitor.  Commands end with ; or \g.
#Your MySQL connection id is 45954
#Server version: 5.6.10 MySQL Community Server (GPL)

#Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

#Oracle is a registered trademark of Oracle Corporation and/or its
#affiliates. Other names may be trademarks of their respective
#owners.

#Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

修改MYSQL 字符集

alter database ${DB_NAME} default character set utf8;

参考资料

查看字符集参考：https://www.cnblogs.com/yangmingxianshen/p/7999428.html
MYSQL和Shell脚本：https://www.cnblogs.com/jiangxiaobo/p/9897041.html
EOF:https://blog.youkuaiyun.com/world_zheng/article/details/83110029