各数据库千万级数据构造

前提

领导希望看到平台支持的各种数据库在大数据量的情况分页，查询的情况，所以就有了前提工作，准备构造千万级的数据，在网上找了各种数据库别人构造的文档，后面整理了这篇。

一、sqlserver

declare  @i int
declare  @j int
set  @i = 1
while @i <= 1000
begin
set  @j = 1
begin  tran 
while @j <= 10000
begin
insert into
   [zlyTest].[dbo].[SS_TYPE](test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11,test12,test13,test14,test15,test16,test17,test18,test19) values(1,'1','1','2021-04-14 10:23:03','2021-04-14 10:23:06','2021-04-14 10:23:08',1.1,1.1,1,'1','1','1',1,'1','2021-04-14 10:23:29',1,'1','1','1')
set  @j = @j + 1 
end
commit tran
set @i = @i + 1
end

执行结果

按1万条提交一次，1000万大约要50分钟

二、mysql

DROP PROCEDURE IF EXISTS proc_buildata;
DROP PROCEDURE IF EXISTS proc_buildata_fast;

CREATE PROCEDURE proc_buildata(IN loop_times INT)
BEGIN
        DECLARE var INT DEFAULT 0;
        SET AUTOCOMMIT=0;
        START TRANSACTION;
        WHILE var<loop_times DO
                SET var=var+1;
                INSERT INTO ZLYTEST.SS_TYPE (test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11,test12,test13,test14,test15,test16,test17,test18,test19,test20,test21,test22,test23,test24,test25) VALUES (1,'1','1','1','2021-04-14 00:00:00','2021-04-14 11:01:08',1,1.1,1,1,1,'1','1','1','1','1',1,1,'1','1970-01-01 11:01:28','2021-04-14 11:01:32','1',1,'1','1');
        END WHILE;
        COMMIT;
        SET AUTOCOMMIT=1;
END;

CREATE PROCEDURE proc_buildata_fast(IN loop_times INT)
BEGIN
        DECLARE single_max INT DEFAULT 100000;
        DECLARE i INT DEFAULT 0;
        DECLARE max INT DEFAULT 0;
        SET max=loop_times/single_max;
        SET i=0;
        WHILE i<max DO
                SET i=i+1;
                CALL proc_buildata(single_max);
        END WHILE;
END;

CALL proc_buildata_fast(10000000);

执行结果

按10万条提交一次，1000万大约要91分钟

三、oracle

declare
i integer; --定义变量
begin
i := 1;
loop
/* 插入数据 */
 INSERT INTO SS_TYPE (test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11,test12,test13,test14,test15,test16) VALUES ('1','1','1',to_date('2021-04-14 11:21:11' , 'yyyy-mm-dd hh24:mi:ss'),1.1,1.1,1,1,'1',1.1,1.1,'1',1,to_date('2021-04-14 11:21:20' , 'yyyy-mm-dd hh24:mi:ss'),'1','1');
/* 参数递增 */
i := i + 1;
/* 停止条件 */
exit when i > 10000000;
end loop;
commit;
end;

四、达梦

DECLARE
 i int :=1;
BEGIN
   FOR i IN 1..10000000 LOOP
 INSERT INTO "ss_type"(COLUMN_1,COLUMN_2,COLUMN_3,COLUMN_4,COLUMN_5,COLUMN_6,COLUMN_7,COLUMN_8,COLUMN_9,COLUMN_10,COLUMN_11,COLUMN_12,COLUMN_13,COLUMN_14,COLUMN_15,COLUMN_16,COLUMN_17,COLUMN_18,COLUMN_19) VALUES ('1','1','1',1.1,1.1,1.1,1,1,1,1,1,1.1,1.1,'2021-04-14 14:11:06','2021-04-14 14:11:03','2021-04-14 14:11:05','2021-04-14 14:11:01','1','1');
   END LOOP;
END;
COMMIT

执行时间

1000万大约要15分钟

五、人大金仓

CREATE OR REPLACE PROCEDURE proc_buildata() AS
DECLARE
v_id integer :=1;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO "ss_type"(test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11,test12,test13,test14,test15,test16,test17,test18,test19,test20,test21,test22) VALUES ('1','1','2021-04-14 11:45:28','2021-04-14 11:45:26','2021-04-14 11:45:30',1,1,1,1,1,1,1,1.1,1.1,1.1,'1','1','1',false,1,1,'1');
v_id := v_id+1;
END LOOP;
END;
CALL proc_buildata();

六、神通

declare
i integer; --定义变量
begin
i := 1;
loop
/* 插入数据 */
 INSERT INTO "SS_TYPE"(COLUMN1,COLUMN4,COLUMN7,COLUMN8,COLUMN9,COLUMN10,COLUMN11,COLUMN18,COLUMN19,COLUMN20,COLUMN21,COLUMN22,COLUMN23,COLUMN24,COLUMN25,COLUMN26,COLUMN27) VALUES('1','1','1','1','2021-04-14 11:45:28',1.1,1.1,'1',1.1,1.1,1.1,1,'1','2021-04-14 11:45:26','2021-04-14 11:45:30','1','1');
/* 参数递增 */
i := i + 1;
/* 停止条件 */
exit when i > 100000;
end loop;
commit;
end;