简介
今天在排查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.csdn.net/world_zheng/article/details/83110029