sqlite 高速插入文本文件

<?php
$t1 = microtime(true);
ini_set("memory_limit",'-1');
class sqllite{
    private $r_db;
    private $r_table='test';   
       
public function __construct() {
    try{
    $this->r_db=new PDO('sqlite:test.db3');
$sql =<<<EOF
PRAGMA temp_store = memory;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA synchronous = OFF;
PRAGMA cache_size = 400000;
PRAGMA page_size = 4096;
PRAGMA auto_vacuum=NONE;
PRAGMA count_changes = OFF;
PRAGMA journal_mode = OFF;

CREATE TABLE IF NOT EXISTS {$this->r_table} (
        'id'  INTEGER primary key AUTOINCREMENT NOT NULL,
        'test' char(15) NOT NULL
);
EOF;
//exit($sql.PHP_EOL);
$this->r_db->exec($sql);
    } catch (PDOException $ex){
        die ("[Error]  ".$ex->getMessage() .PHP_EOL.
        '         sudo apt-get install php5-sqlite php5-pdo'.PHP_EOL.
        '         add/edit in php.ini'.PHP_EOL.
        '         extension=pdo.so or pdo_sqlite.dll'.PHP_EOL
        );
    }
 }
 



public function show_count(){
$sql =<<<EOF
SELECT count(id) from {$this->r_table};
EOF;

$result=$this->r_db->prepare($sql);
$result->execute();
$result= $result->fetch();
return $result[0];
}

public function deleteTable(){
$db=$this->r_db;   
$sql =<<<EOF
drop table if exists {$this->r_table};
drop INDEX if exists qq_idx;
delete from sqlite_sequence;
vacuum;
EOF;
$db->exec($sql);
}


public function import(){
$db=$this->r_db;
$db->beginTransaction();
for ($i=1;$i<=10000000;$i++){
$sql ="insert into {$this->r_table}('test') VALUES ('{$i}');";
//echo $sql.PHP_EOL;
$db->exec($sql);
}
$db->commit();
}
public function __destruct() {
   $this->r_db=null;      
    }

}   


$sql=new sqllite();
$sql->import();
$t2 = microtime(true);
echo '插入 10000000 条数据'.PHP_EOL;
echo '耗时'.round($t2-$t1,3).'秒';
?>

<?php
ini_set("memory_limit",'-1');
$table='sgk';

$txt='moko.txt';  //要导入的文本
$from='moko';    //数据来源
//$db=$from.'.db3';  //测试临时库名

empty($db)?$db='sgk.db3':$db;

//$index="{$table}_idx";

$db = new SQLite3($db);
$s =<<<EOF
PRAGMA temp_store = memory;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA synchronous = OFF;
PRAGMA cache_size = 400000;
PRAGMA page_size = 4096;
PRAGMA auto_vacuum=NONE;
PRAGMA count_changes = OFF;
PRAGMA journal_mode = OFF;

CREATE TABLE IF NOT EXISTS {$table} (
        'id'  INTEGER primary key AUTOINCREMENT NOT NULL,
        'uname' char(15) NOT NULL ,
        'pass'  char(25)  NOT NULL,
'from'  char(15) NULL
);

EOF;

$db->exec($s);

$sql ="insert into {$table}('uname','pass','from') VALUES (?,?,'{$from}');";

$stmt = $db->prepare($sql);

$db->exec("BEGIN");
if(FALSE !==($fp=fopen($txt,'r'))){
while(!feof($fp)){
$buffer=rtrim(fgets($fp));

@list($email,$uname,$pass)=@explode("----",$buffer);
@$stmt->reset();
@$stmt->clear();
$stmt->bindValue(1, $email."t".$uname, SQLITE3_TEXT);        
$stmt->bindValue(2, $pass, SQLITE3_TEXT);
@$stmt->execute();        
}
$db->exec("COMMIT");
}

?>