iOS Sqlite3

发布于：2025-08-16 ⋅ 阅读:(18) ⋅ 点赞:(0)

一、DDL（Data Definition Language，数据定义语言）​​

​用途​：定义或修改数据库结构（如表、索引、视图等），不涉及具体数据操作。
​核心命令及示例​：

  1. ​**CREATE**​：创建数据库对象 
    CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);
  2. ​**ALTER**​：修改表结构 
    ALTER TABLE students ADD COLUMN email VARCHAR(100);  -- 添加列
  3. ​**DROP**​：删除对象 
    DROP TABLE students;  -- 删除表
  4. ​**TRUNCATE**​：清空表数据（保留结构） 
    TRUNCATE TABLE students;  -- 快速清空数据，不可回滚
  5. ​**RENAME**​：重命名对象 
    RENAME TABLE students TO pupils;  -- 重命名表

二、DML（Data Manipulation Language，数据操作语言）​​

​用途​：对表中的数据进行增删改查操作。
​核心命令及示例​：

  1. ​**INSERT**​：插入数据 
    INSERT INTO students (id, name, age) VALUES (1, 'Alice', 20);
  2. ​**UPDATE**​：更新数据 
    UPDATE students SET age = 21 WHERE id = 1;  -- 修改年龄
  3. ​**DELETE**​：删除数据 
    DELETE FROM students WHERE id = 1;  -- 删除指定行
  4. ​**SELECT**​：查询数据（部分分类中归为 DQL） 
    SELECT * FROM students WHERE age > 18;  -- 查询条件数据


 三：SQL语句 sqlite_exec 和 sqlite_prepare_v2的用法和区别

  • sqlite3_exec 的使用场景，适用于：
    • 简单的SQL语句
    • 不需要参数绑定
    • 一次性执行
    • 不需要处理返回结果的详细信息
    • 不能防止SQL注入
      • `sqlite_exec`函数在执行SQL语句时，直接将传入的SQL字符串发送给数据库引擎执行。如果这个SQL字符串包含了用户输入的数据，并且没有经过适当的验证和转义处理，就可能导致SQL注入问题。
  • 典型用例：
// 函数签名
int sqlite3_exec(
    sqlite3*,                     // 数据库连接
    const char *sql,              // SQL语句
    int (*callback)(void*,int,char**,char**),  // 回调函数
    void *,                       // 回调函数的第一个参数
    char **errmsg                 // 错误信息
);



// 1. 创建表
const char *createTableSQL = "CREATE TABLE IF NOT EXISTS users ("
                             "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                             "name TEXT NOT NULL, "
                             "age INTEGER)";

int result = sqlite3_exec(db, createTableSQL, NULL, NULL, NULL);
if (result != SQLITE_OK) {
    NSLog(@"创建表失败: %s", sqlite3_errmsg(db));
}
  • sqlite3_prepare_v2 的使用场景，适用于：
    • 需要参数绑定
    • 复杂查询
    • 需要遍历结果集
    • 重复执行的语句
    • 需要防止SQL注入
      • 参数化查询和预处理语句可以防止SQL注入的原因在于，它们将SQL代码和用户输入严格分开。这样，用户输入的数据被视为纯粹的数据，而不是SQL代码的一部分。数据库引擎会将参数化查询中的参数作为数据处理，而不是代码执行。
  • 典型用例：
// 函数签名
int sqlite3_prepare_v2(
    sqlite3 *db,              // 数据库连接
    const char *zSql,         // SQL语句
    int nByte,                // SQL长度
    sqlite3_stmt **ppStmt,    // 编译后的语句
    const char **pzTail       // 未处理的SQL部分
);


// 1. 带参数的插入
- (BOOL)insertUserWithName:(NSString *)name age:(int)age {
    const char *sql = "INSERT INTO users (name, age) VALUES (?, ?)";
    sqlite3_stmt *stmt;
    
    // 准备语句
    int result = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"准备语句失败: %s", sqlite3_errmsg(db));
        return NO;
    }
    
    // 绑定参数
    sqlite3_bind_text(stmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 2, age);
    
    // 执行
    result = sqlite3_step(stmt);
    
    // 清理
    sqlite3_finalize(stmt);
    
    return (result == SQLITE_DONE);
}

// 2. 查询并返回结果
- (NSArray *)getAllUsers {
    const char *sql = "SELECT id, name, age FROM users";
    sqlite3_stmt *stmt;
    NSMutableArray *users = [NSMutableArray array];
    
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
        
        // 遍历结果集
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int userId = sqlite3_column_int(stmt, 0);
            const char *userName = (const char *)sqlite3_column_text(stmt, 1);
            int userAge = sqlite3_column_int(stmt, 2);
            
            NSDictionary *user = @{
                @"id": @(userId),
                @"name": [NSString stringWithUTF8String:userName ?: ""],
                @"age": @(userAge)
            };
            [users addObject:user];
        }
    }
    
    sqlite3_finalize(stmt);
    return users;
}

// 3. 条件查询
- (NSArray *)getUsersOlderThan:(int)minAge {
    const char *sql = "SELECT * FROM users WHERE age > ?";
    sqlite3_stmt *stmt;
    NSMutableArray *users = [NSMutableArray array];
    
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
        sqlite3_bind_int(stmt, 1, minAge);
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 处理结果...
        }
    }
    
    sqlite3_finalize(stmt);
    return users;
}

四：sqlite3_prepare_v2 的核心作用

// sqlite3_prepare_v2 做了什么：
// 1. 词法分析：将 SQL 字符串分解为 tokens
// 2. 语法分析：检查 SQL 语法是否正确
// 3. 语义分析：检查表名、列名是否存在
// 4. 生成执行计划：优化查询路径
// 5. 编译成字节码：生成 SQLite 虚拟机可执行的指令

const char *sql = "SELECT * FROM users WHERE age > ?";
sqlite3_stmt *stmt;

// 这一步就完成了 SQL 的完整解析和编译
int result = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);


// 编译后的 stmt 包含：
// ✅ 解析好的 SQL 结构
// ✅ 优化后的执行计划  
// ✅ 字节码指令
// ✅ 参数占位符信息

// 这就是为什么可以重复使用的原因
// === 每次都用 sqlite3_exec (低效) ===
for (int i = 0; i < 1000; i++) {
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO users (name, age) VALUES ('%@', %d)", names[i], ages[i]];
    sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
    
    // 每次执行都要：
    // 1. 解析 SQL 语法
    // 2. 检查表结构
    // 3. 生成执行计划
    // 4. 执行
    // 总共 1000 次解析！
}

// === 使用 sqlite3_prepare_v2 (高效) ===
const char *sql = "INSERT INTO users (name, age) VALUES (?, ?)";
sqlite3_stmt *stmt;

// 只解析一次！
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

for (int i = 0; i < 1000; i++) {
    // 绑定参数
    sqlite3_bind_text(stmt, 1, [names[i] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 2, ages[i]);
    
    // 直接执行（跳过解析步骤）
    sqlite3_step(stmt);
    
    // 重置以便下次使用
    sqlite3_reset(stmt);
}

sqlite3_finalize(stmt);  // 释放资源
4.1 sqlite3_stmt
// stmt 实际上包含：
typedef struct sqlite3_stmt {
    // 1. 编译后的字节码
    VdbeOp *aOp;           // 虚拟机指令数组
    
    // 2. 参数信息
    Mem *aVar;             // 绑定参数的存储
    int nVar;              // 参数数量
    
    // 3. 执行计划
    Parse *pParse;         // 解析树
    
    // 4. 结果集信息
    int nResColumn;        // 结果列数
    ColCache *aCol;        // 列信息缓存
    
    // 5. 执行状态
    int pc;                // 程序计数器
    int rc;                // 最后的返回码
    
    // ... 还有很多其他信息
} sqlite3_stmt;
4.2 重复使用的机制
  • 完整的重复使用示例：
  • - (void)demonstrateStatementReuse {
    const char *sql = "UPDATE users SET last_login = ? WHERE id = ?";
    sqlite3_stmt *stmt;
    
    // 一次编译
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
        
        NSArray *userIds = @[@1, @2, @3, @4, @5];
        NSDate *now = [NSDate date];
        
        for (NSNumber *userId in userIds) {
            // 1. 绑定新参数
            sqlite3_bind_int64(stmt, 1, [now timeIntervalSince1970]);
            sqlite3_bind_int(stmt, 2, [userId intValue]);
            
            // 2. 执行（使用已编译的字节码）
            int result = sqlite3_step(stmt);
            
            if (result == SQLITE_DONE) {
                NSLog(@"User %@ updated", userId);
            }
            
            // 3. 重置语句状态（但保留编译结果）
            sqlite3_reset(stmt);
            
            // 4. 清除绑定的参数值
            sqlite3_clear_bindings(stmt);
        }
        
        // 5. 最后释放编译结果
        sqlite3_finalize(stmt);
    }
}
    4.3 sqlite3_reset的作用
  • // sqlite3_reset 做什么：
// ✅ 重置执行状态（程序计数器归零）
// ✅ 清除执行过程中的临时数据
// ❌ 不清除绑定的参数值
// ❌ 不释放编译结果

// sqlite3_clear_bindings 做什么：
// ✅ 清除所有绑定的参数值
// ❌ 不影响编译结果

// sqlite3_finalize 做什么：
// ✅ 释放所有资源
// ✅ 销毁编译结果
// ❌ stmt 不能再使用
    4.4 最佳实践
  • @interface SQLiteManager : NSObject
@property (nonatomic, strong) NSMutableDictionary *preparedStatements;
@end

@implementation SQLiteManager

- (sqlite3_stmt *)preparedStatementForSQL:(NSString *)sql {
    sqlite3_stmt *stmt = [self.preparedStatements[sql] pointerValue];
    
    if (!stmt) {
        // 首次使用，编译并缓存
        if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK) {
            self.preparedStatements[sql] = [NSValue valueWithPointer:stmt];
        }
    }
    
    return stmt;
}

- (void)dealloc {
    // 释放所有缓存的语句
    for (NSValue *value in self.preparedStatements.allValues) {
        sqlite3_stmt *stmt = [value pointerValue];
        sqlite3_finalize(stmt);
    }
}

@end

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布