从一个微服务接口的演变趟 MySQL CASE 操作符的坑

最新推荐文章于 2024-07-15 19:44:21 发布

小皇叔！

最新推荐文章于 2024-07-15 19:44:21 发布

阅读量96

点赞数

文章标签： mysql 微服务数据库

本文链接：https://blog.csdn.net/Velen_zou/article/details/130744797

版权

本文涉及到的主要环境包括：
MySQL：8.0.33
Mybatis：3.5.4

问题引入

现状

在一个名为 待办 的微服务中，有这样两张表：

CREATE TABLE `todo` (
  `id` bigint unsigned NOT NULL COMMENT '主键ID',
  `subject_id` bigint unsigned NOT NULL COMMENT '业务主键ID',
  `start_time` datetime NOT NULL COMMENT '待办开始时间',
  `end_time` datetime NOT NULL COMMENT '待办截止时间',
  `title` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '待办名称',
  ...
  PRIMARY KEY (`id`),
  ...
) COMMENT='待办任务主表';

CREATE TABLE `todo_member` (
  `id` bigint unsigned NOT NULL COMMENT '主键ID',
  `todo_id` bigint unsigned NOT NULL COMMENT '待办任务主表ID',
	`subject_id` bigint unsigned NOT NULL COMMENT '业务主键ID',
  `member_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '成员ID',
  `status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '待办状态(0:未完成;20:已完成)',
  ...
  PRIMARY KEY (`id`),
	UNIQUE INDEX idx_todo_member_sub_mem (`subject_id`, `member_id`),
  ...
) COMMENT='待办任务成员表';

显而易见，

todo 表中记录的是待办任务的主体内容，它包含了待办名称、起止时间 等公共信息，同时还记录了业务主键；

从微服务解耦的层面说，其实 todo 表中记录业务主键并不是一个好主意。但现实情况是：待办是一个新服务，要推动十几个业务端改表来记录待办的主键可以说难于登天。

也正是因为采用记录业务主键的这个方案，才导致这次逻辑上的调整，从而有了这篇文章。

todo_member 表中记录的是成员与待办任务的关系，以及成员的待办状态等。同时为了提高部分并发场景下的查询效率，对业务主键还做了冗余，甚至联合 member_id 字段建立了唯一索引。

演变

待办服务上线后不久，有业务端反馈一种场景，在他们的这个业务主键下，每个成员对应的待办起止时间需要不一样，而且需要和修改成员待办状态一样要能修改每个成员的待办起止时间。

也就是说，同一个 subject_id 需要对应多个起止时间，显然我们目前是无法满足的。

一个可能的解决方案是这样的：业务端记录了他们的这个业务主键与成员之间的关联关系，那么业务把这个关联关系的主键作为新的 subject_id 传给待办服务问题就解决了，完美！

答：没有这个关联关系

问：做一个关联关系不难吧，就一张表三两个字段，而且以后业务的查询也能从这张表受益

答：口子太多，改起来容易漏逻辑，测试需要覆盖的面广，暂时看不到有需求能从关联表受益，客户需求急，巴拉巴拉、巴拉巴拉。。。巴拉

经过一番文明绅士的探讨，本着对客户负责的态度，最终我们还是决定把待办的盘子做大“一丢丢”，支持成员维度的起止时间。

也就是说，todo_member 表中需要新增两个字段：

`start_time` datetime NOT NULL '成员维度的待办开始时间',
`end_time` datetime NOT NULL '成员维度的待办结束时间',

对于不支持成员维度起止时间的待办，可以把起止时间依旧落在 todo 表中，这样能避免起止时间的变更引起大量写操作，

同时因为 todo_member 表中这两个字段设置的是非空，可以分别使用 ‘1000-01-01 00:00:00’ 和 ‘9999-12-31 23:59:59’ 来代替 NULL 值；

对于支持成员维度起止时间的待办，那就需要把起止时间落在 todo_member 表中，todo 表中落个 ‘1000-01-01 00:00:00’ 和 ‘9999-12-31 23:59:59’ 即可。

接口层面，改动涉及到修改成员待办的接口。这个接口中原来有一个字段用来接收需要修改待办的那些成员：

...

private Long subjectId;

private List<String> memberIds;

private Integer status;

...

既然要支持成员维度待办起止时间的修改，那显然要能在指定成员的时候也能指定其新的起止时间。

同时要维持在其他业务端不改动代码的情况下，接口能正常接受原来的 memberIds。

调整后的接口部分字段是这样的：

...

private Long subjectId;

private List<String> memberIds;

private Integer status;

private List<TimeSensitiveMember> timeSensitiveMembers;

...

@Data
public static class TimeSensitiveMember {

		private String memberId;

		private LocalDateTime startTime;

		private LocalDateTime endTime;
}

So far, So good!

实现层面，原接口根据 subjectId、memberIds 直接批量更新成员的待办状态：update todo_member set status = #{status} where (subject_id, member_id) in ((..., ...), (..., ...)...)。

现在我们面临这样两个问题：

需要在更新 status 的同时，更新 start_time、end_time 两个字段，而且每条记录中 start_time、end_time 的值可以是完全不一样的；
在兼容 memberIds 字段的情况下，有部分数据的 start_time、end_time 其实是不需要更新的。

一条一条记录更新，恐怕要被 CodeView 毒打。

上网冲浪良久，对比了 MySQL 的 replace into、insert into ... on duplicate key 等方案后，还是对 MySQL 在 UPDATE 语句中使用 CASE 操作符 来完成批量更新比较感兴趣。

CASE 操作符

MySQL 中支持 CASE 操作符和 CASE 语句。

简单来说，CASE 语句主要用在复合语句，比如存储过程，它以 CASE 开头、以 END CASE 结尾，不允许使用 ELSE NULL 子句；CASE 操作符则是作为函数用在单条语句中，它以 CASE 开头、以 END 结尾，允许使用 ELSE NULL 子句。

我们这里要介绍的是后者。

语法简介

CASE 操作符又分为两种语法：

CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

CASE 
    WHEN condition THEN result 
    [WHEN condition THEN result ...] 
    [ELSE result] 
END

The first CASE syntax returns the result for the first ***value***=***compare_value*** comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.

也就是说，这两种语法中，CASE 操作符都是逐个计算 WHEN 是否成立，如成立则返回其后 THEN 中的内容；如果所有的 WHEN 都不成立则返回 ELSE 中的内容。

听起来很像 Java 中每个分支自带 break; 和 default 的 switch 语句。

两种语法的区别在于，

第一种语法中，MySQL 是判断 value=compare_value 的结果是否为 true，为 true 则当前 WHEN 成立；
第二种语法中，MySQL 是判断 condition 是否为 true，为 true 则当前 WHEN 成立；

感觉没多大区别？

两种语法浅析

在多数用例中，第一种语法中的 value 会是表中的一个字段，比如我们表中的主键 id：
在这里插入图片描述
但如果是多个字段需要参与比较呢，那就得要使用第二种语法了：

所以这两种语法一个较明显的区别是：相对而言第一种语法适合的场景更简单些；第二种语法则更普适。

当然“更简单”也意味着，在只需要比较一个字段时第一种语法更简洁。

入门指北

另外在第一种语法中，value 不一定是一个字段或者常量，它实际上也可以是一个表达式：

在这里插入图片描述

当一个字段值是 NULL 就返回一个默认值时，其实除了使用 MySQL 的 IFNULL()、IF() 函数外，也可以使用 CASE 操作符：
在这里插入图片描述

但初次接触 CASE 操作符的 CRUD BOY 可能不会写出上例这样的 SQL，更可能会在使用第一种语法时写出这样的 SQL：

select case id
when null then '真棒'
else '加油哇'
end as trans
from (select null as id) a \G

今儿个横竖也得是个 “真棒”！

但 MySQL 有自己的想法：
在这里插入图片描述

原因其实很简单：MySQL 中 NULL 值与任何值（包括 NULL 值本身）比较返回的都是 NULL。

同时，第一种语法中判断的条件是 value=compare_value，

NULL=NULL 的结果是 NULL，所以…听懂掌声！

使用第二种语法可能就不一样了，当你写出 id = null 的时候，你不禁会思考：

嘶~
我应该拿 = 跟 NULL 值比较而不是使用 is null 吗？
我应该在 Java 里使用 == 比较对象而不是使用 .equals() 吗？
… 容我 ChatGPT 一下。

好了，既然我们现在已经掌握了微积分，那么回到业务中，试着解下这个 100 以内的加减乘除问题吧。

CASE 历险记

循规蹈矩 CRUD

作为一个操作符，CASE 可以使用在 SELECT 语句中，当然也能使用在 UPDATE 语句中。

在 UPDATE 语句中使用第一种语法批量更新的 SQL 示例如下：

update todo_member
set status     = case id
                     when 1 then 0
                     when 2 then 20
		    end,
    start_time = case id
                     when 1 then '2023-05-01 12:00:00'
                     when 2 then '2023-10-01 00:00:00'
        end,
    end_time   = case id
                     when 1 then '2023-06-01 12:00:00'
                     when 2 then '2023-12-01 00:00:00'
        end
where id in (1, 2);

我们先来定义一个 DTO，用于记录成员待办的哪些字段需要通过 UPDATE 语句来批量更新：

@Data
public class TodoMemberUpdateDto {

    private Long id;

		private String memberId;

    private LocalDateTime startTime;

    private LocalDateTime endTime;

    private Integer status;

		...
}

只要根据 subjectId + memberIds 去数据库查出对应的数据，

再根据接口里接受到的数据覆盖各个成员需要更新的字段值，

最后使用 UPDATE 语句批量更新不就完事了吗。

部分核心代码如下：

...

// 取出 timeSensitiveMembers 中所有 memberId，和 memberIds 字段取并集并去重
// @formatter:off
Map<String, TimeSensitiveMember> id2SensitiveMember = ofNullable(req.getTimeSensitiveUsers())
    .orElse(newArrayList())
    .stream()
    .filter(Objects::nonNull)
    .collect(toMap(TimeSensitiveMember::getMemberId, identity(), (a, b) -> a));
Set<String> memberIds = ofNullable(req.getMemberIds())
		.orElse(newArrayList())
    .stream()
    .filter(StringUtils::isNotBlank)
    .collect(toSet());
// @formatter:on
SetView<String> allMemberIds = SetUtils.union(id2SensitiveMember.keySet(), memberIds);

// 查询已存在的成员待办数据
List<TodoMemberUpdateDto> dtos = todoMemberMapper.selectBySubjectIdAndMemberIds(req.getSubjectId(), allMemberIds);

// 根据请求内容更新成员待办数据
for (TodoMemberUpdateDto dto : dtos) {
    dto.setStatus(req.getStatus());
    TimeSensitiveMember member = id2SensitiveMember.get(dto.getMemberId());
    if (member != null) {
        dto.setStartTime(member.getStartTime());
        dto.setEndTime(member.getEndTime());
    }
}

// 批量更新
if (isNotEmpty(dtos)) {
    todoMemberMapper.batchUpdateDetails(dtos);
}

...

大胆的想法

一套 CRUD 行云流水。

但是有追求的 CRUD BOY 就说了，楞个拿着数据库又是查又是更新的，天天追着几张表来回抽插的就不会索然无味吗。

既然 todo_member 里都有 (subject_id, member_id) 的唯一索引了，请求里也带着 subject_id 和 member_id 信息，直接更新表里的数据是不是有点搞头？

嘶~

大胆的想法，有点东西。

如果要一条 SQL 直接更新到位，首先我们就不能使用第一种语法的 CASE 操作符了，因为能用的只有 subject_id 和 member_id 字段值，而没有 id 字段值。

其次，我们得要知道更新后的 status、start_time、end_time 数据值，尤其是后两个字段，

对于请求中 memberIds 对应的成员来说，他们不会被指定起止时间，他们的起止时间需要保持库中已有的值，不查数据库怎么知道已有的值是什么。

当然也可以按照目前的逻辑直接把这些 memberIds 的两个时间分别设置成 ‘1000-01-01 00:00:00’ 和 ‘9999-12-31 23:59:59’ 再更新回去，

不过如果后面逻辑再调整，支持成员维度和待办维度起止时间相互转换，也就是待办维度起止时间类型在 todo_member 表里落的不再是 ‘1000-01-01 00:00:00’ 和 ‘9999-12-31 23:59:59’ 的话，那这里就是给自己挖坑了。

试试就逝世

换个思路，如果批量 UPDATE 中允许在设置多个字段值的时候，每个字段设置成不同数量的 WHEN 分支，没有匹配到 WHEN 的就不更新，那也是可行的，类似通过 MyBatis 封装的 updateSelective 方法一样。

也就是说我们想要的是这样的一种语法：

update todo_member
set status     = case
                     when subject_id = 1 and member_id = 'member_1' then 0
                     when subject_id = 1 and member_id = 'member_2' then 0
                     when subject_id = 1 and member_id = 'member_3' then 0
		    end,
    start_time = case
                     when subject_id = 1 and member_id = 'member_2' then '2023-05-01 12:00:00'
        end,
    end_time   = case
                     when subject_id = 1 and member_id = 'member_2' then '2023-06-01 12:00:00'
        end
where (subject_id, member_id) in ((1, 'member_1'), (1, 'member_2'), (1, 'member_3'));

那 MySQL 支持吗，试试先：
在这里插入图片描述

好消息是没有报语法错误，说明这种写法是受 MySQL 支持的；

坏消息是报了个 Column 'start_time' cannot be null 的错误。

嘶~

等下，

这黑底白字明明白白的你 MySQL 也欺负老实猿是不是，哪个地方设置 NULL 值了？

其实还得回到前面介绍 CASE 操作符时的那段官方文档上来：

If no comparison or condition is true, the result after ELSE is returned, **or NULL if there is no ELSE part**.，

没有 ELSE 的 CASE 里，其实是有个默认的 else null。

也就是说上面的 SQL 中，由于 CASE 中没有提供 ELSE 对应的内容，

导致 (1, ‘member_2’) 和 (1, ‘member_3’) 对应的数据在设置 start_time 和 end_time 的时候实际设置的就是 NULL 值。

灵光乍现

咋整，就是想要保持这俩记录的起止时间不变才没写 WHEN 来设置值，现在 WHEN 没匹配上就给整成了 else null，

难不成还非得写死了 ‘1000-01-01 00:00:00’ 和 ‘9999-12-31 23:59:59’ 才算完事？

灵光一现，突然想起曾经为了避免并发更新导致超卖问题的一种 SQL 写法：update xxx set count = count + 1 where …，把字段本身放在操作符两侧。

再来试下：
在这里插入图片描述

完美！！

在 MyBatis 里

SQL 原型有了，那接下来就是把它套到 MyBatis 里了，这活拿手。

<update id="batchUpdateDetails">
    UPDATE todo_member
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="status = case " suffix="end,">
        <foreach collection="dtos" item="dto">
          <if test="dto.status != null">
            when subject_id = #{dto.subjectId} and member_id= #{dto.memberId} then #{dto.status}
          </if>
        </foreach>
        else status
      </trim>
      <trim prefix="start_time = case" suffix="end,">
        <foreach collection="dtos" item="dto">
          <if test="dto.startTime != null">
            when subject_id = #{dto.subjectId} and member_id= #{dto.memberId} then #{dto.startTime}
          </if>
        </foreach>
        else start_time
      </trim>
      <trim prefix="end_time = case" suffix="end,">
        <foreach collection="dtos" item="dto">
          <if test="dto.endTime != null">
            when subject_id = #{dto.subjectId} and member_id= #{dto.memberId} then #{dto.endTime}
          </if>
        </foreach>
        else end_time
      </trim>
    </trim>
    WHERE (subject_id, member_id) in
    <foreach collection="dtos" item="dto" separator="," open="(" close=")">
      (#{dto.subjectId}, #{dto.memberId})
    </foreach>
</update>

当然前面演示的代码逻辑也需要调整，

删几段代码哪个 CRUD BOY 都会，这里就不再拿调整后的 Java 代码凑字数了。

造个数据来回归下接口老逻辑吧，也就是待办起止时间不落在成员维度上的时候：

{
    "subjectId": 1,
    "status": 0,
    "memberIds":
    [
        "member_1",
        "member_2",
        "member_3"
    ]
}

走你！

500！

查日志！

### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL 
server version for the right syntax to use near 'else status end,
      start_time = case else start_time end,
      end_time =' at line 10
### The error may exist in class path resource [mybatis/mapper/todo/TodoMemberMapper.xml]

start_time = case else start_time？WHEN 呢？

ELSE 前面没了 WHEN，显然 MySQL 不惯这毛病。

嘶~

大意了，我没闪！

请求的数据相当于都没有设置 member 的 startTime，

也就是说到批量更新这步，<if test="dto.startTime != null"> 会导致一个 WHEN 都不会生成。

标签里的判断其实是严谨而且必要的，不然就会出现 when* subject_id = 1 *and* member_id = 'member_1' *then null，

问题出在没有考虑所有待更新数据其某个字段可能都不需要更新的情况。

灵光再现

如果挫点，可以在 Mapper 方法的入参里加两个 boolean 字段，传入是否所有对象的 startTime/endTime 值都为空，

然后在 MyBatis 更新 start_time 和 end_time 字段的外层再用这两个 boolean 判断下，看看是不是真的有必要更新这俩字段。

也就是：

<if test="startTimeUpdate">  
  <trim prefix="start_time = case" suffix="end,">
    <foreach collection="dtos" item="dto">
      <if test="dto.startTime != null">
        when subject_id = #{dto.subjectId} and member_id= #{dto.memberId} then #{dto.startTime}
      </if>
    </foreach>
    else start_time
  </trim>
</if>
<if test="endTimeUpdate">  
  <trim prefix="end_time = case" suffix="end,">
    <foreach collection="dtos" item="dto">
      <if test="dto.endTime != null">
        when subject_id = #{dto.subjectId} and member_id= #{dto.memberId} then #{dto.endTime}
      </if>
    </foreach>
    else end_time
  </trim>
</if>

但哪有这么挫的 CRUD BOY，挫的只能不断地添加入参、反复的循环判断。

有没有优雅一些的方式，全是技巧木得感情的那种。

记不记得在 MyBatis 中如果不使用 <where/> 或者 <trim/> 标签，都是怎样让这段查询不会报错的吗：

select id 
from user
where 
<if test="test1">
	and username = #{username}
</if> 
<if test="test2">
	...
</if>

在 WHERE 后面加一个 1=1 啊！

回到我们的 Mapper 中，显然可以这么做：

<trim prefix="start_time = case" suffix="end,">
	when 1 = 0 then null
  <foreach collection="dtos" item="dto">
    <if test="dto.startTime != null">
      when subject_id = #{dto.subjectId} and member_id= #{dto.memberId} then #{dto.startTime}
    </if>
  </foreach>
  else start_time
</trim>
<trim prefix="end_time = case" suffix="end,">
	when 1 = 0 then null
  <foreach collection="dtos" item="dto">
    <if test="dto.endTime != null">
      when subject_id = #{dto.subjectId} and member_id= #{dto.memberId} then #{dto.endTime}
    </if>
  </foreach>
  else end_time
</trim>

当然也可以将其放到各自的标签中，这就看个人喜好了。

更大胆的想法

把时间往回倒十分钟：

update todo_member
set status     = case id
                     when 1 then 0
                     when 2 then 20
		    end,
    start_time = case id
                     when 1 then '2023-05-01 12:00:00'
                     when 2 then '2023-10-01 00:00:00'
        end,
    end_time   = case id
                     when 1 then '2023-06-01 12:00:00'
                     when 2 then '2023-12-01 00:00:00'
        end
where id in (1, 2);

不知道在介绍 CASE 的第一种语法时，你脑海中有没有闪过这样一种更为大胆的想法：

你看，
这都已经在 CASE 里把所有要更新的 ID 枚举出来了，
那还犯得着在 WHERE 里把它们再 IN 一遍吗？

当看到后面应用第二种语法来批量更新的时候，这个念头可能愈发强烈了，

where (subject_id, member_id) in ((1, 'member_1'), (1, 'member_2'), (1, 'member_3'));

这要是给整一百来个 IN，WHRE 条件不得溢出屏幕了，

WHERE 再小也是个带宽，我今儿个可给你省了。

想归想，试总归要试的：
在这里插入图片描述

嘶~

又来。

但这次你已经精通 CASE 操作符了，

显然分支又走到了默认的 else null。

我们沉浸在 “枚举” 的幻想中，忘记了一条古老的信条：

不要忘了 DELETE 语句后面的 WHERE。UPDATE 也一样。

所以上例中，如果不是字段设置了 NOT NULL 约束，我们已经全表更新一遍了，而不仅仅是 “枚举” 的那几条记录。

多思考一步总是好的

使用 CASE 操作符批量更新的时候，很有可能会有这样的一个担忧：

WHEN… THEN… 的个数是与更新的字段数、一次更新的记录数成正比的，

会不会更新的字段数太多，或者一次更新的记录数太多，导致 SQL 语句过大从而报错呢。

确实，MySQL 里是有对单个 SQL 大小的限制（Packet Too Large），

这个限制在服务器端的默认值是 64M。

大致估算下，

假设 SQL 语句的 WHEN… THEN… 绝大部分是英文、数字等 ASCII 范围内字符，

那无论是使用 utf8 还是 utf8mb4 做连接的字符串，一个字符差不多可以按照占一个字节算，

假设我们更新 10 个字段，每个字段中 WHEN… THEN 的平均长度为 500 个字符（我们的示例中满打满算最长也不到 100 个字符），

则大致可以一次更新 64 * 1024 * 1024 / 10 / 500 = 13421 条记录。

一般来讲都会在 SQL 语句之前做好数量控制，是不会一次性更新这么多记录的，

否则慢 SQL 告警可能早早的找上门来了。

好嘞，今儿个就到这。

撤！

参考资料

小皇叔！

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
从一个微服务接口的演变趟 MySQL CASE 操作符的坑

也就是说，这两种语法中，CASE 操作符都是逐个计算 WHEN 是否成立，如成立则返回其后 THEN 中的内容；如果所有的 WHEN 都不成立则返回 ELSE 中的内容。听起来很像 Java 中每个分支自带break;和default的 switch 语句。两种语法的区别在于，第一种语法中，MySQL 是判断的结果是否为 true，为 true 则当前 WHEN 成立；第二种语法中，MySQL 是判断condition。
复制链接

扫一扫