如何在Swift中执行SQL WHERE IN查询并完成语句参数绑定?
问题分析
- SQL语法错误:
IN关键字后不需要加等号,且必须用括号包裹匹配值列表,无法用单个?占位符直接匹配整个数组 - 参数类型不匹配:
sqlite3_bind_text仅支持绑定单个字符串值,无法直接接收[String]数组作为入参 - 代码笔误:绑定参数时引用了不存在的
terminalNameList变量,应使用传入的list参数 - 关键字冲突:参数名
class是Swift保留关键字,需要用反引号包裹或重命名避免编译报错
解决方案
核心思路是根据数组长度动态生成对应数量的占位符,拼接合法的SQL语句后循环绑定每个数组元素,修正后的完整代码如下:
// 全局定义SQLITE_TRANSIENT,Swift环境下默认未导出该常量 let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self) public func getName(list: [String], `class`: String) -> [String] { var arr : [String] = [] // 数组为空直接返回空结果,避免生成非法SQL guard !list.isEmpty else { return arr } // 1. 动态生成IN子句的占位符,比如数组长度为3则生成 "?,?,?" let inPlaceholders = Array(repeating: "?", count: list.count).joined(separator: ",") let queryStatementString = "SELECT terms FROM termo WHERE terms IN (\(inPlaceholders)) AND class=?" var queryStatement: OpaquePointer? = nil if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK { // 2. 循环绑定list中的每个字符串参数 for (index, item) in list.enumerated() { // SQLite参数索引从1开始 let bindIndex = Int32(index + 1) if sqlite3_bind_text(queryStatement, bindIndex, item, -1, SQLITE_TRANSIENT) != SQLITE_OK { let errmsg = String(cString: sqlite3_errmsg(db)!) print("failure binding list item \(index): \(errmsg)") } } // 3. 绑定class参数,索引为数组长度+1 let classBindIndex = Int32(list.count + 1) if sqlite3_bind_text(queryStatement, classBindIndex, `class`, -1, SQLITE_TRANSIENT) != SQLITE_OK { let errmsg = String(cString: sqlite3_errmsg(db)!) print("failure binding class: \(errmsg)") } // 读取查询结果 while sqlite3_step(queryStatement) == SQLITE_ROW { if let terPtr = sqlite3_column_text(queryStatement, 0) { let ter = String(cString: terPtr) arr.append(ter) } } } else { let errmsg = String(cString: sqlite3_errmsg(db)!) print("SELECT statement could not be prepared: \(errmsg)") } sqlite3_finalize(queryStatement) return arr }
内容的提问来源于stack exchange,提问作者Yatish Agrawal
相关产品推荐
相关产品推荐

