快速插入mysql数据库的方法

mysql版本为5.1

打开mysql的命令行工具，先 use 数据库

然后输入：

LOAD DATA local INFILE 'f:/insertcardtotalinfo2.sql' INTO TABLE  cardtotalinfo(code,companyid,faceprice,purchaseprice,purchasetime,networkid,sendtime,saletime,networkmoney,nwmoneytime,companymoney,cpmoneytime)  ;
不用添加“FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'” 在末尾，否则报错

注意sql文件需要用\t分割字段值，用\r分割每行

结果如下：

100万行数据，插入需要4.69秒，太bt了,再一次测试花了36秒。

可惜分区尚未实现。

生成文件的php代码：

<?php
set_time_limit(1000);
$myFile="f:/insertcardtotalinfo3.sql";
try{
$fh=fopen($myFile,'wb');//ab追加 wb重写
if($fh){$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<1000000)//1000000
{
$i++;
$code = "Abc".rand(5, 15);
$company = rand(1, 1000);
$network = rand(1, 10000);
fwrite($fh,$code."\t".$company.$sql.$network.$sql2."\r\n");
}
echo"写入成功";
}
}
catch(Exception$err){
echo"页面出错->".$err->getMessage();
}
?>

文本文件名称为：insertcardtotalinfo3.sql

内容为：

Abc12 573 10.5 6 2011-11-3012:43:03 10622011-11-3012:43:122011-11-3012:43:1972011-11-3012:43:2962011-11-3012:43:37
Abc13 583 10.5 6 2011-11-3012:43:03 36112011-11-3012:43:122011-11-3012:43:1972011-11-3012:43:2962011-11-3012:43:37
Abc14 465 10.5 6 2011-11-3012:43:03 91772011-11-3012:43:122011-11-3012:43:1972011-11-3012:43:2962011-11-3012:43:37
Abc15 706 10.5 6 2011-11-3012:43:03 19542011-11-3012:43:122011-11-3012:43:1972011-11-3012:43:2962011-11-3012:43:37
Abc14 97 10.5 6 2011-11-3012:43:03 61172011-11-3012:43:122011-11-3012:43:1972011-11-3012:43:2962011-11-3012:43:37
Abc10 223 10.5 6 2011-11-3012:43:03 23042011-11-3012:43:122011-11-3012:43:1972011-11-3012:43:2962011-11-3012:43:37
Abc5 550 10.5 6 2011-11-3012:43:03 12932011-11-3012:43:122011-11-3012:43:1972011-11-3012:43:2962011-11-3012:43:37

数据库字段有：

DROP TABLE IF EXISTS `cardtotalinfo`;
CREATE TABLE `cardtotalinfo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `companyid` int(11) NOT NULL,
  `faceprice` float NOT NULL,
  `purchaseprice` float NOT NULL,
  `purchasetime` datetime NOT NULL,
  `networkid` int(11) NOT NULL DEFAULT '0',
  `isfirst` bit(1) NOT NULL DEFAULT b'0',
  `sendtime` datetime DEFAULT NULL,
  `issaled` bit(1) NOT NULL DEFAULT b'0',
  `saletime` datetime DEFAULT NULL,
  `networkmoney` float NOT NULL DEFAULT '0',
  `nwmoneytime` datetime DEFAULT NULL,
  `isspended` bit(1) NOT NULL DEFAULT b'0',
  `companymoney` float NOT NULL DEFAULT '0',
  `cpmoneytime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`companyid`,`networkid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gb2312

分区语句如下：

/*!50100 PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (3000000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (4000000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (5000000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (6000000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (7000000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (8000000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (9000000) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (10000000) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;