PostgreSql 数据库

PGSQL快速生成模拟数据

#PgSql

背景

有时候，我们为了测试数据库的性能，通常需要快速构建测试数据，PgSql 提供了快速构建数据的工具，方便我们能够快捷的构建模拟数据。

生成函数

顺序生成

生成 SQL

-- 生成一批顺序值  
SELECT  
    id  
FROM  
    GENERATE_SERIES(1, 10) t(id);

结果

id
1
2
3
4
5
6
7
8
9
10

随机数

生成 SQL

-- 生成一批随机整型
SELECT
    (RANDOM() * 100)::INT
FROM
    GENERATE_SERIES(1, 10)
;

结果

int4
66
8
11
36
53
43
22
94
99
36

随机字符串

生成 SQL

-- 生成一批随机字符串
SELECT 
    MD5(RANDOM()::TEXT)
FROM
    GENERATE_SERIES(1, 10);

md5
717e8603559b452af4bfd6e9631096d0
07320af223c367eb02984eb7e85d74a9
d99e7ccb95873e7bf69a64c04ba5e7d3
2a6bac203480291cd1bdbaa5e8eb856f
320e52646959fdc8d3f17d10e50be6e4
d4e35b9bcbe466e2b5516602f7cf76cd
8695b0d27430bc9a2635954bb2246b51
448e4d4985c0dac75536045abcfc21e9
32d9de8bda7c16d6d4ee4225fe9e62fa
70e0092ed83668552b5304d052fcdcf0

随机中文

生成随机中文函数

-- 随机中文函数
CREATE OR REPLACE FUNCTION gen_hanzi(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;

生成汉字 SQL

-- 生成随机汉字  
SELECT  
    gen_hanzi(10)  
FROM  
    GENERATE_SERIES(1, 10);

结果

gen_hanzi
鬁絹囬橩微鯤馴潳嫕甡
擭嫩馣倂差榦逝薍艾荰
猧炒畴稛瑵操綂愘冯粌
款应闦岷衕後槀鷰鞃絑
卺翦骚騕裰简鑝櫒窂産
魴旻閖烀呾岨垯勣拏澥
鵅伩睰蹵臱嘦刃鍑璓冠
睛锱賃轋賁件蝔婘鳗椩
瞉斑酃惪荦桔灪濭脦娰
豟噹癀僨緯翁豃揵嗪訅

身份证号

生成身份证号函数

-- 随机身份证号函数
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;

生成随机身份证号 SQL

-- 随机身份证号
SELECT 
    gen_id('1900-01-01', '2017-10-16')
FROM
    GENERATE_SERIES(1, 10);

结果

gen_id
409560192602091816
163490201705024702
797974192007296218
429540201212090405
846256194810318314
244942195609190207
729623200508134305
589417196110115508
882567199310083306
67153519821129281X

随机日期

生成随机日期函数

-- 生成随机日期
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 := TRUNC(RANDOM() * (interval_days - 1) + 1);
    random_date := start_date + random_days;
    RETURN random_date;
END ;
$BODY$
    LANGUAGE plpgsql;

生成随机日期 SQL

SELECT
    get_rand_date('2018-01-01', '2023-12-01')
FROM
    GENERATE_SERIES(1, 10);

结果

get_rand_date
2020-06-23
2021-04-16
2019-01-18
2022-09-18
2018-06-08
2023-11-24
2022-01-25
2019-08-21
2023-06-08
2018-09-02

随机时间

生成随机时间函数

-- 生成随机时间
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;

生成随机时间 SQL

SELECT
    get_rand_datetime('2018-01-01', '2023-12-01')
FROM
    GENERATE_SERIES(1, 10);

结果

get_rand_datetime
2021-06-20 20:33:32.000000
2021-10-01 18:47:55.000000
2021-07-12 00:10:05.000000
2019-03-23 18:32:06.000000
2023-04-03 20:23:25.000000
2022-07-09 10:12:32.000000
2019-12-02 20:08:22.000000
2021-11-14 06:51:23.000000
2023-05-10 07:42:13.000000
2021-09-07 23:11:36.000000

枚举字符串

生成枚举字符串函数

-- 返回随机字符串函数
CREATE OR REPLACE FUNCTION get_split_str(str VARCHAR) RETURNS VARCHAR AS
$BODY$
DECLARE
    array_str  VARCHAR[];
    i          INT;
    rand_index INT;
BEGIN
    str := TRIM(str);
    array_str := REGEXP_SPLIT_TO_ARRAY(str, '[，,]');
    --正在表达式匹配
    -- array_length(array_str,1)1代表维度，一维、二维
    rand_index := CAST((RANDOM() * (ARRAY_LENGTH(array_str, 1) - 1) + 1) AS INT);
    RETURN array_str[rand_index];
END ;
$BODY$
    LANGUAGE plpgsql;

生成枚举字符串 SQL

-- 返回指定的字符串
SELECT
    get_split_str('hello ,zdata ,asher ,enmo,mogdb,zcloud')
FROM
    GENERATE_SERIES(1, 10);

结果

get_split_str
asher
mogdb
asher
zcloud
asher
asher
mogdb
enmo
asher
enmo

指定范围 INT

生成指定范围的 INT 函数

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;

SQL

SELECT
    get_rand_int(100, 1000)
FROM
    GENERATE_SERIES(1, 10)
;

结果

get_split_str
asher
mogdb
asher
zcloud
asher
asher
mogdb
enmo
asher
enmo

指定范围 BIGINT

生成指定范围 BIGINT 函数

CREATE OR REPLACE FUNCTION get_rand_bigint(m BIGINT, n BIGINT) RETURNS BIGINT AS
$BODY$
BEGIN
    RETURN CAST((RANDOM() * (n - m) + m) AS BIGINT);
END ;
$BODY$
    LANGUAGE plpgsql;

SQL

SELECT
    get_rand_bigint(100, 1000)
FROM
    GENERATE_SERIES(1, 10)
;

结果

get_rand_bigint
703
958
213
835
699
152
561
832
834
478

生成模拟数据

-- 创建 user 数据表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    id_card VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20),
    address VARCHAR(255),
    job_title VARCHAR(255),
    education_level VARCHAR(255),
    salary DECIMAL(10, 2),
    hire_date DATE,
    leave_date DATE,
    remarks TEXT,
    status VARCHAR(255),
    created_at TIMESTAMP,
    created_by VARCHAR(255) DEFAULT 'system'
);

-- 批量生成 1W 条模拟数据

INSERT INTO
    users (username, password, id_card, email, phone, address, job_title, education_level, salary, hire_date, leave_date, remarks, status, created_at)
SELECT
    SUBSTR(MD5(RANDOM()::TEXT), 10),
    MD5(RANDOM()::TEXT),
    gen_id('1970-01-01', '2018-10-16'),
    SUBSTR(MD5(RANDOM()::TEXT), 10) || '@qq.com',
    get_rand_bigint(13812341234, 19912341234),
    gen_hanzi(10),
    get_split_str('JAVA,C++,SQL,VUE,WEB,C#,C,GO'),
    get_split_str('本科,硕士,博士,专科'),
    get_rand_int(6000, 30000),
    get_rand_date('2018-01-01', '2023-12-01'),
    get_rand_date('2018-01-01', '2023-12-01'),
    SUBSTR(MD5(RANDOM()::TEXT), 10),
    get_split_str('在职,离职'),
    get_rand_datetime('2018-01-01', '2023-12-01')
FROM
    GENERATE_SERIES(1, 10000);

如果觉得文章对你有用，请随意赞赏

PgSql 数据库

PGSQL快速生成模拟数据

https://blog.booleadev.xyz/archives/1715159734910

作者

Jantor

发布于

2024-05-08

更新于

2024-05-08

许可协议

CC BY 4.0

PGSQL快速生成模拟数据

背景

生成函数

顺序生成

随机数

随机字符串

随机中文

身份证号

随机日期

随机时间

枚举字符串

指定范围 INT

指定范围 BIGINT

生成模拟数据

作者

发布于

更新于

许可协议

评论