C++操作SQLite3插入报column index out of range错误排查

报错error 25 : column index out of range的核心原因是你把SQL语句字符串query和预编译语句指针pStmt定义为类的共享成员变量，跨函数调用时被意外篡改：

1. 进入createObject函数时，你先给成员变量query赋值了带6个占位符的INSERT语句
2. 紧接着调用getColorByLabel查询颜色ID，这个函数内部直接把共享的query成员改成了只有1个占位符的SELECT查询语句
3. 等getColorByLabel执行完返回createObject时，query已经被覆盖成SELECT语句，后续sqlite3_prepare_v2实际预编译的是只有1个占位符的SELECT语句，完全不是你预期的INSERT语句
4. 之后你尝试给这个只有1个占位符的语句绑定索引为2的参数，直接触发索引越界的25号错误。

另外代码还有两个隐藏bug：

• getColorByLabel函数里的colorId是未初始化的局部变量，如果查询执行失败、或者没有匹配到对应颜色的行，会返回栈上的随机垃圾值，引发不可预期的逻辑错误
• 当colorIndex == -1时，你跳过了第2个占位符的绑定，虽然SQLite会默认给未绑定的参数赋NULL，但显式处理逻辑更清晰，避免后续维护踩坑

1. 移除类定义里的两个共享成员，不要跨函数复用语句变量：

// 删除类中这两个成员定义
// std::string query;
// sqlite3_stmt *pStmt; 

2. 修正getColorByLabel函数，使用局部变量存储语句和预编译指针，初始化返回值默认值：

int getColorByLabel(std::string sColor)
{
    int colorId = -1; // 初始化默认值，匹配上层判断逻辑
    const std::string query = "SELECT id FROM color WHERE label = (?)";
    sqlite3_stmt *pStmt = nullptr;
    int rc;
    rc = sqlite3_prepare_v2(db, query.c_str(), -1, &pStmt, NULL);
    if (rc != SQLITE_OK)
    {
        std::cout << "prepare getColorByLabel didn t went through" << std::endl;
        manageSQLiteErrors(pStmt);
        return colorId;
    }

    if (sqlite3_bind_text(pStmt, 1, sColor.c_str(), -1, NULL) != SQLITE_OK)
    {
        std::cout << "bind color label didn t went through" << std::endl;
        manageSQLiteErrors(pStmt);
        return colorId;
    }

    while ((rc = sqlite3_step(pStmt)) == SQLITE_ROW)
    {
        colorId = sqlite3_column_int(pStmt, 0);
    }
    sqlite3_finalize(pStmt);
    return colorId;
}

3. 修正createObject函数，使用局部变量存储语句和预编译指针，显式处理颜色字段为空的场景：

void createObject(Object object)
{
    const std::string query = "INSERT INTO object (label,color_id,position_x,position_y,position_z,distance) VALUES (?,?,?,?,?,?)";
    sqlite3_stmt *pStmt = nullptr;
    int rc;
    int colorIndex = -1;
    if (!object.color.empty() && object.color != "0")
    {
        colorIndex = getColorByLabel(object.color); 
    }
    rc = sqlite3_prepare_v2(db, query.c_str(), -1, &pStmt, NULL);
    if (rc != SQLITE_OK)
    {
        std::cout << "prepare createObject didn t went through" << std::endl;
        manageSQLiteErrors(pStmt);
        return ;
    }

    if (sqlite3_bind_text(pStmt, 1, object.label.c_str(), -1, NULL) != SQLITE_OK){
        std::cout << "bind object label didn t went through" << std::endl;
        manageSQLiteErrors(pStmt);
        return ;
    }

    // 无论colorIndex是否有效都显式绑定，避免漏绑
    if (colorIndex != -1){
        rc = sqlite3_bind_int(pStmt, 2, colorIndex);
    } else {
        rc = sqlite3_bind_null(pStmt, 2);
    }
    if ( rc != SQLITE_OK){
        std::cout << "bind object color index didn t went through" << std::endl;
        std::cout << std::to_string(colorIndex) << std::endl;
        manageSQLiteErrors(pStmt);
        return ;
    }

    if (sqlite3_bind_double(pStmt, 3, object.pos_x) != SQLITE_OK){
        std::cout << "bind object pos x didn t went through" << std::endl;
        manageSQLiteErrors(pStmt);
        return ;
    }
    if (sqlite3_bind_double(pStmt, 4, object.pos_y) != SQLITE_OK){
        std::cout << "bind person object y didn t went through" << std::endl;
        manageSQLiteErrors(pStmt);
        return ;
    }
    if (sqlite3_bind_double(pStmt, 5, object.pos_z) != SQLITE_OK){
        std::cout << "bind person object z didn t went through" << std::endl;
        manageSQLiteErrors(pStmt);
        return ;
    }
    if (sqlite3_bind_double(pStmt, 6, object.distance) != SQLITE_OK){
        std::cout << "bind person object distance didn t went through" << std::endl;
        manageSQLiteErrors(pStmt);
        return ;
    }

    if ((rc = sqlite3_step(pStmt)) != SQLITE_DONE) {
        std::cout << "step didn t went through" << std::endl;
        manageSQLiteErrors(pStmt);
        return ;
    }
    sqlite3_finalize(pStmt);
}

修复后重新编译即可正常插入数据，不会再触发25号索引越界错误。

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