[TOC] ### 循环插入数据 创建存储过程 `CREATE PROCEDURE batchInsert() ` ``` DROP PROCEDURE IF EXISTS batchInsert; CREATE PROCEDURE batchInsert() BEGIN DECLARE num int; DECLARE user_id VARCHAR(200); DECLARE user_login VARCHAR(200); DECLARE tnum VARCHAR(200); SET num = 105; WHILE num <= 1000 DO SET tnum = num; SET user_id = CONCAT('569505695',CAST(tnum AS CHAR(20))); SET user_login = CONCAT('user175',CAST(tnum AS CHAR(20))); insert into hs_user (`USER_ID`, `USER_LOGIN`, `USER_NAME`, `USER_STATUS`, `USER_PWD`, `USER_PWD_TYPE`) VALUES (user_id, user_login, user_login, 1, 'c4ca4238a0b923820dcc509a6f75849b', 1); insert into hs_user_tree (`TREE_ID`,`DEPT_ID`, `USER_ID`, `USER_NAME`, `USER_ORDER_ID`) VALUES (UUID(), '5695102', user_id, user_login, '1000'); SET num = num + 1; END WHILE; END; ``` 调用 存储过程 `CALL` ``` CALL batchInsert(); ``` ### 定义变量关键词`DECLARE`: ``` DECLARE num int; ``` ### 循环关键词`WHILE num <= 1000 DO` ``` WHILE num <= 1000 DO END WHILE; ``` ### 拼接字符串关键词`CONCAT ` ``` CONCAT('569505695',CAST(tnum AS CHAR(20))); ``` ### 转化整形为字符串关键词`CAST`： ``` CAST(tnum AS CHAR(20))); ```