mysql存储过程

CREATE TABLE `member` (
`lv_id` int(255) NOT NULL AUTO_INCREMENT,
`uname` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`passwd` varchar(255) DEFAULT NULL,
`sex` int(11) DEFAULT NULL,
PRIMARY KEY (`lv_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20002 DEFAULT CHARSET=utf8;

 

 

DELIMITER //

CREATE PROCEDURE create_time_table()

BEGIN

DECLARE i INT DEFAULT 1; -- 初始化计数器变量为1

 

DROP TABLE IF EXISTS time_data; -- 如果已经存在该表则删除

CREATE TABLE time_data (

id INT AUTO_INCREMENT PRIMARY KEY,

date DATE NOT NULL

);

WHILE i <= 365 DO -- 遍历从今天开始到明年结束的日期范围（共365天）

INSERT INTO time_data(date) VALUES (DATE_ADD('2022-01-01', INTERVAL i - 1 DAY)); -- 将每一天的日期插入表中

SET i = i + 1; -- 更新计数器变量

END WHILE;

END//

DELIMITER ;

BEGIN
DECLARE i INT DEFAULT 0;
DECLARE p_name VARCHAR (255);
DECLARE p_email VARCHAR (255);
DECLARE p_sex INT;
WHILE i < 10000 DO
SET p_name = CONCAT('test_', i);
SET p_email = CONCAT('test_', i, '@qq.com');
IF i % 2 = 0 THEN
SET p_sex = 0;
ELSE
SET p_sex = - 1;
END
IF;
INSERT INTO member (uname, email, `passwd`, sex)
VALUES
(
p_name,
p_email,
'e10adc3949ba59abbe56e057f20f883e',
p_sex
);
SET i = i + 1;
END
WHILE;
END