SQLite3学习笔记7：prepare + bind（C API）

核心知识点

1. Prepared Statement（预编译语句）是 SQLite C API 的主流用法
   把 SQL 编译成sqlite3_stmt*（类似“SQL 句柄”），之后通过bind绑定参数，再step执行，最后finalize释放。
2. 相比 sqlite3_exec：更安全、更高性能
   • bind直接传参数，避免字符串拼 SQL → 防 SQL 注入
   • 同一条语句可反复执行：reset + rebind + step，批量写入性能更好
3. SELECT 不再依赖 callback
   之前用sqlite3_exec的 callback 来逐行处理结果集；
   Prepared 模式下：while (sqlite3_step(stmt) == SQLITE_ROW) { sqlite3_column_*... }自己取列值。
4. UPDATE / DELETE 依然要带 WHERE
   这条规范在 prepared 模式下一样适用，避免全表误操作。

核心函数速查

CRUD 模板代码：prepare + bind + step + finalize

1）C（Create / Insert）模板

场景：插入一条数据（或循环插入多条）

// INSERT 模板：插入一条constchar*sql="INSERT INTO device_params (param_name, param_value, update_ts) ""VALUES (?, ?, datetime(CURRENT_TIMESTAMP, '+8 hours'));";sqlite3_stmt*stmt=NULL;rc=sqlite3_prepare_v2(db,sql,-1,&stmt,NULL);if(rc!=SQLITE_OK){fprintf(stderr,"prepare insert failed: %s\n",sqlite3_errmsg(db));return-1;}// 绑定参数（idx 从 1 开始，对应第 1/2 个 ?）sqlite3_bind_text(stmt,1,"temp",-1,SQLITE_TRANSIENT);sqlite3_bind_double(stmt,2,26.5);// 执行：DML 期望 SQLITE_DONErc=sqlite3_step(stmt);if(rc!=SQLITE_DONE){fprintf(stderr,"step insert failed: %s\n",sqlite3_errmsg(db));sqlite3_finalize(stmt);return-1;}sqlite3_finalize(stmt);

循环批量插入的核心差异（复用 stmt）：

// prepare 一次// for (...) { bind -> step -> reset + clear_bindings } -> finalizefor(inti=0;i<N;i++){sqlite3_bind_text(stmt,1,name,-1,SQLITE_TRANSIENT);sqlite3_bind_double(stmt,2,value);rc=sqlite3_step(stmt);if(rc!=SQLITE_DONE){/* error handling */}// 为下一次循环复用 stmt（关键：reset + clear）sqlite3_reset(stmt);sqlite3_clear_bindings(stmt);}

2）R（Read / Select）模板

2.1 查询多行（SELECT * / SELECT 多条）

constchar*sql="SELECT id, param_name, param_value, update_ts FROM device_params;";sqlite3_stmt*stmt=NULL;rc=sqlite3_prepare_v2(db,sql,-1,&stmt,NULL);if(rc!=SQLITE_OK){fprintf(stderr,"prepare select failed: %s\n",sqlite3_errmsg(db));return-1;}// SELECT：step 每次推进一行，返回 SQLITE_ROW 表示“有一行可读”while((rc=sqlite3_step(stmt))==SQLITE_ROW){intid=sqlite3_column_int(stmt,0);constunsignedchar*name=sqlite3_column_text(stmt,1);doublevalue=sqlite3_column_double(stmt,2);constunsignedchar*ts=sqlite3_column_text(stmt,3);printf("id=%d name=%s value=%.2f ts=%s\n",id,name?(constchar*)name:"NULL",value,ts?(constchar*)ts:"NULL");}// 结束时应是 SQLITE_DONE（表示遍历完成）if(rc!=SQLITE_DONE){fprintf(stderr,"step select failed: %s\n",sqlite3_errmsg(db));sqlite3_finalize(stmt);return-1;}sqlite3_finalize(stmt);

2.2 带条件查询（WHERE xxx = ?）

constchar*sql="SELECT param_name, param_value FROM device_params WHERE param_name = ?;";sqlite3_stmt*stmt=NULL;rc=sqlite3_prepare_v2(db,sql,-1,&stmt,NULL);if(rc!=SQLITE_OK){/* handle */}sqlite3_bind_text(stmt,1,"temp",-1,SQLITE_TRANSIENT);while((rc=sqlite3_step(stmt))==SQLITE_ROW){constunsignedchar*name=sqlite3_column_text(stmt,0);doublevalue=sqlite3_column_double(stmt,1);printf("%s %.2f\n",name?(constchar*)name:"NULL",value);}if(rc!=SQLITE_DONE){/* handle */}sqlite3_finalize(stmt);

3）U（Update）模板

关键：UPDATE 一定要写 WHERE（避免全表误更新）

constchar*sql="UPDATE device_params ""SET param_value = ?, update_ts = datetime(CURRENT_TIMESTAMP, '+8 hours') ""WHERE param_name = ?;";sqlite3_stmt*stmt=NULL;rc=sqlite3_prepare_v2(db,sql,-1,&stmt,NULL);if(rc!=SQLITE_OK){/* handle */}// 第1个 ?：新值；第2个 ?：条件sqlite3_bind_double(stmt,1,27.0);sqlite3_bind_text(stmt,2,"temp",-1,SQLITE_TRANSIENT);rc=sqlite3_step(stmt);if(rc!=SQLITE_DONE){fprintf(stderr,"step update failed: %s\n",sqlite3_errmsg(db));sqlite3_finalize(stmt);return-1;}sqlite3_finalize(stmt);// 可选：查看受影响行数// int changed = sqlite3_changes(db);

4）D（Delete）模板

关键：DELETE 一定要写 WHERE（避免全表误删）

constchar*sql="DELETE FROM device_params WHERE param_name = ?;";sqlite3_stmt*stmt=NULL;rc=sqlite3_prepare_v2(db,sql,-1,&stmt,NULL);if(rc!=SQLITE_OK){/* handle */}sqlite3_bind_text(stmt,1,"humidity",-1,SQLITE_TRANSIENT);rc=sqlite3_step(stmt);if(rc!=SQLITE_DONE){fprintf(stderr,"step delete failed: %s\n",sqlite3_errmsg(db));sqlite3_finalize(stmt);return-1;}sqlite3_finalize(stmt);// 可选：int changed = sqlite3_changes(db);

完整 C 代码示例

创建文件：sqlite3_c_demo4.c

#include<stdio.h>#include<stdlib.h>#include<sqlite3.h>// 统一错误打印staticvoidprint_sqlite_error(sqlite3*db,constchar*tag,intrc){fprintf(stderr,"[ERROR] %s failed (rc=%d): %s\n",tag,rc,sqlite3_errmsg(db));}intmain(intargc,char*argv[]){sqlite3*db=NULL;intrc=SQLITE_OK;// 1) 打开数据库rc=sqlite3_open("embedded_db.db",&db);if(rc!=SQLITE_OK){print_sqlite_error(db,"sqlite3_open",rc);sqlite3_close(db);return-1;}printf("[INFO] 数据库打开成功\n");// 2) 建表constchar*create_table_sql="CREATE TABLE IF NOT EXISTS device_params (""id INTEGER PRIMARY KEY AUTOINCREMENT, ""param_name TEXT NOT NULL, ""param_value REAL NOT NULL, ""update_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);";// 建表这类无参数 SQL，用 prepare 也行；这里用 prepare 统一风格sqlite3_stmt*stmt_create=NULL;rc=sqlite3_prepare_v2(db,create_table_sql,-1,&stmt_create,NULL);if(rc!=SQLITE_OK){print_sqlite_error(db,"sqlite3_prepare_v2(create)",rc);sqlite3_close(db);return-1;}rc=sqlite3_step(stmt_create);if(rc!=SQLITE_DONE){print_sqlite_error(db,"sqlite3_step(create)",rc);sqlite3_finalize(stmt_create);sqlite3_close(db);return-1;}sqlite3_finalize(stmt_create);printf("[INFO] 表创建成功（已存在则忽略）\n");// 3) INSERT（带参数绑定）// 对应：INSERT INTO device_params(param_name, param_value, update_ts) VALUES (?, ?, datetime(...));constchar*insert_sql="INSERT INTO device_params (param_name, param_value, update_ts) ""VALUES (?, ?, datetime(CURRENT_TIMESTAMP, '+8 hours'));";sqlite3_stmt*stmt_insert=NULL;rc=sqlite3_prepare_v2(db,insert_sql,-1,&stmt_insert,NULL);if(rc!=SQLITE_OK){print_sqlite_error(db,"sqlite3_prepare_v2(insert)",rc);sqlite3_close(db);return-1;}// 模拟写两条：temp / humiditystruct{constchar*name;doublevalue;}rows[]={{"temp",26.5},{"humidity",61.0}};for(inti=0;i<2;i++){// bind idx 从 1 开始：第1个 ? 是 name，第2个 ? 是 valuesqlite3_bind_text(stmt_insert,1,rows[i].name,-1,SQLITE_TRANSIENT);sqlite3_bind_double(stmt_insert,2,rows[i].value);rc=sqlite3_step(stmt_insert);if(rc!=SQLITE_DONE){print_sqlite_error(db,"sqlite3_step(insert)",rc);sqlite3_finalize(stmt_insert);sqlite3_close(db);return-1;}// 为下一次循环复用 stmt（关键：reset + clear）sqlite3_reset(stmt_insert);sqlite3_clear_bindings(stmt_insert);}sqlite3_finalize(stmt_insert);printf("[INFO] 数据插入成功（temp/humidity）\n");// 4) SELECT（查询所有数据：逐行取列）constchar*select_all_sql="SELECT id, param_name, param_value, update_ts FROM device_params;";sqlite3_stmt*stmt_select_all=NULL;rc=sqlite3_prepare_v2(db,select_all_sql,-1,&stmt_select_all,NULL);if(rc!=SQLITE_OK){print_sqlite_error(db,"sqlite3_prepare_v2(select_all)",rc);sqlite3_close(db);return-1;}printf("[查询所有设备参数]\n");while((rc=sqlite3_step(stmt_select_all))==SQLITE_ROW){intid=sqlite3_column_int(stmt_select_all,0);constunsignedchar*param_name=sqlite3_column_text(stmt_select_all,1);doubleparam_value=sqlite3_column_double(stmt_select_all,2);constunsignedchar*update_ts=sqlite3_column_text(stmt_select_all,3);printf(" id=%d, param_name=%s, param_value=%.2f, update_ts=%s\n",id,param_name?(constchar*)param_name:"NULL",param_value,update_ts?(constchar*)update_ts:"NULL");}if(rc!=SQLITE_DONE){print_sqlite_error(db,"sqlite3_step(select_all)",rc);sqlite3_finalize(stmt_select_all);sqlite3_close(db);return-1;}sqlite3_finalize(stmt_select_all);// 5) 条件 SELECT（按 param_name = ?）constchar*select_by_name_sql="SELECT param_name, param_value FROM device_params WHERE param_name = ?;";sqlite3_stmt*stmt_select_one=NULL;rc=sqlite3_prepare_v2(db,select_by_name_sql,-1,&stmt_select_one,NULL);if(rc!=SQLITE_OK){print_sqlite_error(db,"sqlite3_prepare_v2(select_one)",rc);sqlite3_close(db);return-1;}sqlite3_bind_text(stmt_select_one,1,"temp",-1,SQLITE_TRANSIENT);printf("[查询温度参数]\n");while((rc=sqlite3_step(stmt_select_one))==SQLITE_ROW){constunsignedchar*name=sqlite3_column_text(stmt_select_one,0);doublevalue=sqlite3_column_double(stmt_select_one,1);printf(" param_name=%s, param_value=%.2f\n",name?(constchar*)name:"NULL",value);}if(rc!=SQLITE_DONE){print_sqlite_error(db,"sqlite3_step(select_one)",rc);sqlite3_finalize(stmt_select_one);sqlite3_close(db);return-1;}sqlite3_finalize(stmt_select_one);// 6) UPDATE（把 temp 改成 27.0，WHERE param_name = ?）constchar*update_sql="UPDATE device_params ""SET param_value = ?, update_ts = datetime(CURRENT_TIMESTAMP, '+8 hours') ""WHERE param_name = ?;";sqlite3_stmt*stmt_update=NULL;rc=sqlite3_prepare_v2(db,update_sql,-1,&stmt_update,NULL);if(rc!=SQLITE_OK){print_sqlite_error(db,"sqlite3_prepare_v2(update)",rc);sqlite3_close(db);return-1;}sqlite3_bind_double(stmt_update,1,27.0);sqlite3_bind_text(stmt_update,2,"temp",-1,SQLITE_TRANSIENT);rc=sqlite3_step(stmt_update);if(rc!=SQLITE_DONE){print_sqlite_error(db,"sqlite3_step(update)",rc);sqlite3_finalize(stmt_update);sqlite3_close(db);return-1;}sqlite3_finalize(stmt_update);printf("[INFO] 温度更新成功（temp -> 27.0）\n");// 7) DELETE（删除 humidity，WHERE param_name = ?）constchar*delete_sql="DELETE FROM device_params WHERE param_name = ?;";sqlite3_stmt*stmt_delete=NULL;rc=sqlite3_prepare_v2(db,delete_sql,-1,&stmt_delete,NULL);if(rc!=SQLITE_OK){print_sqlite_error(db,"sqlite3_prepare_v2(delete)",rc);sqlite3_close(db);return-1;}sqlite3_bind_text(stmt_delete,1,"humidity",-1,SQLITE_TRANSIENT);rc=sqlite3_step(stmt_delete);if(rc!=SQLITE_DONE){print_sqlite_error(db,"sqlite3_step(delete)",rc);sqlite3_finalize(stmt_delete);sqlite3_close(db);return-1;}sqlite3_finalize(stmt_delete);printf("[INFO] 湿度数据删除成功（humidity）\n");// 8) 关闭数据库rc=sqlite3_close(db);if(rc!=SQLITE_OK){print_sqlite_error(db,"sqlite3_close",rc);return-1;}printf("[INFO] 数据库关闭成功\n");return0;}

操作步骤

1. 编译：

gcc sqlite3_c_demo4.c-osqlite3_c_demo4-lsqlite3

2. 运行程序

./sqlite3_c_demo4

3. 命令行验证：

sqlite3 embedded_db.db"SELECT * FROM device_params;"