【数据库】数据库测试，测试数据构造

---

前言

数据库功能及性能测试数据构造

---

一、generate_series函数

test=# \df generate_s*
                                                                          List of functions
   Schema   |             Name             |         Result data type          |                              Argument data types                              | Type
------------+------------------------------+-----------------------------------+-------------------------------------------------------------------------------+------
 pg_catalog | generate_series              | SETOF bigint                      | bigint, bigint                                                                | func
 pg_catalog | generate_series              | SETOF bigint                      | bigint, bigint, bigint                                                        | func
 pg_catalog | generate_series              | SETOF integer                     | integer, integer                                                              | func
 pg_catalog | generate_series              | SETOF integer                     | integer, integer, integer                                                     | func
 pg_catalog | generate_series              | SETOF numeric                     | numeric, numeric                                                              | func
 pg_catalog | generate_series              | SETOF numeric                     | numeric, numeric, numeric                                                     | func
 pg_catalog | generate_series              | SETOF timestamp without time zone | timestamp without time zone, timestamp without time zone, pg_catalog.interval | func
 pg_catalog | generate_series              | SETOF timestamp with time zone    | timestamp with time zone, timestamp with time zone, pg_catalog.interval       | func
 pg_catalog | generate_series_int4_support | internal                          | internal                                                                      | func
 pg_catalog | generate_series_int8_support | internal                          | internal                                                                      | func
 pg_catalog | generate_subscripts          | SETOF integer                     | anyarray, integer                                                             | func
 pg_catalog | generate_subscripts          | SETOF integer                     | anyarray, integer, boolean                                                    | func
(12 rows)

二、测试数据类型

2.1 顺序数

--插入1-10的顺序值
test=# create table tab1(a int);
CREATE TABLE
test=# insert into tab1 select a from generate_series(1,5) as a;
INSERT 0 5
test=# select * from tab1 ;
 a
---
 1
 2
 3
 4
 5
(5 rows)

2.2 随机数

--10内的随机整数
select (random()*10)::int from generate_series(1,10);
--100以内的随机整数
select (random()*100)::int from generate_series(1,10);
--10内的随机2位小数
test=# select (random()*100)::number(5,2) from generate_series(1,10);
 numeric
---------
   52.00
   19.99
   49.82
   94.88
   27.74
   75.63
   46.02
   27.61
   40.02
   82.94
(10 rows)

2.3 随机字符串

--随机生成32位md5字符串
test=# select md5(random()::text) from generate_series(1,5);
               md5
----------------------------------
 e58a720d19254b478db175f403348223
 737fd55601928259eb08580030b86b43
 2bdb63d0a5e6155ab9f16658628bd510
 16b0eceeabf936708a6cbd04fa0f9eaa
 8e1d71d6ced032a3df0115ed957eb1cf
(5 rows)

--随机生成2个32位md5字符串连接值
test=# select repeat(md5(random()::text),2) from generate_series(1,5);
                              repeat
------------------------------------------------------------------
 ce3c2456e442df85159f87555bed7a2ece3c2456e442df85159f87555bed7a2e
 9be019e0c94c3386452a6132736a675d9be019e0c94c3386452a6132736a675d
 4f9a31965fb72168abcd5a25385c4e724f9a31965fb72168abcd5a25385c4e72
 856dba661e4d5179470eb1e5cc9a1582856dba661e4d5179470eb1e5cc9a1582
 a06722b1748e34581eac30f6b7ac4357a06722b1748e34581eac30f6b7ac4357
(5 rows)

--指定字符串10次以内随机重复

test=# select repeat('abcc',(random()*10)::integer);
        repeat
----------------------
 abccabccabccabccabcc
(1 row)

2.4 拼接字符串

test=# select concat('ab','cd');
 concat
--------
 abcd
(1 row)
test=# select concat('ab',' ','cb');
 concat
--------
 ab cb
(1 row)
test=# select concat('ab',' ','cb',' ','ef');
  concat
----------
 ab cb ef
(1 row)

2.5 生成时间

--间隔5秒
test=# select generate_series(to_date('20230505','yyyymmdd'),to_date('20230506','yyyymmdd'),'5s');
   generate_series
---------------------
 2023-05-05 00:00:00
 2023-05-05 00:00:05
 2023-05-05 00:00:10
 2023-05-05 00:00:15
 2023-05-05 00:00:20
 2023-05-05 00:00:25
 .
 .
--间隔5分
test=# select generate_series(to_date('20230505','yyyymmdd'),to_date('20230506','yyyymmdd'),'5m');
--间隔5小时
test=# select generate_series(to_date('20230505','yyyymmdd'),to_date('20230506','yyyymmdd'),'5h');
   generate_series
---------------------
 2023-05-05 00:00:00
 2023-05-05 05:00:00
 2023-05-05 10:00:00
 2023-05-05 15:00:00
 2023-05-05 20:00:00
(5 rows)
--间隔5天
test=# select generate_series(to_date('20230505','yyyymmdd'),to_date('20230530','yyyymmdd'),'5d');
   generate_series
---------------------
 2023-05-05 00:00:00
 2023-05-10 00:00:00
 2023-05-15 00:00:00
 2023-05-20 00:00:00
 2023-05-25 00:00:00
 2023-05-30 00:00:00
(6 rows)

2.6 自定义函数

2.6.1 随机整数

create or replace function get_rand_int (m int ,n int) returns int as $body$
begin
    return cast((random() *(n - m) + m) as int);
end ;
$body$
language plpgsql;
--随机5次生成100以内整数
test=# select get_rand_int (1,100) from generate_series(1,5);
 get_rand_int
--------------
           25
           26
           57
           76
           47
(5 rows)

2.6.2 随机中文

create or replace function get_ch(int) returns text as $$    
declare    
  res text;    
begin    
  if $1 >=1 then    
    select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);    
    return res;    
  end if;    
  return null;    
end;    
$$ language plpgsql strict;
--随机5次生成10个中文字符
test=# select get_ch(10) from generate_series(1,5);
        get_ch
----------------------
 慲厐槄惓酻蕫抢噦骽汽
 謶躼圔攲嗝奪輻恝藓鉝
 筂秶鵪纍羷鵵莊荎鷫縺
 瑴鍏曣嬞珑苌痓萝揍袃
 欙噎漧僃睰刜擂鴑眿兓
(5 rows)

2.6.3 随机身份证号

create or replace function gen_id(    
  a date,    
  b date    
)     
returns text as $$    
select lpad((random()*99)::int::text, 2, '0') ||     
       lpad((random()*99)::int::text, 2, '0') ||     
       lpad((random()*99)::int::text, 2, '0') ||     
       to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||     
       lpad((random()*99)::int::text, 2, '0') ||     
       random()::int ||     
       (case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ;    
$$ language sql strict; 
--随机生成10个身份证号码
test=# select gen_id('2000-01-01', '2023-05-05') from generate_series(1,5);
       gen_id
--------------------
 400930200611276501
 733628200909156504
 080385201805181310
 387506201707241505
 823795200910097502
(5 rows)

2.6.4 随机电话号

create or replace function get_tel() returns varchar(300) as $body$
declare
        startlength int  default 11 ;
        endlength int  default 11  ;
        first_no varchar(100) default '1';
        chars_str varchar(100) default '0123456789';
        return_str varchar(300) default substring('3578' , cast((1 + random()*3 ) as int),1);
        i int ;
        end1 int;
    begin
        end1 :=cast((random()*(endlength - startlength)) as int)+startlength;
        for i in 1 .. end1-2 loop
        return_str = concat(return_str,substring(chars_str , cast((1 + random()*9 ) as int),1));
        end loop;
        return concat(first_no,return_str);
    end;
$body$
language 'plpgsql' ;
--随机生成5个电话号码
test=# select get_tel() from  generate_series(1,5);
   get_tel
-------------
 17309758692
 17339839623
 18641043151
 17410527953
 13228545447
(5 rows)

2.6.5 随机日期

create or replace function get_rand_date(start_date date,end_date date) returns date as $body$   
declare
        interval_days integer ;
        random_days integer ;
        random_date date ;
    begin
        interval_days := end_date - start_date ;
        random_days := get_rand_int (0, interval_days) ;
        random_date := start_date + random_days ;
        return random_date;
 end ;
$body$
language plpgsql;
--指定时间生产随机日期
test=# select get_rand_date('2023-01-01', '2023-05-05') from  generate_series(1,5);
    get_rand_date
---------------------
 2023-03-12 00:00:00
 2023-03-07 00:00:00
 2023-03-20 00:00:00
 2023-02-27 00:00:00
 2023-04-18 00:00:00
(5 rows)

2.6.6 随机时间

create or replace function get_rand_datetime(start_date date, end_date date) returns timestamp as $body$  
declare   
        interval_days integer;
        random_seconds integer; 
        random_dates integer;
        random_date date; 
        random_time time;
    begin
        interval_days := end_date - start_date;
        random_dates:= trunc(random()*interval_days);
        random_date := start_date + random_dates;
        random_seconds:= trunc(random()*3600*24);
        random_time:=' 00:00:00'::time+(random_seconds || ' second')::interval;
        return random_date +random_time;
    end; 
$body$ 
language plpgsql;
--指定时间生产随机时间
test=# select get_rand_datetime('2023/01/01', '2023/05/05') from  generate_series(1,5);
  get_rand_datetime
---------------------
 2023-02-12 12:06:32
 2023-03-27 23:18:39
 2023-03-19 11:26:05
 2023-01-28 07:10:31
 2023-01-19 04:03:08
(5 rows)

三、千万、亿级测试数据

test=# create unlogged table test(id int primary key,info text,crt_time timestamp);
CREATE TABLE
test=# create unlogged table test1(id int primary key,info text,crt_time timestamp);
CREATE TABLE
test=# create unlogged table test2(id int primary key,info text,crt_time timestamp);
CREATE TABLE
--千万
test=# insert into test select generate_series(1,10000000),md5(random()::text),now();
--十亿
test=# insert into test select generate_series(1,10000000),md5(random()::text),now();
--百亿
for ((i=1;i<=100;i++));do ksql -c "insert into test2 select generate_series(($i-1)10000000+1,$i10000000),'test', now();" & done