Sqlite批量插入速度慢的解决方法【ThinkPHP】

使用ThinkPHP大批量转移数据一份,结果发现SQLite插入速度慢,几分钟才插入几千条,要了我的小命了。

后来在网上找到SQLite的插入机制,知道要在插入时使用事务,可以加速。

SQLite的事务请使用begin和commit,在TP中,可以直接使用startTrans和commit;

$db = M("userlist");
$db->startTrans();
foreach ($userList as $qq=>$userName)
{
	$data['id']	= NULL;
	$data['username']	= $userName;
	$data['psw']		= '';
	$data['email']		= '';
	$data['qq']			= $qq;
	$data['dateline']	= 0;
	$data['status']		= 0;
	
	$db	->data($data)
		->add();
}
$db->commit();

SQLite中Integer字段才支持自动增加,设置为主键可自增

CREATE TABLE [userList] ([id] Integer NOT NULL,
	 [username] varchar(200),
	 [psw] varchar(200),
	 [email] varchar(200),
	 [status] int DEFAULT 0,
	 [dateline] int,
	 [qq] int,
CONSTRAINT [PK_userList] PRIMARY KEY ([id]))

一开始设置为int+主键,莫名发现不支持自增,茫然中。后来查到id设置为integer primary key即可

PHP+PDO+SQLITE数据库开发日志

前言,一直在关注PHP6,也一直在努力按PHP6的标准进行开发。

目前,PDO使用比较熟练。

以下总结几点经验。

一,SQLite默认使用Utf-8编码,所以,在中文方面会遇到一些问题。

  1. 为了方便操作,我建议使用utf-8编码开发,即将PHP程序文件设置为utf-8编码。这样,读取存储数据不需要再处理即可输出。
  2. 但这里要注意,UTF-8文件中,有可能出现中文数据库文件路径出错的问题,其中就有file_exists,需要将编码转换成gb2312。
  3. 如果项目已经确定了其它编码,则需要在查询数据时转换一下编码,可能会有点复杂。

二,SQLite一些特殊的查询方法。

  1. 随机查询:mysql->order by rand();sqlite->order by random();

PHP不能连接Sqlite3的解决办法

火车采集器2008的商业版中,采用了Sqlite3做为默认数据库格式,以期待存放大型数据的需求.

目前,PHP5.3以前的版本还不能直接支持Sqlite3,即不支持DB3后缀的Sqlite数据库文件.

使用PHP连接Sqlite3时会报错,以下为一个解决办法:

If you get an error message:

Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database

then most probably your code is accessing a SQLite3 database.

sqlite_open() only supports SQLite2. Use PDO to access SQLite3.

目前找到的解决办法是使用PDO即可连接Sqlite3.

这里也回忆了一下PDO和ADOdb各是什么东东.
PDO和ADOdb均是数据库抽象层.总的功能是可以实现连接多个数据库,特别适合一个程序中实现多个数据库.
PDO支持数据库移植,如果你的部署将来有多种数据库,那就用它了.同时,PDO是C设计的,执行效率较高.他已经封装为PHP的扩展库组件了.运行快,效率高,我总结吧,还要安全一些,可以防SQL注入,初学,不知道有没有错.
ADOdb是PHP类,是一个PHP文件,相对PDO来讲,运行效率较低,但支持性也较广泛,使用起来也简单方便.

据说Adodb也支持Sqlite,不过,ADOdb也是基于PHP的组件,那么肯定也不支持SQLite3了,所以PDO是解决这个问题的唯一办法.
当然,使用php5.3或者更高版本可以直接解决问题。

这里,表示一下茫然,Sqlite3出了有四年了吧,PHP5干什么吃的去了?

然后记下PDO的下载地址,以及PDO与SQLite的连接方法:

PDO下载地址:http://pecl.php.net/package/pdo
PDO_Sqlite 函数及语法:http://cn.php.net/manual/en/ref.pdo-sqlite.php

下载以后并行安装就可以了,PHP5.1已经内置了PDO的相关组件,我们要做的就是打开PHP.ini把PDO和PDO_sqlite启用了

extension=php_pdo.dll
extension=php_pdo_sqlite.dll

启用PDO和PDO_dqlite以后,我就开始写一段测试代码,拷了一个任务数据库到程序目录中以备测试.

<?
$dbh = new PDO('sqlite:spiderresult.db');
if ($dbh){
echo 'OK';
}else{
echo 'Err';
}

foreach ($dbh->query('SELECT id FROM content limit 10;') as $row)
{
echo $row[0];
}
?>

Sqllite介绍,下载,进阶,教程

1、SQLite简介
SQLite第一个Alpha版本诞生于2000年5月. 至今已经有4个年头了. 而在今年的5月SQLite也迎来了一个新的里程: SQLite 3. SQLite官方网站: http://www.sqlite.org/
2、SQLite类型
SQLite的数据类型
首先你会接触到一个让你惊讶的名词: Typelessness(无类型). 对! SQLite是无类型的. 这意味着你可以保存任何类型的数据到你所想要保存的任何表的任何列中, 无论这列声明的数据类型是什么(只有在一种情况下不是, 稍后解释). 对于SQLite来说对字段不指定类型是完全有效的. 如:
Create Table ex1(a, b, c);
诚然SQLite允许忽略数据类型, 但是仍然建议在你的Create Table语句中指定数据类型. 因为数据类型对于你和其他的程序员交流, 或者你准备换掉你的数据库引擎. SQLite支持常见的数据类型, 如:
CREATE TABLE ex2(
a VARCHAR(10),
b NVARCHAR(15),
c TEXT,
d INTEGER,
e FLOAT,
f BOOLEAN,
g CLOB,
h BLOB,
i TIMESTAMP,
j NUMERIC(10,5)
k VARYING CHARACTER (24),
l NATIONAL VARYING CHARACTER(16)
);
前面提到在某种情况下, SQLite的字段并不是无类型的. 即在字段类型为”Integer Primary Key”时.

SQLite支持SQL92 标准。
3、用.NET操作sqlite数据库
System.Data.SQLite 
是一个原始SQLite的加强版.  它将是一个原版的sqlite3.dll完全替代品 (你甚至就可以把它重命名为sqlite3.dll).  它不需要链接.NET 运行时,所以可以脱离.NET独立发布, 然而它内嵌了一个完整的 ADO.NET 2.0 引擎,为开发提供了完整的支持.

以下是它的特性简介:

完整的 ADO.NET 2.0 实现
整个工程完全基于VS2005 和 ADO.NET 2.0全新构建, 使用了全部的ADO.NET framework新特性.  包括完整的 DbProviderFactory 支持, 自动的分布式事务调用, 广泛的模式支持, 此外所有的类都是从 ADO.NET 2.0 的基类继承下来的.

支持完整和精简的 .NET Framework 以及 C/C++
这个库不需要链接依赖.NET运行时,100%兼容原始的sqlite3.dll,可以使用非托管的C/C++ 进行开发.

可移植的数据库文件
未加密的 SQLite 数据库文件可以自由的跨平台和处理器使用,包括非Windows平台. 加密之后的数据库可以在全部Windows平台上使用.

可以信赖的速度,比包括Sql Server Mobile 在内的其它大多数嵌入式数据库都要快速
SQLite'安装所占用的空间相对于Sql Mobile可谓忽略不计了. 它在运行的时候占用更少的内存,同时生成的数据库也更小.

数据库加密
可以对整个数据库文件进行加密.  支持二进制和明文的密码.

支持使用Visual Studio 2005 设计
你可以向Server Explorer添加一个SQLite 连接, 使用查询设计器创建处查询语句, 向一个数据集中拖拽一个表格等等! SQLite的开发者可以在包括体验版在内的各种Visual Studio 2005下工作.

单文件再发布包容量在400kb以下
将SQLite本身和ADO.NET 封装捆绑编译在一起.  预编译的二进制文件提供了 x86, IA64, x64 和ARM 的版本.

广泛的SQL语言支持
SQLite 支持大部分的SQL92 标准(see below).  支持命名和未命名的参数以 UTF-8 和UTF-16 编码通过优化的管道传入SQLite 内核.

用户自定义的函数 和 排序
全面支持用户自定义函数和排序方式,意味着你可以用自己喜欢的.NET语言来实现SQLite没有提供的特性.  这一切将非常的简单.

提供了全部的源代码.  100% 免费.
全部的封装库源代码都是公有的.  无论是个人还是商业应用都没有任何的协议约束.
主页地址:http://sqlite.phxsoftware.com/

4、SQLite的管理
管理工具也有不少,建议您使用sqlitemanager,用法酷像phpmyadmin.
可以到 http://sqlitemanager.sourceforge.net/下载

SQLite Query Analyzer Documentation :http://www.monkeyshinez.net/SQLiteQA

可惜作者没有维护了,最新的可以参看这篇文章

SQLiteQA3: A SQLite Query Analyzer Clone for Visual Studio 2005 :http://www.eggheadcafe.com/articles/20060803.asp

管理SQLite数据库的工具SQLite Developer你可以从http://www.sharpplus.com/免费下载

5、SQLite 缺省情况下采用 Binary 方式比较字符串,因此我们使用
select * from [user] where [username] = 'Username1'
是找不出 [Username] = 'username1' 这样记录的。显然,这和我们平常使用的 SQL 检索有所区别。
解决方法:
创建数据库时,为目标字段添加 "COLLATE NOCASE" 即可。
如:

CREATE TABLE [User]
(
  [Username] TEXT NOT NULL COLLATE NOCASE
);

SQLite 3.0 Membership and Role Provider for ASP.NET 2.0

参考:http://www.cnblogs.com/redmoon/archive/2006/12/09/587617.html

sqlite,SQLite 中文:http://www.sqlite.com.cn/POPclass.asp?bigclassid=1