使用Postgresql 实现快速插入测试数据

1.创建常规的企业信息表

create table t_cEnterprises( objectid Bigint not null, /*唯一编号(6位行政区号+6位sn)*/ divid uuid not null, /*行政区唯一代码*/ name text not null, /*企业名称*/ address text not null, /*企业地址*/ post text, /*企业邮编*/ contacts text, /*联系人*/ tel text, /*联系电话*/ fax text, /*传真*/ describe text, /*企业备注*/  date timestamp default now() not null, /*创建日期*/ constraint pk_centerPRisess_objectid Primary key (objectid), constraint fk_centerprises_divid foreign key(divid) references ts_divisions(objectid) on delete casCADe);
    create index idx_centerprises_divid on t_centerprises(divid);
    

2.需要使用的函数

/*转换16进制到字符*/drop function if exists hex_to_string(text);
    create or replace function hex_to_string( text)  returns text as $$ declare result text;
     begin execute 'select U&
    ''\' || $1 || '''' INTO result;
     return result;
     end;
    $$ language plpgSQL;
     /*随机生成汉字 汉字范围U+4E00..U+9FA5*/drop function if exists gen_random_zh(int,int);
    create or replace function gen_random_zh(imin int,imax int)  returns text as $$ declare vlen integer;
     result text;
     begin result := '';
     vlen = floor(random()*(imax-imin)+imin);
     for i in 1..vlen loop  result := result || hex_to_string(to_hex(floor(random()*(42191-19968)+19968)::integer));
     end loop;
     return result;
     end;
    $$ language plpgsql;
    

3.常规测试数据插入(5000000条)

insert into t_centerprises(objectid,divid,name,address,post,contacts,tel,fax,describe)  select (vdivid|| lpad(id::text,6,'0'))::bigint as objectid,'110101',  gen_random_zh(5,25) as name,gen_random_zh(10,50) as address,  floor(random()*(699999-600000)+600000) as post,gen_random_zh(2,8) as contacts,  floor(random()*(69999999-60000000)+60000000) as tel,floor(random()*(69999999-60000000)+60000000) as fax,  gen_random_zh(32,128) as describe From generate_series(1,5000000) as id;
    

在普通pc机上插入,大概完成时间约8小时,过程不可监控,并且cpu/内存占用率高,磁盘基本满负荷动作,读写率基本上都是100%.

4.改进后的方法, 插入(10000000条)

do $$ declare vStart bigint;
     declare vEnd bigint;
     declare MAXVALE bigint;
     declare INTERVAL bigint;
     declare vprovince integer;
     declare vprefecture integer;
     declare vcounty integer;
     declare vdivid text;
     declare vdividex uuid;
    begin vprovince := 10;
    vprefecture := 1;
    vcounty := 1;
      MAXVALE := 1000000;
     INTERVAL := 1000;
     vStart := 1 ;
    vEnd := INTERVAL;
     vdivid := (lpad(vprovince::text,2,'0') || lpad(vprefecture::text,2,'0') || lpad(vcounty::text,2,'0'))::text;
     vdividex := (select objectid from ts_divisions where province=vprovince and prefecture=vprefecture and county=vcounty);
     loop  insert into t_centerprises(objectid,divid,name,address,post,contacts,tel,fax,describe)  select (vdivid|| lpad(id::text,6,'0'))::bigint as objectid,vdividex as divid,  gen_random_zh(5,25) as name,gen_random_zh(10,50) as address,  floor(random()*(699999-600000)+600000) as post,gen_random_zh(2,8) as contacts,  floor(random()*(69999999-60000000)+60000000) as tel,floor(random()*(69999999-60000000)+60000000) as fax,  gen_random_zh(32,128) as describe from generate_series(vStart,vEnd) as id;
      raise notice '%', vEnd;
     vStart := vEnd + 1;
     vEnd := vEnd + INTERVAL;
     if( vEnd >
     MAXVALE ) then  return;
     elsif(vEnd = MAXVALE) then  vEnd := vEnd - 1;
     end if;
     end loop;
    end$$;
    

因为运算原因, cpu/内存占用率仍然很高, 硬盘负荷较小,读写率也比较低,大概完成时间约1.5小时.

补充：postgreSQL数据库 向表中快速插入1000000条数据

不用创建函数，直接向表中快速插入1000000条数据

create table tbl_test (id int, info text, c_time timestamp);
    insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp();
    select count(id) from tbl_test;
     --查看个数据条数

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• 使用postgresql 模拟批量数据插入的案例
• PostgreSQL upsert(插入更新)数据的操作详解
• postgreSQL数据库 实现向表中快速插入1000000条数据
• Python随机生成数据后插入到PostgreSQL
• postgres 使用存储过程批量插入数据的操作

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！