从PHP脚本创建一个非常大的MySQL数据库

INSERT INTO `test` VALUES
    (1,2,3,4,5,6,7,8,9,0),(1,.....
    (1,0)

我创建了一个PHP脚本来执行此操作.首先,我尝试构建一个可容纳100万次插入但仍失败的查询.然后我尝试了100 thousend,它又失败了. 50 thousends也不这样做.我的巢尝试是10?? 000,它工作正常.我想我正在达到从PHP到MySQL的传输限制.这是代码：

<?php
set_time_limit(0);
ini_set('memory_limit',-1);
define('NUM_INSERTS_IN_QUERY',10000);
define('NUM_QUERIES',100);

// build query
$time = microtime(true);
$queries =  array();
for($i = 0; $i < NUM_QUERIES; $i++){
    $queries[$i] = 'INSERT INTO `test` VALUES ';
    for($j = 0; $j < NUM_INSERTS_IN_QUERY; $j++){
            $queries[$i] .= '(1,';
    }
    $queries[$i] = rtrim($queries[$i],',');
}

echo "Building query took " . (microtime(true) - $time) . " secondsn";

mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('store') or die(mysql_error());
mysql_query('DELETE FROM `test`') or die(mysql_error());

// execute the query
$time = microtime(true);
for($i = 0; $i < NUM_QUERIES; $i++){
    mysql_query($queries[$i]) or die(mysql_error());
    // verify all rows inserted
    if(mysql_affected_rows() != NUM_INSERTS_IN_QUERY){
            echo "ERROR: on run $i not all rows inserted (" . mysql_affected_rows() . ")n";
            exit;
    }
}

echo "Executing query took " . (microtime(true) - $time) . " secondsn";
$result = mysql_query('SELECT count(*) FROM `test`') or die(mysql_error());
$row = mysql_fetch_row($result);
echo "Total number of rows in table: {$row[0]}n";
echo "Total memory used in bytes: " . memory_get_usage() . "n";
?>

我的Win 7开发机器上的结果是：

Building query took 0.30241012573242 seconds
Executing query took 5.6592788696289 seconds
Total number of rows in table: 1000000
Total memory used in bytes: 22396560

因此,对于1密耳插件,需要5秒半.然后我用这个设置运行它：

define('NUM_INSERTS_IN_QUERY',1);
define('NUM_QUERIES',1000000);

这基本上是每个查询执行一次插入.结果是：

Building query took 1.6551470756531 seconds
Executing query took 77.895285844803 seconds
Total number of rows in table: 1000000
Total memory used in bytes: 140579784

然后我尝试创建一个文件,其中每个查询都有一个插入,如@jancha所示.我的代码略有修改：

$fid = fopen("query.sql","w");
fputs($fid,"use store;");
for($i = 0; $i < 1000000; $i++){
    fputs($fid,"insert into `test` values (1,0);n");
}
fclose($fid);
$time = microtime(true);
exec("mysql -uroot < query.sql");
echo "Executing query took " . (microtime(true) - $time) . " secondsn";

结果是：

Executing query took 79.207592964172 seconds

与通过PHP执行查询相同.因此,最快的方法可能是在一个查询中执行多个插入,使用PHP完成工作不应该是一个问题.