java springboot mybatis mysql 大批量插入 时间比较及查询

背景

• 批量插入带时间戳数据
• 插入后，根据时间戳判定失效数据，然后删除

注意事项

• new Date()创建的时间值，会带小数点如2021-01-13 15:07:47.815，保存到mysql数据库时，小数值会四舍五入变成2021-01-13 15:07:48，查询失效数据时进行时间条件判断会有出入。现象如下：

--失败 
select * from base_station_test WHERE domain='1' and net_workg='2g' and create_time = '2021-01-13 15:07:47.815';
--成功
select * from base_station_test WHERE domain='1' and net_workg='2g' and create_time = '2021-01-13 15:07:48';

要点示例

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;


    private void updateDate(List<Map<String, Object>> mapList, String domain, String net_workg)  {
        try {
            Date creatTime = DateUtils.parseDate(DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"),"yyyy-MM-dd HH:mm:ss");
//            creatTime = new Date();
            List<BaseStation> domainList=mapList2DomainList(mapList,domain,net_workg,creatTime);
//测试（大批量插入及删除）：条数-耗时：6300条-2s,16800条-5s，24000条-7s 67300条-18s
            for (int i=0,j=0,betw=3000; i < domainList.size(); i+=betw) {
                j=i+betw;
//betw可取值3000/5000/10000/。。。测看大批量效果。
                try{
//                baseStationMapper.insertOne(domainList.get(i));
                    if(j<domainList.size()) {
                        baseStationMapper.insertBatch(domainList.subList(i, j));
                    }else{
                        baseStationMapper.insertBatch(domainList.subList(i, domainList.size()));
                        break;
                    }
                }catch (Exception e){
                    log.error(domainList.get(i));
                    e.printStackTrace();
                }
            }
            baseStationMapper.deleteDomainOldData(domain,net_workg,creatTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    void insertBatch(@Param("list")List<BaseStation> dataList);

  <insert id="insertBatch" >
    INSERT INTO  base_station_test
    (
    `enode_bid`, `eci`, `domain`, `net_workg`, `lac`,
    `cellid`, `create_time`, `longitude`, `latitude`, `base_station_type`,
    `province`, `station_name`, `ne_name`, `city`, `county`,
    `address`, `village_longitude`, `village_latitude`, `antenna_angle`,`cover_radius`,
    `update_time`
    ) VALUES
    <foreach collection ="list" item="info" separator="," >
      (
      #{info.enodeBID},#{info.eci},#{info.domain},#{info.network},#{info.lac},
      #{info.cellid},#{info.createTime},#{info.longitude},#{info.latitude},#{info.baseStationType},
      #{info.province},#{info.stationName},#{info.neName},#{info.city},#{info.county},
      #{info.address},#{info.villageLongitude},#{info.villageLatitude},#{info.antennaAngle},#{info.coverRadius},
      #{info.updateTime}
      )
    </foreach>
  </insert>

  <delete id="deleteDomainOldData">
    <if test="domain != null  and net_workg != null and create_time != null">
      delete from base_station_test where domain=#{domain} and net_workg=#{net_workg}  and create_time != #{create_time}
    </if>
  </delete>

SELECT * from base_station_test where create_time = str_to_date('2021-01-13 10:06:09', '%Y-%m-%d %H:%i:%s')
DELETE from base_station_test where create_time !='2021-01-13 10:06:09'
delete FROM base_station_test WHERE domain='1' and net_workg='2g' and create_time = '2021-01-13 14:38:29.52';