mysql 插入大量数据千万级别

最新推荐文章于 2024-07-01 11:42:01 发布

「已注销」

最新推荐文章于 2024-07-01 11:42:01 发布

阅读量3.3k

点赞数

分类专栏： mysql 文章标签： mysql sql exception table network php

本文链接：https://blog.csdn.net/xuhaiyan8825/article/details/7070518

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

php：用于生成sql文件，其实就是字段值，用\t分割，每一行用\r\n分割

<?php
header ( "Content-Type: text/html; charset=utf-8" );
set_time_limit(1000);
$myFile="l:/insertcardtotalinfo3.sql";
try{
$fh=fopen($myFile,'wb');//ab追加 wb重写
if($fh){

/*
$sql='INSERTINTO`cardtotalinfo`(`code`,`companyid`,`faceprice`,`purchaseprice`,`purchasetime`,'
.'`networkid`,`isfirst`,`sendtime`,`issaled`,`saletime`,`networkmoney`,`nwmoneytime`,`isspended`,`companymoney`,`cpmoneytime`)'
.'VALUES(\'ADFE3993\',\'1\',\'10.5\',\'6\',\'2011-11-3012:43:03\',\'1\','
.'b\'0\',\'2011-11-3012:43:12\',b\'0\',\'2011-11-3012:43:19\',\'7\',\'2011-11-3012:43:29\',b\'0\',\'6\','
.'\'2011-11-3012:43:37\')';

fwrite($fh,$sql);
$sql=',(\'ADFE3993\',\'1\',\'10.5\',\'6\',\'2011-11-3012:43:03\',\'1\','
.'b\'0\',\'2011-11-3012:43:12\',b\'0\',\'2011-11-3012:43:19\',\'7\',\'2011-11-3012:43:29\',b\'0\',\'6\','
.'\'2011-11-3012:43:37\')';
$i=0;
while($i<1000000)//1000000
{
$i++;
fwrite($fh,$sql);
}
echo"写入成功";
*/
//以下用于LOADDATAINFILE
//LOAD DATA local INFILE 'l:/insertcardtotalinfo3.sql' INTO TABLE test2(code,companyid,faceprice,purchaseprice,purchasetime,networkid,sendtime,saletime,networkmoney,nwmoneytime,companymoney,cpmoneytime) ;

$sql="\t10.5\t6\t2011-11-3012:43:03\t";
$sql2="\t2011-11-3012:43:12\t2011-11-3012:43:19\t7\t2011-11-3012:43:29\t6\t2011-11-3012:43:37";

$i=0;
while($i<10000000)//1000000
{
$i++;
srand() ;
$code = "DEF".rand(10000000, 90000000);
$company = "Cp".rand(10000000, 90000000);
$network = "nw".rand(10000000, 90000000);
fwrite($fh,$code."\t".$company.$sql.$network.$sql2."\r\n");
}
echo"写入成功";

}
}
catch(Exception$err){
echo"页面出错->".$err->getMessage();
}
?>

sql语句：

LOAD DATA local INFILE 'l:/insertcardtotalinfo3.sql' INTO TABLE test2(code,companyid,faceprice,purchaseprice,purchasetime,networkid,sendtime,saletime,networkmoney,nwmoneytime,companymoney,cpmoneytime) ;

耗时：

时间: 298.962秒

如果code为索引，则

[SQL] LOAD DATA local INFILE 'f:/insertcardtotalinfo3.sql' INTO TABLE test(number,companyid,faceprice,purchaseprice,purchasetime,networkid,sendtime,saletime,networkmoney,nwmoneytime,companymoney,cpmoneytime) ;
受影响的行: 10000000
时间: 621.969s（笔记本性能较差的情况下）