【MySQL】字符集不同导致的问题

对于一个数据库开发者来说，牢固扎实的基础是十分重要的，golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《【MySQL】字符集不同导致的问题》，主要介绍了MySQL，希望对大家的知识积累有所帮助，快点收藏起来吧，否则需要时就找不到了！

1、两表字符集不同导致无法使用索引

开发反映有个请求sql很慢，需要3s以上。sql如下：

SELECT
  tc.customer_name,
  cb.service_record_id~~~~,
  tc.customer_code,
  tc.customer_desc,
  tc.customer_account_id,
  tc.phone
FROM
  tur_customer tc
INNER JOIN tur_customer_bind cb ON tc.customer_account_id = cb.customer_account_id
WHERE
  cb.employee_account_id = '181'
AND tc.is_delete = 0
ORDER BY
  cb.service_record_id DESC
LIMIT 0,10

表结构：

CREATE TABLE `tur_customer` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_code` varchar(64) NOT NULL COMMENT '客户编码',
  `customer_name` varchar(255) NOT NULL COMMENT '客户姓名',
  `customer_name_hash` varchar(128) DEFAULT NULL COMMENT '客户姓名Hash',
  `customer_account_id` varchar(32) NOT NULL COMMENT '帐号系统对应的唯一标识',
  `real_name_status` int(11) DEFAULT '10' COMMENT '实名制认证状态: 10 未认证, 20 已实名, 30 已认证',
  `sex` int(11) DEFAULT '1' COMMENT '性别;1男；0女',
  `phone` varchar(128) NOT NULL COMMENT '联系电话，可能时手机号，也可能是座机号码',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_customercode` (`customer_code`) USING BTREE,
  UNIQUE KEY `uniq_customeraccountid` (`customer_account_id`) USING BTREE,
  KEY `idx_customernamehash` (`customer_name_hash`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=267031 DEFAULT CHARSET=utf8 COMMENT='客户基本信息表';
 
CREATE TABLE `tur_customer_bind` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `customer_account_id` varchar(32) NOT NULL COMMENT '客户账号',
  `employee_account_id` varchar(32) NOT NULL COMMENT '顾问账号',
  `employee_account_name` varchar(32) NOT NULL COMMENT '顾问名称',
  `service_record_id` int(11) NOT NULL COMMENT '最新服务记录ID',
  `bind_time` datetime NOT NULL COMMENT '绑定时间',
  `store_code` varchar(32) DEFAULT NULL COMMENT '店面编码',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_account` (`customer_account_id`) USING BTREE COMMENT '客户账号唯一索引',
  KEY `idx_employee_account_id` (`employee_account_id`) USING BTREE COMMENT '顾问账号索引'
) ENGINE=InnoDB AUTO_INCREMENT=14583 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='当前客户关系表';

分析表的执行计划如下：

+----+-------------+-------+------+--------------------------------------+-------------------------+---------+-------+-------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys                        | key                     | key_len | ref   | rows  | Extra                                                  |
+----+-------------+-------+------+--------------------------------------+-------------------------+---------+-------+-------+--------------------------------------------------------+
|  1 | SIMPLE      | cb    | ref  | uniq_account,idx_employee_account_id | idx_employee_account_id | 130     | const |     1 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | tc    | ALL  | NULL                                 | NULL                    | NULL    | NULL  | 58697 | Using where; Using join buffer (Block Nested Loop)     |
+----+-------------+-------+------+--------------------------------------+-------------------------+---------+-------+-------+--------------------------------------------------------+

两表关联字段tur_customer.customer_account_id和tur_customer_bind.customer_account_id都有索引，且类型都为varchar，但是索引没有用到，查看字符集发现一个表是utf8一个是utf8mb4。

两表的字符集不同，会造成无法使用索引的情况发生。 查看库的字符集为utf8，排序规则为utf8_general_ci.

将表tur_customer修改为utf8mb4格式后，该sql的执行时间仅为0.1s，再次查看执行计划如下：

+----+-------------+-------+--------+--------------------------------------+-------------------------+---------+-----------------------------+------+----------------------------------------------------+
| id | select_type | table | type   | possible_keys                        | key                     | key_len | ref                         | rows | Extra                                              |
+----+-------------+-------+--------+--------------------------------------+-------------------------+---------+-----------------------------+------+----------------------------------------------------+
|  1 | SIMPLE      | cb    | ref    | uniq_account,idx_employee_account_id | idx_employee_account_id | 130     | const                       |    1 | Using index condition; Using where; Using filesort |
|  1 | SIMPLE      | tc    | eq_ref | uniq_customeraccountid               | uniq_customeraccountid  | 130     | test.cb.customer_account_id |    1 | Using where                                        |
+----+-------------+-------+--------+--------------------------------------+-------------------------+---------+-----------------------------+------+----------------------------------------------------+

2、两表排序规则不同进行join连表报错

报错信息如下：

SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='"

查了下两表的 DDL，一个是utf8mb4_unicode_ci，一个是utf8mb4_general_ci，所以导致了这个问题

解决方法： 将这两个表统一改为 utf8mb4_unicode_ci 或者 utf8mb4_general_ci。

如果不修改表结构，可以在查询的时候指定等号一边的字符集编码:

• 格式：WHERE 列名 COLLATE utf8mb4_unicode_ci = 嵌套语句
• 或者：WHERE 列名 = 嵌套语句 COLLATE utf8mb4_unicode_ci 达到让两边的字符集相等！

到这里，我们也就讲完了《【MySQL】字符集不同导致的问题》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！