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