php大数组数据通常直接批量插入数据库可能导致的问题是:
情况1.php内存限制。常见的提示是这种
Fatal Error: Allowed memory size of xxxxxx bytes exhausted
通常设置 ini_set('memory_limit',-1);
即可解决这种问题。
情况2.一次性插入的sql语句过长,可能导致失败。
2.1 prepared statement contains too many placeholders,提示占位符过多。Mysql默认支持的占位符最多为65535(2^16-1)个,写入数据为m列,n行。m*n必须小于65535。
2.2 SQL语句长度默认设置是1M,也就是说mysql通讯的数据包大小设置是1M,这就容易造成sql语句执行失败。当然也可以更改mysql的配置文件(my.ini)中的max_allowed_packet = 6M变大,问题就解决了。除此之外,为解决这种问题,我们可以从程序层面进行解决这类问题:也就是拆分数组的方法。
class ArrayHelps
{
public static function chunk($ub, $default_num, callable $callback)
{
unset($insertdata);
$page = ceil(count($ub) / $default_num);
for ($ipage = 0; $ipage <= $page; $ipage++) {
$sbub = array_slice($ub, ($ipage - 1) * $default_num, $default_num, false);
$callback($sbub);
}
}
}
for ($i = 0; $i < 10000; $i++) {
$con[] = [
'num' => $i,
'name' => '',
];
}
ArrayHelps::chunk($con, 1000, function ($data) {
//插入数据库或者其他操作
});
ArrayHelps 也可以是Traits 不一定要用class ,视情况而定。
当然如果数据来源于数据库就没有这个必要了,很多php框架query自带chunk机制。比如laravel中的chunk就是这样实现的。
public function chunk($count, callable $callback)
{
$this->enforceOrderBy();
$page = 1;
do {
// We'll execute the query for the given page and get the results. If there are
// no results we can just break and return from here. When there are results
// we will call the callback with the current chunk of these results here.
$results = $this->forPage($page, $count)->get();
$countResults = $results->count();
if ($countResults == 0) {
break;
}
// On each chunk result set, we will pass them to the callback and then let the
// developer take care of everything within the callback, which allows us to
// keep the memory low for spinning through large result sets for working.
if ($callback($results, $page) === false) {
return false;
}
unset($results);
$page++;
} while ($countResults == $count);
return true;
}
我是老猫coder,边工作边总结,期待与你一起!加油。