c++ - 如何发送C++和mysql动态mysql查询

Question

使用 Visual Studio、Windows 7 和 mysql.h 库。

我想要做的是发送这样的 MySQL 查询:

mysql_query(conn, "SELECT pass FROM users WHERE name='Leo Tolstoy'");

我唯一无法工作的是发送一个查询，其中名称不是上面所示的常量，而是从文本字段或其他任何内容获取的变量。那么我应该如何使用变量而不是常量呢？

希望我的问题说清楚了。

Answer 1

使用准备好的语句，它可以让您参数化值，类似于函数如何让您在语句 block 中参数化变量。如果使用 MySQL Connector/C++ :

// use std::unique_ptr, boost::shared_ptr, or whatever is most appropriate for RAII
// Connector/C++ requires boost, so 
std::unique_ptr<sql::Connection> db;
std::unique_ptr<sql::PreparedStatement> getPassword
std::unique_ptr<sql::ResultSet> result;
std::string name = "Nikolai Gogol";
std::string password;

...

getPassword = db->prepareStatement("SELECT pass FROM users WHERE name=? LIMIT 1");

getPassword->setString(1, name);
result = getPassword->execute();
if (result->first()) {
    password = result->getString("pass");
} else {
    // no result
    ...
}

// smart pointers will handle deleting the sql::* instances

创建类来处理数据库访问并将其包装在一个方法中，应用程序的其余部分甚至不需要知道正在使用数据库。

如果您出于某种原因确实想使用旧的 C API:

MYSQL *mysql;
...

const my_bool yes=1, no=0;
const char* getPassStmt = "SELECT password FROM users WHERE username=? LIMIT 1";
MYSQL_STMT *getPassword;
MYSQL_BIND getPassParams;
MYSQL_BIND result;

std::string name = "Nikolai Gogol";
std::string password;

if (! (getPassword = mysql_stmt_init(mysql))) {
    // error: couldn't allocate space for statement
    ...
}
if (mysql_stmt_prepare(getPassword, getPassStmt, strlen(getPassStmt))) {
    /* error preparing statement; handle error and 
       return early or throw an exception. RAII would make
       this easier.
    */
    ...
} else {
    unsigned long nameLength = name.size();
    memset(&getPassParams, 0, sizeof(getPassParams));
    getPassParams.buffer_type = MYSQL_TYPE_STRING;
    getPassParams.buffer = (char*) name.c_str();
    getPassParams.length = &nameLength;

    if (mysql_stmt_bind_param(getPassword, &getPassParams)) {
        /* error binding param */
        ...
    } else if (mysql_stmt_execute(getPassword)) {
        /* error executing query */
        ...
    } else {
        // for mysql_stmt_num_rows()
        mysql_stmt_store_result(getPassword);
        if (mysql_stmt_num_rows(getPassword)) {
            unsigned long passwordLength=0;
            memset(&result, 0, sizeof(result));
            result.length = &passwordLength;
            mysql_stmt_bind_result(getPassword, &result);

            mysql_stmt_fetch(getPassword);
            if (passwordLength > 0) {
                result.buffer = new char[passwordLength+1];
                memset(result.buffer, 0, passwordLength+1);
                result.buffer_length = passwordLength+1;
                if (mysql_stmt_fetch_column(getPassword, &result, 0, 0)) {
                    ...
                } else {
                    password = static_cast<const char*>(result.buffer);
                }
            }
        } else {
            // no result
            cerr << "No user '" << name << "' found." << endl;
        }
    }
    mysql_stmt_free_result(getPassword);
}
mysql_stmt_close(getPassword);

mysql_close(mysql);

如您所见，Connector/C++ 更简单。它也不太容易出错；我使用 C API 时犯的错误可能比使用 Connector/C++ 时犯的错误还要多。

另请参阅:

• Developing Database Applications Using MySQL Connector/C++
• Connector C++在 MySQL Forge 维基