MySQL和SQLite数据访问的初步比较

我们首先通过EMS MySQL Manager来建立名为dbtest的数据库和测试表格.表格如下(数据和表结构均摘自Oracle SQL基础培训教程)

一、测试数据表结构:

表格:Department

DEPARTMENT_ID	DEAPRTMENT_NAME	MANAGER_ID	LACATION_ID
90	Executive	100	1700
60	IT	103	1400

表格:Employees

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY
100	Steven	King	SKING@mail.com	515.123.4567	2003-08-19	AD_PRES	24000
101	Neena	Kochhar	NKochhar@mail.com	515.123.4568	2002-08-21	AD_VP	17000
103	Lex	Dehaan	LDehaan@mail.com	515.123.4569	2003-08-28	AD_VP	17000

二、初步比较
用PHPMyAdmin导出数据和表结构,在d:sqlite目录下建立名为dbtest.sqlite的数据库.并用SQLiteManager直接将从phpMyAdmin导出来的SQL语句来生成数据表和数据.这样两个测试数据库的表结构和数据都是完全一样的.
先做一个简单的测试。那就是在PHP中对不同的数据库实现几乎同样的功能。从中，我们可以基本看出两种数据库API用法的相同点和不同点，也可以看出结果的相同点和不同点。首先访问MySQL数据库
//建立连接
$link = mysql_connect('localhost', 'root', '')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
//选择测试数据库
mysql_select_db('dbtest') or die('Could not select database');
// 执行SQL语句
$query = 'SELECT * FROM department';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
//以数组一次访问一行的方式来取得数据，并打印出查询结果
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
print_r($line);
}
//清除资源
mysql_free_result($result);
mysql_close($link);
?>
得到的结果如下:
Array
(
[DEPARTMENT_ID] => 90
[DEAPRTMENT_NAME] => Executive
[MANAGER_ID] => 100
[LACATION_ID] => 1700
)
Array
(
[DEPARTMENT_ID] => 60
[DEAPRTMENT_NAME] => IT
[MANAGER_ID] => 103
[LACATION_ID] => 1400
)
//打开SQLite数据库
$db = sqlite_open("d:sqlitedbtest.sqlite");
$rows =array();
$rs = sqlite_query($db,"select * from department");
//以数组一次访问一行的方式来取得数据，并打印出查询结果
while ($r = sqlite_fetch_array($rs)) {
$rows[] = $r;
print_r($r);
sqlite_close($db);
}
?>
得到的数据如下：
Array
(
[0] => 90
[DEPARTMENT_ID] => 90
[1] => Executive
[DEAPRTMENT_NAME] => Executive
[2] => 100
[MANAGER_ID] => 100
[3] => 1700
[LACATION_ID] => 1700
)
Array
(
[0] => 60
[DEPARTMENT_ID] => 60
[1] => IT
[DEAPRTMENT_NAME] => IT
[2] => 103
[MANAGER_ID] => 103
[3] => 1400
[LACATION_ID] => 1400
)
从以上可以看出,SQLite和MySQL的连接上有点不同,这是由于MySQL是基于客户端、服务器结构的，而SQLite是文件型数据，当然是不一样的，其它好多地方都可以只将mysql替换成sqlite就可以，但是细心一点就会发现，两个fetch_array方法获得的数组也有很大的不同，sqlite_fetch_array()获得的数组是字段数的两倍，既可以以字段名$line[DEPARTMENT_ID]或者$line[‘DEPARTMENT_ID’]来访问，也可以索引$line[0]来访问，但是MySQL只能以$line[DEPARTMENT_ID]或$line[‘DEPARTMENT_ID’]来访问。注意，字段名访问的大小写是严格区分的。
三、重大差距
以上这种差别不会引起程序迁移方面太大的问题，因为一般以字段名访问，这点是一致的。更重要的差别在于执行多表关联查询时得到的结果的问题。
我们将上面的查询语句改成：
SELECT a.*,b.* FROM department a,employees b where b.employee_id=a.manager_id
在访问mysql数据库时，得到的结果如下（代码见附件）：
Array
(
[DEPARTMENT_ID] => 90
[DEAPRTMENT_NAME] => Executive
[MANAGER_ID] => 100
[LACATION_ID] => 1700
[EMPLOYEE_ID] => 100
[FIRST_NAME] => Steven
[LAST_NAME] => King
[EMAIL] => SKING@mail.com
[PHONE_NUMBER] => 515.123.4567
[HIRE_DATE] => 2003-08-19
[JOB_ID] => AD_PRES
[SALARY] => 24000
)
Array
(
[DEPARTMENT_ID] => 60
[DEAPRTMENT_NAME] => IT
[MANAGER_ID] => 103
[LACATION_ID] => 1400
[EMPLOYEE_ID] => 103
[FIRST_NAME] => Lex
[LAST_NAME] => Dehaan
[EMAIL] => LDehaan@mail.com
[PHONE_NUMBER] => 515.123.4569
[HIRE_DATE] => 2003-08-28
[JOB_ID] => AD_VP
[SALARY] => 17000
)
用同样的查询语句改写访问SQLite数据库的文件，得到的结果除了上述提到的不同之外，更重要的是在字段名之前加上了表别名的前缀。相比看来，SQLite的结果更加合理，因为可以绝对避免不同表的字段名相同时访问所产生的问题，但是为程序从MySQL到SQLite的迁移增大了麻烦。
Array
(
[0] => 90
[a.DEPARTMENT_ID] => 90
[1] => Executive
[a.DEAPRTMENT_NAME] => Executive
[2] => 100
[a.MANAGER_ID] => 100
[3] => 1700
[a.LACATION_ID] => 1700
[4] => 100
[b.EMPLOYEE_ID] => 100
[5] => Steven
[b.FIRST_NAME] => Steven
[6] => King
[b.LAST_NAME] => King
[7] => SKING@mail.com
[b.EMAIL] => SKING@mail.com
[8] => 515.123.4567
[b.PHONE_NUMBER] => 515.123.4567
[9] => 2003-08-19
[b.HIRE_DATE] => 2003-08-19
[10] => AD_PRES
[b.JOB_ID] => AD_PRES
[11] => 24000
[b.SALARY] => 24000
)
Array
(
[0] => 60
[a.DEPARTMENT_ID] => 60
[1] => IT
[a.DEAPRTMENT_NAME] => IT
[2] => 103
[a.MANAGER_ID] => 103
[3] => 1400
[a.LACATION_ID] => 1400
[4] => 103
[b.EMPLOYEE_ID] => 103
[5] => Lex
[b.FIRST_NAME] => Lex
[6] => Dehaan
[b.LAST_NAME] => Dehaan
[7] => LDehaan@mail.com
[b.EMAIL] => LDehaan@mail.com
[8] => 515.123.4569
[b.PHONE_NUMBER] => 515.123.4569
[9] => 2003-08-28
[b.HIRE_DATE] => 2003-08-28
[10] => AD_VP
[b.JOB_ID] => AD_VP
[11] => 17000
[b.SALARY] => 17000
)
四、为什么要迁移
SQLite是文件型数据库，对于建立个人学习研究型网站是个很好的选择，其空间价格同Access一样，但是可以跨平台访问。而MySQL数据库的空间价格同MS SQL Server的数据库是一样的。具体的可以参考中国万网的虚拟主机情况，里面提供了一款支持SQLite的Unix主机。
附件包含了所有测试代码，从PhpMyAdmin导出来的SQL文件。请改成rar格式然后解压。
http://wuxingblog.blogchina.com/inc/dbtest.doc
参考与工具：
PHPManual
EMS MySQL Manager
PHPMyAdmin
SQLiteManager
Zend Development Environment  
转摘http://wuxingblog.bokee.com/2651648.html

这里面有一个sqlitemanager，这个软件，可以想想有什么用！

当然是用来管理sqlite数据库的软件了，很简洁实用。似乎还是免费的。
个人建议还是用mysql，sqlite的索引优化等还不是很完整，不知道会不会和ACCESS一样，文件大的时候，出现大量占用内存等现象？
mysql的迁移也方便，遗憾的是数据库需要单独购买而已。
在php5.3成为主流之前，sqlite应用不会平民化。

这里指出楼主帖子中的一处错误，mysql_fetch_array()在没有用$result_type参数指定返回数组的格式时，同sqlite_fetch_array()一样，返回的也是同时包含关联和数字索引的数组。

我用国外空间，数据库不是问题，速度是问题 哈哈