php快速插入大批量数据到Sqlite3

来源：互联网发布：淘宝卖家被冻结编辑：程序博客网时间：2024/05/16 11:33

因为需要为一批不大不小的文件插入到数据库分析，为操作方便所以选择了php和Sqlite3。刚开始直接使用的SQLite3类插入数据，我的个天呀慢的要死，30秒只插入了300多条数据最后还提示php-cgi.exe超时了。后来查资料得知用事务提交的方式会比较快，经测试成功后写了一个php类贴出来，用MySQL数据库也可用类似的方式。

class MyDB extends PDO{protected $transactionCounter = 0;protected $lasterror = "";function __construct($index = 0){try{if($index == 1){parent::__construct('sqlite:'.dirname(__FILE__)."/data.db");}else{parent::__construct('sqlite:'.dirname(__FILE__)."/tools.db");}$this->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);}catch(PDOException $e){$this->lasterror = $e->getMessage();}}function query($sql){$result = array();try{$sth = $this->prepare($sql);if(!$sth){$this->lasterror = "Incorrect statement";return array();}$sth->execute();$result = $sth->fetchAll();}catch(PDOException $e){$this->lasterror = $e->getMessage();}catch(Exception $e){$this->lasterror = $e->getMessage();}return $result;}function exec($sql){$count = 0;try{$count = parent::exec($sql);}catch(PDOException $e){$count = 0;$this->rollback();$this->lasterror = $e->getMessage();}return $count;}function beginTransaction(){if(!$this->transactionCounter++){return parent::beginTransaction();}return $this->transactionCounter >= 0;}function commit(){if(!--$this->transactionCounter){return parent::commit();}return $this->transactionCounter >= 0;}function close(){}function rollback(){if($this->transactionCounter >= 0){$this->transactionCounter = 0;return parent::rollBack();}$this->transactionCounter = 0;return false;}function getLasterror(){return $this->lasterror;}}

使用示例：

$db = new MyDB(1);$db->beginTransaction();$fs = fopen('c:\\xxx.txt','r');$change = 0;while(!feof($fs)){$tmp = fgets($fs,1024 * 10);//......$sql = "insert into $newtable values(NULL,'xxx','yyyy')";if(!$db->exec($sql))echo $db->getLasterror()."<br>";else$change += 1;if($change % 1000 >= 999){$db->commit();$db->beginTransaction();}}$db->commit();fclose($fs);echo "add $change items<br>";

0 0