MySQL 批量插入数据，用存储过程

-- ------------------------------------------------------ 插入 30万条数，弊端id不是代码生成的，看需求吧
-- 仅仅导入数据

-- ----创建两个临时表
-- 主表 
create table queuemanage_202208261407 as select * from queuemanage where 1=2;
-- 子表
create table queuemanagelist_202208261407 as select * from queuemanagelist where 1=2;

-- ---- 新建存储过程
DROP PROCEDURE IF EXISTS queuemanage_bulk_data;
DELIMITER $$
CREATE PROCEDURE queuemanage_bulk_data()
BEGIN

    DECLARE i INT DEFAULT 1;
    WHILE i<300000 DO
    #DECLARE i INT DEFAULT 1562980410;
    #WHILE i<1563280410 DO

        INSERT INTO queuemanage_202208261407 ( ID, MINECODE, NOTICENO, PLANNO, CUSTNO, PREAMOUNT, VARNO, MORPHOLOGY, PACKAGETYPE, ISWEIGHT, ISFBWEIGHT, DRIVERNAME, DRIVERID, DRIVERPHONE, SHIPTYPE, EXCHANGETYPE, VEHICLENO, SUBVEHICLENO, VEHICLETYPE, TRANSITCOM, ARRIVETIME, BOXNUM, FLAG, SUPERCARGO, SUPERCARGOID, TAREFLAG, EFFFLAG, NEXTFLAG, INPUTER, INPUTTIME, AUDITFLAG, ORDERTIME, IS_UPLOAD, UP_FLAG, FREEZE_FLAG, AXLES, ISSUPERCARGO, receiving_address ) VALUES ( i, '10017748', '20220825', '1562765089303158786', '30022104', 32, '11520532', '3', '1', '0', '0', '衣林', '510121198807102918', '15270041587', '0', '1', '鲁V25961', '鲁V5961挂', '', '1061327', '2022-08-26', '', '0', '', '', '0', '1', '', '1123598821738675201', '2022-08-26 09:15:46', '0', '2022-08-26 09:15:46', '1', '0', '1', '', '0', '天津敦煌包装有限公司' );

INSERT INTO queuemanagelist_202208261407 ( ID, MINECODE, MAINID, NOTICENO, PLANNO, WAREHOUSECODE, CUSTNO, VARNO, VARCODE, STATUS, ASSAYFLAG, ASSAYCOMPLETE, PREAMOUNT, WAREHOUSER, WAREHOUSETIME, INPUTTIME, receiving_address ) VALUES ( LEFT(MD5(UUID()),10), '10017748', i, '20220825', '1562765089303158786', '1562765873906442240', '30022104', '11520532', '2420D-新疆', '1', '0', '0', 32, '', '', '2022-08-26 09:15:47', '天津敦煌包装有限公司' );
        SET i = i+1;
    END WHILE;
END $$

-- 执行 并插入临时表
CALL queuemanage_bulk_data();


-- 通过临时表插入到正式表
insert into queuemanage  select * from queuemanage_202208261407;
insert into queuemanagelist  select * from queuemanagelist_202208261407;

-- 完成！


-- 这里开始清理和删除
-- 根据身份证号删除刚才导入的派车单
delete queuemanage,queuemanagelist from queuemanage  left join queuemanagelist  on queuemanage.id = queuemanagelist.MAINID WHERE queuemanage.driverid = '510121198807102918';


-- 清理两个临时表
delete from queuemanage_202208261407;
delete from queuemanagelist_202208261407;

-- 删除两个临时表
drop table queuemanage_202208261407;
drop table queuemanagelist_202208261407;

 

一个例子，看自己需求吧。