史上最全mysql的字符集字符集编码修改、设置方法（Illegalmixofcollations ，乱码问题）。

问题引出：由于mysql数据库的字符集设置不正确，导致很多的乱码、查询问题，如存储过程出现“Illegalmixofcollations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)foroperation ‘=,

中文乱码等问题，这里整理、收集、转载、实践了大多数的字符集设置修改的方法

my.cnf配置(mysql 5.6.35)
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysql]
prompt=”MySQL [\d]> ”
no-auto-rehash

[mysqld]
port = 3306
socket = /tmp/mysql.sock

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1

init-connect = ‘SET NAMES utf8mb4’
character-set-server = utf8mb4

。。。

mysql储存过程字符集

# 创建存储过程中设置了 参数的字符集

DROP PROCEDURE IF EXISTS r_test2;
CREATE PROCEDURE r_test2(IN inStr VARCHAR(255) character set gbk)
BEGIN
END;

在查询语句中增加校验集指定：

select  id into hisid from members where   memberscol  COLLATE utf8_unicode_ci  =in_hismobile;

 

查看数据库的字符集：

方法一：

MySql>use yourdatabasename
MySQL [yourdatabasename]> status;
————–
mysql Ver 14.14 Distrib 5.6.35, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 5084
Current database: twocats
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.6.35-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Uptime: 4 days 14 hours 50 min 46 sec

方法二：

MySQL [yourdatabasename]> show variables like ‘character%’;
+————————–+———————————-+
| Variable_name | Value |
+————————–+———————————-+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+————————–+———————————-+
8 rows in set (0.00 sec)

character_set_client为客户端编码方式；

character_set_connection为建立连接使用的编码；

character_set_database数据库的编码；

character_set_results结果集的编码；

character_set_server数据库服务器的编码；

只要保证以上四个采用的编码方式一样，就不会出现乱码问题。

 

修改数据库的字符编码：

方法一：如第一条描述的my.cnf配置；

方法二：ALTER

修改数据库字符集：
代码如下:ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE …];如：ALTER DATABASE `数据库名` character set utf8mb4 COLLATE utf8mb4_general_ci ;把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：
代码如下:ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE …]如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
只是修改表的默认字符集：
代码如下:

 

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE…];

如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
修改字段的字符集：
代码如下:

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE …];

如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
查看数据库编码：
代码如下:

SHOW CREATE DATABASE db_name;

查看表编码：
代码如下:

SHOW CREATE TABLE tbl_name;

如：

members | CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sex` int(11) DEFAULT NULL COMMENT ‘1男2女’,
`name` varchar(16) CHARACTER SET utf8 DEFAULT ‘UUID()’,
`mobile` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`pwd` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`pay_pwd` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`photo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`signature` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`register_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`login_times` varchar(11) CHARACTER SET utf8 NOT NULL DEFAULT ‘0’,
`last_login_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_ip` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
`status` smallint(6) NOT NULL DEFAULT ‘0’ COMMENT ‘1正常，2关闭，3解除绑定’,
`alias` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
`balance` varchar(16) CHARACTER SET utf8 DEFAULT ”,
`level_id` int(11) DEFAULT NULL,
`eq_uses` int(8) DEFAULT ‘0’ COMMENT ‘设备使用次数’,
`uuid` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`memberscol` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`wx_openid` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uuid` (`uuid`),
UNIQUE KEY `mobile` (`mobile`),
KEY `IDX_45A0D2FF5FB14BA7` (`level_id`),
CONSTRAINT `FK_45A0D2FF5FB14BA7` FOREIGN KEY (`level_id`) REFERENCES `member_levels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=617 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

查看字段编码：
代码如下:

SHOW FULL COLUMNS FROM tbl_name;

 

修改存储过程的字符集编码：

将存储过程的创建代码备份出来，待数据库的字符集编码修改后，在执行一次存储过程的创建代码就会是存储过程的字符集编码和数据库的字符集编码相同，也就不会在调用存储过程进行中文

插入数据库时乱码了。

实践时的情况如下：通常会在本地开发，在本地创建数据库，然后发布项目时，会备份数据库到生成环境，备份语句如下(做了压缩)：

mysqldump -uroot -p123456 -R –single-transaction yourdatabasename | $GZIP -9 >”/disk2/backup/db/yourdb.gz”

这样备份了存储过程，但是，你打开备份文件，会发现，在创建存储过程前，会有如下红色的语句：

DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
ALTER DATABASE `twocats` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
/*!50003 DROP PROCEDURE IF EXISTS `update_wxuser_info` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = ‘NO_ENGINE_SUBSTITUTION’ */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `update_wxuser_info`(
in_openid varchar(50),
in_memberid int(11) ,
in_mobile varchar(50)
)
begin update users_wxinfo set memberid = in_memberid where openid=in_openid;
select 1 as result;end ;;

看了红色的命令，你就会发现，这时数据库的字符集和校验集被改了N次，导致的结果是：

<MySql>show procedure status;

 会发现 ：character_set_client | collation_connection | Database Collation

会有不一样的，乱啊

所以，方法就是吧那个alter数据库的语句删掉

或者生成仅仅包含存储过程创建的语句如

create procedure aaaa(..)

begin

….

end;

到生产环境执行存储过程的创建，当然前提是这个生成环境已经配置好字符集（第一点）。