表复制

SQL语法结构：insert into a_table（字段1，字段2，字段3，......）select 字段1，字段2，字段3，...... from b_table

为什么要使用表复制

只要是做后台开发的，都会遇到一种表结构“头行结构”，大家对这这种结构是又爱又恨，因为头行结构复杂起来十分的复杂，有时候头行结构在进行更新或者插入的时候涉及到的表数量多，而且逻辑复杂。现在要说的是表复制在头行结构的插入操作中能解决的一些需求问题。

当出现如下类似需求时表复制的好处

1.头数据和行数据对应关系为1：n，且头数据的id在行数据中做逻辑外键
2.需要批量新增头数据和行数据，且数量不固定，数据量的大的时候一次性通过excel导入几百条甚至更多
拿到这个需求大多数人想到的是通过mybatis的forach标签进行批量插入，但是这种方式会有问题：行数据需要存头数据的主键id，因此直接批量不能对行数据使用，那么头数据只能使用单个插入，插入成功拿到头数据的id放入行数据中然后在使用forach标签进行批批量操作，这看似没问题，满足了业务需求，但是忽视了一点 “性能”，如果头数据：行数据是1:10，如果采用这种办法，假设插入10条头数据，和数据库的链接次数是20次，而数据库需要执行的sql条数是10+10*10一共是110条虽然数据库单条执行次数非常快（除去复杂的SQL）但是当一次性执行的SQL条数过多，等待结果的时间就变长了，因此这种方式在项目进行压力测试会暴露出问题，这个时候表复制这种方式就能派上用处，使用表复制方式进行批量新增的时候对于一张表的操作来说就是一条SQL，对于行表来说也只有一条SQL，总共就两条SQL数据库连接次数只有两次，虽然数据量大的时候SQL执行的时间会加长，但是执行时间远远小于上面的方式，而且还能返回数据的影响总行数

头表批量新增SQL

<insert id="insertList" parameterType="java.util.List">
    insert into fnd_andon_bin_b(
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TENANT_ID,
ID,ANDON_BIN_CODE,ANDON_BIN_TYPE,WORKCELL_ID,PROD_LINE_ID,LOCATION_ID,MAC_ADDRESS,ANDON_BIN_CONTROL)
select CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TENANT_ID,
ID,ANDON_BIN_CODE,ANDON_BIN_TYPE,WORKCELL_ID,PROD_LINE_ID,LOCATION_ID,MAC_ADDRESS,ANDON_BIN_CONTROL
from (
<foreach collection="list" item="item" separator="union all">
        select
# {item.createdBy} as CREATED_BY,
# {item.creationDate} as CREATION_DATE,
# {item.lastUpdatedBy} as LAST_UPDATED_BY,
# {item.lastUpdateDate} as LAST_UPDATE_DATE,
# {item.lastUpdateLogin} as LAST_UPDATE_LOGIN,
# {item.tenantId} as TENANT_ID,
#{item.id} as ID,
#{item.andonBinCode} as ANDON_BIN_CODE,
#{item.andonBinType} as ANDON_BIN_TYPE,
#{item.workcellId} as WORKCELL_ID,
#{item.prodLineId} as PROD_LINE_ID,
#{item.locationId} as LOCATION_ID,
#{item.macAddress} as MAC_ADDRESS,
#{item.andonBinControl} as ANDON_BIN_CONTROL
    ) item_list
</insert>

这里也用到了foreach标签，这里起到的作用是把集合中的数据迭代出来，形成一张临时表然后在借用表复制进行数据插入,看到这里大家会有疑问了，行表里面的外键是的值怎么来（头表的主键id）这里有两种解决办法，每一行数据在在进行维护的时候一定会有些字段是不能与其他字段重复的（基本上这样，如果头表里面的数据允许所有字段都允许重复，那就没有维护数据的意义）在进行数据的实体类型转换的时候可以在行数据中新增几个字段，比如说行表有两个字段需要唯一，那么行数据实体中也添加这两个字段，在进行dto->entity或者其他对象转换的时候将行数据对象的这两个字段赋值给对应的对象，行数据获取头数据主键id的第一种方式（这个在我目前的项目是支持数据回写，至于其他的项目上情况如何暂时没有验证），通过java里面的比较去匹配拿头数据主键

  List<FndAndonBinBase> binList = this.buildBin(insertList);
            // 插入fnd_andon_bin_b表
            binBaseMapper.insertList(binList);
            // 插入fnd_andon_bin_tl表
            List<FndAndonBinTl> fndAndonBinTls = this.buildBinTl(insertList, binList);
            binTlMapper.insertList(fndAndonBinTls);

 private List<FndAndonBinTl> buildBinTl(List<AndonBinDTO> list, List<FndAndonBinBase> baseList) {
        List<FndAndonBinTl> returnList = new ArrayList<>();
        for (AndonBinDTO dto : list) {
            // COL_BIN和COL_WORKCELL在来源数据中必须是唯一不能重复
            FndAndonBinTl fndAndonBinTl = new FndAndonBinTl();
            Long andonBinId = Change.getLong(dto.getId());
            if (andonBinId == null) {
                // 获取头数据的主键id，赋值给语言表做外键关联
                andonBinId = baseList.stream().filter(a -> a.getAndonBinCode().equals(dto.getAndonBinCode())
                        && a.getWorkcellId().equals(Change.getLong(dto.getWorkcellId()))).map(FndAndonBinBase::getId)
                        .collect(Collectors.toList()).get(0);
            }
    

这里使用的是框架会在新增之后返回id到实体中的这性质，第二种方式是直接在SQL中进行，行数据中有头数据的字段，且这些字段在行数据是需要唯一存在的，将foreach迭代出来的表和头数据表进行管理查询，获取头数据的主键id，以及其他需要插入的数据，这里又可以看成是一张新的表，在将这张新表与需要新增的行数据表进行表复制操作

批量更新使用“多表”关联更新

这里说的 "多表"只是一张逻辑上的表，并不是实际存在表，在实际的业务上会遇到批量更新这种需求，有些开发者拿到需求第一反应就是mybatis的foreach标签,但是你会发现，需要更新的数据每一行实际内容的都不一样，而且更新的条件就是id等于某个或者其他列作为更新判断，当发现foreach解决不了，这个时候又会想到在java里面循环，循环虽然能解决问题，但却牺牲了系统性能，更新一行就和数据库连接一次，在压力测试的时候依然会暴露问题，那有没有在满足需求的同是有尽量少浪费性能？有的，请看下图SQL

<update id="updateList" parameterType="java.util.List">
    update fnd_andon_bin_b as b,
(<foreach collection="list" item="bin" separator="union all">
    select
#{bin.lastUpdatedBy} as LAST_UPDATED_BY,#{bin.lastUpdateDate} as LAST_UPDATE_DATE,
#{bin.lastUpdateLogin} as LAST_UPDATE_LOGIN,#{bin.id} as ID,#{bin.andonBinCode} as ANDON_BIN_CODE,
#{bin.andonBinType} as ANDON_BIN_TYPE,#{bin.workcellId} as WORKCELL_ID,#{bin.prodLineId} as PROD_LINE_ID,
#{bin.locationId} as LOCATION_ID,#{bin.macAddress} as MAC_ADDRESS,#{bin.andonBinControl} as ANDON_BIN_CONTROL,
#{bin.enabledFlag} as ENABLED_FLAG
</foreach>) as a
set
a.LAST_UPDATED_BY = b.LAST_UPDATED_BY,a.LAST_UPDATE_DATE = b.LAST_UPDATE_DATE,a.ENABLED_FLAG = b.ENABLED_FLAG
a.LAST_UPDATE_LOGIN = b.LAST_UPDATE_LOGIN,a.ANDON_BIN_CODE = b.ANDON_BIN_CODE,
a.ANDON_BIN_TYPE = b.ANDON_BIN_TYPE,a.WORKCELL_ID = b.WORKCELL_ID,a.PROD_LINE_ID = b.PROD_LINE_ID,
a.LOCATION_ID = b.LOCATION_ID,a.MAC_ADDRESS = b.MAC_ADDRESS,a.ANDON_BIN_CONTROL = b.ANDON_BIN_CONTROL,
where
a.ID = b.ID
</update>

<foreach>中只是将需要更新的数据迭代出来，采用SQL的自身属性，将其看成一张表b，set后面写的是你需要将表b那些列对应的值赋表a对应的列，where后面的条件进行更新筛选，a表中那些行需要更新，只有当a和b表的行同时满足where后面的条件，才会将b表当前满足条件的行x的列上的值赋给a表中满足当前条件的y行的对应列，这中写法不但减少了性能开发，还能返回所有的影响行数

注意！！！！在使用上述两种SQL的过程中，如果涉及到的表关联逻辑多，造成表数量多，一定要使用DISTINCT去重，否则会导致SQL异常