TypeCodes

Linux c语言连接MySQL数据库实例

工作上自己在Linux C/C++开发时,用的都是Oracle数据库,毕竟企业级应用追求稳定性好、安全可靠。业余时间做了一些WEB开发,接触到MySQL数据库比较多,也比较喜欢开源的MySQL。之前都是用PHP连接MySQL数据库,这里自己用C语言连接MySQL,执行一些简单的连接、查询操作、异常处理等操作。

1 测试程序

代码比较简单,先把MySQL数据库安装后得到的头文件mysql.h包含进来,然后编译时需要用到MySQL对外提供的API接口。查阅了下MySQL官方文档,MySQL对C语言提供了一个静态库libmysqlclient.a和一个动态库libmysqlclient.so接口文件,本文选择使用动态库libmysqlclient.so。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
/** 
 * @FileName    linux_c_mysql.c
 * @Describe    A simple example for operating mysql using c programming in linux system.
 * @Author      vfhky 2015.12.29 15:40 https://typecodes.com/cseries/linuxconnectmysql.html
 * @Compile     gcc -I/usr/local/mysql/include  -L/usr/local/mysql/lib -lmysqlclient  linux_c_mysql.c -o linux_c_mysql
 * @Reference   http://dev.mysql.com/doc/refman/5.7/en/c-api-function-overview.html
 */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>


//MySQL connection.
MYSQL *pMysqlConn;
//result set.
MYSQL_RES *pMysqlRes;
//an instance of a row from the result.
MYSQL_ROW MysqlRow;

#define MAX_BUF_SIZE 1024

const char *pHostName = "localhost";    //or set the remote ip address.
const char *pUserName = "root";
const char *pPassword = "password";
const char *pDbName = "typecodes";      //database name of my typecho blog.
const unsigned int iDbPort = 3306;

/* print the last error message. */
void finish_with_error(const char *msg)
{
    if( msg )
        printf("Error message[%s]: [%s].\n", msg, mysql_error(pMysqlConn) );
    else
        printf( "Error message[%s].\n", mysql_error(pMysqlConn) );
    mysql_close(pMysqlConn);
    /**
     * When errors such as MySQL server has gone away hapended, the program should be end immeidately.
     * Otherwise, we should encounter the error below.
     * *** Error in `./linux_c_mysql': double free or corruption (!prev): 0x0000000001223560 ***
     */
    exit(-1);
}

/**
 * mysql_query() cannot be used for statements that contain binary data; you must use mysql_real_query() instead. 
 * Binary data may contain the “\0” character, which mysql_query() interprets as the end of the statement string.
 * In addition, mysql_real_query() is faster than mysql_query() because it does not call strlen() on the statement string. 
 */
int executesql( const char * sql )
{
    if( mysql_real_query( pMysqlConn, sql, strlen(sql) ) )
        return -1;
    return 0;
}

/* init the mysql connection. */
int init_mysql()
{
    pMysqlConn = mysql_init(NULL);
    if( pMysqlConn == NULL ) 
        return -1;
    if( !mysql_real_connect( pMysqlConn, pHostName, pUserName, pPassword, pDbName, iDbPort, NULL, 0 ) )
        return -2;
    //set the language for the results excuted.
    if( executesql("set names utf8") )
        return -3;
    return 0;
}


int main( int argc, char ** argv )
{
    int x=0, i=0;
    printf( "A example for connecting mysql using c program in linux.\n" );
    if( init_mysql() )
        finish_with_error(NULL);

    char cSqlData[MAX_BUF_SIZE] = {0x00};

    memcpy( cSqlData, "SELECT * FROM typecodes_users", strlen("SELECT * FROM vfhkytpvfhky_users") );
    if( executesql( cSqlData ) )
        finish_with_error(NULL);

    //pMysqlRes = mysql_use_result(pMysqlConn);
    pMysqlRes = mysql_store_result(pMysqlConn);

    int iNum_rows = mysql_num_rows(pMysqlRes);
    int iNum_fields = mysql_num_fields(pMysqlRes);

    printf( "Table have [%d] records containing [%d] fields in each one.\n", iNum_rows, iNum_fields );
    printf( "+------------------------------------------------+\n" );

    while( ( MysqlRow = mysql_fetch_row(pMysqlRes) ) )
    {
        i = 0;
        while( i < iNum_fields )
        {
            printf( "| %s", MysqlRow[i]?MysqlRow[i] : "NULL" );
            x = strlen(MysqlRow[i++]);
            for( ; x < 21; x++ )
                printf(" ");
        }
        printf("|\n");
    }

    printf( "+------------------------------------------------+\n" );
    mysql_free_result(pMysqlRes);
    mysql_close(pMysqlConn);
    return 0;
}

2 gcc编译方法

由于之前在搭建MySQL服务的时候,已经把MySQL的动态库路径通过ldconfig命令添加到了Linux系统动态库搜索路径当中了,所以这里直接生成可执行文件就行了。当然,还可以像上一篇文章中的其它3种操作方法,把MySQL动态库mysqlclient.so链接进来最终生成可执行文件。

用下面这条命令编译即可,其中-I参数表示MySQL数据库头文件路径,-L参数表示MySQL数据库的动态库路径。

[root@typecodes ~]# gcc -I/usr/local/mysql/include  -L/usr/local/mysql/lib -lmysqlclient  linux_c_mysql.c -o linux_c_mysql
[root@typecodes ~]# ldd linux_c_mysql

ldd命令查看可执行文件所依赖的动态库

3 执行结果

下图是程序执行获取的之前Typecho博客数据库typecodes中的用户表typecodes_users中的数据:

ldd命令查看可执行文件所依赖的动态库

Comments »