MySQL 5.7升级8.0后，排序规则问题解决方案汇总

最新推荐文章于 2025-04-11 15:05:59 发布
原创 最新推荐文章于 2025-04-11 15:05:59 发布 · 1.3k 阅读 · 7 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #oceanbase #数据库

比较操作中使用不同的字符集或排序规则通常会触发此问题，MySQL 8.0 默认 COLLATE 为 utf8mb4_0900_ai_ci 和 对应列 COLLATE 的 utf8mb4_general_ci 不匹配。

作者：陈举超

问题现象

MySQL 5.7.34 升级到 8.0.32 后部分查询语句报错如下：

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'find_in_set'

问题原因

比较操作中使用不同的字符集或排序规则通常会触发此问题，MySQL 8.0 默认 COLLATE 为 utf8mb4_0900_ai_ci 和 对应列 COLLATE 的 utf8mb4_general_ci 不匹配。

问题重现过程

创建测试表。

CREATE TABLE `t01` (
  `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

执行查询语句。

SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE, ID._xxx )
order by A_CODE;

报错。

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'find_in_set'

问题分析

查看默认排序规则。

mysql> show collation like 'utf8mb4_0900_ai_ci';
+--------------------+---------+-----+---------+----------+---------+---------------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+--------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
+--------------------+---------+-----+---------+----------+---------+---------------+
1 row in set (0.00 sec)

mysql> show collation like 'utf8mb4_general_ci';
+--------------------+---------+----+---------+----------+---------+---------------+
| Collation          | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+--------------------+---------+----+---------+----------+---------+---------------+
| utf8mb4_general_ci | utf8mb4 | 45 |         | Yes      |       1 | PAD SPACE     |
+--------------------+---------+----+---------+----------+---------+---------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.COLLATIONS WHERE IS_DEFAULT='Yes' and CHARACTER_SET_NAME='utf8mb4';
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
1 row in set (0.00 sec)

查看相关参数。

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

其中:
mysql> show global variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

查看配置文件参数。

mysql@CJC-DB-01:/home/mysql$cat /etc/my.cnf 
......
[mysqld]
collation_server = utf8mb4_general_ci

可以看到，客户端局部会话变量 collation_connection 的值为 utf8mb4_0900_ai_ci，而全局变量值为 utf8mb4_general_ci，两者不一致。

这是由于服务端在客户端连接时，获取了客户端对字符集和排序规则的缺省设置，也就是 utf8mb4_0900_ai_ci。

解决方案

  1. 修改参数
  2. 修改表 COLLATE
  3. 修改 SQL 语句

1. 修改参数

参数collation_connection 在客户端局部变量值和全局变量值不一致，如何改成一致？官网参考材料

--character-set-client-handshake
Command-Line Format:--character-set-client-handshake[={OFF|ON}]
Deprecated:8.0.35
Type:Boolean
Default Value:ON

参数说明

  • 不忽略客户端发送的字符集信息
  • 为了忽略客户端信息并使用默认的服务器字符集
  • 使用参数：--skip-character-set-client-handshake

此选项在 MySQL 8.0.35 及更高版本的 MySQL 8.0 中已被弃用。在该版本中，无论何时使用此选项，都会发出警告，并将在未来版本的 MySQL 中删除。

依赖此选项的应用程序应该尽快开始迁移。

添加 my.cnf 参数。

[mysqld]
skip-character-set-client-handshake

重启 MySQL。

mysqladmin -uroot -p****** shutdown
mysqld --defaults-file=/etc/my.cnf --user=mysql &

登录

mysql -uroot -p cjc

查看参数，collation_connection 参数值修改成功

mysql> show global variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

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

再次执行，问题解决。

SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE, ID._xxx )
order by A_CODE;
Empty set, 2 warnings (0.00 sec)

2. 修改表 COLLATE

先改回原参数，查询报错。

SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE, ID._xxx )
order by A_CODE;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'find_in_set'

修改表排序规则。

mysql> show create table t01\G;
*************************** 1. row ***************************
       Table: t01
Create Table: CREATE TABLE `t01` (
  `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

ERROR: 
No query specified

修改所有列 COLLATE，实际上只修改 A_CODE、B_CODE 列 COLLATE 也可解决此问题。

ALTER TABLE cjc.t01 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE cjc.t01 MODIFY COLUMN `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
ALTER TABLE cjc.t01 MODIFY COLUMN `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL;
ALTER TABLE cjc.t01 MODIFY COLUMN `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL;

再次执行，问题解决。

SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE, ID._xxx )
order by A_CODE;

Empty set, 2 warnings (0.00 sec)

查看表结构。

mysql> show create table t01\G;
*************************** 1. row ***************************
       Table: t01
Create Table: CREATE TABLE `t01` (
  `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

3. 修改 SQL 语句

将 A_CODE，B_CODE 列的 COLLATE 在 SQL 语句中转换为 utf8mb4_0900_ai_ci。

改写后的SQL如下：

SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE COLLATE utf8mb4_0900_ai_ci ) FROM t01 WHERE FIND_IN_SET( B_CODE COLLATE utf8mb4_0900_ai_ci, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE COLLATE utf8mb4_0900_ai_ci, ID._xxx )
order by A_CODE;

总结

比较三种解决方案，每种解决方案适用场景不同，请根据实际情况选择解决方案。

  1. 修改参数

    适用于数据库是从 5.7 或更低版本升级到 8.0，并且表数量较多、数据量加大。不适用于批量修改所有表、列字符集和排序规则。

  2. 修改表 COLLATE

    适用于修改过程会锁表，数据量越大时间越长，使用于数据量小的场景，建议将所有表、列字符集和排序规则改成 8.0 默认值，后续新增表时不指定字符集和排序规则。

  3. 修改 SQL 语句

    适用于临时查询，改SQL影响最小。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle

从 MySQL 5.7 (ZIP 版) 升级到 MySQL 8.0 (ZIP 版) 详细指南
码觉客的博客
05-26 591
MySQL 5.7升级到8.0的关键步骤：1）必须完整备份数据目录和配置文件；2）使用MySQL Shell检查兼容性问题；3）推荐在新目录安装8.0并迁移数据（包括初始化、配置调整、服务安装和数据导入）；4）升级后需验证版本、更新用户认证方式并全面测试应用。特别注意8.0的默认认证插件和字符集变更，以及移除的旧配置选项。相比直接覆盖，新安装迁移更安全可靠。整个升级过程需谨慎操作，确保数据安全。
MySQL批量修改库、表、列的排序规则
杀神lwz的博客
11-09 7144
utf8 是模糊匹配排序规则，这里需要替换为你数据库中需要替换的字段的排序规则，上述语句可以增加TABLE_SCHEMA='数据库名'，来限定数据库。utf8mb4_general_ci设置的是替换的排序规则，utf8mb4设置的是替换的字符集，把修正SQL复制出来直接运行即可。把修正SQL 复制出来直接运行即可。把修正SQL 复制出来直接运行即可。
MySQL DBA必备：MySQL 5.7升级8.0过程（全）
Enmotech的博客
05-09 2098
墨墨导读：每个 DBA 在工作中都会面临数据库升级的问题，本文详细介绍MySQL 从 5.7 升级到 8.0 的过程，希望可以帮助到大家。为什么升级到MySQL 8.0基于安全考虑基于性...
MySQL 5.7升级8.0后出现排序规则问题的解决方案汇总例子解析
IT老农民的博客
09-12 671
在MySQL 5.7升级到8.0后，可能会遇到排序规则（Collations）的问题，因为MySQL 8.0默认的排序规则有所变化。：可以通过设置参数来解决客户端和服务端排序规则不一致的问题。可以在MySQL配置文件my.cnf中添加来忽略客户端的字符集信息，使用服务器的默认字符集。这样，的值将会与全局变量值保持一致。重启MySQL服务后，可以通过来检查参数值是否修改成功。：如果问题是由于表或列的排序规则与默认排序规则不匹配导致的，可以通过修改表或列的排序规则来解决。例如，可以使用。
MySQL数据库5.7升级到8.0版本
weixin_43876826的博客
04-12 8175
CentOS7下MySQL5.7版本升级到8.0版本
警惕参数变化，MySQL 8.0 升级避免再次踩坑
真水无香，大象无形，做本分的自己，记录技术点滴
04-04 8931
MySQL 8.0新特性专栏目录 我们知道MySQL 8.0相对于MySQL 5.7加入了很多新特性，在功能和安全性上做了很多的优化和调整，这就不可避免地会修改一些参数或者函数。**我们现有业务数据从MySQL 5.7及之前的版本升级到8.0，需要特别警惕这些参数的变化，这些变化可能会导致业务行为发生一些意料之外的结果！！！** 最近MySQL 8.0 升级的过程中，我梳理了一些业务可能用到的、重要的参数变化，包括一些MySQL 8.0中已经废弃的参数和部分常见参数默认值的变化。
mysql的那些事
感冒石头的博客
01-13 683
1、mysql 5.7和mysql 8.0版本两个版本有啥区别 (1)、NoSql存储 Mysql从5.7 版本提供了NoSQL的存储功能,在8.0中这部分得到一些修改,不过这个在实际中用的极少 (2)、隐藏索引 隐藏索引的特性对于性能调试非常有用,在8.0 中,索引可以被隐藏和显示,当一个索引隐藏时,他不会被查询优化器所使用 也就是说可以隐藏一个索引,然后观察对数据库的影响.如果性能下降,就说明这个索引是有效的,于是将其”恢复显示”即可;如果数据库性能看不出变化,说明这个索引是多于的,可以删掉了 .
mysql常见的经典问题
m0_59877551的博客
08-27 562
MySQL5.7及其以前的版本，MySQL服务器重启，会重新扫描表的主键最大值，如果之前已经删除过id=100的数据，但是表中当前记录的最大值如果是99，那么经过扫描，下一条记录的id是100，而不是101。比如where后面的多个搜索条件都使用到了一个二级索引列，这些搜索条件中虽然出现了索引列，有些却不能使用到索引，像like ‘%...’查询，MySQL为了避免不必要的回表，从二级索引取得的索引记录，先做条件判断，如果条件不满足，则该二级索引记录不会去回表，这样可以大量的减少回表操作的随机IO成本。
内存泄漏大作战：MySQL8.0内存监控与诊断的全面攻略
[内存泄漏大作战：MySQL8.0内存监控与诊断的全面攻略](https://cdn.botpenguin.com/assets/website/Screenshot_2023_09_01_at_6_57_32_PM_920fd877ed.webp) # 1. MySQL内存管理概述 ## MySQL内存管理的重要性和挑战...
黑马MySQL进阶篇笔记
温涛的博客
04-04 3802
包括存储引擎、索引、SQL优化、视图、存储过程、存储函数、触发器、锁、InnoDB引擎、MySQL管理
mysql 5.7 升级 8.0_MySQL 5.7升级到8.0
weixin_35349608的博客
01-19 789
原地升级原地升级包括关闭旧的MySQL服务器，用新的MySQL二进制文件或软件包替换旧的MySQL二进制文件或软件包，在现有数据目录上重新启动MySQL以及升级现有安装中需要升级的任何其余部分。注意如果要升级最初通过安装多个RPM软件包而产生的安装，请升级所有软件包，而不只是某些软件包。例如，如果您以前安装了服务器和客户端RPM，则不要仅升级服务器RPM。对于某些Linux平台，从RPM或Debi...
MySQL 5.7 升级 MySQL 8.0
lsdb
01-11 1682
MySQL 5.7 升级 MySQL 8.0
mysql实战——mysql5.7升级到mysql8.0
zyjzyjjyzjyz的博客
05-21 3598
楼主只改了for8.0下的配置，别的和mysql5.7相同，还改动了datadir路径和basedir路径。1、上传mysql8.0压缩包到/usr/local目录下。# 最后几个for8.0的参数要格外注意。3、前期配置修改完了，下面执行升级程序。# 修改mysql服务启动项配置。2、更改配置文件my.cnf。# 修改basedir目录。
干货分享|MySQL5.7升级MySQL8.0的亲测详解
Jum的博客
01-12 3373
事出必有因，在这个月的某个项目中，我们面临了一项重要任务，即每年一次的等保测评整改。这次测评的重点是Mysql的一些高危漏洞，客户要求我们无论如何必须解决这些漏洞。尽管我们感到无奈，但为了满足客户的要求，我们只能硬着头皮进行升级。而碰巧的是，Mysql5.7在10月份停止了更新，借着这次机会，我们决定研究一下Mysql5.7升级到Mysql8.0的过程。在本文的最后，我将分享一些在这次升级过程中遇到的问题。升级需求：将5.7.43升级到8.0.34, 升级方式in-place。
MySql5.7升级8.0安装教程
qq_39690581的博客
04-11 1312
4、选择需要的版本号，我的是8.0版本，点击Go to Download Page，下载msi方式进行安装。2、下拉到尾部，点击MySQL Community （GPL）Downloads。3、点击MySQL Community Server。1、进入官网，点击download。
linux离线安装mysql 8.0
北城青的博客
10-19 419
在home下新建一个mysql文件夹 cd /home mkdir mysql cd mysql 1.首先上传文件,把文件传到 /home/mysql下 链接：https://pan.baidu.com/s/1Y2Fjf2gO57jLhRrttyYd3A 提取码：0i88 2.卸载mariadb rpm -qa|grep mariadb rpm -e --nodeps mariadb-libs...
10秒，轻松搞定MySQL 5.7升级到8.0
chenoracle的博客
11-16 1884
10秒，轻松搞定MySQL 5.7升级到8.0
mysql5.7升级至8.0,压缩包方式
lqw_675的博客
03-26 1099
目标服务器: CentOS Stream release 8 x86_64。
MySQL5.7升级到MySQL8.0
linus.lin的博客
04-24 4820
MySQL5.7升级到MySQL8.0 背景: 因为一个项目交接需要需要将mysql物理备份文件还原至MySQL5.7。并且将mysql5.7升级到MySQL8.0 1.升级前准备及注意事项 首先，我们要大概了解下MySQL5.7和8.0有哪些不同，参考官方文档和其他网友文章，概括总结出MySQL8.0以下几点新特性： 默认字符集由latin1变为utf8mb4。 MyISAM系统表全部换成InnoDB表。 JSON特性增强。 支持不可见索引，支持直方图。 sql_mode参数默认值变化。 默认密码策略变
mysql 累计加总
最新发布
08-03
<think>我们正在讨论如何在MySQL中实现累计加总（cumulative sum）的查询。根据用户提供的引用和问题，我们可以总结出几种实现累计加总的方法。累计加总通常是指按某个顺序（如时间顺序）对数值进行累加。 引用[1]展示了一种通过自连接（self-join）和分组来实现月度累计的方法。它通过将同一个表连接，然后连接条件为a.tradeDate>=b.tradeDate，这样对于每个月份，都会累加小于等于该月份的所有数据。 引用[3]则展示了一种使用相关子查询（correlated subquery）的方法。对于每个行，它通过子查询计算同一类型（type）且id小于等于当前行id的所有行的money之和。 另外，还有一种使用窗口函数（window function）的方法，这是MySQL 8.0及以上版本推荐的方式，因为窗口函数可以更简洁高效地实现累计加总。 下面我们将分别介绍这几种方法： 方法一：使用自连接（适用于MySQL所有版本） 思路：将表按照分组字段（如月份）进行分组，然后通过连接条件（a.tradeDate>=b.tradeDate）将当前组和之前的所有组连接起来，然后对连接后的结果进行求和。 示例（基于引用[1]的改写）： ```sql SELECT a.tradeDate, SUM(a.balances) AS monthly_total, -- 当月总计 SUM(b.balances) AS cumulative_total -- 累计总计 FROM (SELECT DATE_FORMAT(tradeDate, '%Y-%m') AS tradeDate, SUM(balances) AS balances FROM trade1 GROUP BY tradeDate) a JOIN (SELECT DATE_FORMAT(tradeDate, '%Y-%m') AS tradeDate, SUM(balances) AS balances FROM trade1 GROUP BY tradeDate) b ON a.tradeDate >= b.tradeDate GROUP BY a.tradeDate ORDER BY a.tradeDate; ``` 注意：这里使用了两个相同的子查询，然后通过连接条件a.tradeDate>=b.tradeDate，这样对于a的每一个月份，都会连接所有小于等于该月份的b，然后按a的月份分组，对b的balances求和即可得到累计值。 方法二：使用相关子查询（适用于MySQL所有版本） 思路：对于主查询的每一行，通过子查询去计算从开始到当前行的累计值。这种方法在数据量大时可能较慢。 示例（基于引用[3]的改写）： ```sql SELECT id, tradeDate, balances, (SELECT SUM(balances) FROM trade1 WHERE tradeDate <= t.tradeDate) AS cumulative_total FROM (SELECT id, DATE_FORMAT(tradeDate, '%Y-%m') AS tradeDate, SUM(balances) AS balances FROM trade1 GROUP BY tradeDate) t ORDER BY tradeDate; ``` 注意：这里我们先按月份分组得到每个月的总额，然后对于每个月份，子查询会计算所有小于等于该月份的总额之和。 方法三：使用窗口函数（MySQL 8.0+） 在MySQL 8.0及以上版本，我们可以使用窗口函数来更高效地实现累计加总。 示例（按月累计）： ```sql WITH monthly_summary AS ( SELECT DATE_FORMAT(tradeDate, '%Y-%m') AS tradeDate, SUM(balances) AS monthly_total FROM trade1 GROUP BY tradeDate ) SELECT tradeDate, monthly_total, SUM(monthly_total) OVER (ORDER BY tradeDate) AS cumulative_total FROM monthly_summary ORDER BY tradeDate; ``` 这里，我们首先按月份汇总每个月的总额（CTE），然后使用窗口函数SUM() OVER (ORDER BY tradeDate)对monthly_total进行累计求和。 如果不需要先按月份汇总，而是对每一笔记录都进行累计（比如按日期顺序），可以这样写： ```sql SELECT tradeDate, balances, SUM(balances) OVER (ORDER BY tradeDate) AS cumulative_total FROM trade1 ORDER BY tradeDate; ``` 注意：如果数据是按天有多个记录，可能要先按天汇总再累计，或者直接按天累计。 总结： 1. 自连接方法：通用，但可能性能较差（特别是数据量大时），因为连接操作可能产生大量中间结果。 2. 相关子查询：写法简单，但性能也随数据量增加而下降。 3. 窗口函数：推荐在MySQL 8.0+使用，性能较好，语法简洁。 请根据实际数据量和MySQL版本选择合适的方法。 另外，如果用户需要按不同的分组（比如按产品类别）分别累计，则需要在上述方法中加入分组字段。例如，在窗口函数中： ```sql SELECT product_type, tradeDate, balances, SUM(balances) OVER (PARTITION BY product_type ORDER BY tradeDate) AS cumulative_total FROM ... ``` 这样，每个产品类别都会单独进行累计。 最后，注意累计加总通常需要按照某个顺序（如时间顺序）进行，所以排序字段的选择很重要。 希望这些方法能帮助用户解决问题。 §§相关问题§§ 1. 如何在不同分组内分别计算累计加总值？ 2. 在MySQL 5.7中，除了自连接和相关子查询，还有哪些方法可以实现累计加总？ 3. 如何优化大数据量下累计加总查询的性能？</think>### MySQL 实现累计加总查询方法详解 累计加总（Cumulative Sum）是指按特定顺序（如时间、ID）对数值进行逐行累加的计算。以下是 MySQL 中实现累计加总的三种常用方法，根据你的数据结构和需求选择合适方案： --- #### 方法一：使用自连接（Self-Join）[^1] **适用场景**：按时间维度（如月度）统计累计值 **优点**：逻辑清晰，适合分组聚合后的累计计算 ```sql SELECT a.tradeDate, a.monthly_total, SUM(b.monthly_total) AS cumulative_total FROM ( SELECT DATE_FORMAT(tradeDate, '%Y-%m') AS tradeDate, SUM(balances) AS monthly_total FROM trade1 GROUP BY DATE_FORMAT(tradeDate, '%Y-%m') ) a JOIN ( SELECT DATE_FORMAT(tradeDate, '%Y-%m') AS tradeDate, SUM(balances) AS monthly_total FROM trade1 GROUP BY DATE_FORMAT(tradeDate, '%Y-%m') ) b ON a.tradeDate >= b.tradeDate GROUP BY a.tradeDate ORDER BY a.tradeDate; ``` **执行逻辑**： 1. 子查询先按月聚合计算当月总和 2. 通过 `a.tradeDate >= b.tradeDate` 连接所有小于等于当前月的记录 3. 对连接结果按月分组求和实现累计 --- #### 方法二：使用相关子查询（Correlated Subquery）[^3] **适用场景**：按行ID或序列号逐行累加 **优点**：无需预聚合，适合原始记录级累计 ```sql SELECT id, tradeDate, balances, (SELECT SUM(balances) FROM trade1 WHERE id <= t.id) AS cumulative_total FROM trade1 t ORDER BY id; ``` **执行逻辑**： 1. 主查询遍历每一行 2. 子查询计算当前行及之前所有行的总和 3. 通过 `id <= t.id` 实现按ID顺序累计 --- #### 方法三：窗口函数（MySQL 8.0+） **适用场景**：MySQL 8.0及以上版本 **优点**：语法简洁，性能最优 ```sql SELECT id, tradeDate, balances, SUM(balances) OVER (ORDER BY id) AS cumulative_total FROM trade1; ``` **关键函数**： - `SUM() OVER (ORDER BY ...)`：按指定排序计算累计和 - 支持分组累计：添加 `PARTITION BY` 子句 ```sql SUM(balances) OVER (PARTITION BY type ORDER BY id) ``` --- #### 性能优化建议 1. **索引策略**： - 为排序字段（如 `tradeDate`, `id`）创建索引 - 聚合字段（如 `balances`）添加复合索引 ```sql CREATE INDEX idx_sort ON trade1(tradeDate, balances); ``` 2. **大数据集处理**： - 避免全表扫描：添加时间范围过滤 ```sql WHERE tradeDate BETWEEN '2023-01-01' AND '2023-12-31' ``` - 分页处理：结合 `LIMIT` 分批计算 3. **版本适配方案**： | MySQL 版本 | 推荐方法 | |-------------|------------------| | < 8.0 | 自连接/相关子查询 | | ≥ 8.0 | 窗口函数 | --- #### 示例数据验证 原始数据： | id | tradeDate | balances | |----|-------------|----------| | 1 | 2023-01-05 | 100 | | 2 | 2023-01-12 | 200 | | 3 | 2023-02-03 | 150 | 累计结果： | id | tradeDate | balances | cumulative_total | |----|-------------|----------|------------------| | 1 | 2023-01-05 | 100 | 100 | | 2 | 2023-01-12 | 200 | 300 | | 3 | 2023-02-03 | 150 | 450 | > 提示：累计计算时需特别注意排序字段的唯一性，非唯一字段可能导致结果不稳定[^2]。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值