linux下用c操作mysql

linux下c語言操作mysql,首先需要引入"/usr/include/mysql/mysql.h",
不管利用那種語言操作資料庫,都需要先初始化連接；獲得資料庫的連接后就可以對資料庫進行CRUD

dbutils.h文件
#include
#include
#include $$
#include
#include "/usr/include/mysql/mysql.h"

typedef struct _USER
{
int id;
char username[32];
int age;
}USER;

int conn_init(MYSQL *conn);

int isExist(MYSQL *conn, char *table, char *colname, char *value);

int delete(MYSQL *conn, char *table, char *cond);

int user_add(MYSQL *conn, USER *user);

int user_get_all(MYSQL *conn, USER user[]);

int user_delete(MYSQL *conn, int userid);

int user_update(MYSQL *conn, int userid, USER *user);

dbutils.c文件：
#include "dbutils.h"
int conn_init(MYSQL *conn)
{
mysql_init(conn);
if (conn == NULL)
{
return -1;
}

mysql_real_connect(conn, "localhost", "root", "", "password", 0, NULL, 0);
if (conn == NULL)
{
printf("%s\n", mysql_error(conn));
return -1;
}

// set no auto commit
mysql_autocommit(conn, 0);

return 0;
}

int isExist(MYSQL *conn, char *table, char *colname, char *value)
{
if (conn == NULL)
{
return -1;
}

MYSQL_RES *res_ptr;
MYSQL_ROW row;
int res;
char buf[4];
int i = 0;

char sql[256];
sprintf(sql, "SELECT %s FROM %s WHERE %s='%s'", colname, table, colname, value);
res = mysql_query(conn, sql);
if (res != 0)
{
printf("%s\n", mysql_error(conn));
return -1;
}

res_ptr = mysql_store_result(conn);
while (row = mysql_fetch_row(res_ptr))
{
mysql_free_result(res_ptr);
return 0;
}
mysql_free_result(res_ptr);

return -1;
}

int delete(MYSQL *conn, char *table, char *cond)
{
if (conn == NULL)
{
return -1;
}

if (cond == NULL)
{
return -1;
}

char sql[256];
sprintf(sql, "DELETE FROM %s WHERE %s", table, cond);

if (mysql_query(conn, sql) != 0)
{
printf("%s\n", mysql_error(conn));
return -1;
}

return 0;
}

int user_add(MYSQL *conn, USER *user)
{
if (conn == NULL)
{
return -1;
}

int res;
char sql[256];
sprintf(sql, "INSERT INTO t_user(username, age) VALUES('%s')", user->username);

if (isExist(conn, "t_user", "username", user->username) == 0)
{
printf("t_user table has %s\n", user->username);
return -1;
}

res = mysql_query(conn, sql);
if (res != 0)
{
printf("%s\n", mysql_error(conn));
return -1;
}

mysql_close(conn);
return 0;
}

int user_get_all(MYSQL *conn, USER user[])
{
if (conn == NULL)
{
return -1;
}

MYSQL_RES *res_ptr;
MYSQL_ROW row;
int res;
char buf[4];
int i = 0;

char *sql = "SELECT id, username, age FROM t_user";
res = mysql_query(conn, sql);
if (res != 0)
{
printf("%s\n", mysql_error(conn));
return -1;
}

res_ptr = mysql_store_result(conn);
while (row = mysql_fetch_row(res_ptr))
{
snprintf(buf, sizeof(buf), "%s", row[0]);
user[i].id = atoi(buf);
snprintf(user[i].username, sizeof(user[i].username), "%s", row[1]);
snprintf(buf, sizeof(buf), "%s", row[2]);
user[i].age = atoi(buf);
i ;
}
mysql_free_result(res_ptr);
mysql_close(conn);

return 0;
}

int user_delete(MYSQL *conn, int userid)
{
if (conn == NULL)
{
return -1;
}

int res;
char sql[256];
sprintf(sql, "id='%d'", userid);

if (delete(conn, "t_user", sql) != 0)
{
printf("%s\n", mysql_error(conn));
return -1;
}

mysql_close(conn);
return 0;
}

int user_update(MYSQL *conn, int userid, USER *user)
{
if (conn == NULL)
{
return -1;
}

int res;
char sql[256];
sprintf(sql, "UPDATE t_user SET username='%s' WHERE userid='%d'", user->username, userid);
res = mysql_query(conn, sql);
if (res != 0)
{
printf("%s\n", mysql_error(conn));
return -1;
}

mysql_close(conn);

return 0;
}

下面是測試文件test.c：

#include "dbutils.h"
int main(int argc, char *argv[])
{
MYSQL conn;
USER user[1024];

if (conn_init(&conn) == -1)
{
printf("connection failed\n");
}
else
{
printf("connection successed\n");
}

user_get_all(&conn, user);
printf("%d\n", user[0].id);
return 0;
}

編譯時使用下面的Makefile：
CC = gcc
INCLUDE_DIR = /usr/include/mysql
LIB_DIR = /usr/lib/mysql

EXES = \
test

LINKS = \
dbutils.o


COMPILE_PARMS = \
-lmysqlclient

all: clean $(EXES)

.PHONY: all

$(EXES): % : %.o $(LINKS)
$(CC) -g -o $@ *.o -I$(INCLUDE_DIR) -L$(LIB_DIR) $(COMPILE_PARMS)

clean:
rm -f *.o core.* $(EXES)

本文出自 「瘋狂的程序員」 博客,請務必保留此出處http://2272103.blog.51cto.com/2262103/857891

Tags: linux system 系統