linux下c操作mysql之增删改查

2019独角兽企业重金招聘Python工程师标准>>>

书接上文,继续进行linux 下c操作mysql。

1.创建表/插入数据

mysql> desc children
    -> ;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| childno | int(11)     | NO   | PRI | NULL    | auto_increment |
| fname   | varchar(32) | YES  |     | NULL    |                |
| age     | int(11)     | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from children;
+---------+---------+------+
| childno | fname   | age  |
+---------+---------+------+
|       1 | Jenny   |   21 |
|       2 | Andrew  |   17 |
|       3 | Gavin   |    8 |
|       4 | Duncan  |    6 |
|       5 | Emma    |    4 |
|       6 | Alex    |   15 |
|       7 | Adrian  |    9 |
|       8 | jeffsui |    5 |
+---------+---------+------+
8 rows in set (0.00 sec)

2.文件清单

demo.c

/************************************************************
    FileName : demo.c
    FileFunc : C语言接口访问MySQL
    Version  : V0.1
    Author   : JeffSui
    Date     : 2014-02-20
    Descp    : c查询mysql数据库遍历输出
*************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include "/usr/include/mysql/mysql.h"

int main(int argc,char *argv[])
{
        MYSQL my_connection,*conn_ptr;
        MYSQL_RES *res_ptr;
        MYSQL_ROW sqlrow;
        int iRet;
        int iTableRow,iTableCol,i,j;
        char *server = "localhost";
        char *user = "root";
        char *password = "";
        char *database = "csql";
        unsigned int uiTimeOut = 7;//设置连接超时7s

        conn_ptr = mysql_init(&my_connection);//初始化连接句柄
        if( !conn_ptr )
        {
                fprintf(stderr,"mysql_init failed ! \n");
                return EXIT_FAILURE;
        }

        iRet = mysql_options(&my_connection,MYSQL_OPT_CONNECT_TIMEOUT,(const char *)&uiTimeOut);//设置连接超时
        if( iRet )
        {
                fprintf(stderr,"Connection is timeout! \n");
                return EXIT_FAILURE;
  }

        conn_ptr = mysql_real_connect(&my_connection,server,user,password,database,0,NULL,0);//连接数据库
        if( conn_ptr )
        {
                printf("Connection success!\n");

                iRet = mysql_query(&my_connection,"select * from children");//执行SQL语句
                if( iRet )
                {
                        fprintf(stderr,"select error %d: %s !\n",mysql_errno(&my_connection),mysql_error(&my_connection));//>打印错误处理具体信息
                        return EXIT_FAILURE;
                }


                res_ptr = mysql_store_result(&my_connection);//集合
                if( res_ptr )
                {
                        iTableRow = mysql_num_rows(res_ptr);//行
                        iTableCol = mysql_num_fields(res_ptr);//列

                        for(i=0; i<iTableRow; i++)
                        {
                          sqlrow = mysql_fetch_row(res_ptr);
                          for(j=0; j<iTableCol; j++)
                          {
                                printf("%-8s  ",sqlrow[j]);//字符串向左靠，右补空格
                          }
                          printf("\n");

                        }

                        mysql_free_result(res_ptr);//完成对数据的所有操作后,调用此函数来让MySQL库清理它分配的对象
                }
mysql_close(&my_connection);//关闭连接
        }     else
        {

                fprintf(stderr,"Connection failed!\n");
                if( mysql_errno(&my_connection) )
                {
                        fprintf(stderr,"Connection error %d: %s!\n",mysql_errno(&my_connection),mysql_error(&my_connection));
                }
        }

        return EXIT_SUCCESS;

}

insert.c

/******************************************************
    FileName : insert.c
    FileFunc : C语言接口访问MySQL
    Version  : V0.1
    Author   : JeffSui
    Date     : 2014-02-24
    Descp    : c插入mysql数据库
******************************************************/
#include <stdlib.h>
#include <stdio.h>
#include "/usr/include/mysql/mysql.h"
int main() {
        MYSQL *conn_ptr;
        int res;

        conn_ptr = mysql_init(NULL);
        if (!conn_ptr) {
                printf("mysql_init failed\n");
                return EXIT_FAILURE;
        }
        conn_ptr = mysql_real_connect(conn_ptr, "localhost", "root", "", "csql", 0, NULL, 0);
        if (conn_ptr) {
                res = mysql_query(conn_ptr, "insert into children values(null,'jeffsui',5)");   //可以把insert语句替换成delete或者update语句，都一样的
                if(!res){
        printf("Insert %lu rows\n",(unsigned long)mysql_affected_rows(conn_ptr));
}else{
        fprintf(stderr,"Insert error %d: %s\n",mysql_error(conn_ptr),mysql_error(conn_ptr));
}
}else{
        printf("Connection failed!");

}
mysql_close(conn_ptr);
return EXIT_SUCCESS;
}

makefile

#makefile开始
c_mysql_demo:demo.c
        @gcc  -lm -lpthread -ldl -I/usr/include/mysql demo.c -o demo -L/usr/lib64/mysql -lmysqlclient
        @gcc  -lm -lpthread -ldl -I/usr/include/mysql insert.c -o insert -L/usr/lib64/mysql -lmysqlclient
clean:
        @rm -rf demo
        @rm -rf insert
#makefile结束

3.执行make 指令

4.执行目标路径下的 

[root@tf-test c_pro]# ./insert
Insert 1 rows
[root@tf-test c_pro]# ./demo
Connection success!
1         Jenny     21        
2         Andrew    17        
3         Gavin     8         
4         Duncan    6         
5         Emma      4         
6         Alex      15        
7         Adrian    9         
8         jeffsui   5         
9         kelly     22        

转载于:https://my.oschina.net/dlpinghailinfeng/blog/202697