mysql中迅速插入百万条测试数据的方法

复制代码 代码如下:

mysql>delimiter $
mysql>SET AUTOCOMMIT = 0$$
mysql> create procedure test()
begin
declare i decimal (10) default 0 ;
dd:loop
INSERT INTO `million` (`categ_id`, `categ_fid`, `SortPath`, `address`, `p_identifier`, `pro_specification`, `name`, `add_date`, `picture_url`, `thumb_url`, `is_display_front`, `create_html_time`, `hit`, `buy_sum`, `athor`, `templete _style`, `is_hot`, `is_new`, `is_best`) VALUES
(268, 2, '0,262,268,', 0, '2342', '423423', '123123', '2012-01-09 09:55:43', 'upload/product/20111205153432_53211.jpg', 'upload/product/thumb_20111205153432_53211.jpg', 1, 0, 0, 0, 'admin', '0', 0, 0, 0);
commit;
set i = i+1;
if i= 1000000 then leave dd;
end if;
end loop dd ;
end;$
mysql>delimiter ;
mysql> call test;

复制代码 代码如下:

<?php
$t=mktime();
set_time_limit(1000);
$myFile="e:/insert.sql";
$fhandler=fopen($myFile,'wb');
if($fhandler){
$sql="268/t2/t'0,262,268,'/t0/t '2342'/t'423423'/t'123123'/t'23423423'/t'2012-01-09 09:55:43'/t'upload/product/20111205153432_53211.jpg'/t'upload/product/thumb_20111205153432_53211.jpg'/tNULL/tNULL/t38/t'件'/t''/t123/t123/t0";
$i=0;
while($i<1000000)//1,000,000
{
$i++;
fwrite($fhandler,$sql."/r/n");
}
echo"写入成功,耗时：",mktime()-$t;
}

复制代码 代码如下:

LOAD DATA local INFILE 'e:/insert.sql' INTO TABLE tenmillion(`categ_id`, `categ_fid`, `SortPath`, `address`, `p_identifier`, `pro_specification`, `name`, `description`, `add_date`, `picture_url`, `thumb_url`, `shop_url`, `shop_thumb_url`, `brand_id`, `unit`, `square_meters_unit`, `market_price`, `true_price`, `square_meters_price`);