C語言呼叫mysql資料庫API實現簡單的mysql客戶端的功能

輸入可執行程式的名字和資料庫的名字進入該程式，比如 ./mysql_test3 ch11  

(ch11是資料庫的名字)

下面是輸入命令的輸出結果：

mysql_init ok...
mysql_real_connect ok...
mysql> show tables;
-------------------------------------------------------------
Tables_in_ch11 
---------------------------------------------------------------
food 
medicine 
product 
t 
t1 
t2 
teacher 
mysql> create table tttttt(id int, name varchar(20));
create ok
mysql> show tables;
-------------------------------------------------------------
Tables_in_ch11 
---------------------------------------------------------------
food 
medicine 
product 
t 
t1 
t2 
teacher 
tttttt 
mysql> desc tttttt;
-------------------------------------------------------------
Field   Type    Null    Key     Default         Extra 
---------------------------------------------------------------
id      int(11)         YES             (null)   
name    varchar(20)     YES             (null)   
mysql> insert into tttttt values(1, "linux_ever");
mysql> select * from tttttt;
-------------------------------------------------------------
id      name 
---------------------------------------------------------------
1       linux_ever 
mysql> drop table tttttt;
drop ok
mysql> show tables;
-------------------------------------------------------------
Tables_in_ch11 
---------------------------------------------------------------
food 
medicine 
product 
t 
t1 
t2 
teacher 
mysql> quit
mysql_close...

上面的命令有：show, create, desc, insert, select, drop, quit

show tables;
create table tttttt(id int, name varchar(20));
desc tttttt;
insert into tttttt values(1, "linux_ever");
select * from tttttt;
drop table tttttt;
quit;

程式的原始碼參考部落格：http://blog.csdn.net/linux_ever/article/details/50651513

呼叫的主要mysql資料庫的API函式：

mysql_init(&mysql);

mysql_errno(&mysql);

mysql_real_connect(connect, "localhost", "root", "yxk", database_name, 0, NULL, 0);

mysql_query(&mysql, "set names utf8");

mysql_query(connect, query);

mysql_store_result(&mysql);

mysql_field_count(&mysql);//獲得表的列數

mysql_fetch_fields(result);//查詢表頭

mysql_fetch_row(result);//查詢表內容

mysql_free_result(result);//釋放記憶體

mysql_close(connect);//關閉連線

原始碼：

/*************************************************************************
	> File Name:mysql_test.c 
	> Author: 
	> Mail: 
	> Created Time: 2016年02月11日 星期四 10時45分31秒
 ************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>

char database_name[1024];
char query[1024];


int main(int argc, char *argv[])
{

    if(argc <= 1){
        printf("input: %s database_name\n", argv[0]);
        return -1;
    }

    strcpy(database_name, argv[1]);

    int ret = 0;
    
    MYSQL mysql;
    MYSQL * connect = NULL;
    connect = mysql_init(&mysql);
    if(connect == NULL){
        ret = mysql_errno(&mysql);
        printf("mysql_init error, %s\n", mysql_error(&mysql));
        return ret;
    }
    printf("mysql_init ok...\n");


    connect = mysql_real_connect(connect, "localhost", "root", "yxk", database_name, 0, NULL, 0);
    if(connect == NULL){
        ret = mysql_errno(&mysql);
        printf("mysql_real_connect error, err is: %s\n", mysql_error(&mysql));
        return ret;
    }
    printf("mysql_real_connect ok...\n");

	char * pch = NULL;
	
	while(1){
		memset(query, 0, sizeof(query));
		printf("mysql> ");
		gets(query);		
		
		pch = strchr(query, ';');//將最後的分號;換為\0
		if(pch != NULL){
			*pch = '\0';
		}

		if(strcmp("quit", query) == 0){//如果輸入的是quit則退出程式
			break;
		}
		 //設定查詢字符集屬性為utf8
		ret = mysql_query(&mysql, "set names utf8");
		if(ret != 0){
			printf("mysql_query error\n");
			return ret;
		}
		
		//執行下面的命令, select, show, desc
		if((strncmp(query, "select", 6) == 0) || (strncmp(query, "SELECT", 6) == 0) || 
		   (strncmp(query, "show", 4) == 0)	|| (strncmp(query, "SHOW", 4) == 0) ||
		   (strncmp(query, "desc", 4) == 0)	|| (strncmp(query, "DESC", 4) == 0)){
			//查詢資料
			ret = mysql_query(connect, query);
			if(ret != 0){
				printf("mysql_query error\n");
				//return ret;
				continue;
			}
			
			MYSQL_RES *result = mysql_store_result(&mysql);
			if(result == NULL){
				printf("mysql_store_result error\n");
				return -1;
			}
			
			int field_num = mysql_field_count(&mysql);
			//查詢表頭
			MYSQL_FIELD * fields = mysql_fetch_fields(result);
			int i = 0;

			printf("-------------------------------------------------------------\n");
			for(i= 0; i < field_num; i++){
				printf("%s \t", fields[i].name); 
			}
			printf("\n---------------------------------------------------------------\n");


			//查詢表內容
			MYSQL_ROW row = NULL;
			while(row = mysql_fetch_row(result)){
				for(i= 0; i < field_num; i++){
					printf("%s \t", row[i]); 
				}
				printf("\n");
			}

			mysql_free_result(result);//釋放記憶體		
		}	
		else {//執行drop， create, insert等命令 
			ret = mysql_query(connect, query);
			if(ret != 0){
				printf("mysql_query error\n");
				//return ret;
				continue;
			}
			if(strncmp(query, "drop", 4)==0 || strncmp(query, "DROP", 4)==0){
				printf("drop ok\n");
			}
			else if(strncmp(query, "create", 6)==0 || strncmp(query, "CREATE", 6)==0){
				printf("create ok\n");
			}
			else if(strncmp(query, "insert", 6)==0 || strncmp(query, "INSERT", 6)==0){
				printf("insert ok\n");
			}
		}
	}

	mysql_close(connect);
   
    printf("mysql_close...\n");

    return ret;
}