升级MySQL5.7,开发不得不注意的坑

前段时间,将线上MySQL数据库升级到了5.7。考虑到可能产生的不兼容性,在升级之前,确实也是战战兢兢,虽然测试环境,开发环境早在半年前就已提前升级。

基于前期的调研和朋友的反馈,与开发相关的主要有两点:

sql_mode

MySQL 5.6中,其默认值为"NO_ENGINE_SU BSTITUTION",可理解为非严格模式,譬如,对自增主键插入空字符串'',虽然提示warning,但并不影响自增主键的生成。

但在MySQL 5.7中,其就调整为了严格模式,对于上面这个,其不会提示warning,而是直接报错。

分组求最值

分组求最值的某些写法在MySQL5.7中得不到预期结果,这点,相对来说比较隐蔽。

其中,第一点是可控的,毕竟可以调整参数。而第二点,却是不可控的,没有参数与之相关,需要开发Review代码。

下面具体来看看

测试数据

mysql> select * from emp;
+-------+----------+--------+--------+
| empno | ename    | sal    | deptno |
+-------+----------+--------+--------+
|  1001 | emp_1001 | 100.00 |    10 |
|  1002 | emp_1002 | 200.00 |    10 |
|  1003 | emp_1003 | 300.00 |    20 |
|  1004 | emp_1004 | 400.00 |    20 |
|  1005 | emp_1005 | 500.00 |    30 |
|  1006 | emp_1006 | 600.00 |    30 |
+-------+----------+--------+--------+
6 rows in set (0.00 sec)

其中,empno是员工编号,ename是员工姓名,sal是工资,deptno是员工所在部门号。

业务的需求是,求出每个部门中工资最高的员工的相关信息。

在MySQL5.6中,我们可以通过下面这个SQL来实现,

SELECT
    deptno,ename,sal
FROM
    ( SELECT * FROM emp ORDER BY sal DESC ) t
GROUP BY
    deptno;

结果如下,可以看到,其确实实现了预期效果。

+--------+----------+--------+
| deptno | ename    | sal    |
+--------+----------+--------+
|    10 | emp_1002 | 200.00 |
|    20 | emp_1004 | 400.00 |
|    30 | emp_1006 | 600.00 |
+--------+----------+--------+

再来看看MySQL5.7的结果,竟然不一样。

+--------+----------+--------+
| deptno | ename    | sal    |
+--------+----------+--------+
|    10 | emp_1001 | 100.00 |
|    20 | emp_1003 | 300.00 |
|    30 | emp_1005 | 500.00 |
+--------+----------+--------+

实际上,在MySQL5.7中,对该SQL进行了改写,改写后的SQL可通过explain(extended) + show warnings查看。

mysql> explain select deptno,ename,sal from (select * from emp order by sal desc) t group by deptno;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | emp  | NULL      | ALL  | NULL          | NULL | NULL    | NULL |    6 |  100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
  Code: 1003
Message: /* select#1 */ select `slowtech`.`emp`.`deptno` AS `deptno`,`slowtech`.`emp`.`ename` AS `ename`,`slowtech`.`emp`.`sal` AS `sal` from `slowtech`.`emp` group by `slowtech`.`emp`.`deptno`
1 row in set (0.00 sec)

从改写后的SQL来看,其消除了子查询,导致结果未能实现预期效果,官方也证实了这一点,https://bugs.mysql.com/bug.php?id=80131

很多人可能不以为然,认为没人会这样写,但在大名鼎鼎的stackoverflow中,该实现的点赞数就有116个-由此可见其受众之广,仅次于后面提到的“方法二”(点赞数206个)。
https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results

需要注意的是,该SQL在5.7中是不能直接运行的,其会提示如下错误:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个与sql_mode有关,在MySQL 5.7中,sql_mode调整为了

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/a2958cb5d69c9f9012e3cd9c0afba082.html