MySQL++入门与高级用法详解

4星 · 超过85%的资源需积分: 10 65 浏览量更新于2024-08-02 1 收藏 353KB PDF 举报

MySQL++ v3.0.9 使用手册是一本针对MySQL数据库操作的高级教程和参考文献，由Kevin Atkinson、Sinisa Milivojevic、Monty Widenius和Warren Young共同编著。该手册旨在帮助开发者更高效地利用MySQL++库进行数据库交互，提供了详细的接口介绍、示例代码和功能指南。 1. **历史与入门**：章节1介绍了MySQL++的历史，包括其起源和发展过程。如果你在使用过程中遇到问题，该部分会提供联系方式和寻求帮助的途径。 2. **核心概念**： - **连接对象（The Connection Object）**：这部分详细讲解如何创建和管理与MySQL服务器的连接，包括连接的建立、配置选项设置和关闭。 - **查询对象（The Query Object）**：阐述了如何构建SQL语句，并解释了如何执行查询和处理结果集。 - **结果集（ResultSets）**：介绍了如何遍历查询结果，包括处理分页、排序和结果的条件性获取。 - **异常处理（Exceptions）**：讨论了如何捕获和处理在执行过程中可能出现的错误或异常情况。 3. **逐步教程**： - **运行示例**：提供了一系列逐步的实例，从简单的查询到复杂的事务操作，以帮助新手上手。 - **类型转换与NULL值处理**：讲解了如何在C++中映射SQL列类型，以及如何正确处理NULL值。 - **事务支持**：展示了如何使用MySQL++进行原子性操作，确保数据一致性。 - **选择合适的查询类型**：讨论了不同场景下选择不同类型的查询（如SELECT、INSERT等）的最佳实践。 - **高级处理技巧**：包括条件性处理结果行、在遍历结果时执行代码、连接选项的设置等。 4. **模板查询**： - **设置模板查询**：介绍如何预先定义可复用的查询结构，以便动态传递参数。 - **参数化执行**：详细解释如何在执行时设置查询参数，避免SQL注入攻击。 - **默认参数**：探讨如何设置默认值，提高查询效率和代码简洁性。 - **错误处理与调试**：提供了一套有效的错误处理机制，帮助用户解决查询执行过程中的问题。 MySQL++ v3.0.9 使用手册是一本深入浅出的指南，覆盖了从基础连接设置到高级编程技巧的所有关键知识点，是任何MySQL++开发者不可或缺的参考资料。通过学习和实践手册中的内容，你将能更熟练地运用MySQL++进行数据库操作和应用程序开发。

mysqlpp::Null<unsigned char, mysqlpp::NullIsZero> myfield(mysqlpp::null);

cout << myfield << endl;

cout << int(myfield) << endl;

This will print “0” twice. If you had used the default for the second Null template parameter, the ﬁrst output statement

would have printed “(NULL)”, and the second wouldn’t even compile.

3.8. Using Transactions

The Transaction class makes it easier to use SQL transactions in an exception-safe manner. Normally you create the

Transaction object on the stack before you issue the queries in your transaction set. Then, when all the queries in

the transaction set have been issued, you call Transaction::commit(), which commits the transaction set. If

the Transaction object goes out of scope before you call commit(), the transaction set is rolled back. This ensures

that if some code throws an exception after the transaction is started but before it is committed, the transaction isn’t

left unresolved.

examples/transaction.cpp illustrates this:

#include "cmdline.h"

#include "printdata.h"

#include "stock.h"

#include <iostream>

using namespace std;

int

main(int argc, char *argv[])

{

// Get database access parameters from command line

const char* db = 0, *server = 0, *user = 0, *pass = "";

if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {

return 1;

}

try {

// Establish the connection to the database server.

mysqlpp::Connection con(db, server, user, pass);

// Show initial state

mysqlpp::Query query = con.query();

cout << "Initial state of stock table:" << endl;

print_stock_table(query);

// Insert a few rows in a single transaction set

{

mysqlpp::Transaction trans(con);

stock row("Sauerkraut", 42, 1.2, 0.75,

mysqlpp::sql_date("2006-03-06"), mysqlpp::null);

query.insert(row);

query.execute();

cout << "\nRow inserted, but not committed." << endl;

cout << "Verify this with another program (e.g. simple1), "

"then hit Enter." << endl;

getchar();

cout << "\nCommitting transaction gives us:" << endl;

trans.commit();

MySQL++ v3.0.9 User Manual

print_stock_table(query);

}

// Now let's test auto-rollback

{

mysqlpp::Transaction trans(con);

cout << "\nNow adding catsup to the database..." << endl;

stock row("Catsup", 3, 3.9, 2.99,

mysqlpp::sql_date("2006-03-06"), mysqlpp::null);

query.insert(row);

query.execute();

}

cout << "\nNo, yuck! We don't like catsup. Rolling it back:" <<

endl;

print_stock_table(query);

}

catch (const mysqlpp::BadQuery& er) {

// Handle any query errors

cerr << "Query error: " << er.what() << endl;

return -1;

}

catch (const mysqlpp::BadConversion& er) {

// Handle bad conversions

cerr << "Conversion error: " << er.what() << endl <<

"\tretrieved data size: " << er.retrieved <<

", actual size: " << er.actual_size << endl;

return -1;

}

catch (const mysqlpp::Exception& er) {

// Catch-all for any other MySQL++ exceptions

cerr << "Error: " << er.what() << endl;

return -1;

}

return 0;

}

One of the downsides of transactions is that the locking it requires in the database server is prone to deadlocks. The

classic case where this happens is when two programs both want access to the same two rows within a single transaction

each, but they modify them in opposite orders. If the timing is such that the programs interleave their lock acquisitions,

the two come to an impasse: neither can get access to the other row they want to modify until the other program commits

its transaction and thus release the row locks, but neither can ﬁnish the transaction because they’re waiting on row

locks the database server is holding on behalf of the other program.

The MySQL server is smart enough to detect this condition, but the best it can do is abort the second transaction. This

breaks the impasse, allowing the ﬁrst program to complete its transaction.

The second program now has to deal with the fact that its transaction just got aborted. There’s a subtlety in detecting

this situation when using MySQL++. By default, MySQL++ signals errors like these with exceptions. In the exception

handler, you might expect to get ER_LOCK_DEADLOCK from Query::errnum() (or Connection::errnum(),

same thing), but what you’ll almost certainly get instead is 0, meaning “no error.” Why? It’s because you’re probably

using a Transaction object to get automatic roll-backs in the face of exceptions. In this case, the roll-back happens

before your exception handler is called by issuing a ROLLBACK query to the database server. Thus, Query::er-

rnum() returns the error code associated with this roll-back query, not the deadlocked transaction that caused the

exception.

MySQL++ v3.0.9 User Manual

To avoid this problem, a few of the exception objects as of MySQL++ v3.0 include this last error number in the exception

object itself. It’s populated at the point of the exception, so it can differ from the value you would get from

Query::errnum() later on when the exception handler runs.

The example examples/deadlock.cpp demonstrates the problem:

#include "cmdline.h"

#include <mysql++.h>

#include <mysqld_error.h>

#include <iostream>

using namespace std;

// Bring in global holding the value given to the -m switch

extern int run_mode;

int

main(int argc, char *argv[])

{

// Get database access parameters from command line

const char* db = 0, *server = 0, *user = 0, *pass = "";

if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {

return 1;

}

// Check that the mode parameter was also given and it makes sense

if ((run_mode != 1) && (run_mode != 2)) {

cerr << argv[0] << " must be run with -m1 or -m2 as one of "

"its command-line arguments." << endl;

return 1;

}

mysqlpp::Connection con;

try {

// Establish the connection to the database server

con.connect(db, server, user, pass);

// Start a transaction set. Transactions create mutex locks on

// modified rows, so if two programs both touch the same pair of

// rows but in opposite orders at the wrong time, one of the two

// programs will deadlock. The MySQL server knows how to detect

// this situation, and its error return causes MySQL++ to throw

// a BadQuery exception. The point of this example is that if

// you want to detect this problem, you would check the value of

// BadQuery::errnum(), not Connection::errnum(), because the

// transaction rollback process executes a query which succeeds,

// setting the MySQL C API's "last error number" value to 0.

// The exception object carries its own copy of the error number

// at the point the exception was thrown for this very reason.

mysqlpp::Query query = con.query();

mysqlpp::Transaction trans(con);

// Build and run the queries, with the order depending on the -m

// flag, so that a second copy of the program will deadlock if

// run while the first is waiting for Enter.

char dummy[100];

for (int i = 0; i < 2; ++i) {

int lock = run_mode + (run_mode == 1 ? i : -i);

cout << "Trying lock " << lock << "..." << endl;

MySQL++ v3.0.9 User Manual

query << "select * from deadlock_test" << lock <<

" where x = " << lock << " for update";

query.store();

cout << "Acquired lock " << lock << ". Press Enter to ";

cout << (i == 0 ? "try next lock" : "exit");

cout << ": " << flush;

cin.getline(dummy, sizeof(dummy));

}

catch (mysqlpp::BadQuery e) {

if (e.errnum() == ER_LOCK_DEADLOCK) {

cerr << "Transaction deadlock detected!" << endl;

cerr << "Connection::errnum = " << con.errnum() <<

", BadQuery::errnum = " << e.errnum() << endl;

}

else {

cerr << "Unexpected query error: " << e.what() << endl;

}

return 1;

}

catch (mysqlpp::Exception e) {

cerr << "General error: " << e.what() << endl;

return 1;

}

return 0;

}

This example works a little differently than the others. You run one copy of the example, then when it pauses waiting

for you to press Enter, you run another copy. Then, depending on which one you press Enter in, one of the two will

abort with the deadlock exception. You can see from the error message you get that it matters which method you call

to get the error number. What you do about it is up to you as it depends on your program’ s design and system architecture.

3.9.Which Query Type to Use?

There are three major ways to execute a query in MySQL++: Query::execute(), Query::store(), and

Query::use(). Which should you use, and why?

execute() is for queries that do not return data per se. For instance, CREATE INDEX. You do get back some in-

formation from the MySQL server, which execute() returns to its caller in a SimpleResult object. In addition to

the obvious — a ﬂag stating whether the query succeeded or not — this object also contains things like the number of

rows that the query affected. If you only need the success status, it’s a little more efﬁcient to call Query::exec()

instead, as it simply returns bool.

If your query does pull data from the database, the simplest option is store(). (All of the examples up to this point

have used this method.) This returns a StoreQueryResult object, which contains the entire result set. It’s especially

convenient because StoreQueryResult derives from std::vector<mysqlpp::Row>, so it opens the whole

panoply of STL operations for accessing the rows in the result set. Access rows randomly with subscript notation, iterate

forwards and backwards over the result set, run STL algorithms on the set...it all works naturally.

If you like the idea of storing your results in an STL container but don’t want to use std::vector, you can call

Query::storein() instead. It lets you store the results in any standard STL container (yes, both sequential and

set-associative types) instead of using StoreQueryResult. You do miss out on some of the additional database

information held by StoreQueryResult’s other base class, ResultBase, however.

store*() queries are convenient, but the cost of keeping the entire result set in main memory can sometimes be too

high. It can be surprisingly costly, in fact. A MySQL database server stores data compactly on disk, but it returns query

MySQL++ v3.0.9 User Manual

data to the client in a textual form. This results in a kind of data bloat that affects numeric and BLOB types the most.

MySQL++ and the underlying C API library also have their own memory overheads in addition to this. So, if you

happen to know that the database server stores every record of a particular table in 1 KB, pulling a million records

from that table could easily take several GB of memory with a store() query, depending on what’s actually stored

in that table.

For these large result sets, the superior option is a use() query. This returns a UseQueryResult object, which is sim-

ilar to StoreQueryResult, but without all of the random-access features. This is because a “use” query tells the

database server to send the results back one row at a time, to be processed linearly. It’s analogous to a C++ stream’s

input iterator, as opposed to a random-access iterator that a container like vector offers. By accepting this limitation,

you can process arbitrarily large result sets. This technique is demonstrated in examples/simple3.cpp:

#include "cmdline.h"

#include "printdata.h"

#include <mysql++.h>

#include <iostream>

#include <iomanip>

using namespace std;

int

main(int argc, char *argv[])

{

// Get database access parameters from command line

const char* db = 0, *server = 0, *user = 0, *pass = "";

if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {

return 1;

}

// Connect to the sample database.

mysqlpp::Connection conn(false);

if (conn.connect(db, server, user, pass)) {

// Ask for all rows from the sample stock table and display

// them. Unlike simple2 example, we retreive each row one at

// a time instead of storing the entire result set in memory

// and then iterating over it.

mysqlpp::Query query = conn.query("select * from stock");

if (mysqlpp::UseQueryResult res = query.use()) {

// Display header

cout.setf(ios::left);

cout << setw(31) << "Item" <<

setw(10) << "Num" <<

setw(10) << "Weight" <<

setw(10) << "Price" <<

"Date" << endl << endl;

// Get each row in result set, and print its contents

while (mysqlpp::Row row = res.fetch_row()) {

cout << setw(30) << row["item"] << ' ' <<

setw(9) << row["num"] << ' ' <<

setw(9) << row["weight"] << ' ' <<

setw(9) << row["price"] << ' ' <<

setw(9) << row["sdate"] <<

endl;

}

// Check for error: can't distinguish "end of results" and

// error cases in return from fetch_row() otherwise.

if (conn.errnum()) {

MySQL++ v3.0.9 User Manual

剩余91页未读，继续阅读

qiu_tang

粉丝: 0
资源: 2

MySQL++入门与高级用法详解

MySQL++资料

mysql++使用及封装示例文档

Mysql++使用手册：基础与规范

Apache2.2中文手册+CSS_2.0_中文手册+HTML中文手册+MySQL+PHP5面向对象编程+php手册+php中文函数手册+jQuery手册

mysql++编程手册

基于springboot+mybatis+mysql+vue音乐网站管理系统源码+数据库+使用手册（毕业设计）

MySQL+5+中文手册

PHP+Mysql+JS手册

mysql+jdk+jquery+EasyUI+python手册.rar

MySQL+5+中文手册.chm

最新资源