current position:Home>Mysql and Postgresql generate data in batches

Mysql and Postgresql generate data in batches

2022-11-24 22:38:17Delicate years

Mysql

mysq cannot run sql fragments independently, so it must be able to programmatically generate table data by running functions or stored procedures

DROP PROCEDURE IF EXISTS generateUserInfo;CREATE PROCEDURE generateUserInfo(in n int)BEGINDECLARE k INT;declare cityVar VARCHAR(100) DEFAULT 'Shenzhen';declare genderVar char(1) default 'female'; -- variable declaration must be at the topSET k = 2;WHILE k <= n DOCASE k % 2WHEN 0 THENSET genderVar = 'female'; -- must have a semicolonELSESET genderVar = 'male';END CASE;CASEk % 2WHEN 0 THENSET cityVar = 'Shenzhen';WHEN 1 THENSET cityVar = 'Guangzhou';WHEN 2 THENSET cityVar = 'Wuhan';WHEN 3 THENSET cityVar = 'Beijing';WHEN 4 THENSET cityVar = 'Shanghai';WHEN 5 THENSET cityVar = 'Xiaogan';ELSESET cityVar = 'Zhongshan';END case; -- must have a caseINSERT 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;-- The function is called by select, and the procedure is called by callCALL generateUserInfo(100);

Postgresql

pg supports running independent sql judgment, no need to create additional functions or stored procedures

DELETE FROM user_t WHERE id >= 2;-- pg can support the operation of sql fragmentsDO $$DECLARE k integer := 2;beginWHILE k <= 100loopINSERT 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 + 1end loopEND $$;

copyright notice
author[Delicate years],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/328/202211242235067125.html

Random recommended