【MFC】MFC中SQLite使用经验总结

关于事务
int result;
char *zErrMsg = 0;
result = sqlite3_exec(db,"begin transcation",0,0,&zErrorMsg)；       //开启一个事务
result = sqlite3_exec(db,"commit transaction",0,0,&zErrorMsg)；     //提交事务
result = sqlite3_exec(db,"rollback transaction",0,0,&zErrorMsg)；    //回滚事务

高性能
sqlite3_exec(db5, "PRAGMA synchronous = OFF; ", 0, 0, 0);     //关闭写同步
sqlite3_exec(db5, "PRAGMA journal_mode=WAL; ", 0, 0, 0);     //实现原子事务的一种机制
sqlite3_exec(db5, "BEGIN;", 0, 0, 0);

open语句
sqlite3 *db;
CString g_strDbPath = "/路径";
rc = sqlite3_open(g_strDbPath, &db);

转换语句
CString sql;
sql.Format("select memo from bill where del_flag=0 and bill_no = '%s'", strBillNo);

声明一个sqlite对象
sqlite3_stmt *stmt; 

准备语句
sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);

执行语句
sqlite3_step(stmt);

销毁语句，销毁之前sqlite3_prepare创建的准备语句
sqlite3_finalize(stmt);

直接执行语句      便捷函数
sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);

关于errmsg
如果有错误发生，使用sqlite3_free（）释放errmsg占用的内存。

关闭语句
sqlite3_close(db);
db = NULL;

重新使用
result = sqlite3_reset(stmt);
这样， stat 结构又成为 sqlite3_prepare 完成时的状态，你可以重新为它 bind 内容。

额外

1.while((rc=sqlite3_step(stmt))==SQLITE_ROW)调用函数sqlite3_step()，这个函数就是执行一步（本例中就是插入一行），如果函数返回的是SQLite_ROW则说明仍在继续执行，否则则说明已经执行完所有操作。
2.SQLite插入数据效率最快的方式就是：事务+关闭写同步+执行准备（存储过程），如果对数据库安全性有要求的话，就开启写同步。   
3.bind的使用
const char* kInsertSql = "INSERT INTO PERFORMANCE_TEST VALUES(?,?,?,?);";  
  
sqlite3_stmt* stmt = NULL;  
sqlite3_prepare_v2(db, kInsertSql, strlen(kInsertSql), &stmt, NULL);  
for (int i = 0; i < data_count; ++i) {  
  sqlite3_reset(stmt);  
  
  sqlite3_bind_int(stmt, 1, i);  
  sqlite3_bind_int(stmt, 2, i);  
  sqlite3_bind_int(stmt, 3, i);  
  sqlite3_bind_int(stmt, 4, i);  
  sqlite3_step(stmt);  
}  
  
if (turn_on_transaction) {  
  sqlite3_exec(db, kCommitTransaction, NULL, NULL, NULL);  
}  
  
sqlite3_finalize(stmt); 
4.关于sqlite3访问锁的问题
多个进程可同时打开同一个数据库。多个进程可以同时进行SELECT操作，但在任一时刻，只能有一个进程对数据库进行更改。SQLite允许多个进程同时打开一个数据库， 同时读一个数据库。当有任何进程想要写时，它必须在更新过程中锁住数据库文件。但那通常只是几毫秒的时间。其它进程只需等待写进程干完活结束。典型地，其它嵌入式的SQL数据库引擎同时只允许一个进程连接到数据库

5.高性能示例
sqlite3_exec(db, "PRAGMA synchronous = OFF; ", 0, 0, 0);     //关闭写同步
sqlite3_exec(db, "PRAGMA journal_mode=WAL; ", 0, 0, 0);     //实现原子事务的一种机制
sqlite3_exec(db, "begin transcation", 0, 0, &zErrMsg);        //开启事务

sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
sqlite3_step(stmt);

sqlite3_finalize(stmt);
sqlite3_exec(db, "commit transaction", 0, 0, &zErrMsg);

6.【提高插入效率的方法】
1)、手动开启事务，插入后一次性提交。
2)、一次准备（sqlite3_prepare_v2），多次绑定（sqlite3_bind_*）。
3)、如果有定期备份的机制，而且少量数据丢失可接受，可将同步方式设置为OFF，默认为FULL。
sqlite3_exec(db, "PRAGMA synchronous = OFF; ", 0,0,0);
4)、如果不需要将数据存储到文件，可采用内存模式。
sqlite3_open(":memory:", &db);
5)、ournal_mode 调优
sqlite3_exec(db,"pragma journal_mode = WAL; ",0,0,0);
如果在事务执行中程序崩溃或掉电，数据库有可能发生错误。

可参考 http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite?rq=1

7.【提高查询效率的方法】
1)、创建索引，可以显著提高select的速度，同时也可以提高update的速度。
2)、用 replace代替select+insert/update

8.插入读写效率比较（高到低）
显式开启事务执行sqlite3_step>>关闭写同步且显式开启事务执行sqlite3_exec>显式开启事务执行sqlite3_exec>关闭写同步执行sqlite3_step>直接执行sqlite3_step>直接执行sqlite3_exec

 

 

补充:sqlite3返回码对照表

sqlite常量的定义：

const

SQLITE_OK = 0; 返回成功

SQLITE_ERROR = 1; SQL错误或错误的数据库

SQLITE_INTERNAL = 2; An internal logic error in SQLite

SQLITE_PERM = 3; 拒绝访问

SQLITE_ABORT = 4; 回调函数请求中断

SQLITE_BUSY = 5; 数据库文件被锁

SQLITE_LOCKED = 6; 数据库中的一个表被锁

SQLITE_NOMEM = 7; 内存分配失败

SQLITE_READONLY = 8; 试图对一个只读数据库进行写操作

SQLITE_INTERRUPT = 9; 由sqlite_interrupt()结束操作

SQLITE_IOERR = 10; 磁盘I/O发生错误

SQLITE_CORRUPT = 11; 数据库磁盘镜像畸形

SQLITE_NOTFOUND = 12; (Internal Only)表或记录不存在

SQLITE_FULL = 13; 数据库满插入失败

SQLITE_CANTOPEN = 14; 不能打开数据库文件

SQLITE_PROTOCOL = 15; 数据库锁定协议错误

SQLITE_EMPTY = 16; (Internal Only)数据库表为空

SQLITE_SCHEMA = 17; 数据库模式改变

SQLITE_TOOBIG = 18; 对一个表数据行过多

SQLITE_CONSTRAINT = 19; 由于约束冲突而中止

SQLITE_MISMATCH = 20; 数据类型不匹配

SQLITE_MISUSE = 21; 数据库错误使用

SQLITE_NOLFS = 22; 使用主机操作系统不支持的特性

SQLITE_AUTH = 23; 非法授权

SQLITE_FORMAT = 24; 辅助数据库格式错误

SQLITE_RANGE = 25; 2nd parameter to sqlite_bind out of range

SQLITE_NOTADB = 26; 打开的不是一个数据库文件

SQLITE_ROW = 100; sqlite_step() has another row ready

SQLITE_DONE = 101; sqlite_step() has finished executing

......

数据库运行函数sqlite3_exec()与单步运行函数sqliet3_step()的返回值

SQLITE_OK/0 成功执行

SQLITE_ERROR/1 SQL错误或数据库不存在

SQLITE_INTERNAL/2 SQLite内部逻辑错误（没有使用）

SQLITE_PERM/3 访问许可禁止

SQLITE_ABORT/4 回调函数请求中止

SQLITE_BUSY/5 数据库文件被锁定

SQLITE_LOCKED/6 数据库中的一表被锁定

SQLITE_NOMEM/7 malloc()分配堆失败

SQLITE_READONLY/8 企图写只读数据库

SQLITE_INTERRUPT/9 操作被sqlie3_interrupt()中止

SQLITE_IOERR/10 磁盘I/O错误

SQLITE_CORRUPT/11 数据库文件变形

SQLITE_NOTFOUND/12 表或纪录没有找到（没有使用）

SQLITE_FULL/13 插入失败因为数据库满

SQLITE_CANTOPEN/14 不能打开数据库文件

SQLITE_PROTOCOL/15 数据库锁协议错误

SQLITE_EMPTY/16 数据库是空的

SQLITE_SCHEMA/17 数据库模式改变

SQLITE_TOOBIG/18 单行数据过多（没有使用）

SQLITE_CONSTRAINT/19 因约束违背而中止

SQLITE_MISMATCH/20 数据类型不匹配

SQLITE_MISUSE/21 库使用方法不当

SQLITE_NOLFS/22 主机不支持库中操作系统功能

SQLITE_AUTH/23 认证禁止

SQLITE_FORMAT/24 附属数据库格式错误

SQLITE_RANGE/25 sqlite3_bind()第2个参数超出范围

SQLITE_NOTADB/26 打开非数据库文件

SQLITE_ROW/100 sqlite3_step()有另一行数据就绪

SQLITE_DONE/101 sqlite3_step()完成执行，已无数据