Mysql和Postgresql批量生成数据

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 ),'yc@qq.com',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 $$;