Linux c 操作Mysql简单实例

在大型软件编程中，数据库是必不可少的，通常需要通过各种编程语言访问和操作数据库。本文介绍了在Linux 下使用C语言去操作Mysql数据库的方法。还有很重要的一点是： 在编程中，使用基础API操作数据库会比较复杂，不实用，软件开发者通常会在原有基础上抽象出一组使用更方便的API接口，本文也作了简单的抽象API（函数），如top_XX_XX()函数，仅供学习。

1. 首先在命令行通过SQL语句在mysql数据库中创建一个student表，并插入两条数据（1， zhangsan)和(2, lisi), 过程如下：

$mysql -h 127.0.0.1 -uroot -pmysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || kamailio           || mysql              || opensips           || performance_schema || student            |+--------------------+6 rows in set (0.12 sec)mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || event                     || func                      || general_log               |.....mysql> create table student(id int(4) not null primary key auto_increment, name char(20) not null);Query OK, 0 rows affected (0.52 sec)mysql> desc student;+-------+----------+------+-----+---------+----------------+| Field | Type     | Null | Key | Default | Extra          |+-------+----------+------+-----+---------+----------------+| id    | int(4)   | NO   | PRI | NULL    | auto_increment || name  | char(20) | NO   |     | NULL    |                |+-------+----------+------+-----+---------+----------------+2 rows in set (0.19 sec)mysql> insert into student(id, name) values(1, "zhangsan");Query OK, 1 row affected (0.07 sec)mysql> select * from student;+----+----------+| id | name     |+----+----------+|  1 | zhangsan |+----+----------+mysql> insert into student(name) values("lisi");Query OK, 1 row affected (0.09 sec)mysql> select * from student;+----+----------+| id | name     |+----+----------+|  1 | zhangsan ||  2 | lisi     |+----+----------+2 rows in set (0.00 sec)

2. 实例

Linux C mysql 在线API文档:

http://www.yesky.com/imagesnew/software/mysql/manual_Clients.html

在Linux下使用C语言访问Mysql,需要安装额外的开发包：

sudo apt-get install libmysqlclient15-dev

mysql_test.c：#include <mysql/mysql.h>#include <stdio.h>#include <stdlib.h>#define debug() \    printf("%s, %d\n", __FUNCTION__, __LINE__)#define MAX_QUERY_CMD_LEN 1024MYSQL *conn = NULL;char table_name[] = "student";void top_log(const char *log){    printf("%s\n", log);}MYSQL_RES *top_query_database(MYSQL *conn){    MYSQL_RES *res = NULL;    if (!conn) {        top_log("param is NULL");        return NULL;    }    if (mysql_query(conn, "show databases"))     {        top_log(mysql_error(conn));        return NULL;    }    res = mysql_store_result(conn);    if (!res) {        top_log(mysql_error(conn));        return NULL;    }    return res;}MYSQL_RES *top_query_table(MYSQL *conn){    MYSQL_RES *res = NULL;    if (!conn) {        top_log("param is NULL");        return NULL;    }    if (mysql_query(conn, "show tables"))     {        top_log(mysql_error(conn));        return NULL;    }    res = mysql_store_result(conn);    if (!res) {        top_log(mysql_error(conn));        return NULL;    }    return res;}MYSQL_RES *top_insert_into_table(MYSQL *conn, char *table, char *fields, char *values){    MYSQL_RES *res = NULL;    int len = -1;    char sql_cmd[MAX_QUERY_CMD_LEN];    if (!conn || !table || !fields || !values) {        top_log("param is NULL");        return NULL;    }if (fields) {    len = snprintf(sql_cmd, MAX_QUERY_CMD_LEN, "insert into %s%s values%s", table, fields, values);} else {    len = snprintf(sql_cmd, MAX_QUERY_CMD_LEN, "insert into %s values%s", table, values);}    printf("sql_cmd: %s, len: %d\n", sql_cmd, len);    if (mysql_query(conn, sql_cmd))     {        top_log(mysql_error(conn));        return NULL;    }    res = mysql_store_result(conn);    if (!res) {        top_log(mysql_error(conn));        return NULL;    }    return res;}MYSQL_RES *top_select_from_table(MYSQL *conn, char *table, char *condition){    MYSQL_RES *res = NULL;    int len = -1;    char sql_cmd[MAX_QUERY_CMD_LEN];    if (!conn || !table) {        top_log("param is NULL");        return NULL;    }    len = snprintf(sql_cmd, MAX_QUERY_CMD_LEN, "select * from %s", table);    printf("sql_cmd: %s, len: %d\n", sql_cmd, len);    if (condition) {        snprintf(sql_cmd + len, MAX_QUERY_CMD_LEN, " where %s", condition);    }    printf("sql_cmd: %s, len: %d\n", sql_cmd, len);    if (mysql_query(conn, sql_cmd))     {        top_log(mysql_error(conn));        return NULL;    }    res = mysql_store_result(conn);    if (!res) {        top_log(mysql_error(conn));        return NULL;    }    return res;}int main() {    MYSQL_RES *res;    MYSQL_ROW row;    int ret = -1;    int i, j;    char server[] = "localhost";    char user[] = "root";    char password[] = "hello";    char database[] = "mysql";        conn = mysql_init(NULL);        if (!mysql_real_connect(conn, server,user, password, database, 0, NULL, 0))     {        fprintf(stderr, "%s\n", mysql_error(conn));        exit(1);    }     res = top_query_database(conn);    if (!res) {        printf("error\n");        exit(1);    }    printf("MySQL num_rows: %lu, num_fileds: %d\n",                    (unsigned long)mysql_num_rows(res), mysql_num_fields(res));        while ((row = mysql_fetch_row(res)) != NULL)    {        printf("%s \n", row[0]);    }    /* select db*/    ret = mysql_select_db(conn, "mysql");    if (ret) {        printf("select db error: %s\n", mysql_error(conn));    }    res = top_query_table(conn);    if (!res) {        printf("error\n");        exit(1);    }    printf("MySQL num_rows: %lu, num_fileds: %d\n",                    (unsigned long)mysql_num_rows(res), mysql_num_fields(res));    while ((row = mysql_fetch_row(res)) != NULL)    {// print all tables in mysql database        printf("%s \n", row[0]);    }    mysql_free_result(res);top_insert_into_table(conn, table_name, "(name)", "(\"wangwu\")");    res = top_select_from_table(conn, table_name, NULL);     printf("MySQL num_rows: %lu, num_fileds: %d\n",                    (unsigned long)mysql_num_rows(res), mysql_num_fields(res));        int field_count = res->field_count;    printf("field_count: %d\n", field_count);    while ((row = mysql_fetch_row(res)) != NULL)    {        for(i = 0; i < field_count; i++) {            printf("%s\n", row[i]);        }    }    mysql_free_result(res);    mysql_close(conn);        return 0;}Makefile:CFLAGS=-gall:gcc mysql_test.c -o a.out -lmysqlclient -g

结果：

$ ./a.out 
MySQL num_rows: 6, num_fileds: 1
information_schema 
kamailio 
mysql 
opensips 
performance_schema 
student 
MySQL num_rows: 25, num_fileds: 1
columns_priv 
db 
event 
func 
general_log 
help_category 
help_keyword 
help_relation 
help_topic 
host 
ndb_binlog_index 
plugin 
proc 
procs_priv 
proxies_priv 
servers 
slow_log 
student 
tables_priv 
time_zone 
time_zone_leap_second 
time_zone_name 
time_zone_transition 
time_zone_transition_type 
user 
sql_cmd: insert into student(name) values("wangwu"), len: 42


sql_cmd: select * from student, len: 21
sql_cmd: select * from student, len: 21
MySQL num_rows: 3, num_fileds: 2
field_count: 2
1
zhangsan
2
lisi
3
wangwu