PGSQL快速生成模拟数据

背景

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

生成函数

顺序生成

生成 SQL

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

结果

随机数

生成 SQL

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

结果

随机字符串

生成 SQL

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

随机中文

生成随机中文函数

-- 随机中文函数
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);

结果

身份证号

生成身份证号函数

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

结果

随机日期

生成随机日期函数

-- 生成随机日期
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);

结果

随机时间

生成随机时间函数

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

结果

枚举字符串

生成枚举字符串函数

-- 返回随机字符串函数
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);

结果

指定范围 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)
;

结果

指定范围 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)
;

结果

生成模拟数据

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