SQLite批量插入和其他一些使用心得

 SQLite是我在客户端上使用得最多的数据库，方便易用稳定强大，其支持很多的平台和语言：我分别在Windows （C++、QT）、Android、IOS上开发过使用SQLite来保存客户端本地数据的应用。下面说说对使用SQLite的一些心得。

      1、批量插入。很多人发现使用SQLite插入大量数据的时候非常慢。我有印象刚使用SQLite的时候发现插入2000条数据（10列左右）居然要好几分钟，速度慢得令人发指。但后来发现如果是正确使用SQLite，一般这是在小几百毫秒就能完成的工作。要点：

      A）务必要使用事务。这点可以数量级地提高批量插入的速度。

      B）使用Statement并用好，具体地说是： prepare（此步奏只运行一次） -> (开始处理第一条记录) -> bind（填充第一列） -> bind（填充第二列……） -> execute（执行插入） -> clearBind（别忘了清除填充的数据） ->  (开始处理第二条记录)  -> bind（填充第一列） -> bind （填充第二列……）-> execute -> clearBind（清除填充的数据） -> （处理第三条记录……）。Statement的prepare也是一个比较耗时的操作，在批量插入的时候没必要每次都去调用。这点对性能的提升没有使用事务来得明显，但一般也能提升几倍的性能。

      《Android批量插入代码示例》

      《IOS批量插入代码示例》

      2、数据库升级机制。产品周期较长的软件开发过程中，难免要对数据库的表结构进行修改，比如增加表、字段、索引。一开始我是通过表名来进行区分，比如用户表是t_user，如果表结构需要修改就建立一张新表t_user_v2。在数据库打开的时候，检查各表是否存在，如果不存在则创建。后面开发安卓应用，发现Google的解决方法优雅多了：其可以为数据库定义一个版本号（举个栗子——19），之后如果数据库表结构需要修改，则增加这个版本号（举个栗子——增加到20）。然后在打开数据库的时候会自动触发 void onUpgrade(SQLiteDatabase db, int oldVersion[=19], int newVersion[=20] ) 函数，该函数有之前的版本号和目前的版本号，然后就可以执行相应的数据库升级脚本了。

      这套机制即使SDK没有自带，实现起来也很简单：就是在数据库初始化或者升级后找个地方保存版本记录（建议参考安卓用SQLite的user_version设置，详见：http://sqlite.org/pragma.html#pragma_schema_version，或者用SharedPreference、文本文件也是可以的。）。下次打开数据库的时候，对比保存的版本和当前的数据库版本。如果当前的数据库版本更高，则调用数据库升级函数进行升级（记得之后更新版本记录为新的版本号）。我在IOS上就是使用这种方式来进行数据库升级的。

      3、数据库划分。建议：如果没有事务、外键等需要，尽量把不同用途的表划分在多个数据库文件中，而不是使用单一的数据库文件。这么做最主要优点是有利于模块划分，避免其他所有模块都要访问同一个巨大务必的数据库模块。另外性能也会更好，比如SQLite并不支持多线程读写，所以访问同一个数据库的时候是要加同步锁的。使用多个数据库可以降低锁等待的可能。

      4、理解rowid。

       rowid是SQLite比较有特色的功能。rowid是SQLite为每条记录保存的一个隐藏列（select * from table 查看不了，需要的 select rowid,* from table），rowid从1开始递增。如果拿rowid作为外键来使用的时候，需要注意的一点是：“insert or replace into" 是会改变rowid的值。所以如果想更新记录，应该使用update语句，如果想忽略新值，应该用"insert or ignore into"语句。

      最后但很重要的是：掌握SQL的基本知识。推荐一本书：《SQL反模式》。SQL是很简单，但用久了发现里面还是有不少门道。磨刀不误砍柴工，多看一本书，也许就可以少犯很多错。

转自：