mysql随机生成百万条数据

建表
CREATE TABLE ts1 (a int, b int, index idx_a_b(a,b desc));



DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN     
    DECLARE i INT DEFAULT 1;
    WHILE i < 800
    DO
        insert into ts1 select RAND()*80000, RAND()*80000;
        SET i = i+1;
    END WHILE;
    commit;
END //
DELIMITER;

先执行上面的封装的函数

然后调用函数
CALL ts_insert();    

CREATE DATABASE atguigudb1;
USE atguigudb1;


#1.创建学生表和课程表 
CREATE TABLE `student_info` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
    `student_id` INT NOT NULL,
    `name` VARCHAR ( 20 ) DEFAULT NULL,
    `course_id` INT NOT NULL,
    `class_id` INT ( 11 ) DEFAULT NULL,
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;


CREATE TABLE `course` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
    `course_id` INT NOT NULL,
    `course_name` VARCHAR ( 40 ) DEFAULT NULL,
    PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

SELECT @@log_bin_trust_function_creators;
SET GLOBAL log_bin_trust_function_creators = 1;

#函数1：创建随机产生字符串函数 

DELIMITER //
CREATE FUNCTION rand_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;






#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num ( from_num INT, to_num INT ) RETURNS INT ( 11 ) BEGIN
    DECLARE
        i INT DEFAULT 0;
    
    SET i = FLOOR(
        from_num + RAND()*(
            to_num - from_num + 1 
        ));
    RETURN i;
    
END // 
DELIMITER;



# 存储过程1：创建插入课程表存储过程  
DELIMITER //
CREATE PROCEDURE insert_course (max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0; 
    SET autocommit = 0;#设置手动提交事务 
    REPEAT #循环 
    SET i = i + 1; #赋值 
    INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6)); 
    UNTIL i = max_num
END REPEAT;
COMMIT;#提交事务 
END // 
DELIMITER;



# 存储过程2：创建插入学生信息表存储过程
    
DELIMITER  //
    CREATE PROCEDURE insert_stu ( max_num INT ) 
    BEGIN
    DECLARE i INT DEFAULT 0; 
        SET autocommit = 0;#设置手动提交事务
        REPEAT#循环 
        SET i = i + 1; #赋值
        INSERT INTO student_info ( course_id, class_id, student_id, NAME ) VALUES ( rand_num ( 10000, 10100 ), rand_num ( 10000, 10200 ), rand_num ( 1, 200000 ), rand_string ( 6 ));
        UNTIL i = max_num 
END REPEAT;
        COMMIT;#提交事务 
END // 
DELIMITER;
        
        
        
CALL insert_course(100);
CALL insert_stu(1000000);
        
        
        
        
        
        
        
        
        
        
        
        
        

TRANSLATE with

Bing Webmaster Portal