【SQLite3】 3_sql 数据绑定机制 [常用基础]

sql 语句执行流程：分析 - 编译 - 执行

问题

当执行10000次插入操作时，sql 语句需要被“分析 - 编译 - 执行”10000次，这是比较低效的；

• 示例：

char insert_sql[128] = {0};

for (int i=0; i<10000; ++i)
{
    sprintf(insert_sql, "insert into bindtest values(%d, %d)", i, i*100);
    ret = sqlite3_exec(ppdb, insert_sql, NULL, NULL, &errmsg);
    if (ret != SQLITE_OK)
    {
        printf("insert fail\n");
        break;
    }
    sqlite3_free(errmsg);
}

尝试解决

用绑定机制，可以使 sql 语句只被解析一次，之后的操作使用第一次生成的执行计划，以此带来更高的执行效率。

使用步骤:

装备

int sqlite3_prepare(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

绑定数据

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_blob64(sqlite3_stmt*, int, const void*, sqlite3_uint64,
                        void(*)(void*));
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
int sqlite3_bind_text64(sqlite3_stmt*, int, const char*, sqlite3_uint64,
                         void(*)(void*), unsigned char encoding);
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
int sqlite3_bind_pointer(sqlite3_stmt*, int, void*, const char*,void(*)(void*));
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
int sqlite3_bind_zeroblob64(sqlite3_stmt*, int, sqlite3_uint64);

执行

int sqlite3_step(sqlite3_stmt* mStmt);

如果循环操作（绑定）时

int sqlite3_finalize(sqlite3_stmt *pStmt);

释放

sqlite3_finalize(stmt);

• 示例：

sqlite3_stmt *pStmt = NULL;
char insert[] = "insert into bindtest values(?, ?)";

sqlite3_prepare(ppdb, insert, sizeof(insert), &pStmt, NULL);
// 2. 绑定数据
for (int i=0; i<10000; ++i)
{
    sqlite3_bind_int(pStmt, 1, i);          // 注意这里，绑定从 1 开始 ！！！
    sqlite3_bind_int(pStmt, 2, i * 100);
    sqlite3_step(pStmt);    // 执行插入
    sqlite3_reset(pStmt);   // 重置
}
// 3. 释放
sqlite3_finalize(pStmt);

编程实验

#include <stdio.h>
#include <sqlite3.h>
#include <time.h>

int main()
{
    // 1. 打开数据库
    sqlite3 *ppdb = NULL;
    int ret = sqlite3_open(":memory:", &ppdb);
    if (ret != SQLITE_OK)
    {
        printf("open fail\n");
        return -1;
    }

    // 2. 执行 sql 语句
    // 2.1 创建表格 create table apitest(id int, number int);
    const char *create_sql = "create table if not exists bindtest(id int, number int)";
    char *errmsg = NULL;
    ret = sqlite3_exec(ppdb, create_sql, NULL, NULL, &errmsg);
    if (ret != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }
    sqlite3_free(errmsg);

    // 2.2 数据直接插入 ==============================
    struct timeval start;
    struct timeval end;
    char insert_sql[128] = {0};

    mingw_gettimeofday(&start, NULL);
    for (int i=0; i<10000; ++i)
    {
        sprintf(insert_sql, "insert into bindtest values(%d, %d)", i, i*100);
        ret = sqlite3_exec(ppdb, insert_sql, NULL, NULL, &errmsg);
        if (ret != SQLITE_OK)
        {
            printf("insert fail\n");
            break;
        }
        sqlite3_free(errmsg);
    }
    mingw_gettimeofday(&end, NULL);
    printf("nsert directly: %ld\n", end.tv_usec - start.tv_usec);

    // 2.3 数据绑定插入 ==============================
    sqlite3_stmt *pStmt = NULL;
    char insert[] = "insert into bindtest values(?, ?)";

    mingw_gettimeofday(&start, NULL);
    sqlite3_prepare(ppdb, insert, sizeof(insert), &pStmt, NULL);
    for (int i=0; i<10000; ++i)
    {
        sqlite3_bind_int(pStmt, 1, i);
        sqlite3_bind_int(pStmt, 2, i * 100);
        sqlite3_step(pStmt);
        sqlite3_reset(pStmt);
    }
    sqlite3_finalize(pStmt);

    mingw_gettimeofday(&end, NULL);
    printf("bind directly: %ld\n", end.tv_usec - start.tv_usec);

    // 3. 关闭数据库
    ret = sqlite3_close(ppdb);
    if (ret != SQLITE_OK)
    {
        printf("close fail\n");
        return -1;
    }

    return 0;
}

输出：

nsert directly: 59873
bind directly: 27925