问题引出:由于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)
代码如下: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;
/*!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 ;;