通过SQL编程实现插入大数据量的数据

骑猪看日落 2022-08-30 14:55 208阅读 0赞

百万数据插入
    DROP FUNCTION IF EXISTS mock_data;
    -- 写函数之前必须要写，标志:$$
    DELIMITER $$
    CREATE FUNCTION mock_data()
    RETURNS INT
    -- 注意returns，否则报错。
    BEGIN
    DECLARE num INT DEFAULT 1000000;
    -- num 作为截止数字，定义为百万，
    DECLARE i INT DEFAULT 0;
    WHILE i < num DO
    INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
    VALUES(CONCAT(用户, i), CONCAT(100,i,@qq.com), CONCAT(13, FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
    SET i = i + 1;
    END WHILE;
    RETURN i;
    END;

SELECT mock\_data() -- 执行此函数 生成一百万条数据

FLOOR(RAND()\*(999999999-100000000)+100000000))：向下取整取9位数  
FLOOR(RAND()\*2)：向下取整，取1或者2

## 新的方式 ##

-- 建表
    use family;
    drop table if exists app_user;
    create table app_user(
    	id int(11) primary key auto_increment,
        name varchar(100),
        email varchar(100),
        phone varchar(100),
        gender int(2),
        password varchar(100),
        age int(11)
    )engine = InnoDB char set utf8mb4;
    
    -- 编写存储过程
    DROP PROCEDURE IF EXISTS `app_user`;
    DELIMITER $$
    CREATE PROCEDURE `app_user`(IN n int)
    BEGIN
        DECLARE i int unsigned DEFAULT 41835;
        WHILE i < n DO
            INSERT INTO app_user(name, email, phone, gender, password, age)
    		VALUES(CONCAT('user', i), CONCAT('100',i,'@qq.com'), CONCAT(13, FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
            SET i = i+1;
        END WHILE;
    END $$
    DELIMITER ;
    
    -- 调用存储过程
    call app_user(10000);

## 对多个表进行插入不同数量的数据 ##

create table t1(id int primary key, a int, b int, index(a));
    create table t2 like t1;
    drop procedure idata;
    delimiter ;;
    create procedure idata()
    begin
      declare i int;
      set i=1;
      while(i<=1000)do
        insert into t1 values(i, 1001-i, i);
        set i=i+1;
      end while;
      
      set i=1;
      while(i<=1000000)do
        insert into t2 values(i, i, i);
        set i=i+1;
      end while;
    
    end;;
    delimiter ;
    call idata();