--首先创建表
-- zhy.student definition
CREATE TABLE `student` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '用户名称',
`email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '电话',
`gender` tinyint unsigned NOT NULL COMMENT '1:男; 0:女',
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码',
`age` tinyint DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生表';
-- 1. 先执行 删除函数语句
DROP FUNCTION IF EXISTS mock_data;
-- 2. 然后执行 创建函数
-- 插入100万条数据
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num int default 1000000;
DECLARE i int default 0;
WHILE i<num DO
INSERT INTO `zhy`.`student`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'8563967666@qq.com',concat('18',floor(RAND()* 999999999)),floor(RAND()* 2),uuid(),floor(RAND()* 100));
SET i = i+1;
END WHILE;
RETURN i;
end;
// 3. 最后调用创建好的函数
select mock_data();