Mysql和Postgresql批量生成数据

岁月玲珑 2022-11-24 22:35:32 阅读数:359

MysqlPOSTGRESQL生成批量成数

Mysql

mysq不能独立运行sql片段, 所以必能通过运行函数或者存储过程方式来编程生成表数据

DROP PROCEDURE IF EXISTS generateUserInfo;
CREATE PROCEDURE generateUserInfo(in n int)
BEGIN
DECLARE k INT;
declare cityVar VARCHAR(100) DEFAULT '深圳';
declare genderVar char(1) default '女'; -- 变量声明必须要在最前面
SET k = 2;
WHILE k <= n DO
CASE k % 2
WHEN 0 THEN
SET genderVar = '女'; -- 必须有分号
ELSE
SET genderVar = '男';
END CASE;
CASE
k % 2
WHEN 0 THEN
SET cityVar = '深圳';
WHEN 1 THEN
SET cityVar = '广州';
WHEN 2 THEN
SET cityVar = '武汉';
WHEN 3 THEN
SET cityVar = '北京';
WHEN 4 THEN
SET cityVar = '上海';
WHEN 5 THEN
SET cityVar = '孝感';
ELSE
SET cityVar = '中山';
END case; -- 必须要有case
INSERT INTO user_info_t ( id, user_id, user_ext_id, nickname, email, gender, birthday, score, reg_time, last_login_time, vip, city )
VALUES
(k,k,k,concat( 'user', k ),'[email protected]',genderVar,
'2020-10-03 00:00:00',0,'2017-10-01 00:00:00','2017-10-24 21:30:36',1,cityVar);
SET k = k + 1;
END WHILE;
END;
DELETE FROM user_info_t WHERE id >= 2;
-- 函数使用select调用, 过程使用call调用
CALL generateUserInfo(100);

Postgresql

pg支持运行独立的sql判断, 不需要创建额外的函数或存储过程

DELETE FROM user_t WHERE id >= 2;
-- pg可以支持sql片段的运行
DO $$
DECLARE k integer := 2;
begin
WHILE k <= 100
loop
INSERT INTO user_t ( id, NAME, PASSWORD, create_time, update_tinme, create_by, update_by )
VALUES
( k, concat( 'user', k ), NULL, now(), NULL, 'System Admin', NULL );
k = k + 1
end loop
END $$;
版权声明:本文为[岁月玲珑]所创,转载请带上原文链接,感谢。 https://blog.csdn.net/yin18827152962/article/details/127955265