C语言操作mysql

 

php中 mysqli, pdo 可以用 mysqlnd 或 libmysqlclient 实现

前者 从 php 5.3.0起已内置到php中, 并且支持更多的特性，推荐用 mysqlnd

 

mysqlnd ， libmysqlclient 对比：
http://php.net/manual/en/mysqlinfo.library.choosing.php

 

mysqlnd 目前是php源码的一部分

http://php.net/manual/en/intro.mysqlnd.php

 

php编译参数：

// Recommended, compiles with mysqlnd
$ ./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd

// Alternatively recommended, compiles with mysqlnd as of PHP 5.4
$ ./configure --with-mysqli --with-pdo-mysql --with-mysql

// Not recommended, compiles with libmysqlclient
$ ./configure --with-mysqli=/path/to/mysql_config --with-pdo-mysql=/path/to/mysql_config --with-mysql=/path/to/mysql_config

 

环境准备：

1、安装 libmysqlclient

http://cdn.mysql.com/Downloads/Connector-C/mysql-connector-c-6.0.2.tar.gz

1. Change location to the top-level directory of the source distribution.

2. Generate the Makefile:

   shell> cmake -G "Unix Makefiles"
   

   Or, for a Debug build:

   shell> cmake -G "Unix Makefiles" -DCMAKE_BUILD_TYPE=Debug
   

   By default, the installation location for Connector/C is /usr/local/mysql. To change this location, use theCMAKE_INSTALL_PREFIX option to specify a different directory when generating the Makefile. For example:

   shell> cmake -G "Unix Makefiles" -DCMAKE_INSTALL_PREFIX=/opt/local/mysql
   

   For other CMake options that you might find useful, see Other Connector/C Build Options.

3. Build the project:

   shell> make
   

4. As root, install the Connector/C headers, libraries, and utilities:

   root-shell> make install

 示例代码：

//main.c
//gcc main.c -o test -lmysqlclient

// @link http://dev.mysql.com/doc/refman/5.6/en/c-api-function-overview.htm

// libmysqlclient library

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

MYSQL *get_conn()
{
    //连接配置
    char *host = "127.0.0.1";
    char *user = "root";
    char *passwd = "";
    char *db = "test";
    int  port = 3306;
    my_bool reconnect = 1;
    
    MYSQL *my_con = (MYSQL *)malloc( sizeof(MYSQL) ); //数据库连接句柄

    //连接数据库
    mysql_init(my_con); 
    
    mysql_options(my_con, MYSQL_OPT_RECONNECT, &reconnect);
    mysql_real_connect(my_con, host, user, passwd, db, port, NULL, CLIENT_FOUND_ROWS);

    mysql_query(my_con, "set names utf8");
    
    return my_con;
}

/**
 * 释放空间，关闭连接
 * 
 * @param mysql
 * @return 
 */
void free_conn(MYSQL *mysql)
{
    mysql_close(mysql);
    free(mysql);
}

//发生错误时，输出错误信息，关闭连接，退出程序
void error_quit(const char *str, MYSQL *connection)
{
    fprintf(stderr, "%s\n errno: %d\n error:%s\n sqlstat:%s\n",
            str, mysql_errno(connection),
            mysql_error(connection),
            mysql_sqlstate(connection));

    if( connection != NULL )
    {
        mysql_close(connection);
    }
    
    free(connection);
    
    exit(EXIT_FAILURE);
}

void insert(MYSQL *my_con)
{
    int res;
    
    res = mysql_query(my_con, "INSERT INTO test(fid) VALUES(null)");
    if( res != 0 )
    {
        error_quit("Select fail", my_con);
    }
    
    printf("affected rows:%d \n", mysql_affected_rows(my_con));
    printf("last insertId :%d \n", mysql_insert_id(my_con));
    
}

void update(MYSQL *my_con)
{
    int res;
    
    res = mysql_query(my_con, "UPDATE test SET FScore=119.10");
    if( res != 0 )
    {
        error_quit("Select fail", my_con);
    }
    
    printf("affected rows:%d \n", mysql_affected_rows(my_con));
    
}

void delete(MYSQL *my_con)
{
    int res;
    
    res = mysql_query(my_con, "DELETE FROM test WHERE FID=31");
    if( res != 0 )
    {
        error_quit("Select fail", my_con);
    }
    
    printf("affected rows:%d \n", mysql_affected_rows(my_con));
    
}

void query(MYSQL *my_con)
{
    MYSQL_RES   *my_res;    //查询结果
    MYSQL_FIELD *my_field;  //结果中字段信息
    MYSQL_ROW    my_row;    //结果中数据信息
    
    unsigned long *lengths;
    int cols, res, i;
    
    //获取整个表的内容
    
    res = mysql_query(my_con, "SELECT * FROM test LIMIT 5");
    if( res != 0 )
    {
        error_quit("Select fail", my_con);
    }        
   
    /*
    mysql_query , mysql_real_query 区别

    While a connection is active, the client may send SQL statements to the server using mysql_query() or mysql_real_query(). 
    The difference between the two is that mysql_query() expects the query to be specified as a null-terminated string whereas mysql_real_query() expects a counted string. 
    If the string contains binary data (which may include null bytes), you must use mysql_real_query().     
     
     */
    
    //从服务端取回结果 mysql_store_result 会把数据全部拉取到客户端, mysql_use_result() 则不会
    my_res = mysql_store_result(my_con); // A MYSQL_RES result structure with the results. NULL (0) if an error occurred or has not result like delete
    if( NULL == my_res ) //可以通过返回值来判断是否是 select 
    {
        error_quit("Get result fail", my_con);
    }

    // mysql_row_seek(), mysql_data_seek() , mysql_num_rows 只有在用mysql_store_result 才可以使用
    printf("num rows:%d \n", mysql_num_rows(my_res));
   
    //获取表的列数
    cols = mysql_num_fields(my_res);
    printf("num cols:%d \n", cols);
    
    
    
    //获取字段信息
    my_field = mysql_fetch_fields(my_res);
    
    for(i=0; i<cols; i++)
    {
        printf("%s\t", my_field[i].name);
    }
    printf("\n");
    
    for(i=0; i<cols; i++)
    {
        //字段类型
        printf("%d\t", my_field[i].type);
    }
    
    printf("\n");
    
    //输出执行结果
    while( my_row = mysql_fetch_row(my_res) )
    {
        for(i=0; i<cols; i++)
        {
            //数据长度
            lengths = mysql_fetch_lengths(my_res);
            printf("%s(%lu)\t", my_row[i], lengths[i]);
        }
        
        printf("\n");
    }

    mysql_free_result(my_res);
    
}

void status(MYSQL *my_con)
{
    printf("mysql_get_server_info: %s \n", mysql_get_server_info(my_con));
    printf("mysql_stat: %s \n", mysql_stat(my_con));
    printf("mysql_get_proto_info: %u \n", mysql_get_proto_info(my_con));
    
}

int main(int argc, char *argv[]) 
{
    //连接数据库
    MYSQL *my_con = get_conn();
    if( NULL == my_con ) 
    {
        error_quit("Connection fail", my_con);
    }
    
    printf("Connection success \n");

    status(my_con);
  
    insert(my_con);
    
    delete(my_con);
    
    update(my_con);

    //select
    query(my_con);
    
    // free the memory
    free_conn(my_con);

    return EXIT_SUCCESS;
}

 

test.sql

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50524
Source Host           : 127.0.0.1:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50524
File Encoding         : 936

Date: 2015-09-16 15:02:57
*/

create DATABASE test;

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `test`
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `FID` int(11) NOT NULL AUTO_INCREMENT,
  `FTableName` char(60) NOT NULL DEFAULT '',
  `FFieldName` char(30) NOT NULL DEFAULT '',
  `FTemplate` char(30) NOT NULL DEFAULT '',
  `FScore` decimal(5,2) NOT NULL DEFAULT '0.00' COMMENT 'ио╩§',
  PRIMARY KEY (`FID`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO test VALUES ('1', 'A', 'xx', 'TEMPALTE 1', '119.10');
INSERT INTO test VALUES ('2', 'B', 'jj', 'TEMPALTE 1', '119.10');
INSERT INTO test VALUES ('3', 'D', 'k', 'TEMPALTE 1', '119.10');
INSERT INTO test VALUES ('4', 'C', 'm', 'TEMPALTE 1', '119.10');
INSERT INTO test VALUES ('5', 'B', 'y', 'TEMPALTE 2', '119.10');
INSERT INTO test VALUES ('6', 'D', 'k', 'TEMPALTE 2', '119.10');
INSERT INTO test VALUES ('7', 'C', 'm', 'TEMPALTE 2', '119.10');
INSERT INTO test VALUES ('8', 'E', 'n', 'TEMPALTE 2', '119.10');
INSERT INTO test VALUES ('9', 'D', 'z', 'TEMPALTE 3', '119.10');
INSERT INTO test VALUES ('10', 'E', 'n', 'TEMPALTE 3', '119.10');
INSERT INTO test VALUES ('11', 'A', 'x', 'TEMPALTE 2', '119.10');
INSERT INTO test VALUES ('12', 'A', 'x', 'TEMPALTE 3', '119.10');
INSERT INTO test VALUES ('13', 'A', 'x', 'TEMPALTE 4', '119.10');
INSERT INTO test VALUES ('14', 'E', 'p', 'TEMPALTE 4', '119.10');
INSERT INTO test VALUES ('15', 'A', 'x', 'TEMPALTE 5', '119.10');
INSERT INTO test VALUES ('16', 'C', 'q', 'TEMPALTE 5', '119.10');
INSERT INTO test VALUES ('17', '', '', '', '119.10');

 

 

参考文档：http://dev.mysql.com/doc/refman/5.6/en/c-api-function-overview.html

 http://www.linuxfocus.org/ChineseGB/September2003/article304.shtml#304lfindex3