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: 59873bind directly: 27925