如何在sql查询中传递c++变量

Question

MYSQL* conn;

conn = mysql_init(0);

conn = mysql_real_connect(conn, "localhost", "root", "", "seat_alloc", 0, NULL, 0);


int college_name="abc";

int branch_name="def";

int rank=123;

int qstate4 = mysql_query(conn,("INSERT into allocated_seats (rank,college,branch) values (rank,'" + college_name + "','" + branch_name + "')"  ).c_str());

我想在查询中传递整型变量和字符串变量。字符串变量可以很好地使用。但是列0不是添加到数据库中，而是添加到数据库中。在表格中插入这3个变量的正确方法是什么？

Answer 1

不要使用字符串操作构建sql语句。使用占位符编写语句，准备它们并绑定值。

#include <mysql.h>

#include <cstring>
#include <string>

int main() {
  MYSQL* conn = mysql_init(0);

  conn = mysql_real_connect(conn, "localhost", "root", "", "seat_alloc", 0, NULL, 0);

  std::string college_name = "abc";

  std::string branch_name = "def";

  int rank = 123;

  auto* stmt = mysql_stmt_init(conn);
  std::string query = "INSERT into allocated_seats (rank,college,branch) values (?, ?, ?)";
  mysql_stmt_prepare(stmt, query.c_str(), query.length());
  MYSQL_BIND bind[3];
  memset(bind, 0, sizeof(bind));
  bind[0].buffer_type = MYSQL_TYPE_LONG;
  bind[0].buffer = &rank;
  bind[0].is_null= 0;
  bind[0].length= 0;

  unsigned long str1_length = college_name.length();
  bind[1].buffer_type = MYSQL_TYPE_STRING;
  bind[1].buffer = const_cast<char*>(college_name.c_str());
  bind[1].buffer_length = college_name.length();
  bind[1].is_null= 0;
  bind[1].length = &str1_length;

  unsigned long str2_length = branch_name.length();
  bind[2].buffer_type = MYSQL_TYPE_STRING;
  bind[2].buffer = const_cast<char*>(branch_name.c_str());
  bind[2].buffer_length = branch_name.length();
  bind[2].is_null= 0;
  bind[2].length = &str2_length;

  mysql_stmt_bind_param(stmt, bind);

  mysql_stmt_execute(stmt);

  mysql_stmt_close(stmt);
}

当然，您应该检查所有返回值并添加错误处理。

在这里你可以找到关于这个主题的更多信息：https://dev.mysql.com/doc/refman/5.7/en/c-api-prepared-statements.html