mysql批量插入

错误写法

insert into `oactivity_people`(hr_id,type,remark,open_id) values(
select * from (select hr_id,'massage','高管',wx_id from staff where st_id >4610 and st_id <6000 and wx_id is not null )
)  

快捷技巧
select group_concat(distinct COLUMN_NAME) from information_schema.COLUMNS where table_name = ‘表名’;

正确写法
批量插入

insert into `oactivity_people`(hr_id,type,remark,open_id) 
select hr_id,'massage','高管',wx_id from staff where st_id >4610 and st_id <6000 and wx_id is not null  

批量更新

UPDATE omeal_record
        SET batch_id = #{batchId}
        WHERE  order_date = CURRENT_DATE
            AND batch_id IS NULL
            and lunch = 1
            AND id IN (
                       select id from (
                          SELECT	omr.id
                                     FROM	meal_record omr,
                                    meal_statitics oms
								      where 	omr.dept_code = oms.dept_code
                                                AND omr.batch_id IS NULL
                                                and omr.lunch = 1
                                                and omr.order_date = CURRENT_DATE
								                and oms.order_date = CURRENT_DATE
								                and oms.company_type = #{companyType}
								                and omr.company_type = #{companyType}
                                     ORDER BY    oms.lunch_num,omr.dept_code,omr.dept_level,omr.real_dept_code
								     limit #{perBatchSize}
                                     ) t
                       )

批量删除

   DELETE
        cp,cpi,sqi,slqi FROM prc_comparison_price cp
        LEFT JOIN prc_comparison_price_item cpi ON cp.id = cpi.price_id
        LEFT JOIN prc_supplier_quotation_info sqi ON cpi.id = sqi.price_item_id
        LEFT JOIN prc_supplier_ladder_quotation_info slqi ON sqi.id = slqi.supplier_quotation_id
        WHERE cp.id =
        #{id};

见其他书签优化（inset value (),(),()…其实不快）[LOAD DATA]

    saveBatch(notNullList, 1000);   // 1953条 5034ms
        insertTask.stop();
        log.info("重新生成工作日之批量插入用时：{}", insertTask.getTotalTimeMillis());
    }

    /**
     * 多线程+批量插入
     *
     * @param list
     * @param dealSize
     */
    private void saveBatch(List<EhrCheckworkAttendanceDetailsEntity> list, int dealSize) {
        int totalSize = list.size();
        //线程数
        int runSize = (totalSize / dealSize) + 1;
        //thread
        ThreadFactory namedThreadFactory = new ThreadFactoryBuilder().setNameFormat("ehr-saveBatch-%d").build();
        ExecutorService executor = new ThreadPoolExecutor(5, 10, 0L, TimeUnit.MILLISECONDS, new LinkedBlockingQueue(), namedThreadFactory);
        CountDownLatch latch = new CountDownLatch(runSize);

        for (int i = 0; i < runSize; i++) {
            //计算每个线程执行的列表数据
            int startIndex = (i * dealSize);
            List<EhrCheckworkAttendanceDetailsEntity> threadList;
            if ((i + 1) == runSize) {
                int endIndex = totalSize;
                threadList = list.subList(startIndex, endIndex);
            } else {
                int endIndex = (i + 1) * dealSize;
                threadList = list.subList(startIndex, endIndex);
            }

            executor.execute(
                    () -> {
                        try {
                            ehrCheckworkAttendanceDetailsService.insertBatch(threadList);
                        } finally {
                            latch.countDown();
                        }
                    });
        }

        try {
            latch.await();
        } catch (Exception e) {
            e.printStackTrace();
        }
        executor.shutdown();
        try {
            //等待所有子线程任务执行完毕再结束
            executor.awaitTermination(1, TimeUnit.DAYS);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

    }