C访问MySQL

// C API文档：http://dev.mysql.com/doc/refman/5.7/en/c-api-functions.html
// C++ 相关下载：http://dev.mysql.com/downloads/connector/cpp/
// C++ 文档：http://dev.mysql.com/doc/connector-cpp/en/index.html
// vs IDE配置实用MySQL（C++版，与C基本一致）：http://dev.mysql.com/doc/connector-cpp/en/connector-cpp-apps-windows-visual-studio.html
///< 下面程序我们用的是最新5.7版本的，64位的C驱动，所以需要选择x64平台，连接时需要libmysql.dll放在可执行文件目录

#include <windows.h> 
#include <stdio.h> 
#include <string.h> 
#include <mysql.h> 
/**
* @brief
* 	注意事项：
*	①x64平台
*       ②一定要将libmysql.dll 放在执行文件目录上
SQL：
CREATE DATABASE test;
USE test;
DROP DATABASE test;


*/
#pragma warning(disable:4996)
#pragma comment (lib, "libmysql.lib") 
//#pragma comment (lib, "mysqlclient.lib") 


int main() 
{ 
	MYSQL * mysql = nullptr; 
	try
	{
		char szDBName[] = "test"; 
		char szSqlText[500]=""; 
		mysql = mysql_init((MYSQL*)0); 


		//连接数据库 
		if(mysql_real_connect( mysql, NULL, "root", "", szDBName, MYSQL_PORT, NULL, 0)) 
		{ 
			printf("数据库连接成功！\n"); 
			MYSQL_RES* pRes = nullptr;
			if (pRes = mysql_list_dbs(mysql, ""))
			{
				MYSQL_ROW row;
				unsigned int num_fields;
				unsigned int num_row;
				unsigned int i;
				//pRes->row_count
				num_row = mysql_num_rows(pRes); // 获取多少行结果
				num_fields = mysql_num_fields(pRes); // 一行有几个字段
				while ((row = mysql_fetch_row(pRes)))
				{
					unsigned long *lengths;
					lengths = mysql_fetch_lengths(pRes); //获取值长度
					auto pFiled = mysql_fetch_field(pRes);
					for(i = 0; i < num_fields; i++)
					{
						printf("[%.*s] ", (int) lengths[i],
							row[i] ? row[i] : "NULL");
					}
					printf("\n");
				}
				
			}
			mysql_free_result(pRes);
			
			// 查询mytable的字段类型
			pRes = nullptr;
			if (!mysql_query(mysql, "desc mytable"))
			{
				// 取结果集
				pRes = mysql_store_result(mysql);
				// 或用mysql_use_result(mysql);
				auto num_row = mysql_num_rows(pRes);
				auto num_fields = mysql_num_fields(pRes); //表格每行字段个数都相等
				for (int i = 0; i < num_row; ++i)
				{
					auto pRow = mysql_fetch_row(pRes);
					for (int j = 0; j < num_fields; ++j)
					{
						printf(" %s\n", pRow[j]);
					}
				}
			}
			else
			{
				mysql_free_result(pRes);
				throw -1;
			}
			//构造SQL语句 
			sprintf(szSqlText, "create table mytable" "(time datetime, s1 char(6), " "s2 char(11), s3 int, s4 int)"); 
			if (mysql_query( mysql, szSqlText)) 
			{//执行SQL语句出错 
				printf( "Can't create table\n"); 
				throw -1;
			} 
			printf("表创建成功\n"); 
			mysql_close(mysql); 
		}
		else
			throw -1;
	}
	catch(...)
	{
		printf("Error:%s\n", mysql_error(mysql));
		mysql_close( mysql ); 
	}
	getchar();
	return TRUE; 
}


#if 0
// 多条执行语句
/* connect to server with the CLIENT_MULTI_STATEMENTS option */
if (mysql_real_connect (mysql, host_name, user_name, password,
	db_name, port_num, socket_name, CLIENT_MULTI_STATEMENTS) == NULL)
{
	printf("mysql_real_connect() failed\n");
	mysql_close(mysql);
	exit(1);
}


/* execute multiple statements */
status = mysql_query(mysql,
					 "DROP TABLE IF EXISTS test_table;\
					 CREATE TABLE test_table(id INT);\
					 INSERT INTO test_table VALUES(10);\
					 UPDATE test_table SET id=20 WHERE id=10;\
					 SELECT * FROM test_table;\
					 DROP TABLE test_table");
if (status)
{
	printf("Could not execute statement(s)");
	mysql_close(mysql);
	exit(0);
}


/* process each statement result */
do {
	/* did current statement return data? */
	result = mysql_store_result(mysql);
	if (result)
	{
		/* yes; process rows and free the result set */
		process_result_set(mysql, result);
		mysql_free_result(result);
	}
	else          /* no result set or error */
	{
		if (mysql_field_count(mysql) == 0)
		{
			printf("%lld rows affected\n",
				mysql_affected_rows(mysql));
		}
		else  /* some error occurred */
		{
			printf("Could not retrieve result set\n");
			break;
		}
	}
	/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
	if ((status = mysql_next_result(mysql)) > 0)
		printf("Could not execute statement\n");
} while (status == 0);


mysql_close(mysql);
#endif

// 多线程访问的时候需要注意，编译时加上--with-thread-safe-client，其次用的时候每个线程独享自己的MYSQL *指针；当然如果想也可加全局锁共享一个MYSQL*（效率就打折扣了），自己因情况而定。