一个需求的三种实现(sql)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 一个需求的三种实现(sql)

需求1:系统有一张订单表futao_order,该订单表的数据根据第三方进销存系统的数据生成,现需要同步进销存的订单,如果订单已经同步过,则skip,否则insert新订单

  • 表结构

-- auto-generated definition
create table futao_order
(
  id             varchar(32)                         not null
  comment '订单主键',
  userId         varchar(32)                         not null
  comment '用户id',
  erpOrderId     varchar(32)                         not null
  comment '进销存订单id',
  remark         varchar(300)                        null
  comment '备注',
  createTime     timestamp default CURRENT_TIMESTAMP not null
  comment '创建时间',
  lastModifyTime timestamp default CURRENT_TIMESTAMP not null
  comment '最后修改时间',
  constraint futao_order_id_uindex
  unique (id)
)
  comment '订单表';
alter table futao_order
  add primary key (id);
  • 思路1(通过java代码实现):  根据erpOrderId查询订单表,如果数据不存在则insert新订单,如果存在则继续判断下一条erpOrderId


  • 代码实现:
  • dao层-根据erpOrderid查询订单信息

/**
     * 根据erpOrderid查询订单信息
     *
     * @param erpOrderId 进销存订单编号
     * @return 订单实体
     */
    @Select("select * from futao_order where erpOrderId=#{erpOrderId}")
    Order queryIfExistByErpOrderId(@Param("erpOrderId") String erpOrderId);
  • service层-根据erpOrderId查询订单是否存在

/**
     * 根据erpOrderId查询订单是否存在
     *
     * @param erpOrderId
     * @return
     */
    @Override
    public Boolean queryIfExistByErpOrderId(String erpOrderId) {
        Order order = orderDao.queryIfExistByErpOrderId(erpOrderId);
        return order != null;
    }
  • dao层-新增订单

/**
     * 新增订单
     *
     * @param id             订单id
     * @param userId         用户id
     * @param erpOrderId     进销存订单id
     * @param createTime     创建时间
     * @param lastModifyTime 最后修改时间
     * @return 插入的条数
     */
    @Insert("insert " +
            "into futao_order(id,userId," +
            "erpOrderId,createTime,lastModifyTime) " +
            "values(#{id},#{userId},#{erpOrderId}," +
            "#{createTime},#{lastModifyTime})")
    int add(@Param("id") String id, @Param("userId") String userId,
            @Param("erpOrderId") String erpOrderId,
            @Param("createTime") Timestamp createTime, @Param("lastModifyTime") Timestamp lastModifyTime);
  • service层-新增订单

/**
     * 新增订单
     *
     * @param erpOrderId 进销存订单id
     * @return
     */
    @Override
    public int addErpOrder(String erpOrderId) {
        Timestamp currentTimeStamp = currentTimeStamp();
        return orderDao.add(UUIDService.get(), userService.currentUser().getId(), erpOrderId, currentTimeStamp, currentTimeStamp);
    }
  • 同步进销存订单方法

@Override
    public String sync(int times) {
        long startTime1 = System.currentTimeMillis();
        for (int i = 0; i < times; i++) {
            String uuid = UUIDService.get();
            if (!queryIfExistByErpOrderId(uuid)) {
                addErpOrder(uuid);
            }
        }
        long time1 = System.currentTimeMillis() - startTime1;
        return "先查询再新增耗时:" + time1
}
  • ok,这是第一种实现方式,通过java代码来判断订单是否存在,再决定是否将数据插入数据库。


  • 思路2(通过sql实现): 通过一条sql,如果我们的条件(不存在指定的erpOrderId)成立,则新增,否则啥也不做


  • 先上mysql写法

insert into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
SELECT '3', '1', '1', current_timestamp, current_timestamp
from dual
where not exists(select id from futao_order where id = '0018ec3a7e0d4dbeb5b99e436f94ad57');
或者(可省略from dual)
insert into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
SELECT 主键, 用户id, 进销订单id, current_timestamp, current_timestamp
where not exists(select * from futao_order where id = '1');
  • oracle写法,不能省略from dual

insert into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
SELECT '3', '1', '1', current_timestamp, current_timestamp
from dual
where not exists(select id from futao_order where id = '0018ec3a7e0d4dbeb5b99e436f94ad57');
  • mybatis

/**
     * @param id
     * @param userId
     * @param erpOrderId
     * @param createTime
     * @param lastModifyTime
     * @return
     */
    @Insert("insert " +
            "into futao_order(id,userId,erpOrderId," +
            "createTime,lastModifyTime)  " +
            "select #{id},#{userId},#{erpOrderId}," +
            "#{createTime},#{lastModifyTime} " +
            " where not exists (" +
            "select id from futao_order where erpOrderId=#{erpOrderId})")
    int addOrder(@Param("id") String id, @Param("userId") String userId,
                 @Param("erpOrderId") String erpOrderId,
                 @Param("createTime") Timestamp createTime, @Param("lastModifyTime") Timestamp lastModifyTime);
  • 还有思路3:根据需求可以知道,erpOrderId在订单表中是唯一的,所以可以在数据库中给erpOrderId字段添加唯一约束UNIQUE。随后java代码就可以将所有数据直接执行insert狂怼入DB,如果存在之前已经插入过的erpOrderId,数据库必定会报异常,插入失败,我们只需要catch住异常,不影响程序的执行,并继续往后执行即可,新erpOrderId数据因为没有发生异常,会正常插入数据库。


需求2:在需求1的基础上,如果数据库中已经存在指定的erpOrderId,则更新这条数据,否则进行新增(这类需求非常常见,存在即更新、不存在则插入)


思路1:类似需求1的思路1,先通过一条sql查询数据库中是否已经存在满足条件的数据,如果不存在再执行另外一条insert sql。


思路2:一条sql搞定。通过sql判断有没有满足我们条件的数据,如果存在则执行update操作,否则执行insert操作,都在一条sql中。


  • oracle的实现

merge into futao_order T1
merge into futao_order T1
using(select erpOrderId as a from dual) T2
on (T1.erpOrderId=T2.a)
when matched then
  update set T1.remark='备注'
when not matched then
  insert (id, userId, erpOrderId, createTime, lastModifyTime)
  values('3', '1', '1', current_timestamp, current_timestamp)
  • mysql实现(需要设置erpOrderId为唯一索引约束)

-- 如果唯一索引命中,则执行insert操作,否则执行update操作,update哪些字段由update后面的语句决定
insert into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
values ('1', '11111', '11111', current_timestamp, current_timestamp)
on duplicate key update erpOrderId = '1111';
-- 如果唯一约束命中则删除之前的数据,在重新插入修改之后的数据
replace into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
values ('99', '11111', '99', current_timestamp, current_timestamp)

mysql没有oracle那么灵活,有一个比较坑的地方是,如果一张表中有很多唯一索引,必须所有的唯一索引约束都未命中,才会执行insert,否则命中一个唯一索引约束就会执行update


  • 结合项目,使用mysql+mybatis实现需求

/**
     * ByDuplicateKey
     *
     * @param id
     * @param userId
     * @param erpOrderId
     * @param remark
     * @param createTime
     * @param lastModifyTime
     * @return
     */
    @Insert("insert into futao_order (id, userId, erpOrderId,remark, createTime, lastModifyTime)" +
            "values (#{id}, #{userId}, #{erpOrderId}, #{remark},#{createTime}, #{lastModifyTime})" +
            "on duplicate key update erpOrderId = #{erpOrderId},remark=#{remark}")
    int addOrUpdateByDuplicateKey(@Param("id") String id, @Param("userId") String userId,
                                  @Param("erpOrderId") String erpOrderId, @Param("remark") String remark,
                                  @Param("createTime") Timestamp createTime, @Param("lastModifyTime") Timestamp lastModifyTime);
    /**
     * ByReplace
     *
     * @param id
     * @param userId
     * @param erpOrderId
     * @param remark
     * @param createTime
     * @param lastModifyTime
     * @return
     */
    @Insert("replace into futao_order (id, userId, erpOrderId,remark, createTime, lastModifyTime)" +
            "values (#{id}, #{userId}, #{erpOrderId}, #{remark},#{createTime}, #{lastModifyTime})")
    int addOrUpdateByReplace(@Param("id") String id, @Param("userId") String userId,
                             @Param("erpOrderId") String erpOrderId, @Param("remark") String remark,
                             @Param("createTime") Timestamp createTime, @Param("lastModifyTime") Timestamp lastModifyTime);


  • 测试
  • byDuplicateKey测试
    新增


image.png

image.png

修改


image.png

image.png

再来一条


image.png

byReplace测试


新增


image.png


修改

image.png

再来一条


image.png

image.png


需要注意,byReplace方法会删除之前的数据再新增,byDuplicateKey是在原来的基础上update,请关注测试结果的时间戳

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3月前
|
SQL 数据库 开发者
SQL中为什么不要使用1=1?
【8月更文挑战第11天】在SQL查询语句中,偶尔会遇到使用1=1作为WHERE子句一部分的情况,这种做法看似无害,实则隐藏着一些潜在的问题和更好的替代方案。本文将深入探讨为什么不建议在SQL中使用1=1,并分享更优化的查询构建策略。
92 2
|
4月前
|
SQL 存储 大数据
SQL技巧
【7月更文挑战第26天】SQL技巧
24 1
|
6月前
|
SQL 数据库 索引
八、SQL-Limite
八、SQL-Limite
48 0
|
SQL 存储 数据库
SQL 能做什么?
SQL 能做什么?
113 0
|
SQL 存储 缓存
或许你不知道的12条SQL技巧
或许你不知道的12条SQL技巧
|
SQL Oracle 关系型数据库
SQL必知必会(三)
作用是从一个或多个表中检索信息
|
SQL 存储 搜索推荐
几个SQL问题
几个SQL问题
134 0
|
SQL 存储 监控
xttstartupnomount.sql
connect / as sysdba; startup force nomount; exit;
723 0