使⽤Postgresql实现快速插⼊测试数据
1.创建常规的企业信息表
create table t_centerpris(
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_centerpriss_objectid primary key (objectid),
constraint fk_centerpris_divid foreign key(divid) references ts_divisions(objectid) on delete cascade
);
create index idx_centerpris_divid on t_centerpris(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 'lect 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条)
inrt into t_centerpris(objectid,divid,name,address,post,contacts,tel,fax,describe)
lect (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_ries(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 := (lect objectid from ts_divisions where province=vprovince and prefecture=vprefecture and county=vcounty);
loop
inrt into t_centerpris(objectid,divid,name,address,post,contacts,tel,fax,describe)
开工证明
lect (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_ries(vStart,vEnd) as id;
系领带
rai 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);
inrt into tbl_test lect generate_ries(1,100000),md5(random()::text),clock_timestamp();
lect count(id) from tbl_test; --查看个数据条数
以上为个⼈经验，希望能给⼤家⼀个参考，也希望⼤家多多⽀持。如有错误或未考虑完全的地⽅，望不吝赐教。
