C语言编写MySQL程序时批量删除多条记录的高效方法咨询

原方案的性能问题 #

你当前的循环单条执行DELETE的方式，存在三类额外开销：

• 每次执行都要发起一次客户端到MySQL服务端的网络交互
• 每条SQL都要经历MySQL的语法解析、执行计划生成流程
• 默认自动提交事务的场景下，每条DELETE都会触发一次事务落盘，IO开销极高
  300条记录的场景下，这类重复开销会占到总耗时的90%以上。

最优方案：IN语句批量删除 #

只需要执行1次SQL请求即可完成所有匹配记录的删除，性能提升最明显，适合ID是数字类型、不存在注入风险的场景：

// 注意原代码的unit32_t为拼写错误，正确为uint32_t
uint32_t id_nos[100];
// 缓冲区放大，避免SQL过长溢出，100个数字ID的总长度不会超过1024
char query[1024];
int offset = 0;

// 拼接SQL前缀
offset += sprintf(query + offset, "DELETE FROM Students WHERE id IN (");
for(int i = 0; i < 100; i++) {
    // 非第一个ID前拼接逗号
    if(i > 0) offset += sprintf(query + offset, ",");
    // 数字类型ID不需要加单引号包裹
    offset += sprintf(query + offset, "%u", id_nos[i]);
}
offset += sprintf(query + offset, ")");

// 单次执行即可
if(mysql_query(con, query)) {
    printf("Failed to delete records\n");
    printf("Error: %s\n", mysql_error(con));
    mysql_close(con);
    return;
} else {
    // 输出实际删除的记录数
    printf("%lld records deleted successfully\n", (long long)mysql_affected_rows(con));
}

注意事项 #

• 如果需要删除的ID数量超过1000条，可以拆分批次，比如每200个ID执行一次IN查询，避免SQL长度超过MySQL的max_allowed_packet配置限制
• 如果ID是字符串类型，必须先调用mysql_real_escape_string对ID做转义再拼接，避免SQL注入风险

次优方案：预处理语句批量执行 #

如果你需要更高的安全性、或者ID是字符串类型，推荐使用预处理语句实现，SQL只需要解析一次，也不需要手动处理转义：

1. 预编译SQL模板：DELETE FROM Students WHERE id = ?
2. 循环绑定每个ID参数、执行语句
3. 所有ID处理完成后统一提交事务
   该方案性能比IN批量删除略低，但远高于循环单条执行自动提交的方案。

临时优化方案（不改SQL结构的情况） #

如果暂时不想改拼接逻辑，只需要在循环前开启事务，所有循环执行完成后统一提交，性能也能提升5~10倍：

// 开启事务
mysql_query(con, "START TRANSACTION");
for(int i=0; i< 100; i++) {
    // 原有执行逻辑不变
}
// 全部执行完成后提交
mysql_query(con, "COMMIT");

内容的提问来源于stack exchange，提问作者user174w