iOS中SQLite多CREATE TABLE语句仅首个执行问题排查

问题本质 #

你的代码中仅第一张表被创建，核心原因是SQLite原生C API的sqlite3_prepare_v2默认只会解析并执行第一条SQL语句，后续的CREATE TABLE语句会被完全忽略。虽然在线SQLite工具支持批量执行多语句，但原生API不会自动处理多语句逻辑，你只调用了一次sqlite3_step，仅完成了第一个表的创建操作。

解决方法 #

方法一：拆分SQL语句，逐个执行（推荐）

将每个建表语句拆分为独立字符串，循环执行每条语句，确保所有表都被创建：

static func CreateDatabase() -> Bool {
    let tableCommands = [
        """
        CREATE TABLE IF NOT EXISTS Conversations(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, LastMsgDate REAL NOT NULL, Name TEXT NULL, AvatarId INTEGER NOT NULL, Unread INTEGER NOT NULL DEFAULT '0', LastMsg TEXT NULL, Flags INTEGER NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
        """,
        """
        CREATE TABLE IF NOT EXISTS Communities(Id INTEGER NOT NULL, UbietyId INTEGER NOT NULL, Name TEXT NOT NULL, Distance REAL NOT NULL, InsertDate REAL NOT NULL, Photo BLOB NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL, PRIMARY KEY (Id, UbietyId) ON CONFLICT REPLACE);
        """,
        """
        CREATE TABLE IF NOT EXISTS Images(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, ExpiryDate REAL NULL, Data BLOB NOT NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
        """
    ]
    
    for cmd in tableCommands {
        var stmt: OpaquePointer?
        guard sqlite3_prepare_v2(db, cmd, -1, &stmt, nil) == SQLITE_OK else {
            print(String(cString: sqlite3_errmsg(db)))
            sqlite3_finalize(stmt)
            return false
        }
        
        guard sqlite3_step(stmt) == SQLITE_DONE else {
            print(String(cString: sqlite3_errmsg(db)))
            sqlite3_finalize(stmt)
            return false
        }
        sqlite3_finalize(stmt)
    }
    return true
}

方法二：使用sqlite3_exec执行多语句

sqlite3_execAPI会自动处理单字符串中的多条SQL语句，适合执行固定的批量建表逻辑：

static func CreateDatabase() -> Bool {
    let cmd = """
    CREATE TABLE IF NOT EXISTS Conversations(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, LastMsgDate REAL NOT NULL, Name TEXT NULL, AvatarId INTEGER NOT NULL, Unread INTEGER NOT NULL DEFAULT '0', LastMsg TEXT NULL, Flags INTEGER NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
    CREATE TABLE IF NOT EXISTS Communities(Id INTEGER NOT NULL, UbietyId INTEGER NOT NULL, Name TEXT NOT NULL, Distance REAL NOT NULL, InsertDate REAL NOT NULL, Photo BLOB NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL, PRIMARY KEY (Id, UbietyId) ON CONFLICT REPLACE);
    CREATE TABLE IF NOT EXISTS Images(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, ExpiryDate REAL NULL, Data BLOB NOT NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
    """
    
    let result = sqlite3_exec(db, cmd, nil, nil, nil)
    guard result == SQLITE_OK else {
        print(String(cString: sqlite3_errmsg(db)))
        return false
    }
    return true
}

注意事项 #

• 优先选择方法一：拆分语句逐个执行更安全，便于单语句调试，也能避免潜在的SQL注入风险（如果后续语句包含动态内容）。
• sqlite3_exec仅适合执行固定无用户输入的SQL，若语句中包含动态参数，必须使用参数化查询，禁止直接拼接字符串。

内容的提问来源于stack exchange，提问作者Ugur