SQLite3学习笔记7：prepare + bind（C API）
核心知识点
- Prepared Statement（预编译语句）是 SQLite C API 的主流用法
把 SQL 编译成sqlite3_stmt*（类似“SQL 句柄”），之后通过bind绑定参数，再step执行，最后finalize释放。 - 相比 sqlite3_exec：更安全、更高性能
bind直接传参数，避免字符串拼 SQL → 防 SQL 注入- 同一条语句可反复执行：
reset + rebind + step，批量写入性能更好
- SELECT 不再依赖 callback
之前用sqlite3_exec的 callback 来逐行处理结果集；
Prepared 模式下：while (sqlite3_step(stmt) == SQLITE_ROW) { sqlite3_column_*... }自己取列值。 - UPDATE / DELETE 依然要带 WHERE
这条规范在 prepared 模式下一样适用，避免全表误操作。
核心函数速查
| 函数 | 核心作用 | 关键点 |
|---|---|---|
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) | 预编译 SQL → 得到sqlite3_stmt* | -1表示 SQL 以\0结尾 |
sqlite3_bind_int/ double/ text/ null(stmt, idx, val, ...) | 绑定参数 | idx从1开始，对应第 1 个? |
sqlite3_step(stmt) | 执行一步 | DML：通常返回SQLITE_DONE；SELECT：每行SQLITE_ROW |
sqlite3_column_int/double/text(stmt, col) | 读取当前行的列值 | col从0开始 |
sqlite3_reset(stmt) | 重置 stmt 以便重复执行 | 常用于批量插入/循环查询 |
sqlite3_clear_bindings(stmt) | 清除已绑定参数 | 可选；有些场景更稳妥 |
sqlite3_finalize(stmt) | 释放 stmt | 必须调用，否则泄漏 |
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;}操作步骤
- 编译：
gcc sqlite3_c_demo4.c-osqlite3_c_demo4-lsqlite3- 运行程序
./sqlite3_c_demo4- 命令行验证：
sqlite3 embedded_db.db"SELECT * FROM device_params;"