php - 用巨大的数组填充表

Question

我正在尝试用从页面上抓取的内容填充表格。问题是，同时插入了将近二十万个项目，破坏了系统。我收到错误:

Warning: mysql_query() [function.mysql-query]: MySQL server has gone away in C:\xampp\htdocs\testing\scrabble2.php on line 15

Warning: mysql_query() [function.mysql-query]: Error reading result set's header in C:\xampp\htdocs\testing\scrabble2.php on line 15

Could not add item: MySQL server has gone away

<?
include('simple_html_dom.php');
$html = new simple_html_dom();
$html->load_file('http://kisa.ca/scrabble/');
$collection = $html->find('.words');
$con = mysql_connect("localhost","root","root") or die('Could not connect: ' .    mysql_error());
mysql_select_db( 'test' ) or die('Could not select database: ' . mysql_error());
foreach ($collection as $word)
{
    $add_item = "INSERT INTO ScrabbleWords (ID, Words) VALUES ('', '" . $word . "')";
    mysql_query($add_item) or die('Could not add item: ' . mysql_error());
    echo $word;
    echo ", ";
}
echo "Done";
mysql_close($con);
?>

那么我怎样才能用这么大的数组填充我的表呢？

Answer 1

使用MySQL扩展插入，会大大减少查询次数:

INSERT INTO ScrabbleWords (ID, Words) VALUES ('', 'Word1'), ('', 'Word2'), ('', 'Word3'), ...;

您可以循环遍历并将 1000 个单词组合到一个插页中。

您还可以增加 MySQL 连接超时:

ini_set('mysql.connect_timeout', 300); // seconds
ini_set('default_socket_timeout', 300);