【MySQL】Navicat踩坑：Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'instr'

 

在Navicat客户端上面执行SQL报错

 

SQL语句：

WITH RECURSIVE transfer  (start_station, stop_station, stops, path) AS (
  SELECT station_name, next_station, 1, CAST(CONCAT(line_name, ':', station_name , ' -> ', line_name, next_station) AS CHAR(1000))
    FROM nanchang_metro WHERE station_name = '艾溪湖西'
   UNION ALL
  SELECT p.start_station, e.next_station, stops + 1, CONCAT(p.path, '->', e.line_name, e.next_station)
    FROM transfer p
    JOIN nanchang_metro e
      ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0)
) 
SELECT * FROM transfer WHERE stop_station ='沥山';

报错信息：

> 1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'instr'
> 时间: 0s

 

我一开始也以为是自己的库，表，字段，排序规则不一致导致的，但是不是

 

这是库：

这是表和字段：

CREATE TABLE `nanchang_metro` (
  `station_id` int NOT NULL AUTO_INCREMENT,
  `line_name` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `station_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `next_station` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `direction` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`station_id`)
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 

然后我检查了Collation变量，发现Connection设置的规则，有是0900ci

mysql> show variables where Variable_name like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)

mysql> 

然后百度搜怎么配置这个connection

在my.ini配置文件中已经如下声明具体的规则和字符集：

[mysqld]
# mysql所在的目录
basedir=E:\mysql-8.0.23-winx64

# mysql 数据库存储的目录
datadir=E:\mysql-8.0.23-winx64\data\

# mysql服务端默认使用的字符集
character-set-client-handshake = FALSE 
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4 COLLATE server=utf8mb4_general_ci'


# 默认使用的存储引擎
default-storage-engine=INNODB

# mysql服务端的端口号 默认3306
port=3308

# 配置二进制日志格式
binlog_format = MIXED

# 允许导入导出
secure_file_priv = ''

[mysql]
# mysql客户端默认使用的字符集
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

重启服务后确实能指定collation_server

但是connection就是指定不了，就是很奇怪

 

 

然后我一想，连接的是Navicat来操作，是不是Navicat配置的问题
果然啊，一找才知道TNND，Navicat自动配置的是按照MySQL默认的排序规则走的

更改成UTF-8：

 

再次配置查看，就发现都一致了

mysql> show variables where Variable_name like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)

mysql>