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

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);

/*转换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;

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;

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 $$;

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; --查看个数据条数