MySQL 存储过程插入大量测试数据

create database testdb6;
use testdb6;
/* 用户表 */
drop table if exists users;
create table users(
	id int primary key auto_increment,
	name varchar(20)
);
insert into users(name) values ('A');
insert into users(name) values ('B');
insert into users(name) values ('C');
insert into users(name) values ('D');
insert into users(name) values ('E');
insert into users(name) values ('F');
insert into users(name) values ('G');
insert into users(name) values ('H');
insert into users(name) values ('I');
insert into users(name) values ('J');

/* 订单表 */
drop table if exists orders;
create table orders(
	id int primary key auto_increment,/*订单id*/
	order_no varchar(20) not null,/*订单编号*/
	title varchar(20) not null,/*订单标题*/
	goods_num int not null,/*订单数量*/
	money decimal(7,4) not null,/*订单金额*/
	user_id int not null	/*订单所属用户id*/
)engine=myisam default charset=utf8 ;


delimiter $$
drop procedure batch_orders $$

create procedure batch_orders(in max int)
begin
declare start int default 0;
declare i int default 0;
set autocommit = 0;  
 while i < max do
	set i = i + 1;
	insert into orders(order_no,title,goods_num,money,user_id) 
	values (concat('NCS-',floor(1 + rand()*1000000000000 )),concat('订单title-',i),i%50,(100.0000+(i%50)),i%10);
 end while;
commit;
end $$
delimiter ;

call batch_orders(10000000);

in和exists

分析：

in执行顺序：先执行in中的子查询，作为我们最外层循环，主查询作为内层循环

exists: 主查询作为最外层循环，子查询作为最内层循环

而根据我们的时间复杂度，最外层循环小于内层循环的时候，使用的时间相对较少

结论：永远小表驱动大表是最优的选择方式