MySQL C 语言应用程序接口开发教程

从数据库中取回数据
在这个实例中我们从表中取回数据。
步骤：
(1)创建连接
(2)执行查询
(3)获取结果集
(4)提取所有可用的记录
(5)释放结果集实例程序打印 writers 表中所有的记录（姓名）。

#include <my_global.h>
#include <mysql.h>
 
int main(int argc, char * argv[])
{
	MYSQL * conn;
	MYSQL_RES * result;
	MYSQL_ROW row;
	int num_fields;
	int i;
 
	conn = mysql_init(NULL);
	mysql_real_connect(conn, "localhost", "username", "password", "testdb", 0, NULL, 0);
 
	mysql_query(conn, "SELECT * FROM writers");
	result = mysql_store_result(conn);
 
	num_fields = mysql_num_fields(result);
 
	while ((row = mysql_fetch_row(result)))
	{
		for (i=0; i<num_fields; i++)
		  printf("%s ", row[i] ? row[i] : "NULL");
		printf("n");
	}
 
	mysql_free_result(result);
	mysql_close(conn);
}

mysql_query(conn, "SELECT * FROM writers");

result = mysql_store_result(conn);

取得结果集。

num_fields = mysql_num_fields(result);

获得表中的字段数量。

mysql_query(conn, "SELECT * FROM writers");while ((row = mysql_fetch_row(result)))
{
	for (i=0; i<num_fields; i++)
	  printf("%s ", row[i] ? row[i] : "NULL");
	printf("n");
}

mysql_free_result(result);

字段名称
这个实例里将要打印数据并显示字段名称。
为此我们创建一个新的表 friends。

mysql> CREATE TABLE friends (id int not null primary key auto_increment,name varchar(20), age int);

mysql> insert into friends(name, age) values('Tom', 25);
mysql> insert into friends(name, age) values('Elisabeth', 32);
mysql> insert into friends(name, age) values('Jane', 22);
mysql> insert into friends(name, age) values('Luke', 28);

#include <my_global.h>
#include <mysql.h>
 
int main(int argc, char * argv[])
{
	MYSQL * conn;
	MYSQL_RES * result;
	MYSQL_ROW row;
	MYSQL_FIELD * field;
 
	int num_fields;
	int i;
 
	conn = mysql_init(NULL);
	mysql_real_connect(conn, "localhost", "username", "password", "testdb", 0, NULL, 0);
 
	mysql_query(conn, "SELECT * FROM friends");
	result = mysql_store_result(conn);
 
	num_fields = mysql_num_fields(result);
 
	while ((row = mysql_fetch_row(result)))
	{
		for(i=0; i<num_fields; i++)
		{
			if (i == 0) {
				while(field = mysql_fetch_field(result)) {
					printf("%s ", field->name);
				}
				printf("n");
			}
			printf("%s ", row[i] ? row[i] : "NULL");
		}
	}
	printf("n");
 
	mysql_free_result(result);
	mysql_close(conn);
 
	return 0;
}

这个实例和之前有一点差别，仅仅增加了字段名称。

mysql_fetch_field() 返回一个 MYSQL_FIELD 结构。我们从这个结构中得到名称(name)。

./header
id name age
1  Tom  25
2  Elisabeth  32
3  Jane  22
4  Luke  28

插件图片到 MySQL 数据库
一些用户喜欢将图片存入数据库，还有一些用户喜欢将图片存入本地文件系统。图片是二进制数据，MySQL 有专用的数据类型 BLOB(Binary Large Object) 以存储二进制数据。

mysql> describe images;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI |         |       |
| data  | mediumblob | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

CREATE TABLE images(id int not null primary key, data mediumblob);

#include <my_global.h>
#include <mysql.h>
 
int main(int argc, char * argv[])
{
	MYSQL * conn;
 
	int len, size;
	char data[1000*1024];
	char chunk[2*1000*1024+1];
	char query[1024*5000];
 
	FILE * fp;
 
	conn = mysql_init(NULL);
	mysql_real_connect(conn, "localhost", "username", "password", "testdb", 0, NULL, 0);
 
	fp = fopen("image.png", "rb");
	size = fread(data, 1, 1024*1000, fp);
 
	mysql_real_escape_string(conn, chunk, data, size);
 
	char *stat = "INSERT INTO images(id, data) VALUES('1', '%s')";
	len = snprintf(query, sizeof(stat)+sizeof(chunk), stat, chunk);
 
	mysql_real_query(conn, query, len);
 
	fclose(fp);
 
	mysql_close(conn);
 
	return 0;
}

fp = fopen("images.png", "rb");
size = fread(data, 1, 1024*1000, fp);

mysql_real_escape_string(conn, chunk, data, size);

char * stat = "INSERT INTO images(id, data) VALUES('1', '%s')";
len = snpritnf(query, sizeof(stat)+sizeof(chunk), stat, chunk);

mysql_real_query(conn, query, len);

#include <my_global.h>
#include <mysql.h>
 
int main(int argc, char * argv[])
{
	MYSQL * conn;
	MYSQL_RES * result;
	MYSQL_ROW row;
 
	unsigned long * lengths;
	FILE * fp;
 
	conn = msyql_init(NULL);
	mysql_real_connect(conn, "localhost", "username", "password", "testdb", 0, NULL, 0);
 
	fp = fopen("image.png", "wb");
 
	mysql_query(conn, "SELECT data FROM images WHERE id=1");
	result = mysql_store_result(conn);
 
	row = mysql_fetch_row(result);
	lengths = mysql_fetch_lengths(result);
 
	fwrite(row[0], lengths[0], 1, fp);
	mysql_free_result(result);
 
	fclose(fp);
	mysql_close(conn);
 
	return 0;
}

fp = fopen("image.png", "wb");//以可写的方式打开一个文件。
mysql_query(conn, "SELECT data FROM images WHERE id=1");//选择 ID 为 1 的图片。
row = mysql_fetch_row(result);//row 包含了原始数据。