MySQL C库的使用

1.初始化和清理

MYSQL mysql;            //数据库句柄
MYSQL_RES* res;         //查询结果集
MYSQL_ROW row;          //记录结构体(就是一行数据)
mysql_init(&mysql);      //初始化数据库(单线程模式mysql_init自动调用，线程不安全)
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk");  //设置字符编码
int ret = mysql_options(&mysql, MYSQL_OPT_CONNECT_TIMEOUT, &to); //超时设置，失败返回非0 
int reconn = 1;                                                  
ret = mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconn);   //设置自动重连
mysql_free_result(res);     //关闭结果集
mysql_close(&mysql);        //关闭数据库连接
mysql_library_end();

2.连接与查询数据库

mysql_real_connect(&mysql,"127.0.0.1","root","root123456","hui",3306,NULL,0) //失败返回NULL
const char* sql = "select * from students";         //sql语句
ret = mysql_real_query(&mysql, sql,strlen(sql));    //查询
res = mysql_store_result(&mysql);                   //获取查询结果集
int field_num = mysql_num_fields(res);              //获取表字段的数量
MYSQL_FIELD* fields = mysql_fetch_fields(res);      //获取表字段名数组
MYSQL_ROW row = mysql_fetch_row(res)；               //从结果集中获取一行数据
unsigned long* len_ptr = mysql_fetch_lengths(res);//从结果集中获取一行字段数据的长度数组

sample

#include <mysql.h>
#include <iostream>
#include <stdio.h>
int main()
{
    MYSQL mysql;            //数据库句柄
    MYSQL_RES* res;         //查询结果集
    MYSQL_ROW row;          //记录结构体(就是一行数据)

    mysql_init(&mysql);      //初始化数据库(单线程模式mysql_init自动调用，线程不安全)

    /// <summary>
    /// mysql_options设置请在连接前设置
    /// </summary>
    mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk");  //设置字符编码
    int to = 3;

    int ret = mysql_options(&mysql, MYSQL_OPT_CONNECT_TIMEOUT, &to); //超时设置，失败返回非0 
    if (ret != int(0))
    {
        std::cout << "设置超时失败" << std::endl;
    }

    int reconn = 1;                                                  
    ret = mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconn);   //设置自动重连
    if (ret != 0)
    {
        std::cout << "自动重连设置失败" << std::endl;
    }
     /// <summary>  
    ///连接数据库
    /// </summary>
    if (mysql_real_connect(&mysql,"127.0.0.1","root","root123456","hui",3306,NULL,0)==NULL)
    {
        printf("错误原因：%s\n", mysql_error(&mysql));
        printf("连接失败");
        exit(-1);
    }

    /// <summary>
    /// 查询数据；获取结果集
    /// </summary>
    const char* sql = "select * from students";
    ret = mysql_real_query(&mysql, sql,strlen(sql));
    if (ret!= 0)
    {
        printf("ret:%s\n", mysql_error(&mysql));
    }
    res = mysql_store_result(&mysql);   
        
    //获取表字段的数量
    int field_num = mysql_num_fields(res);
    std::cout << "数据库表字段有" << field_num << "个" << std::endl;

    MYSQL_FIELD* fields = mysql_fetch_fields(res); //获取表字段名数组
    for (int i = 0; i < field_num; i++)
    {
        printf("Field %u is %s\n", i, fields[i].name);
    }
    while (row = mysql_fetch_row(res))                               
    {
        unsigned long* len_ptr = mysql_fetch_lengths(res);                 //获取结果集的长度要在mysql_fetch_row后
        for (int i = 0;i<field_num;++i)
        {
            printf("字段%d的长度为:%d,字段名为:%s,数据为:%s\n",i, len_ptr[i], fields[i].name ,row[i]);
        }
    }
    /// <summary>
    ///释放结果集；关闭数据库
    /// </summary>
    mysql_free_result(res);               
    mysql_close(&mysql);       
    mysql_library_end();
    system("pause");
    return 0;
}