使用C操作MySQL数据库（vs 2008）

最近几天闲来无事，便尝试了一下用C操作数据库，下面便是我测试的具体结果：

开发环境是windows 7 ultimate(英文版) IDE 是vs 2008 professional 中文版,数据库是mysql 5.1

 

首先，我在MySQL库中创建了一个test表：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(3) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

 

然后，要配置好vs 2008下的开发环境：

工具->选项->项目和解决方案->vc++目录

1. 在窗口右边添加包含文件（我的是D:/Program Files/MySQL/MySQL Server 5.1/include）

2.添加库文件（D:/Program Files/MySQL/MySQL Server 5.1/lib/opt）

 

配置好后，即可创建新的工程了。下面便是我的测试代码，注释比较详细，我这里就不做过多的介绍了。

注释大多数都是我翻译了英文文档，英文好的可以直接参考MySQL的官方文档。

MySQL官网：www.mysql.com

 #include <iostream> #include <windows.h> #include <stdio.h> #include "mysql.h" //非常重要 #pragma comment(lib, "libmysql.lib") using namespace std; int main() { MYSQL *mysql; MYSQL_RES *result; MYSQL_ROW row; unsigned int field_count; unsigned int num_fields; my_ulonglong affected_rows; const char* selectString = "SELECT name,age,gender FROM test"; const char* updateString = "UPDATE test SET age=30 WHERE id=4"; const char* deleteString = "DELETE FROM test WHERE id=4"; const char* insertString = "INSERT INTO test VALUES(null,'zhaoliu',24,'m')"; const char* mutilExecution = "INSERT INTO test VALUES(null,'zhaoliu',24,'m');DELETE FROM test WHERE id=4"; /* MYSQL *mysql_init(MYSQL *mysql) 函数描述： 分配或初始化一个适合mysql_real_connect()函数的MYSQL对象。如果mysql是空指针，则函数分配并初始化 一个新的MYSQL对象，作为返回值返回。否则，直接初始化对象，并返回对象的地址。如果用mysql_init()函数 初始化了一个新的对象，则在调用mysql_close()关闭连接，并释放MYSQL对象。 Allocates or initializes a MYSQL object suitable for mysql_real_connect(). If mysql is a NULL pointer, the function allocates, initializes, and returns a new object. Otherwise, the object is initialized and the address of the object is returned. If mysql_init() allocates a new object, it is freed when mysql_close() is called to close the connection. 返回一个初始化了的MYSQL*句柄。如果没有足够的内存，则返回一个NULL Return an initialized MYSQL* handle. NULL if there was insufficient memory to allocate a new object. */ mysql = mysql_init(NULL); /* MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag) 函数描述： mysql_real_connect()尝试和运行在host服务器上的MYSQL数据库引擎建立一个连接。如果你想执行任何其他需要MYSQL连接 句柄的API函数，那么mysql_real_connect()必须成功执行。 mysql_real_connect() attempts to establish a connection to a MySQL database engine running on host. mysql_real_connect() must complete successfully before you can execute any other API functions that require a valid MYSQL connection handle structure. 参数描述: host:主机名或主机IP地址。如果host为NULL或“localhost”，那么默认连接本地主机。决定连接的类型。 其它参数察看MYSQL官方文档 */ if(!mysql_real_connect(mysql, "localhost", "root", "123456", "test", 3306, NULL, 0)) { cout<<"Failed to connect database: "<<endl; } /* int mysql_set_character_set(MYSQL *mysql, const char *csname) 函数描述： 该函数用于设置但前连接的默认字符集,相当于SET NAMES。如果返回0则表示成功了，不为0的数则表示失败。 This function is used to set the default character set for the current connection. Zero for success. Nonzero if an error occurred. */ if(!mysql_set_character_set(mysql, "utf-8")) { cout<<"New client character set: "<<endl; } /* int mysql_real_query(MYSQL *mysql, const char *stmt_str, unsigned long length) 函数描述: 一般情况下，字符串必须是单个SQL语句，而且你不能在SQL语句后面添加分号，或者其转义字符。如果启用了批处理功能， 那么字符串可以包含多个SQL语句，并且通过分号隔开。mysql_query()不能执行包含二进制的SQL语句，而你必须使用 mysql_real_query().（二进制数据可能包含"/0"的字符，mysql_query()函数则认为那是字符串的结尾）。另外， mysql_real_query()的执行速度比mysql_query()更快，因为在处理SQL字符串时，它不会去调用strlen()函数。 Normally,the string must consist of a single SQL statement and you should not add a terminating semicolon (“;”) or /g to the statement. If multiple-statement execution has been enabled, the string can contain several statements separated by semicolons. mysql_query() cannot be used for statements that contain binary data; you must use mysql_real_query() instead. (Binary data may contain the “/0” character, which mysql_query() interprets as the end of the statement string.) In addition, mysql_real_query() is faster than mysql_query() because it does not call strlen() on the statement string. */ cout<<"update db"<<endl; mysql_real_query(mysql, insertString, strlen(insertString)); affected_rows = mysql_affected_rows(mysql); cout<<"Affected rows: "<<affected_rows<<endl; cout<<"select from db"<<endl; mysql_real_query(mysql, selectString, strlen(selectString)); /* unsigned int mysql_field_count(MYSQL *mysql) 函数描述: */ field_count = mysql_field_count(mysql); cout<<"Field count: "<<field_count<<endl; /* MYSQL_RES *mysql_store_result(MYSQL *mysql) 函数描述: 读取所有返回给客服端的结果. */ result = mysql_store_result(mysql); /* 注意 */ num_fields = mysql_num_fields(result); cout<<"Number of fields: "<<num_fields<<endl; /* MYSQL_ROW mysql_fetch_row(MYSQL_RES *result) 函数描述: */ while((row = mysql_fetch_row(result))) { cout<<"NAME: "<<row[0]<<"/t"<<"AGE: "<<row[1]<<"/t"<<"GENDER: "<<row[2]<<endl; } mysql_free_result(result); /* void mysql_close(MYSQL *mysql) 函数描述： 关闭已建立的连接。同时释放指向mysql的连接句柄，只要是由mysql_init()或者mysql_connect()函数自动分配的。 Closes a previously opened connection. mysql_close() also deallocates the connection handle pointed to by mysql if the handle was allocated automatically by mysql_init() or mysql_connect(). */ mysql_close(mysql); return 0; }