linux连接MySQL数据库（C语言 API 分析,动态插入修改数据）

###Ubuntu14.04--mysql5.5.6###

###2020/11/14###

常见的MYSQL，MYSQL_RES，MYSQL_ROW结构体等，详见：https://dev.mysql.com/doc/dev/mysql-server/latest/mysql_8h.html

1.转载代码：

/*
        single_thread_mysql_client.cpp
    */
    #include <stdio.h>
    #include <string.h>
    #include <stdlib.h>
    #include <mysql/mysql.h>
    #include <pthread.h>
    #include <unistd.h>

    #define DBHOST      "localhost"
    #define DBUSER      "root"
    #define DBPASS      "***"    //填入本机数据库密码
    #define DBPORT      3306
    #define DBNAME      "test"
    #define DBSOCK      NULL //"/var/lib/mysql/mysql.sock"
    #define DBPCNT      0

    int main()
    {
        MYSQL_RES *result;
        MYSQL_ROW row;
        MYSQL_FIELD *field;   //FIELD数据结构指针
        unsigned int num_fields;
        unsigned int i;
        const char *pStatement = "select * from children";
        mysql_library_init(0, NULL, NULL);
        MYSQL *mysql = mysql_init(NULL);
        unsigned int timeout = 3000;
        mysql_options(mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);

        if (mysql_real_connect(mysql, DBHOST, DBUSER, DBPASS, DBNAME, DBPORT, DBSOCK, DBPCNT) == NULL)//与本机数据库建立连接
        {
            printf("connect failed: %s\n", mysql_error(mysql));
            mysql_close(mysql);
            mysql_library_end();
            return 0;
        }

        printf("connect succssfully\n");

        if (0 != mysql_real_query(mysql, pStatement, strlen(pStatement)))   //执行查询语句
        {
            printf("query failed: %s\n", mysql_error(mysql));
            mysql_close(mysql);
            mysql_library_end();
            return 0;
        }

        result = mysql_store_result(mysql); //执行SQL语句后，结果保存在result指针中

        if (result == NULL)
        {
            printf("fetch result failed: %s\n", mysql_error(mysql));
            mysql_close(mysql);
            mysql_library_end();
            return 0;
        }

        num_fields = mysql_num_fields(result);//获取数据库表的列字段个数
        printf("numbers of result: %d\n", num_fields);

        while (NULL != (field = mysql_fetch_field(result)))//获取指向数据库列字段数组指针
        {
            printf("field name: %s\n", field->name);
        }

        while (NULL != (row = mysql_fetch_row(result)))//获取每行记录的指针
        {
            unsigned long *lengths;
            lengths = mysql_fetch_lengths(result);//获取列字段行宽

            for (i = 0; i < num_fields; i++)
            {
                printf("{%.*s} ", (int) lengths[i], row[i] ? row[i] : "NULL");
        　　　　 printf("lenghts[%d]=%d\n",i,lengths[i]);
            }

            printf("\n");
        }

        mysql_free_result(result);
        mysql_close(mysql);
        mysql_library_end();
        return 0;
    }

编译：gcc mys.c -lmysqlclient  -o mys

执行：./mys

 2.C语言动态插入数据至数据库。

实现代码：

int insert(MYSQL *p)
{    
    int no,age;
    char name[20];
    printf("input no,name,age用换行符分隔\n");//用逗号分隔会产生bug,经研究发现是CHAR类型和VARCHAR类型之间的问题，因为我的数据库字段为VARCHAR类型，所以更换为换行符分隔。
    scanf("%d\n%s\n%d",&no,name,&age);
    char *sqls="insert into children values(%d,'%s',%d)";//SQL语句中实现变量替换，需要借助sprintf函数实现，这个是核心。
    char sqlsbuf[500];
    sprintf(sqlsbuf,sqls,no,name,age);
    mysql_real_query(p,sqlsbuf,strlen(sqlsbuf));
    return 0;
}

完整代码：（copy代码运行时，需要修改#define DBPASS ***等）

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

#define DBHOST      "localhost"
#define DBUSER      "root"
#define DBPASS      "***" //输入数据库密码
#define DBPORT      3306
#define DBNAME      "test"
#define DBSOCK      NULL //"/var/lib/mysql/mysql.sock"
#def    ine DBPCNT      0
    
int insert(MYSQL *p)
{    
    
    
    int no,age;
    char name[20];
    printf("input no,name,age用换行符分隔\n");
    scanf("%d\n%s\n%d",&no,name,&age);
    char *sqls="insert into children values(%d,'%s',%d)";
    char sqlsbuf[500];
    sprintf(sqlsbuf,sqls,no,name,age);
    mysql_real_query(p,sqlsbuf,strlen(sqlsbuf));
    return 0;
}
    /*int delete(MYSQL *p)//删除数据库中的一行数据
    {    char name[20];
        printf("delet one row data,输入name:\n");
        scanf("%s",name);
        char *sqls="delete from children where fname='%s'";
        char sqlsbuf[500];
        sprintf(sqlsbuf,sqls,name);
        mysql_real_query(p,sqlsbuf,strlen(sqlsbuf));
        return 0;
    }*/
    int main()
    {
        MYSQL_RES *result;
        MYSQL_ROW row;
        MYSQL_FIELD *field;
        unsigned int num_fields;
        unsigned int i;
        const char *pStatement = "select * from children";
        mysql_library_init(0, NULL, NULL);
        MYSQL *mysql = mysql_init(NULL);
        unsigned int timeout = 3000;
        mysql_options(mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);

        if (mysql_real_connect(mysql, DBHOST, DBUSER, DBPASS, DBNAME, DBPORT, DBSOCK, DBPCNT) == NULL)
        {
            printf("connect failed: %s\n", mysql_error(mysql));
            mysql_close(mysql);
            mysql_library_end();
            return 0;
        }

        printf("connect succssfully\n");
    insert(mysql);
//    delete(mysql);
        if (0 != mysql_real_query(mysql, pStatement, strlen(pStatement)))
        {
            printf("query failed: %s\n", mysql_error(mysql));
            mysql_close(mysql);
            mysql_library_end();
            return 0;
        }

        result = mysql_store_result(mysql);

        if (result == NULL)
        {
            printf("fetch result failed: %s\n", mysql_error(mysql));
            mysql_close(mysql);
            mysql_library_end();
            return 0;
        }

        num_fields = mysql_num_fields(result);
        printf("numbers of result: %d\n", num_fields);

        while (NULL != (field = mysql_fetch_field(result)))
        {
            printf("field name: %s\n", field->name);
        }

        while (NULL != (row = mysql_fetch_row(result)))
        {
            unsigned long *lengths;
            lengths = mysql_fetch_lengths(result);

            for (i = 0; i < num_fields; i++)
            {
                printf("{%.*s} ", (int) lengths[i], row[i] ? row[i] : "NULL");
            }

            printf("\n");
        }

        mysql_free_result(result);
        mysql_close(mysql);
        mysql_library_end();
        return 0;
    }

插入数据：14，liuqiangdong,99

执行：./mys1

 3.实现复杂的循环插入，删除等。

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

#define DBHOST "localhost"
#define DBUSER "root"
#define DBPASS "***" //输入数据库密码
#define DBPORT 3306
#define DBNAME "test"
#define DBSOCK NULL //"/var/lib/mysql/mysql.sock"
#define DBPCNT 0

MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD *field;
MYSQL *mysql,*a;

void menu() //显示界面
{
printf("1.返回数据\n");
printf("2.插入数据\n");
printf("3.删除数据\n");
printf("4.退出系统\n");
printf("5.显示主界面\n");
}
void loop(MYSQL *p)
{
int flag=1;
menu();
while(flag)
{
printf("请输入功能标号***\n");
scanf("%d",&flag);
printf("flag=%d\n",flag);
switch(flag){
case 1:{get_data(p);break;}
case 2:{insert(p);printf("insert done!\n");break;}
case 3:{delete(p);printf("delete done!\n");break;}
//case 4:{quit(p);}
case 5:{menu();break;}
default:{break;}
}
if(flag==4)
{
quit();
break;
}
else
continue;
}

}
int insert(MYSQL *p)//向数据库中插入数据
{
init_connect(p);
int no,age;
char name[20];
printf("插入数据 no,name,age请用换行符分隔\n");
scanf("%d\n%s\n%d",&no,name,&age);
char *sqls="insert into children values(%d,'%s',%d)";
char sqlsbuf[500];
sprintf(sqlsbuf,sqls,no,name,age);
if(0!=mysql_real_query(p,sqlsbuf,strlen(sqlsbuf)))
{
printf("insert query failed: %s\n", mysql_error(p));
mysql_close(p);
mysql_library_end();
return 0;
}
return 0;
}
int delete(MYSQL *p)//删除数据库中的一行数据
{
init_connect(p);
char name[20];
printf("删除数据delet one row data,请输入要删除的名字name:\n");
scanf("%s",name);
char *sqls="delete from children where fname='%s'";
char sqlsbuf[500];
sprintf(sqlsbuf,sqls,name);
if(0!=mysql_real_query(p,sqlsbuf,strlen(sqlsbuf)))
{
printf("delete query failed: %s\n", mysql_error(p));
mysql_close(p);
mysql_library_end();
return 0;
}
return 0;
}
int get_data(MYSQL *p)
{
const char *pStatement = "select * from children";
if(0!=mysql_real_query(p,pStatement,strlen(pStatement)))
{
printf("delete query failed: %s\n", mysql_error(p));
mysql_close(p);
mysql_library_end();
return 0;
}
unsigned int num_fields;
unsigned int i;
result = mysql_store_result(p);
if (result == NULL)
{
printf("fetch result failed: %s\n", mysql_error(p));
mysql_close(p);
mysql_library_end();
return 0;
}
num_fields = mysql_num_fields(result);
printf("numbers of result: %d\n", num_fields);
while (NULL != (field = mysql_fetch_field(result)))
{
printf("field name: %s\n", field->name);
}
while (NULL != (row = mysql_fetch_row(result)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(result);

for (i = 0; i < num_fields; i++)
{
printf("{%.*s} ", (int) lengths[i], row[i] ? row[i] : "NULL");
}

printf("\n");
}
return 0;
}
int init_connect()
{
mysql=mysql_init(NULL);
mysql_library_init(0, NULL, NULL);
printf("执行数据库初始化\n");
unsigned int timeout = 3000;
mysql_options(mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);
if (mysql_real_connect(mysql, DBHOST, DBUSER, DBPASS, DBNAME, DBPORT, DBSOCK, DBPCNT) == NULL)
{
printf("connect failed: %s\n", mysql_error(mysql));
mysql_close(mysql);
mysql_library_end();
return 0;
}
printf("connect succssfully\n");
return 0;

}
int quit()
{
mysql_free_result(result);
mysql_close(mysql);
mysql_library_end();
printf("-----END-----\n");
}

int main()
{

init_connect();
loop(mysql);
return 0;
}

 

 执行./mys2