day88 - 数据库函数设计-插入100w条数据

数据库插入100w条数据

 CREATE TABLE `app_user` (
 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
 `email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
 `phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
 `gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
 `password` VARCHAR(100) NOT NULL COMMENT '密码',
 `age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
 `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
 `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
 ​
 -- 插入100w条数据
 DELIMITER $$
 -- 写函数之前必须要写，标志
 CREATE FUNCTION mock_data2()
 RETURNS INT DETERMINISTIC
 BEGIN
     DECLARE num INT DEFAULT 1000000;
     DECLARE i INT DEFAULT 0;
     WHILE i<num DO
         INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
         VALUES(CONCAT('用户',i),'[email protected]',CONCAT('19',FLOOR((RAND()*(999999999-100000000))+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
         SET i=i+1;
     END WHILE;
     RETURN i;
 END;
 ​
 SELECT mock_data2() -- 执行此函数 生成一百万条数据
 ​
 INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
 VALUES(CONCAT('用户',1),'[email protected]',CONCAT('19',FLOOR((RAND()*(999999999-100000000))+100000000)),FLOOR(RAND()*2)
 ,UUID(),FLOOR(RAND()*100));
 ​
 ​
 ​
 ​
 ​
 SELECT * FROM `app_user` 
 WHERE `name` = '用户9999';
 ​
 -- id_表名_字段名
 CREATE INDEX id_app_user_name ON `app_user`(`name`);