核心知识点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_DONESELECT每行SQLITE_ROWsqlite3_column_int/double/text(stmt, col)读取当前行的列值col从0开始sqlite3_reset(stmt)重置 stmt 以便重复执行常用于批量插入/循环查询sqlite3_clear_bindings(stmt)清除已绑定参数可选有些场景更稳妥sqlite3_finalize(stmt)释放 stmt必须调用否则泄漏CRUD 模板代码prepare bind step finalize1CCreate / Insert模板场景插入一条数据或循环插入多条// INSERT 模板插入一条constchar*sqlINSERT INTO device_params (param_name, param_value, update_ts) VALUES (?, ?, datetime(CURRENT_TIMESTAMP, 8 hours));;sqlite3_stmt*stmtNULL;rcsqlite3_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_DONErcsqlite3_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(inti0;iN;i){sqlite3_bind_text(stmt,1,name,-1,SQLITE_TRANSIENT);sqlite3_bind_double(stmt,2,value);rcsqlite3_step(stmt);if(rc!SQLITE_DONE){/* error handling */}// 为下一次循环复用 stmt关键reset clearsqlite3_reset(stmt);sqlite3_clear_bindings(stmt);}2RRead / Select模板2.1 查询多行SELECT * / SELECT 多条constchar*sqlSELECT id, param_name, param_value, update_ts FROM device_params;;sqlite3_stmt*stmtNULL;rcsqlite3_prepare_v2(db,sql,-1,stmt,NULL);if(rc!SQLITE_OK){fprintf(stderr,prepare select failed: %s\n,sqlite3_errmsg(db));return-1;}// SELECTstep 每次推进一行返回 SQLITE_ROW 表示“有一行可读”while((rcsqlite3_step(stmt))SQLITE_ROW){intidsqlite3_column_int(stmt,0);constunsignedchar*namesqlite3_column_text(stmt,1);doublevaluesqlite3_column_double(stmt,2);constunsignedchar*tssqlite3_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*sqlSELECT param_name, param_value FROM device_params WHERE param_name ?;;sqlite3_stmt*stmtNULL;rcsqlite3_prepare_v2(db,sql,-1,stmt,NULL);if(rc!SQLITE_OK){/* handle */}sqlite3_bind_text(stmt,1,temp,-1,SQLITE_TRANSIENT);while((rcsqlite3_step(stmt))SQLITE_ROW){constunsignedchar*namesqlite3_column_text(stmt,0);doublevaluesqlite3_column_double(stmt,1);printf(%s %.2f\n,name?(constchar*)name:NULL,value);}if(rc!SQLITE_DONE){/* handle */}sqlite3_finalize(stmt);3UUpdate模板关键UPDATE 一定要写 WHERE避免全表误更新constchar*sqlUPDATE device_params SET param_value ?, update_ts datetime(CURRENT_TIMESTAMP, 8 hours) WHERE param_name ?;;sqlite3_stmt*stmtNULL;rcsqlite3_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);rcsqlite3_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);4DDelete模板关键DELETE 一定要写 WHERE避免全表误删constchar*sqlDELETE FROM device_params WHERE param_name ?;;sqlite3_stmt*stmtNULL;rcsqlite3_prepare_v2(db,sql,-1,stmt,NULL);if(rc!SQLITE_OK){/* handle */}sqlite3_bind_text(stmt,1,humidity,-1,SQLITE_TRANSIENT);rcsqlite3_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#includestdio.h#includestdlib.h#includesqlite3.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*dbNULL;intrcSQLITE_OK;// 1) 打开数据库rcsqlite3_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_sqlCREATE 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_createNULL;rcsqlite3_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;}rcsqlite3_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_sqlINSERT INTO device_params (param_name, param_value, update_ts) VALUES (?, ?, datetime(CURRENT_TIMESTAMP, 8 hours));;sqlite3_stmt*stmt_insertNULL;rcsqlite3_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(inti0;i2;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);rcsqlite3_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 clearsqlite3_reset(stmt_insert);sqlite3_clear_bindings(stmt_insert);}sqlite3_finalize(stmt_insert);printf([INFO] 数据插入成功temp/humidity\n);// 4) SELECT查询所有数据逐行取列constchar*select_all_sqlSELECT id, param_name, param_value, update_ts FROM device_params;;sqlite3_stmt*stmt_select_allNULL;rcsqlite3_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((rcsqlite3_step(stmt_select_all))SQLITE_ROW){intidsqlite3_column_int(stmt_select_all,0);constunsignedchar*param_namesqlite3_column_text(stmt_select_all,1);doubleparam_valuesqlite3_column_double(stmt_select_all,2);constunsignedchar*update_tssqlite3_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_sqlSELECT param_name, param_value FROM device_params WHERE param_name ?;;sqlite3_stmt*stmt_select_oneNULL;rcsqlite3_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((rcsqlite3_step(stmt_select_one))SQLITE_ROW){constunsignedchar*namesqlite3_column_text(stmt_select_one,0);doublevaluesqlite3_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.0WHERE param_name ?constchar*update_sqlUPDATE device_params SET param_value ?, update_ts datetime(CURRENT_TIMESTAMP, 8 hours) WHERE param_name ?;;sqlite3_stmt*stmt_updateNULL;rcsqlite3_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);rcsqlite3_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删除 humidityWHERE param_name ?constchar*delete_sqlDELETE FROM device_params WHERE param_name ?;;sqlite3_stmt*stmt_deleteNULL;rcsqlite3_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);rcsqlite3_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) 关闭数据库rcsqlite3_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.dbSELECT * FROM device_params;