使用MySQL Connect/C++的C++银行系统出现1054字段未知错误

问题背景 #

基于MySQL开发入门级C银行系统，使用官方Connect/C库实现数据库交互。编译链接无异常，但执行createCust函数时触发错误：

Unknown column ' ' in 'field list' (MySQL error code: 1054, SQLState: 42S22)

相关函数代码如下：

void createCust(sql::Connection* con)
{
    std::string name;
    std::cout<<"Enter your first name: ";
    std::cin >> name;
    std::string rand = std::to_string((std::rand() % 10000)); //generated a random number to assign acc_no to new customer
    this->account.acc_no = accIsPresent(con, rand); // assign unique account numbers


    // Create a statement
    std::unique_ptr<sql::Statement> stmt(con->createStatement());

    // query1: create a row with the first_name taken as input
    std::ostringstream query1, query2, query3, query4;
    query1 << "INSERT INTO customer (first_name) VALUES ("
        << name << ")";

    // Execute query1
    stmt->executeUpdate(query1.str());

    // query2: fetch 'id' from table customer
    query2 << "SELECT id FROM customer WHERE first_name = ' " << name <<" ' ";

    sql::ResultSet* res = stmt->executeQuery(query2.str());

    // assign customer id to cust_id
    this->cust_id = res->getInt("id");

    // add account for the new customer
    query3 << "INSERT INTO accounts(cust_id, acc_no)" << "VALUES (" << this->cust_id <<", " << this->account.acc_no <<")"; // error will show
    stmt->executeUpdate(query3.str());

    query4 << "UPDATE accounts SET bal = 0, transaction0 = 0, transaction1= 0, transaction2 = 0 WHERE acc_no = '" << this->account.acc_no << "'";
    stmt->executeUpdate(query4.str());

    this->account.balance = 0;
    std::cout << "Account created Successfully!" << std::endl;
    std::cout << "Your Customer ID is " << this->cust_id << " and your Account Number is " << this->account.acc_no << std::endl;
}

错误原因与修复方案 #

1. 字符串值未加单引号导致SQL语法错误 #

query1中直接拼接字符串name到SQL语句，比如输入John时，生成的SQL是：

INSERT INTO customer (first_name) VALUES (John)

MySQL会把John识别为列名而非字符串值，从而抛出Unknown column错误。

修复：必须给字符串值添加单引号，更安全的方式是使用PreparedStatement（见下文）。

2. 查询条件中多余的空格导致匹配失败 #

query2的WHERE条件里，单引号和name之间添加了空格：

query2 << "SELECT id FROM customer WHERE first_name = ' " << name <<" ' ";

生成的SQL会变成WHERE first_name = ' John '，而数据库中存储的是无空格的John，导致查询不到结果，后续访问res->getInt("id")会触发空ResultSet的异常。

修复：去掉单引号内的空格：

query2 << "SELECT id FROM customer WHERE first_name = '" << name << "'";

3. 用PreparedStatement彻底避免语法错误与SQL注入 #

直接拼接字符串不仅容易出现语法错误，还存在SQL注入风险。改用PreparedStatement可以同时解决这两个问题，且代码更健壮。

4. 正确获取自增ID的方式 #

插入customer后，不要通过SELECT查询获取自增的id，而是使用getGeneratedKeys()直接获取插入生成的主键，更高效且避免查询错误。

修复后的完整代码 #

void createCust(sql::Connection* con)
{
    std::string name;
    std::cout << "Enter your first name: ";
    std::cin >> name;
    std::string rand_str = std::to_string((std::rand() % 10000));
    this->account.acc_no = accIsPresent(con, rand_str);

    // 使用PreparedStatement插入customer并获取自增ID
    std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(
        "INSERT INTO customer (first_name) VALUES (?)",
        sql::Statement::RETURN_GENERATED_KEYS
    ));
    pstmt->setString(1, name);
    pstmt->executeUpdate();

    // 获取生成的自增id
    sql::ResultSet* res = pstmt->getGeneratedKeys();
    if (res->next()) {
        this->cust_id = res->getInt(1); // 自增主键是结果集第一列
    }
    delete res; // 释放ResultSet资源

    // 插入accounts记录（合并原INSERT+UPDATE逻辑）
    pstmt.reset(con->prepareStatement(
        "INSERT INTO accounts(cust_id, acc_no, bal, transaction0, transaction1, transaction2) VALUES (?, ?, 0, 0, 0, 0)"
    ));
    pstmt->setInt(1, this->cust_id);
    pstmt->setString(2, this->account.acc_no); // 若acc_no是整数类型，改用setInt
    pstmt->executeUpdate();

    this->account.balance = 0;
    std::cout << "Account created Successfully!" << std::endl;
    std::cout << "Your Customer ID is " << this->cust_id << " and your Account Number is " << this->account.acc_no << std::endl;
}

额外注意事项 #

• 确保customer表的id字段是自增主键（AUTO_INCREMENT），否则getGeneratedKeys()无法获取正确值。
• 使用PreparedStatement时，要根据字段类型调用对应的setXxx方法（字符串用setString，整数用setInt）。
• 手动创建的ResultSet要记得调用delete释放资源，避免内存泄漏。

内容的提问来源于stack exchange，提问作者Kovidh V.S. Bhati