MYSQL的C语言预处理接口示例

最新推荐文章于 2021-08-09 19:01:24 发布

代码穿越

最新推荐文章于 2021-08-09 19:01:24 发布

阅读量272

点赞数 1

分类专栏： Linux C 文章标签： mysql c++

本文链接：https://blog.youkuaiyun.com/qq_38697681/article/details/115273122

版权

Linux C 专栏收录该内容

7 篇文章

订阅专栏

该博客展示了使用MySQL C API进行数据插入、查询和条件查询的示例代码，包括了连接数据库、预处理语句、参数绑定及执行过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MYSQL的建表语句

DROP TABLE IF EXISTS engineer.test_tbl;

CREATE TABLE engineer.test_tbl(
   `Row1` INT NOT NULL,
   `Row2` VARCHAR(100) NOT NULL,
   `Row3` float NOT NULL,
   `Row4` double NOT NULL,
   `Row5` timestamp NOT NULL default CURRENT_TIMESTAMP,
   PRIMARY KEY ( `Row1` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

all: insert query query2

insert:insert.o
	gcc -o insert insert.o -lmysqlclient -L/usr/local/mysql/lib 
	rm insert.o

insert.o: insert.c
	gcc -c insert.c -I/usr/local/mysql/include -o insert.o

query:query.o
	gcc -o query query.o -lmysqlclient -L/usr/local/mysql/lib 
	rm query.o

query.o: query.c
	gcc -c query.c -I/usr/local/mysql/include -o query.o

query2:query2.o
	gcc -o query2 query2.o -lmysqlclient -L/usr/local/mysql/lib 
	rm query2.o

query2.o: query2.c
	gcc -c query2.c -I/usr/local/mysql/include -o query2.o

clean:
	rm insert query query2

客户端到服务端

#include <stdio.h>
#include <mysql.h>

#define PRINT_MYSQL_ERR(fun_name) printf("%s fail:%s\n",fun_name,mysql_error(conn));


//sql语句自动处理脚本

int main()
{
    MYSQL *conn = mysql_init(NULL);

    if(!mysql_real_connect(conn,"192.168.234.129","root","123456","engineer",3306,NULL,0))
    {
        PRINT_MYSQL_ERR("mysql_real_connect")
        return -1;
    }
    else
    {
        printf("engineer connect success\n");
    }

    MYSQL_STMT *stmt=mysql_stmt_init(conn);

    char *insert ="insert into test_tbl(Row1,Row2,Row3,Row4) values(?,?,?,?);";

    if(mysql_stmt_prepare(stmt,insert,strlen(insert)))
    {
        PRINT_MYSQL_ERR("mysql_stmt_prepare")
        return -1;
    }

    int     row1=1; 
    char    row2[20]="abcd"; 
    float   row3=1.1;
    double  row4=1.2;

    //printf("int char[20] float double: ");

    //scanf("%d %s %lf %lf", &row1, row2, &row3, &row4);

    MYSQL_BIND params[4];
    
    memset(params, 0, sizeof(params));
    
    params[0].buffer_type =MYSQL_TYPE_LONG;

    params[0].buffer = &row1;

    params[0].buffer_length = sizeof(row1);

    params[1].buffer_type =MYSQL_TYPE_STRING;

    params[1].buffer =row2;

    params[1].buffer_length = strlen(row2);

    params[2].buffer_type =MYSQL_TYPE_FLOAT;

    params[2].buffer =&row3;

    params[2].buffer_length = sizeof(row3);

    params[3].buffer_type =MYSQL_TYPE_DOUBLE;

    params[3].buffer =&row4;

    params[3].buffer_length = sizeof(row4);

    if(mysql_stmt_bind_param(stmt, params))
    {
        PRINT_MYSQL_ERR("mysql_stmt_bind_param")
        return -1;
    }

    if(mysql_stmt_execute(stmt))
    {
        PRINT_MYSQL_ERR("mysql_stmt_execute")
        return -1;
    }

    mysql_stmt_close(stmt);

    mysql_close(conn);

    return 0;
}

服务端到客户端

#include <stdio.h>
#include <mysql.h>

#define PRINT_MYSQL_ERR(fun_name) printf("%s fail:%s\n",fun_name,mysql_error(conn));

//参考网址 https://dev.mysql.com/doc/c-api/8.0/en/mysql-stmt-fetch.html

int main()
{
    MYSQL *conn = mysql_init(NULL);

    //MYSQL_RES   *prepare_meta_result;

    if(!mysql_real_connect(conn,"192.168.234.129","root","123456","engineer",3306,NULL,0))
    {
        PRINT_MYSQL_ERR("mysql_real_connect")
        return -1;
    }
    else
    {
        printf("engineer connect success\n");
    }

    MYSQL_STMT *stmt_i=mysql_stmt_init(conn);

    char *query="select Row1,Row2,Row3,Row4,Row5 from test_tbl;";
    
    if(mysql_stmt_prepare(stmt_i,query,strlen(query)))
    {
        PRINT_MYSQL_ERR("mysql_stmt_prepare_i")
        return -1;
    }

    int     row1_i; 
    char    row2_i[20]; 
    float   row3_i;
    double  row4_i;
    MYSQL_TIME    row5_i;

    MYSQL_BIND params[5];
    
    memset(params, 0, sizeof(params));
    
    params[0].buffer_type =MYSQL_TYPE_LONG;

    params[0].buffer = &row1_i;

    params[0].buffer_length = sizeof(row1_i);

    params[1].buffer_type =MYSQL_TYPE_STRING;

    params[1].buffer =(char *)row2_i;

    params[1].buffer_length = sizeof(row2_i);

    params[2].buffer_type =MYSQL_TYPE_FLOAT;

    params[2].buffer =&row3_i;

    params[2].buffer_length = sizeof(row3_i);

    params[3].buffer_type =MYSQL_TYPE_DOUBLE;

    params[3].buffer =&row4_i;

    params[3].buffer_length = sizeof(row4_i);

    params[4].buffer_type =MYSQL_TYPE_TIMESTAMP;

    params[4].buffer =(char *)&row5_i;

    params[4].buffer_length = sizeof(row5_i);

    if(mysql_stmt_bind_result(stmt_i,params))
    {
        PRINT_MYSQL_ERR("mysql_stmt_bind_param_i")
        return -1;
    }
    
    if(mysql_stmt_execute(stmt_i))
    {
        PRINT_MYSQL_ERR("mysql_stmt_execute_i")
        return -1;
    }

    if(mysql_stmt_store_result(stmt_i))
    {
        PRINT_MYSQL_ERR("mysql_stmt_store_result_i")
        return -1;
    }

    while(mysql_stmt_fetch(stmt_i)==0)
    {
        printf("%d %s %f %f %04d-%02d-%02d %02d:%02d:%02d\n", row1_i, row2_i, row3_i, row4_i, row5_i.year, row5_i.month, row5_i.day,row5_i.hour, row5_i.minute, row5_i.second);  
    }

    mysql_stmt_close(stmt_i);

    mysql_close(conn);

    return 0;
}

客户端到服务端再到客户端

#include <stdio.h>
#include <mysql.h>

#define PRINT_MYSQL_ERR(fun_name) printf("%s fail:%s\n",fun_name,mysql_error(conn));

//参考网址 https://dev.mysql.com/doc/c-api/8.0/en/mysql-stmt-fetch.html

int main()
{
    MYSQL *conn = mysql_init(NULL);

    //MYSQL_RES   *prepare_meta_result;

    if(!mysql_real_connect(conn,"192.168.234.129","root","123456","engineer",3306,NULL,0))
    {
        PRINT_MYSQL_ERR("mysql_real_connect")
        return -1;
    }
    else
    {
        printf("engineer connect success\n");
    }

    MYSQL_STMT *stmt_i=mysql_stmt_init(conn);

    char *query="select Row1,Row2,Row3,Row4,Row5 from test_tbl where Row1=?;";
    
    if(mysql_stmt_prepare(stmt_i,query,strlen(query)))
    {
        PRINT_MYSQL_ERR("mysql_stmt_prepare_i")
        return -1;
    }

    int     row1_i; 
    char    row2_i[20]; 
    float   row3_i;
    double  row4_i;
    MYSQL_TIME    row5_i;

    printf("输入主键:");
    scanf("%d", &row1_i);

    MYSQL_BIND params[5];
    
    memset(params, 0, sizeof(params));
    
    params[0].buffer_type =MYSQL_TYPE_LONG;

    params[0].buffer = &row1_i;

    params[0].buffer_length = sizeof(row1_i);

    params[1].buffer_type =MYSQL_TYPE_STRING;

    params[1].buffer =(char *)row2_i;

    params[1].buffer_length = sizeof(row2_i);

    params[2].buffer_type =MYSQL_TYPE_FLOAT;

    params[2].buffer =&row3_i;

    params[2].buffer_length = sizeof(row3_i);

    params[3].buffer_type =MYSQL_TYPE_DOUBLE;

    params[3].buffer =&row4_i;

    params[3].buffer_length = sizeof(row4_i);

    params[4].buffer_type =MYSQL_TYPE_TIMESTAMP;

    params[4].buffer =(char *)&row5_i;

    params[4].buffer_length = sizeof(row5_i);

    if(mysql_stmt_bind_param(stmt_i,params))
    {
        PRINT_MYSQL_ERR("mysqll_stmt_bind_para")
        return -1;
    }

    if(mysql_stmt_bind_result(stmt_i,params))
    {
        PRINT_MYSQL_ERR("mysql_stmt_bind_param_i")
        return -1;
    }
    
    if(mysql_stmt_execute(stmt_i))
    {
        PRINT_MYSQL_ERR("mysql_stmt_execute_i")
        return -1;
    }

    if(mysql_stmt_store_result(stmt_i))
    {
        PRINT_MYSQL_ERR("mysql_stmt_store_result_i")
        return -1;
    }

    while(mysql_stmt_fetch(stmt_i)==0)
    {
        printf("%d %s %f %f %04d-%02d-%02d %02d:%02d:%02d\n", row1_i, row2_i, row3_i, row4_i, row5_i.year, row5_i.month, row5_i.day,row5_i.hour, row5_i.minute, row5_i.second);  
    }

    mysql_stmt_close(stmt_i);

    mysql_close(conn);

    return 0;
}