当前位置：首页 > news >正文

记录 Mysql5.7 升级到 Mysql8.0 遇到的问题

news 2025/5/10 6:34:28

1. 导入存储过程时创建function报错

1.1 Error 信息：

This function has none Of DETERMINISTIC, NO SOL, Or READS SOL DATA in itsdeclaration and binary logging is enabled (you *might* want to use the less safelog bin trust function creators variable)

1.2 解决方法：

方法一：修改Mysql配置文件

 # 在 [mysqld] 下追加如下配置log_bin_trust_function_creators=1

方法二：或者直接在 navicat 执行下面的语句

SET GLOBAL log_bin_trust_function_creators = 1;

2. WEB 项目链接DB是，报 authentication 方面的错误

2.1 错误信息：

# Error: A Database connection using "Mysql" was missing or unable to connect.

# The database server returned this error: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

2.2 错误分析：

错误代码 SQLSTATE[HY000] [2054] 表示：

MySQL 8.0+ 服务器默认使用 caching_sha2_password 认证插件
但 PHP MySQL 客户端（可能是较旧版本的 mysqlnd 驱动）不支持这种认证方法

2.3 解决方案：

修改用户认证方式：

ALTER USER 'your_username'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'your_password';FLUSH PRIVILEGES;

3. SQL 报 GROUP BY 方面的Error

3.1 报错信息：

Error: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pia.v_page_role.PAGE_ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

3.2 错误分析：

这个错误是由于 MySQL 的 sql_mode 包含了 ONLY_FULL_GROUP_BY 模式导致的，这是 MySQL 5.7.5 及以上版本的默认设置。

错误信息表明：

SELECT 查询包含 GROUP BY 子句
但 SELECT 列表中的第2个字段 PAGE_ID 既不在 GROUP BY 子句中，也不是聚合函数
这与 ONLY_FULL_GROUP_BY SQL 模式不兼容

3.3 解决方案

方案一：修改查询语句

确保 SELECT 列表中的所有非聚合列都包含在 GROUP BY 子句中

方案二：临时禁用 ONLY_FULL_GROUP_BY

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

方案三：修改Mysql配置文件，永久修改 SQL 模式

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 在 my.cnf 找到 sql-mode 去掉 ONLY_FULL_GROUP_BY
# 如果没有找到 sql-mode
# 在 [mysqld] 下追加如下配置

sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

# 查看 my.cnf 所在目录
mysql --help | grep "Default options" -A 1

输出结果如下
# Default options are read from the following files in the given order:
# /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

方案四：使用 ANY_VALUE() 函数（需修改SQL）

SELECT column1,ANY_VALUE(PAGE_ID) AS PAGE_ID,MAX(column2) AS max_column2
FROM table_name
GROUP BY column1;

由于项目是即存项目，修改SQL时不行的，所以我这边采用的是 方案三

4. 关于 collation_connection 的错误

4.1 错误信息：

2024-12-03 14:37:33 Error: [PDOException] SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

4.2 错误分析

这个错误发生在尝试比较或连接(使用=运算符)具有不同校对规则的列时：

utf8mb4_0900_ai_ci (MySQL 8.0的默认校对规则)
utf8mb4_general_ci (旧版MySQL的默认校对规则)

校对规则决定了字符串比较和排序的方式。MySQL无法自动处理不同校对规则之间的比较。

4.3 解决办法

修改 collation_connection，保证被 join 的两个表的关联字段是一致的，都是 utf8mb4_general_ci （我们的业务使用 general_ci 就足够了，不需要更细致的排序规则）
修改 Mysql8.0 的校对规则为 utf8mb4_general_ci

# 修改：
# my.cnf 追加如下配置
# 找到 [mysqld]
# 追加或修改 以下两项
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci# 验证：
# 在查询窗口，查看collation / character
show variables like "colla%";    
show variables like "character%";

4.4 `utf8mb4_general_ci` 与 `utf8mb4_0900_ai_ci` 的区别

utf8mb4_0900_ai_ci和utf8mb4_general_ci是MySQL数据库中utf8mb4字符集下的两种不同排序规则，它们在多个方面存在显著差异。以下是对这两种排序规则的详细比较：

一、定义与基础

1. utf8mb4字符集：MySQL中用于支持完整的UTF-8字符（包括表情符号和其他4字节字符）的一种字符集。

2. utf8mb4_0900_ai_ci：
   0900：基于Unicode 9.0的排序和比较规则。
   ai：不区分重音（accent-insensitive）。
   ci：不区分大小写（case-insensitive）。
3. utf8mb4_general_ci：
   general：一种通用的排序规则。
   ci：同样表示不区分大小写。

二、Unicode支持
1. utf8mb4_0900_ai_ci：提供更精确、更现代的Unicode支持。它能够处理非常复杂的Unicode场景，正确地进行语言学上的排序和比较。例如，‘é’和‘e’会被认为是相同的字符。

2. utf8mb4_general_ci：不完全支持Unicode。它不能处理一些非常特殊的Unicode比较和排序规则。同一个字符的不同变体在general_ci中可能不会被认为是相同的字符。

三、排序与比较规则
1. utf8mb4_0900_ai_ci：基于Unicode 9.0的规范进行排序和比较，考虑语言学上的复杂性。
2. utf8mb4_general_ci：简单地基于字母进行排序，不考虑任何语言学的复杂性。

四、性能
1. utf8mb4_0900_ai_ci：由于要处理更复杂的排序和比较规则，性能可能不如utf8mb4_general_ci。
2. utf8mb4_general_ci：在排序和比较时通常比其他utf8mb4排序规则速度更快，因此具有性能优势。

五、适用场景
1. utf8mb4_0900_ai_ci：
适用于需要更高精确度和完整Unicode支持的应用场景。
特别是需要按照国际化标准进行排序和比较的系统，如国际化应用、本地化支持要求高的应用、电子商务平台和内容管理系统等。

2. utf8mb4_general_ci：
适用于需要快速排序和比较、不太注重精确Unicode支持的应用场景。
主要关注性能的场景，如内容不包含复杂的多语言字符的Web应用和内存数据库。

六、版本兼容性
1. utf8mb4_0900_ai_ci：MySQL 8.0及更高版本的默认排序规则。如果将MySQL 8.0版本的表导入到MySQL 5.7或MySQL 5.6版本，会存在字符集无法识别的问题。
2. utf8mb4_general_ci：MySQL 8.0版本之前的默认排序规则。