数据库创建大数据的语法

MYSQL(前置条件：需要创建表里面有几下字段，注意：表字段需要设置字符集（如 name%utf8）)

TRUNCATE student：清空表
select count(*) FROM  student;  统计表数据

CREATE PROCEDURE `insert_operate_log_procedure`(IN circulation INT)
BEGIN
   DECLARE i INT;
   SET i = 1;
   WHILE i <= circulation DO
           INSERT INTO `my_test`.`student`(`id`, `name`, `sex`, `birth`, `department`, `address`)
           VALUES(CONCAT(10000 + i), CONCAT('张先生', i), if(i % 2, '男', '女'), FLOOR(1980 + RAND() * 20), '计算机系', '北京市海淀区');
           SET i = i + 1;
    END WHILE;
END

方法二：

DROP PROCEDURE IF EXISTS `test_insert`;

CREATE PROCEDURE test_insert(IN `num` int)

begin    
    declare i int;   
    set i=0;
    while i < num do
        insert into TB_QUEUE(PHONE,MSG,SID) values('18774888888',CONCAT('测试',i), i);
        set i=i+1;
    end while;
END;
 
call test_insert(1);

 

 

ORACLE生成大量测试数据

TRUNCATE  table student：清空表

select file_name,bytes/1024/1024 "Size MB" from dba_data_files;  查看表空间

select file_name,bytes/1024/1024 "Size MB" from dba_data_files where tablespace_name='TESTTABLE';  查看表空间

alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_DATA01.DBF' offline drop;  删除文件无法启动数据库，需要此操作

alter database open;

create table TestTable as
select rownum as id,
to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
trunc(dbms_random.value(0, 100)) as random_id,
dbms_random.string('x', 20) random_string
from dual
connect by level <= 5000000;

追加

insert into TestTable
(ID, INC_DATETIME,RANDOM_ID,RANDOM_STRING)
select rownum as id,
to_char(sysdate + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
trunc(dbms_random.value(0, 100)) as random_id,
dbms_random.string('x', 20) random_string
from dual
connect by level <= 5000000;

方法二：

--插入多条数据

--插入多条数据
DECLARE
   i number;
begin
   i := 0;
   while i < 10 loop                                                
      insert into TB_QUEUE (PHONE,MSG,SID 
      ) values ('18774888888','测试...',i);
      i := i + 1;
    end loop;
    commit;
end;

 

 

PostgreSQL生成大量测试数据

select pg_table_size(‘表名’)：查看表空间

TRUNCATE  table student：清空表

create or replace function creatData2() returns 
boolean AS
$BODY$
declare ii integer;
begin
II:=1;
FOR ii IN 1000..2000 LOOP
INSERT INTO "t_member_score_item2"("member_score_item_id", "member_code", "score_type", "score", "sys_no", "remark", "insert_time", "insert_oper", "update_time", "update_oper") VALUES
(ii, '01862204522573', '06', '100.000000', '04', NULL, now(), 'mv', now(), 'mv');
end loop;
return true;
end;
$BODY$
LANGUAGE plpgsql;

 

select * from creatData2() as tab;
select * from t_member_score_item2

 

sql server生成大量测试数据

DECLARE @test INTEGER;
SET @test=1;
    BEGIN
       WHILE(@test <= 3000000)
          BEGIN
             insert into testbigdata.testBig (id,phone, name
             )VALUES (@test,'18774888888','测试'+ cast(@test as varchar(50)));
             SET @test = @test+1;
          END;
    END;
GO