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

百万数据插入
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();