Mysql資料庫動态庫： libmysql.dll libmysql.lib mysql.h WinSock2.h
Mysql API資料結構 (mysql.h)
MYSQL:連接配接資料庫前，必須先建立MYSQL變量，此變量在很多Mysql API函數會用到。它包含了一些連接配接資訊等資料。
MYSQL_RES:MYSQL_RES結構體中包含了查詢結果集，也就是從資料庫中查詢到的資料。可以使用mysql_store_result或mysql_use_result函數獲得。
MYSQL_ROW:MYSQL ROW的定義如下：typedef char **MYSQL_ROW;可見，它實際上是char **類型，指向一個字元串數組。存儲一行中各段字元數組，可以通過mysql_fetch_row函數獲得。
MYSQL_FIELD:MYSQL_FIELD中包含了字段名、字段類型和大小等資訊。可以重複調用mysql_fetch_field函數獲得所有字段的資訊。
Mysql C API程式設計步驟
1、首先我們要包含mysql的頭檔案，并連結mysql動态庫。
#include <WinSock2.h> // 進行網絡程式設計需要winsock2.h
#include <mysql.h>
#pragma comment(lib, "libmysql.lib")
2、建立MYSQL變量。如：
MYSQL mysql;
3、初始化MYSQL變量。
mysql_init(&mysql);
4、調用mysql_real_connect函數連接配接Mysql資料庫。
mysql_real_connect函數的MYSQL * STDCALL 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 clientflag);
參數說明：mysql--前面定義的MYSQL變量；host--MYSQL伺服器的位址；user--登入使用者名；passwd--登入密碼；db--要連接配接的資料庫；port--MYSQL伺服器的TCP服務端口；unix_socket--unix連接配接方式，為NULL時表示不使用socket或管道機制；clientflag--Mysql運作為ODBC資料庫的标記，一般取0。
連接配接失敗時該函數傳回0。
5、調用mysql_real_query函數進行資料庫查詢。mysql_real_query函數的原型如下：
int STDCALL mysql_real_query(MYSQL *mysql, const char *q, unsigned long length);
參數說明：mysql--前面定義的MYSQL變量；q--SQL查詢語句；length--查詢語句的長度。
查詢成功則該函數傳回0。
6、通過調用mysql_store_result或mysql_use_result函數傳回的MYSQL_RES變量擷取查詢結果資料。
兩個函數的原型分别為：
MYSQL_RES * STDCALL mysql_store_result(MYSQL *mysql);
MYSQL_RES * STDCALL mysql_use_result(MYSQL *mysql);
這兩個函數分别代表了擷取查詢結果的兩種方式。第一種，調用mysql_store_result函數将從Mysql伺服器查詢的所有資料都存儲到用戶端，然後讀取；第二種，調用mysql_use_result初始化檢索，以便于後面一行一行的讀取結果集，而它本身并沒有從伺服器讀取任何資料，這種方式較之第一種速度更快且所需記憶體更少，但它會綁定伺服器，阻止其他線程更新任何表，而且必須重複執行mysql_fetch_row讀取資料，直至傳回NULL，否則未讀取的行會在下一次查詢時作為結果的一部分傳回，故經常我們使用mysql_store_result。
7、調用mysql_fetch_row函數讀取結果集資料。
上述兩種方式最後都是重複調用mysql_fetch_row函數讀取資料。mysql_fetch_row函數的原型如下：
MYSQL_ROW STDCALL mysql_fetch_row(MYSQL_RES *result);
參數result就是mysql_store_result或mysql_use_result的傳回值。
該函數傳回MYSQL_ROW型的變量，即字元串數組，假設為row，則row[i]為第i個字段的值。當到結果集尾部時，此函數傳回NULL。
8、結果集用完後，調用mysql_free_result函數釋放結果集，以防記憶體洩露。mysql_free_result函數的原型如下：
void STDCALL mysql_free_result(MYSQL_RES *result);
9、不再查詢Mysql資料庫時，調用mysql_close函數關閉資料庫連接配接。mysql_close函數的原型為：
void STDCALL mysql_close(MYSQL *sock);
例子：
1. int main() {
2. MYSQL mysql;
3. MYSQL_RES *res;
4. MYSQL_ROW row;
5. mysql_init(&mysql); // 初始化MYSQL變量
6. // 連接配接Mysql伺服器，本例使用本機作為伺服器。通路的資料庫名稱為"msyql"，參數中的user為你的登入使用者名，***為登入密碼，需要根據你的實際使用者進行設定
7. if (!mysql_real_connect(&mysql, "127.0.0.1", "user", "123", "mysql", 3306, 0, 0)) {
8. cout << "mysql_real_connect failure!" << endl;
9. return 0;
10. }
11. if (mysql_real_query(&mysql, "select * from user", (unsigned long)strlen("select * from user"))){ // 查詢mysql資料庫中的user表
12. cout << "mysql_real_query failure!" << endl;
13. return 0;
14. } // 存儲結果集
15. res = mysql_store_result(&mysql);
16. if (NULL == res) {
17. cout << "mysql_store_result failure!" << endl;
18. return 0;
19. }
20. // 重複讀取行，并輸出第一個字段的值，直到row為NULL
21. while (row = mysql_fetch_row(res)) {
22. cout << row[0] << endl;
23. }
24. mysql_free_result(res); // 釋放結果集
25. mysql_close(&mysql); // 關閉Mysql連接配接
26. return 0; }
10.Char * mysql_get_client_info() 顯示mysql用戶端版本
MySQL client version: 5.0.38
11.int mysql_num_fields(MYSQL_RES *result) 傳回結果子表中域(字段)的個數
12. MYSQL_FIELD * mysql_fetch_field(MYSQL_RES *result) 傳回結果子表中的域結構體指針
13.Void mysql_real_escape_string(MYSQL* con, char* savedata, char *data, int size) 在将二進制資料(非文本)儲存到資料庫之前，需要轉義，否則資料庫不能正常儲存，取出資料時，無需解轉移。轉義時一個字元轉義後2個字元，是以savedata記憶體必須為data的2倍
14. unsigned long * mysql_fetch_lengths(MYSQL_RES *result) 擷取結果中各個字元串的長度，傳回為1維數組
一些有用的例子：
#include
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", "zetcode", "passwd", "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);
}
The example prints all names from the writers table.
$ ./select
Leo Tolstoy
Jack London
Honore de Balzac
Lion Feuchtwanger
Emile Zola
mysql_query(conn, "SELECT * FROM writers");
We execute the query, that will retrieve all names from the writers database.
result = mysql_store_result(conn);
We get the result set.
num_fields = mysql_num_fields(result);
We get the number of fields in the table.
while ((row = mysql_fetch_row(result)))
{
for(i = 0; i < num_fields; i++)
{
printf("%s ", row[i] ? row[i] : "NULL");
}
printf("\n");
}
We fetch the rows and print them to the screen.
mysql_free_result(result);
We free the resources.
Column headers
In the next example, we will retrieve data and show the their column names from the table.
For this, we will create a new table 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);
We insert some data into the table.
MYSQL_FIELD *field;
mysql_query(conn, "SELECT * FROM friends");
if (i == 0) {
while(field = mysql_fetch_field(result)) {
printf("%s ", field->name);
}
printf("\n");
}
printf("%s ", row[i] ? row[i] : "NULL");
printf("\n");
The example is similar to the previous one. It just adds column header names to it.
while(field = mysql_fetch_field(result)) {
printf("%s ", field->name);
The mysql_fetch_field() call returns a MYSQL_FIELD structure. We get the column header names from this structure.
$ ./headers
id name age
1 Tom 25
2 Elisabeth 32
3 Jane 22
4 Luke 28
And this is the output of our program.
Inserting p_w_picpaths into MySQL database
Some people prefer to put their p_w_picpaths into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with millions of p_w_picpaths. Images are binary data. MySQL database has a special data type to store binary data called BLOB (Binary Large Object).
mysql> describe p_w_picpaths;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | | |
| data | mediumblob | YES | | NULL | |
2 rows in set (0.00 sec)
This is the table, that we will use in our example. It can be created by the following SQL statement.
create table p_w_picpaths(id int not null primary key, data mediumblob);
int len, size;
char data[1000*1024];
char chunk[2*1000*1024+1];
char query[1024*5000];
FILE *fp;
fp = fopen("p_w_picpath.png", "rb");
size = fread(data, 1, 1024*1000, fp);
mysql_real_escape_string(conn, chunk, data, size);
char *stat = "INSERT INTO p_w_picpaths(id, data) VALUES('1', '%s')";
len = snprintf(query, sizeof(stat)+sizeof(chunk) , stat, chunk);
mysql_real_query(conn, query, len);
fclose(fp);
In this example, we will insert one p_w_picpath into the p_w_picpaths table. The p_w_picpath can be max 1 MB.
fp = fopen("p_w_picpath.png", "rb");
size = fread(data, 1, 1024*1000, fp);
Here we open the p_w_picpath and read it into the data array.
mysql_real_escape_string(conn, chunk, data, size);
Binary data can obtain special characters, that might cause troubles in the statements. We must escape them. The mysql_real_escape_string() puts the encoded data into the chunk array. In theory, every character might be a special character. That's why the chunk array two times as big as the data array. The function also adds a terminating null character.
char *stat = "INSERT INTO p_w_picpaths(id, data) VALUES('1', '%s')";
len = snprintf(query, sizeof(stat)+sizeof(chunk) , stat, chunk);
These two code lines prepare the MySQL query.
mysql_real_query(conn, query, len);
Finally, we execute the query.
Selecting p_w_picpaths from MySQL database
In the previous example, we have inserted an p_w_picpath into the database. In the following example, we will select the inserted p_w_picpath back from the database.
unsigned long *lengths;
fp = fopen("p_w_picpath.png", "wb");
mysql_query(conn, "SELECT data FROM p_w_picpaths WHERE id=1");
row = mysql_fetch_row(result);
lengths = mysql_fetch_lengths(result);
fwrite(row[0], lengths[0], 1, fp);
In this example, we will create an p_w_picpath file from the database.
fp = fopen("p_w_picpath.png", "wb");
We open a file for writing.
mysql_query(conn, "SELECT data FROM p_w_picpaths WHERE id=1");
We select an p_w_picpath with id 1.
row = mysql_fetch_row(result);
The row contains raw data.
lengths = mysql_fetch_lengths(result);
We get the length of the p_w_picpath.
fwrite(row[0], lengths[0], 1, fp);
We create the p_w_picpath file using the fwrite() standard function call.
![C語言-----初階指針詳解[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)