前言
這篇是接著上篇ios學(xué)習(xí)筆記之SQLite初學(xué)者手冊(cè)（上）的，對(duì)如何優(yōu)化在執(zhí)行大量插入語(yǔ)句之后的性能纵柿，如何回滾數(shù)據(jù)以及如何查詢(xún)做一簡(jiǎn)單的說(shuō)明却汉。
一 性能優(yōu)化
1 DML語(yǔ)句 通過(guò)綁定參數(shù)實(shí)現(xiàn)插入語(yǔ)句
準(zhǔn)備語(yǔ)句（prepared statement）對(duì)象
準(zhǔn)備語(yǔ)句（prepared statement）對(duì)象一個(gè)代表一個(gè)簡(jiǎn)單SQL語(yǔ)句對(duì)象的實(shí)例廊酣，這個(gè)對(duì)象通常被稱(chēng)為“準(zhǔn)備語(yǔ)句”或者編譯好的SQL語(yǔ)句
操作歷程：
- 使用sqlite3_prepare_v2或相關(guān)的函數(shù)創(chuàng)建這個(gè)對(duì)象 如果執(zhí)行成功锉罐，則返回SQLITE_OK,否則返回一個(gè)錯(cuò)誤碼
2.使用sqlite3_bind_*（）給宿主參數(shù)（host parameters）綁定值
3.通過(guò)調(diào)用sqlite3_step() 一次或多次來(lái)執(zhí)行這個(gè)sql
4.使用sqlite3_reset()重置這個(gè)語(yǔ)句帆竹，然后回到第2步，這個(gè)過(guò)程做0次或多次
5.使用sqlite3_finalize()銷(xiāo)毀這個(gè)對(duì)象脓规，防止內(nèi)存泄露
假設(shè)我們要插入很多條數(shù)據(jù)栽连，則第2、3侨舆、4步重復(fù)執(zhí)行秒紧，第1、5步就不需要重復(fù)執(zhí)行了
示例：
（創(chuàng)建數(shù)據(jù)庫(kù)接上篇）
ios學(xué)習(xí)筆記之SQLite初學(xué)者手冊(cè)（上）
為Student類(lèi)添加方法
// 通過(guò)綁定的方式挨下，來(lái)插入語(yǔ)句
func bindInsert() -> () {
let sql = "insert into t_stu(name,age,score) values(?,?,?)"
// 1 根據(jù)sql語(yǔ)句熔恢，創(chuàng)建，準(zhǔn)備語(yǔ)句臭笆，問(wèn)號(hào)代表占位
// 參數(shù)一：一個(gè)已經(jīng)打開(kāi)的數(shù)據(jù)庫(kù)
// 參數(shù)二：sql字符串 "lsfjfflkf"
// 參數(shù)三：即將取出字符串（參數(shù)二這個(gè)字符串）的長(zhǎng)度叙淌，例如：2，就是從前面開(kāi)始愁铺，取2個(gè)字符
// -1代表自動(dòng)計(jì)算
// 參數(shù)四：預(yù)處理語(yǔ)句
// 參數(shù)五：根據(jù)參數(shù)三的長(zhǎng)度鹰霍，取出參數(shù)二的值以后，剩余的數(shù)字茵乱，例如取出了參數(shù)二中的ls
// 兩個(gè)字符衅谷，那么參數(shù)五就是fjfflkf
let db = SQLiteTool.shareInstance.db
var stmt:OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK{
print("預(yù)處理失敗")
return
}
// 2 綁定參數(shù)
// 參數(shù)一:準(zhǔn)備語(yǔ)句
// 參數(shù)二：綁定值的索引，索引從1開(kāi)始
sqlite3_bind_int(stmt, 2, 20) // 綁定第二個(gè)參數(shù)似将，賦值20
sqlite3_bind_double(stmt, 3, 60.0) // 綁定第三個(gè)參數(shù)获黔，賦值60.0
// 綁定文本
// 參數(shù)一：準(zhǔn)備語(yǔ)句
// 參數(shù)二：綁定值的索引，1
// 參數(shù)三：綁定的值
// 參數(shù)四：值取出多少長(zhǎng)度在验，-1玷氏，取出所有
// 參數(shù)五：值的處理方式
// 下面兩個(gè)是宏定義，swift里面沒(méi)有宏定義
// （按住command點(diǎn)擊sqlite3_bind_text方法腋舌，索索SQLITE_STATIC及SQLITE_TRANSIENT,然后會(huì)發(fā)現(xiàn)：
// typedef void (*sqlite3_destructor_type)(void*);
// #define SQLITE_STATIC ((sqlite3_destructor_type)0)
// #define SQLITE_TRANSIENT ((sqlite3_destructor_type)-1)
// sqlite3_destructor_type是指向函數(shù)的指針
// ）
// SQLITE_STATIC:認(rèn)為參數(shù)是一個(gè)常量盏触，不會(huì)被釋放，處理方案：不做任何引用
// SQLITE_TRANSIENT：會(huì)對(duì)參數(shù)進(jìn)行一個(gè)引用
//
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
// 此時(shí)按住option鍵點(diǎn)擊SQLITE_TRANSIENT會(huì)看到：let SQLITE_TRANSIENT: sqlite3_destructor_type块饺，就是說(shuō)此時(shí)SQLITE_TRANSIENT的類(lèi)型是sqlite3_destructor_type
sqlite3_bind_text(stmt, 1, "qingyun", -1, SQLITE_TRANSIENT) //綁定第一個(gè)參數(shù)
// 3 執(zhí)行sql語(yǔ)句赞辩，準(zhǔn)備語(yǔ)句
if sqlite3_step(stmt) == SQLITE_DONE{
print("執(zhí)行成功")
}
// 4 重置語(yǔ)句
sqlite3_reset(stmt)
// 5 釋放準(zhǔn)備語(yǔ)句
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("插入行失敗")
}
}
在控制器中測(cè)試
override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) {
let stu = Student(name:"wangwu",age:39,score:99)
stu.bindInsert()
}
刷新Navicat Premium，發(fā)現(xiàn)插入成功
相比較上篇里講的給我一條語(yǔ)句sql授艰，然后去執(zhí)行sqlite3_exec(db, sql, nil, nil, nil)語(yǔ)句辨嗽，我們的綁定參數(shù)確實(shí)是復(fù)雜了很多，但是sqlite3_exec其實(shí)就是對(duì)綁定參數(shù)方法的一個(gè)封裝淮腾，當(dāng)我們插入很多條語(yǔ)句時(shí)糟需，分別查看兩種方法的性能屉佳，那么肯定是綁定參數(shù)這種方法性能更好。
下面在控制器中對(duì)兩種方法進(jìn)行執(zhí)行時(shí)間測(cè)試：
用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)
}
測(cè)試三次洲押，點(diǎn)擊三次屏幕武花，(將所有的print("插入行成功")等都注釋掉，因?yàn)檫@個(gè)很耗時(shí))結(jié)果：
執(zhí)行成功
0.5521399974823
0.540975987911224
0.548547983169556
用綁定參數(shù)方法：
對(duì)bindInsert()方法進(jìn)行修改杈帐，將第2体箕、3、4步放入循環(huán)
// 通過(guò)綁定的方式挑童，來(lái)插入語(yǔ)句
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("預(yù)處理失敗")
return
}
for i in 0..<1000{
// 2 綁定參數(shù)
let newI = Int32(i) // 將i轉(zhuǎn)換成Int32類(lèi)型
sqlite3_bind_int(stmt, 2, newI) // 綁定第二個(gè)參數(shù)干旁，賦值i
sqlite3_bind_double(stmt, 3, 60.0) // 綁定第三個(gè)參數(shù)，賦值60.0
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
sqlite3_bind_text(stmt, 1, "qingyun", -1, SQLITE_TRANSIENT) //綁定第一個(gè)參數(shù)
// 3 執(zhí)行sql語(yǔ)句炮沐，準(zhǔn)備語(yǔ)句
if sqlite3_step(stmt) == SQLITE_DONE{
// print("執(zhí)行成功")
}
// 4 重置語(yǔ)句
sqlite3_reset(stmt)
}
// 5 釋放準(zhǔn)備語(yǔ)句
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)
}
結(jié)果：
// 執(zhí)行成功
// 0.494072020053864
// 0.520428955554962
// 0.516857028007507
// 0.521933019161224
多次測(cè)試争群，發(fā)現(xiàn)綁定參數(shù)方法略微快點(diǎn)，這還不是我們想要的大年，如果繼續(xù)優(yōu)化换薄，該怎么辦呢？翔试？轻要？其實(shí)，如果使用sqlite3_exec或者垦缅，sqlite3_step()來(lái)執(zhí)行sql語(yǔ)句冲泥，會(huì)自動(dòng)開(kāi)啟一個(gè)”事務(wù)“鬼佣，然后成黄，自動(dòng)提交”事務(wù)“，那么針對(duì)這個(gè)問(wèn)題房待，我們可以這樣解決：只需要手動(dòng)開(kāi)啟事務(wù)怔球，手動(dòng)提交任務(wù)嚼酝，這時(shí)候，函數(shù)內(nèi)部竟坛，就不會(huì)自動(dòng)開(kāi)啟和提交事務(wù)闽巩，這樣可以大大提高執(zhí)行效率。
2 手動(dòng)開(kāi)啟事務(wù)
為SQLiteTool類(lèi)增加方法
// 手動(dòng)開(kāi)啟事務(wù)
func beginTransaction() -> () {
let sql = "begin transaction"
execute(sql: sql)
}
// 手動(dòng)關(guān)閉事務(wù)
func commitTransation() -> () {
let sql = "commit transaction"
execute(sql: sql)
}
將其插入到Student的綁定代碼中
// 通過(guò)綁定的方式担汤，來(lái)插入語(yǔ)句
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("預(yù)處理失敗")
return
}
// 手動(dòng)開(kāi)啟事務(wù)
SQLiteTool.shareInstance.beginTransaction()
for i in 0..<1000{
// 2 綁定參數(shù)
let newI = Int32(i)// 將i轉(zhuǎn)換成Int32類(lèi)型
sqlite3_bind_int(stmt, 2, newI) // 綁定第二個(gè)參數(shù)涎跨，賦值i
sqlite3_bind_double(stmt, 3, 60.0) // 綁定第三個(gè)參數(shù)，賦值60.0
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
sqlite3_bind_text(stmt, 1, "qingyun", -1, SQLITE_TRANSIENT) //綁定第一個(gè)參數(shù)
// 3 執(zhí)行sql語(yǔ)句崭歧，準(zhǔn)備語(yǔ)句
if sqlite3_step(stmt) == SQLITE_DONE{
// print("執(zhí)行成功")
}
// 4 重置語(yǔ)句
sqlite3_reset(stmt)
}
// 提交事務(wù)
SQLiteTool.shareInstance.commitTransation()
// 5 釋放準(zhǔn)備語(yǔ)句
sqlite3_finalize(stmt)
}
在控制器里面執(zhí)行
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)
}
結(jié)果：
執(zhí)行成功
0.00568002462387085
可以看出效率大大提高了隅很。
二 回滾數(shù)據(jù)
當(dāng)我們做多條修改時(shí)，假如有的語(yǔ)句修改成功驾荣，而有的修改失敗外构，那么最終我們是否要提交呢？在數(shù)據(jù)庫(kù)里播掷，我們只有當(dāng)全部數(shù)據(jù)修改成功時(shí)再整體提交审编，如果部分執(zhí)行失敗，就不提交歧匈，保持修改之前的數(shù)據(jù)垒酬。
示例：
我們先在表t_stu中增加字段money,然后手動(dòng)輸入兩條數(shù)據(jù)
在Student類(lèi)里添加更新數(shù)據(jù)的類(lèi)方法
class func update(sql:String) -> (Bool) {
return SQLiteTool.shareInstance.execute(sql: sql)
}
在SQLiteTool類(lèi)添加回滾的語(yǔ)句
func rollbackTransaction() -> () {
let sql = "rollback transaction"
execute(sql: sql)
}
控制器：故意將語(yǔ)句“l(fā)et result2 = Student.update(sql: "update t_stu set money1 = money + 10 where name = 'lisi'")” 中的money錯(cuò)寫(xiě)成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'") // 將張三的錢(qián)減10
// lisi money +10
let result2 = Student.update(sql: "update t_stu set money1 = money + 10 where name = 'lisi'") // 將李四的錢(qián)加10
if result1 && result2 {
SQLiteTool.shareInstance.commitTransation() // 當(dāng)兩條修改語(yǔ)句全部正確執(zhí)行，才會(huì)提交
print("修改數(shù)據(jù)成功件炉，提交")
}else{
SQLiteTool.shareInstance.rollbackTransaction() // 只要有一條操作失敗勘究，就回滾，滾到修改之前的數(shù)據(jù)
print("修改數(shù)據(jù)失敗斟冕，回滾")
}
}
刷新數(shù)據(jù)庫(kù)口糕，發(fā)現(xiàn)數(shù)據(jù)沒(méi)變。但是如果我們將錯(cuò)寫(xiě)的money1改回money磕蛇，則提交成功景描。
三 DQL查詢(xún)語(yǔ)句
1 代碼
為Student方法添加兩個(gè)新的類(lèi)方法，queryAllStmt方法是通過(guò)綁定來(lái)進(jìn)行查詢(xún)秀撇，queryAll是通過(guò)sqlite3_exec進(jìn)行查詢(xún)
class func queryAllStmt() -> () {
// 準(zhǔn)備語(yǔ)句 歷程
let sql = "select * from t_stu "
let db = SQLiteTool.shareInstance.db
// 一.創(chuàng)建準(zhǔn)備語(yǔ)句
// 參數(shù)一：一個(gè)已經(jīng)打開(kāi)的數(shù)據(jù)庫(kù)
// 參數(shù)二：sql語(yǔ)句
// 參數(shù)三：字符串超棺，取的長(zhǎng)度，-1代表全選
// 參數(shù)四：準(zhǔn)備語(yǔ)句的指針
// 參數(shù)五：剩余待取的字符串
var stmt:OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
print("準(zhǔn)備預(yù)處理語(yǔ)句失敗")
return
}
// 二.綁定參數(shù)(這一步可以省略)
// 三.執(zhí)行準(zhǔn)備語(yǔ)句
// sqlite3_step 的作用 執(zhí)行DQL語(yǔ)句時(shí)呵燕，會(huì)把執(zhí)行得到的結(jié)果放到準(zhǔn)備語(yǔ)句（stmt）中
while sqlite3_step(stmt) == SQLITE_ROW {
// 先判斷棠绘，如果還有數(shù)據(jù)(整行數(shù)據(jù))，則繼續(xù)執(zhí)行再扭，while循環(huán)是一行行取
// 讀取數(shù)據(jù)
// 1. 從準(zhǔn)備語(yǔ)句里讀取
let count = sqlite3_column_count(stmt) // sqlite3_column_count(stmt)是用來(lái)計(jì)算預(yù)處理語(yǔ)句里氧苍，一共有多少列的
for i in 0..<count{
// for循環(huán)是一列列取
// 2. 取出列的名稱(chēng)
let columnName = sqlite3_column_name(stmt, i)
let columnNameStr = String(cString:columnName!,encoding:String.Encoding.utf8)
print(columnNameStr as Any)
// 3.取出列的值
// 不同的類(lèi)型，是通過(guò)不同的函數(shù)獲取
// 3.1 獲取這一列的類(lèi)型
let type = sqlite3_column_type(stmt, i)
// 3.2 根據(jù)不同的類(lèi)型泛范，使用不同的函數(shù)
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)
}
}
}
// 四.重置準(zhǔn)備語(yǔ)句(這一步可以省略)
// 五.釋放準(zhǔn)備語(yǔ)句
sqlite3_finalize(stmt)
}
class func queryAll() -> () {
let sql = "select * from t_stu"
let db = SQLiteTool.shareInstance.db
// 參數(shù)一：一個(gè)打開(kāi)的數(shù)據(jù)庫(kù)
// 參數(shù)二：sql語(yǔ)句
// 參數(shù)三：回調(diào)代碼塊
// 參數(shù)1：傳遞過(guò)來(lái)的值
// 參數(shù)2：列的個(gè)數(shù)
// 參數(shù)3：值的數(shù)組
// 參數(shù)4：列名稱(chēng)的數(shù)組
// 返回值：0 繼續(xù)查詢(xún) 1：終止查詢(xún)
// 參數(shù)四：傳遞到參數(shù)三里面的第一個(gè)參數(shù)
// 參數(shù)五：錯(cuò)誤信息
sqlite3_exec(db, sql, { (firstValue, columnCount, values, columnNames) -> Int32 in
let count = Int(columnCount)
for i in 0..<count{
// 列的名稱(chēng)
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)
}
在控制器分別調(diào)用兩個(gè)方法
override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) {
// Student.queryAll()
Student.queryAllStmt()
}
結(jié)果：
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方法進(jìn)行對(duì)比
①.使用后者候引，解析出來(lái)的都是字符串，而使用綁定的方法基本類(lèi)型都可以解析
②.使用后者敦跌，無(wú)法插入二進(jìn)制澄干，因?yàn)椴迦攵M(jìn)制時(shí)，是當(dāng)做字符串進(jìn)行解析的柠傍，而使用綁定的方法麸俘，可以在綁定參數(shù)時(shí)，綁定二進(jìn)制(sqlite3_bind_blob)
