Mysql大量测试数据快速生成

爱被打了一巴掌 2022-03-02 04:16 309阅读 0赞

1.建表

DROP TABLE IF EXISTS `sys_user`;
    CREATE TABLE `sys_user` (
      `user_id` bigint(100) NOT NULL AUTO_INCREMENT,
      `username` varchar(100) DEFAULT NULL COMMENT '用户名',
      `password` varchar(100) DEFAULT NULL COMMENT '密码',
      `salt` varchar(100) DEFAULT NULL COMMENT '盐',
      `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
      `mobile` varchar(100) DEFAULT NULL COMMENT '手机号',
      `status` varchar(100) DEFAULT NULL COMMENT '状态  0：禁用   1：正常',
      PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=112758 DEFAULT CHARSET=utf8 COMMENT='系统用户';
    SET FOREIGN_KEY_CHECKS=1;

2.执行show variables like ‘log\_bin\_trust\_function\_creators’;设置为ON。开启状态。  
set global log\_bin\_trust\_function\_creators=1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzQ3NTQx_size_16_color_FFFFFF_t_70]  
3.创建函数

# 随机字符串
    DELIMITER $$
    CREATE FUNCTION random_string(n INT) RETURNS VARCHAR(255)
    BEGIN
     DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
     DECLARE return_str VARCHAR(255) DEFAULT '';
     DECLARE i INT DEFAULT 0;
     WHILE i < n DO
     SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
     SET i = i + 1;
     END WHILE;
     RETURN return_str;
    END $$

# 随机数字
    DELIMITER $$
    CREATE FUNCTION random_num( ) 
    RETURNS INT(5)  
    BEGIN   
     DECLARE i INT DEFAULT 0;  
     SET i = FLOOR(100+RAND()*10);  
    RETURN i;  
     END $$

#执行存储过程，往sys_user表添加随机数据
    DELIMITER $$
    CREATE PROCEDURE insert_sys_user(IN START INT(10),IN max_num INT(10))  
    BEGIN  
    DECLARE i INT DEFAULT 0;   
     SET autocommit = 0;    
     REPEAT  
     SET i = i + 1;  
     INSERT INTO sys_user (user_id,username,password,salt,email,mobile,status) VALUES (START+i,random_string(10),random_string(6),random_string(10),random_string(20),random_string(16),random_string(12));  
     UNTIL i = max_num  
     END REPEAT;  
     COMMIT;  
     END $$

#执行存储过程，往sys_user表添加50万条数据
    DELIMITER ;
    CALL  insert_sys_user(100001,500000);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzQ3NTQx_size_16_color_FFFFFF_t_70]: /images/20220302/1537e664b267418da6f28ef63c899a16.png