Mysql: 基于MySQL API 接口，Viual Studio，访问 Mysql数据库

用VS 访问Mysql数据库，基于MySQL API 接口（C语言）

开发环境：

- OS: Windows 7, 64位

- VS2008

- Mysql 5.7

Mysql 5.7 的参考手册：

http://downloads.mysql.com/docs/refman-5.7-en.a4.pdf

或者百度网盘：http://pan.baidu.com/s/1slxhhxj

> 准备Mysql

<span style="font-size:14px;">CREATE DATABASE mydb;
SHOW DATABASES;
USE mydb;</span>

<span style="font-size:14px;"></span><pre name="code" class="sql">CREATE TABLE test_tbl
(id serial,
name VARCHAR(20) NOT NULL);
SHOW TABLES;
DESC test_tbl;

insert into test_tbl( name) values ( 'Ray');

select * from test_tbl;

执行后如下：

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> USE mydb;
Database changed
mysql> CREATE TABLE test_tbl
    -> (id serial,
    -> name VARCHAR(20) NOT NULL);
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| test_tbl       |
+----------------+
1 row in set (0.00 sec)

mysql> DESC test_tbl;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into mydb.test_tbl( name) values ( 'Ray');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test_tbl;
+----+------+
| id | name |
+----+------+
|  1 | Ray  |
+----+------+
1 row in set (0.00 sec)

mysql>

1.1 采用Mysql API接口：C语言

新建空白项目，设置项目如下：

项目->属性-> C/C++ ->常规 -> 附加包含目录，添加： C:\Program Files\MySQL\MySQL Server 5.7\include

(与mysql安装目录一致)

项目->属性-> 链接器 ->常规 -> 附加库目录，添加： C:\Program Files\MySQL\MySQL Server 5.7\lib （与mysql的安装目录一致）

项目->属性-> 链接器 ->输入 -> 附加依赖项，添加：libmysql.lib

将文件libmysql.dll（位于C:\Program Files\MySQL\MySQL Server 5.7\lib）复制到system32目录下。

如果你和我一样，电脑和mysql都是安装的64位，请设置：

项目->属性->配置管理器-> 平台 -> 新建 ： 选择X64，设置debug为release，不然链接时，会出现错误。

如果找不到X64，需要为VS2008安装64位编译器。找到VS2008安装包，打开安装程序，操作：更改或移除Visual Studio 2008 -> 下一步 ->  添加或者删除功能 , 为Visual C++ 安装 X64编译器和工具，请自行完成。

新建源文件，程序如下：

<span style="font-size:14px;">#include <stdio.h>
#include "mysql.h"
#include <windows.h>

const int TIME = 2000;
const char* headline = "------------------------";


int main(int argc, char *argv[])
{
	MYSQL mysql_con;
	MYSQL_RES * sql_select;

	int ack;
	char * host = "localhost";
	char * user = "andrew";
	char * pwd = "andrew123";
	char * db_name = "mydb";
	int port = 3306;


	// initialize mysql server
	mysql_init(&mysql_con);

	if ( mysql_real_connect(&mysql_con, host, user, pwd, db_name, port, 0, 0)){
		//print connect successful
		printf("mysql connect successful\n"); 

		// insert a new row to mydb.test.tbl
		char *str = "insert into mydb.test_tbl( name) values ( 'Andrew')";
		ack = mysql_query(&mysql_con, str);
			if (!ack){
				printf("insert successful!");
			}
			else{
				printf("insert error");
			}

		str = "select * from test_tbl";
		ack = mysql_query( &mysql_con, str);
		
		if (!ack){
			sql_select = mysql_store_result(&mysql_con);
			if(sql_select){
				int rowcount =  mysql_num_rows(sql_select);
				printf("result rows: %d\n", rowcount);
				int fieldcount = mysql_num_fields(sql_select);

				int j;
				//print field of table
				MYSQL_FIELD *field;
				MYSQL_ROW row;
				printf("%s\n", headline);
				for (j = 0; j< fieldcount; j++){
						field = mysql_fetch_field_direct(sql_select, j);
						printf("%s\t\t", field->name);
				}
				printf("\n");

				// print rows of table
				while( row = mysql_fetch_row(sql_select)){					
					for (j = 0; j< fieldcount; j++){
						printf("%s\t\t", row[j]);
					}
					printf("\n");
				}
				printf("\n%s\n", headline);
			}
		}

		mysql_close(&mysql_con);
		printf("Mysql colse");
	}
	else{
		// fail to connect
		printf("fail to connect Mysql");
		
	}

	// pritn: program stop
	Sleep(TIME*2);
	printf("the program stop\n");
	return 0;


}</span>

运行结果的部分截图如下：