插入name10时触发UNIQUE约束失败，求助SQLite问题排查

问题描述 #

创建表结构如下：

CREATE TABLE IF NOT EXISTS image_table ("name" INTEGER PRIMARY KEY, "data" BLOB) WITHOUT ROWID;

随后准备插入语句，通过循环插入主键为name0、name1……name19的记录，但插入到name10时触发UNIQUE约束失败错误——即使主键值明显不重复，且绑定参数时传入的长度正确。后续改用TEXT类型主键后问题依然存在，且将绑定参数的销毁策略从SQLITE_STATIC改为SQLITE_TRANSIENT时，错误会提前到插入name1时触发。

最小复现代码（MRVC） #

//#include "sqlite3.h"
#include <iostream>
#include <string>

int main()
{
    sqlite3* db;

    if (sqlite3_open("D:/images.db", &db) != SQLITE_OK)
        return 0;

    /* CREATE SQL STATEMENT */
    const char statement[] = R"(
CREATE TABLE IF NOT EXISTS image_table ("name" TEXT PRIMARY KEY, "data" BLOB) WITHOUT ROWID;
)";

    constexpr auto sizeofstatement = sizeof(statement);
    sqlite3_stmt* create_table_statement;
    int result = sqlite3_prepare_v2(db, statement, sizeof(statement), &create_table_statement, nullptr);
    if (result != SQLITE_OK) std::cout << "Failed to compile SQL statement";

    /* EXECUTE STATEMENT, CREATE TABLE */
    result = sqlite3_step(create_table_statement);

    if (!(result == SQLITE_OK || result == SQLITE_DONE))
    {
        std::cout << sqlite3_errmsg(db);
        return 0;
    }

    sqlite3_finalize(create_table_statement);

    const char insert_statement[] = R"(
        INSERT INTO image_table (name, data) VALUES (?1, ?2);
)";
    sqlite3_stmt* insert_row_statement;
    result = sqlite3_prepare_v2(db, insert_statement, sizeof(insert_statement), &insert_row_statement, nullptr);
    if (result != SQLITE_OK)
    {
        std::cout << sqlite3_errmsg(db);
        return 0;
        int a = 3;
    }

    const char blob_data[] = "This is blob data yada yada yada";

    for (int i = 0; i < 20; ++i)
    {
        std::string key_name = std::string("name").append(std::to_string(i)).c_str();
        auto key_size = key_name.size();
        result = sqlite3_bind_text(insert_row_statement, 1, key_name.data(), key_name.size(), SQLITE_STATIC);// 若改为SQLITE_TRANSIENT，错误会在i=1（key为"name1"）时触发
        result = sqlite3_bind_blob(insert_row_statement, 2, blob_data, sizeof(blob_data), SQLITE_STATIC);
        result = sqlite3_step(insert_row_statement);

        if (!(result == SQLITE_OK || result == SQLITE_DONE))
        {// 插入"name10"时触发UNIQUE约束错误
            std::cout << "Error occurred, key_name: " << key_name << "\n";
            std::cout << sqlite3_errmsg(db) << "\n";
        }
    }

    sqlite3_finalize(insert_row_statement);
}

问题根源 #

异常并非SQLite特性问题，而是代码中的两个错误导致：

1. 悬空指针问题：
   代码中std::string key_name = std::string("name").append(std::to_string(i)).c_str();这一行存在错误——c_str()返回的是临时std::string对象的底层指针，临时对象在赋值语句结束后会被销毁，导致key_name持有悬空指针。后续使用key_name.data()时，内存内容未定义，可能和之前的主键值重复，触发UNIQUE约束。
2. 未重置语句绑定状态：
   循环中重复使用同一个sqlite3_stmt*对象，但每次执行sqlite3_step后未调用sqlite3_reset重置语句的绑定状态，导致后续绑定操作可能无法正确覆盖之前的参数值，造成主键重复的假象。

修复方案 #

1. 修正字符串初始化：
   直接拼接字符串，避免悬空指针：

   std::string key_name = "name" + std::to_string(i);
   

2. 循环内重置语句：
   在sqlite3_step执行完成后调用sqlite3_reset，确保下一次循环可以重新绑定参数：

   result = sqlite3_step(insert_row_statement);
   // 错误处理逻辑...
   sqlite3_reset(insert_row_statement);
   

3. 调整绑定策略（可选）：
   修复字符串问题后，SQLITE_STATIC或SQLITE_TRANSIENT都可以正常使用；若使用SQLITE_TRANSIENT，SQLite会主动复制参数内容，进一步避免内存问题。

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