前言
这篇是接着上篇ios学习笔记之SQLite初学者手册（上）的，对如何优化在执行大量插入语句之后的性能，如何回滚数据以及如何查询做一简单的说明。
一 性能优化
1 DML语句 通过绑定参数实现插入语句
准备语句（prepared statement）对象
准备语句（prepared statement）对象一个代表一个简单SQL语句对象的实例，这个对象通常被称为“准备语句”或者编译好的SQL语句
操作历程：
- 使用sqlite3_prepare_v2或相关的函数创建这个对象 如果执行成功，则返回SQLITE_OK,否则返回一个错误码
2.使用sqlite3_bind_*（）给宿主参数（host parameters）绑定值
3.通过调用sqlite3_step() 一次或多次来执行这个sql
4.使用sqlite3_reset()重置这个语句，然后回到第2步，这个过程做0次或多次
5.使用sqlite3_finalize()销毁这个对象，防止内存泄露
假设我们要插入很多条数据，则第2、3、4步重复执行，第1、5步就不需要重复执行了
示例：
（创建数据库接上篇）
ios学习笔记之SQLite初学者手册（上）
为Student类添加方法
// 通过绑定的方式，来插入语句
func bindInsert() -> () {
let sql = "insert into t_stu(name,age,score) values(?,?,?)"
// 1 根据sql语句，创建，准备语句，问号代表占位
// 参数一：一个已经打开的数据库
// 参数二：sql字符串 "lsfjfflkf"
// 参数三：即将取出字符串（参数二这个字符串）的长度，例如：2，就是从前面开始，取2个字符
// -1代表自动计算
// 参数四：预处理语句
// 参数五：根据参数三的长度，取出参数二的值以后，剩余的数字，例如取出了参数二中的ls
// 两个字符，那么参数五就是fjfflkf
let db = SQLiteTool.shareInstance.db
var stmt:OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK{
print("预处理失败")
return
}
// 2 绑定参数
// 参数一:准备语句
// 参数二：绑定值的索引，索引从1开始
sqlite3_bind_int(stmt, 2, 20) // 绑定第二个参数，赋值20
sqlite3_bind_double(stmt, 3, 60.0) // 绑定第三个参数，赋值60.0
// 绑定文本
// 参数一：准备语句
// 参数二：绑定值的索引，1
// 参数三：绑定的值
// 参数四：值取出多少长度，-1，取出所有
// 参数五：值的处理方式
// 下面两个是宏定义，swift里面没有宏定义
// （按住command点击sqlite3_bind_text方法，索索SQLITE_STATIC及SQLITE_TRANSIENT,然后会发现：
// typedef void (*sqlite3_destructor_type)(void*);
// #define SQLITE_STATIC ((sqlite3_destructor_type)0)
// #define SQLITE_TRANSIENT ((sqlite3_destructor_type)-1)
// sqlite3_destructor_type是指向函数的指针
// ）
// SQLITE_STATIC:认为参数是一个常量，不会被释放，处理方案：不做任何引用
// SQLITE_TRANSIENT：会对参数进行一个引用
//
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
// 此时按住option键点击SQLITE_TRANSIENT会看到：let SQLITE_TRANSIENT: sqlite3_destructor_type，就是说此时SQLITE_TRANSIENT的类型是sqlite3_destructor_type
sqlite3_bind_text(stmt, 1, "qingyun", -1, SQLITE_TRANSIENT) //绑定第一个参数
// 3 执行sql语句，准备语句
if sqlite3_step(stmt) == SQLITE_DONE{
print("执行成功")
}
// 4 重置语句
sqlite3_reset(stmt)
// 5 释放准备语句
sqlite3_finalize(stmt)
}
func insertStudent() -> () {
let sql = "insert into t_stu(name,age,score) values('\(name)',\(age),\(score))"
if SQLiteTool.shareInstance.execute(sql: sql) {
print("插入行成功")
}else{
print("插入行失败")
}
}
在控制器中测试
override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) {
let stu = Student(name:"wangwu",age:39,score:99)
stu.bindInsert()
}
刷新Navicat Premium，发现插入成功
相比较上篇里讲的给我一条语句sql，然后去执行sqlite3_exec(db, sql, nil, nil, nil)语句，我们的绑定参数确实是复杂了很多，但是sqlite3_exec其实就是对绑定参数方法的一个封装，当我们插入很多条语句时，分别查看两种方法的性能，那么肯定是绑定参数这种方法性能更好。
下面在控制器中对两种方法进行执行时间测试：
用sqlite3_exec方法：
override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) {
let stu = Student(name:"wangwu",age:39,score:99)
let beginTime = CFAbsoluteTimeGetCurrent()
for i in 0..<1000{
stu.insertStudent()
}
let endTime = CFAbsoluteTimeGetCurrent()
print(endTime - beginTime)
}
测试三次，点击三次屏幕，(将所有的print("插入行成功")等都注释掉，因为这个很耗时)结果：
执行成功
0.5521399974823
0.540975987911224
0.548547983169556
用绑定参数方法：
对bindInsert()方法进行修改，将第2、3、4步放入循环
// 通过绑定的方式，来插入语句
func bindInsert() -> () {
let sql = "insert into t_stu(name,age,score) values(?,?,?)"
let db = SQLiteTool.shareInstance.db
var stmt:OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK{
print("预处理失败")
return
}
for i in 0..<1000{
// 2 绑定参数
let newI = Int32(i) // 将i转换成Int32类型
sqlite3_bind_int(stmt, 2, newI) // 绑定第二个参数，赋值i
sqlite3_bind_double(stmt, 3, 60.0) // 绑定第三个参数，赋值60.0
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
sqlite3_bind_text(stmt, 1, "qingyun", -1, SQLITE_TRANSIENT) //绑定第一个参数
// 3 执行sql语句，准备语句
if sqlite3_step(stmt) == SQLITE_DONE{
// print("执行成功")
}
// 4 重置语句
sqlite3_reset(stmt)
}
// 5 释放准备语句
sqlite3_finalize(stmt)
}
func insertStudent() -> () {
let sql = "insert into t_stu(name,age,score) values('\(name)',\(age),\(score))"
if SQLiteTool.shareInstance.execute(sql: sql) {
// print("插入行成功")
// }else{
// print("插入行失败")
}
}
控制器
override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) {
let stu = Student(name:"wangwu",age:39,score:99)
let beginTime = CFAbsoluteTimeGetCurrent()
stu.bindInsert()
let endTime = CFAbsoluteTimeGetCurrent()
print(endTime - beginTime)
}
结果：
// 执行成功
// 0.494072020053864
// 0.520428955554962
// 0.516857028007507
// 0.521933019161224
多次测试，发现绑定参数方法略微快点，这还不是我们想要的，如果继续优化，该怎么办呢？？？其实，如果使用sqlite3_exec或者，sqlite3_step()来执行sql语句，会自动开启一个”事务“，然后，自动提交”事务“，那么针对这个问题，我们可以这样解决：只需要手动开启事务，手动提交任务，这时候，函数内部，就不会自动开启和提交事务，这样可以大大提高执行效率。
2 手动开启事务
为SQLiteTool类增加方法
// 手动开启事务
func beginTransaction() -> () {
let sql = "begin transaction"
execute(sql: sql)
}
// 手动关闭事务
func commitTransation() -> () {
let sql = "commit transaction"
execute(sql: sql)
}
将其插入到Student的绑定代码中
// 通过绑定的方式，来插入语句
func bindInsert() -> () {
let sql = "insert into t_stu(name,age,score) values(?,?,?)"
let db = SQLiteTool.shareInstance.db
var stmt:OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK{
print("预处理失败")
return
}
// 手动开启事务
SQLiteTool.shareInstance.beginTransaction()
for i in 0..<1000{
// 2 绑定参数
let newI = Int32(i)// 将i转换成Int32类型
sqlite3_bind_int(stmt, 2, newI) // 绑定第二个参数，赋值i
sqlite3_bind_double(stmt, 3, 60.0) // 绑定第三个参数，赋值60.0
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
sqlite3_bind_text(stmt, 1, "qingyun", -1, SQLITE_TRANSIENT) //绑定第一个参数
// 3 执行sql语句，准备语句
if sqlite3_step(stmt) == SQLITE_DONE{
// print("执行成功")
}
// 4 重置语句
sqlite3_reset(stmt)
}
// 提交事务
SQLiteTool.shareInstance.commitTransation()
// 5 释放准备语句
sqlite3_finalize(stmt)
}
在控制器里面执行
override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) {
let stu = Student(name:"wangwu",age:39,score:99)
let beginTime = CFAbsoluteTimeGetCurrent()
stu.bindInsert()
let endTime = CFAbsoluteTimeGetCurrent()
print(endTime - beginTime)
}
结果：
执行成功
0.00568002462387085
可以看出效率大大提高了。
二 回滚数据
当我们做多条修改时，假如有的语句修改成功，而有的修改失败，那么最终我们是否要提交呢？在数据库里，我们只有当全部数据修改成功时再整体提交，如果部分执行失败，就不提交，保持修改之前的数据。
示例：
我们先在表t_stu中增加字段money,然后手动输入两条数据
在Student类里添加更新数据的类方法
class func update(sql:String) -> (Bool) {
return SQLiteTool.shareInstance.execute(sql: sql)
}
在SQLiteTool类添加回滚的语句
func rollbackTransaction() -> () {
let sql = "rollback transaction"
execute(sql: sql)
}
控制器：故意将语句“let result2 = Student.update(sql: "update t_stu set money1 = money + 10 where name = 'lisi'")” 中的money错写成money1
override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) {
// zhangsan money -10
SQLiteTool.shareInstance.beginTransaction()
let result1 = Student.update(sql: "update t_stu set money = money - 10 where name = 'zhangsan'") // 将张三的钱减10
// lisi money +10
let result2 = Student.update(sql: "update t_stu set money1 = money + 10 where name = 'lisi'") // 将李四的钱加10
if result1 && result2 {
SQLiteTool.shareInstance.commitTransation() // 当两条修改语句全部正确执行，才会提交
print("修改数据成功，提交")
}else{
SQLiteTool.shareInstance.rollbackTransaction() // 只要有一条操作失败，就回滚，滚到修改之前的数据
print("修改数据失败，回滚")
}
}
刷新数据库，发现数据没变。但是如果我们将错写的money1改回money，则提交成功。
三 DQL查询语句
1 代码
为Student方法添加两个新的类方法，queryAllStmt方法是通过绑定来进行查询，queryAll是通过sqlite3_exec进行查询
class func queryAllStmt() -> () {
// 准备语句 历程
let sql = "select * from t_stu "
let db = SQLiteTool.shareInstance.db
// 一.创建准备语句
// 参数一：一个已经打开的数据库
// 参数二：sql语句
// 参数三：字符串，取的长度，-1代表全选
// 参数四：准备语句的指针
// 参数五：剩余待取的字符串
var stmt:OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
print("准备预处理语句失败")
return
}
// 二.绑定参数(这一步可以省略)
// 三.执行准备语句
// sqlite3_step 的作用 执行DQL语句时，会把执行得到的结果放到准备语句（stmt）中
while sqlite3_step(stmt) == SQLITE_ROW {
// 先判断，如果还有数据(整行数据)，则继续执行，while循环是一行行取
// 读取数据
// 1. 从准备语句里读取
let count = sqlite3_column_count(stmt) // sqlite3_column_count(stmt)是用来计算预处理语句里，一共有多少列的
for i in 0..<count{
// for循环是一列列取
// 2. 取出列的名称
let columnName = sqlite3_column_name(stmt, i)
let columnNameStr = String(cString:columnName!,encoding:String.Encoding.utf8)
print(columnNameStr as Any)
// 3.取出列的值
// 不同的类型，是通过不同的函数获取
// 3.1 获取这一列的类型
let type = sqlite3_column_type(stmt, i)
// 3.2 根据不同的类型，使用不同的函数
if type == SQLITE_INTEGER {
let value = sqlite3_column_int(stmt, i)
print(value)
}
if type == SQLITE_FLOAT {
let value = sqlite3_column_double(stmt, i)
print(value)
}
if type == SQLITE_TEXT {
// value:UnsafePointer<UInt8>?
let value = sqlite3_column_text(stmt, i)
let valueStr = String(cString: value!)
print(valueStr)
}
}
}
// 四.重置准备语句(这一步可以省略)
// 五.释放准备语句
sqlite3_finalize(stmt)
}
class func queryAll() -> () {
let sql = "select * from t_stu"
let db = SQLiteTool.shareInstance.db
// 参数一：一个打开的数据库
// 参数二：sql语句
// 参数三：回调代码块
// 参数1：传递过来的值
// 参数2：列的个数
// 参数3：值的数组
// 参数4：列名称的数组
// 返回值：0 继续查询 1：终止查询
// 参数四：传递到参数三里面的第一个参数
// 参数五：错误信息
sqlite3_exec(db, sql, { (firstValue, columnCount, values, columnNames) -> Int32 in
let count = Int(columnCount)
for i in 0..<count{
// 列的名称
let columnName = columnNames?[i]
let columnNameStr = String(cString:columnName!,encoding:String.Encoding.utf8)
// 值
let value = values?[i]
let valueStr = String(cString:value!,encoding:String.Encoding.utf8)
// let valueStr = String.Encoding.utf8
print(columnNameStr as Any,valueStr as Any)
}
return 0
}, nil, nil)
}
在控制器分别调用两个方法
override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) {
// Student.queryAll()
Student.queryAllStmt()
}
结果：
Optional("id")
1
Optional("name")
zhangsan
Optional("age")
10
Optional("score")
99.0
Optional("money")
100.0
Optional("id")
2
Optional("name")
lisi
Optional("age")
9
Optional("score")
88.0
Optional("money")
100.0
2 将绑定方法与sqlite3_exec方法进行对比
①.使用后者，解析出来的都是字符串，而使用绑定的方法基本类型都可以解析
②.使用后者，无法插入二进制，因为插入二进制时，是当做字符串进行解析的，而使用绑定的方法，可以在绑定参数时，绑定二进制(sqlite3_bind_blob)
