用C语言操作MySQL数据库,进行连接、插入、修改、删除等操作

  很多人用到MySQL来开发一些项目,有时为了性能,我们会直接用C语言来开发相关的模块,尤其在我们的web应用中,虽然PHP、JSP等脚本均提供了MySQL的接口,但是显然直接使用C语言具有更好的安全性和性能,Michael以前用PHP开发的多个项目中就使用了C语言编写的这类接口,然后再编译到php里面,供php脚本直接使用,这方面的话题就不多说了,下面主要说一下在Linux下如何用C语言连接MySQL数据库,并且读取里面的数据返回,同时如何进行编译。

  这里的大部分代码参考了MySQL发行包里面的.c源文件,大家也可以去里面找找相关的代码,下面这段代码实现了连接到本地MySQL服务器上9tmd_bbs_utf8数据库,从数据表tbb_user中根据输入的userid取得该用户的用户名并打印输出到终端。

#if defined(_WIN32) || defined(_WIN64)  //为了支持windows平台上的编译
#include <windows.h>
#endif
#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"  //我的机器上该文件在/usr/local/include/mysql下
 
//定义数据库操作的宏,也可以不定义留着后面直接写进代码
#define SELECT_QUERY "select username from tbb_user where userid = %d"
 
int main(int argc, char **argv) //char **argv 相当于 char *argv[]
{
    
MYSQL mysql,*sock;    //定义数据库连接的句柄,它被用于几乎所有的MySQL函数
    
MYSQL_RES *res;       //查询结果集,结构类型
    
MYSQL_FIELD *fd ;     //包含字段信息的结构
    
MYSQL_ROW row ;       //存放一行查询结果的字符串数组
    
char  qbuf[160];      //存放查询sql语句字符串
    
    
if (argc != 2) {  //检查输入参数
        
fprintf(stderr,"usage : mysql_select <userid>\n\n");
        
exit(1);
    
}
    
    
mysql_init(&mysql);
    
if (!(sock = mysql_real_connect(&mysql,"localhost","dbuser","dbpwd","9tmd_bbs_utf8",0,NULL,0))) {
        
fprintf(stderr,"Couldn't connect to engine!\n%s\n\n",mysql_error(&mysql));
        
perror("");
        
exit(1);
    
}
    
    
sprintf(qbuf,SELECT_QUERY,atoi(argv[1]));
    
if(mysql_query(sock,qbuf)) {
        
fprintf(stderr,"Query failed (%s)\n",mysql_error(sock));
        
exit(1);
    
}
    
    
if (!(res=mysql_store_result(sock))) {
        
fprintf(stderr,"Couldn't get result from %s\n", mysql_error(sock));
        
exit(1);
    
}
    
    
printf("number of fields returned: %d\n",mysql_num_fields(res));
        
    
while (row = mysql_fetch_row(res)) {
        
printf("Ther userid #%d 's username is: %s\n", atoi(argv[1]),(((row[0]==NULL)&&(!strlen(row[0]))) ? "NULL" : row[0])) ;
        
puts( "query ok !\n" ) ;
    
}
    
    
mysql_free_result(res);
    
mysql_close(sock);
    
exit(0);
    
return 0;   //. 为了兼容大部分的编译器加入此行
}

编译的时候,使用下面的命令

gcc -o mysql_select ./mysql_select.c -I/usr/local/include/mysql -L/usr/local/lib/mysql -lmysqlclient (-lz) (-lm) 后面两个选项可选,根据您的环境情况

运行的时候,执行下面的命令

./mysql_select 1

将返回如下结果:

number of fields returned: 1
Ther userid #1 's username is: Michael
query ok !

上面的代码我想大部分都能看明白,不明白的可以参考一下MySQL提供的有关C语言API部分文档,各个函数都有详细说明,有时间我整理一份常用的API说明出来。

Category: C / Erlang / Lua
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
12 Responses
  1. 冯明亮 says:

    请问Michael,你的这篇blog的内嵌代码的部分是怎样实现的?
    我想在Word或者openofifce里面实现一个类似的控件,但是还考虑不清楚用什么方式比较好。有空的话请回个邮件,谢谢了~~~

  2. TEST says:

    注意 ‘ or 这些字符

  3. xmpp says:

    utf8乱码

  4. We offer various wedding accessories at affordable price with high quality. At our website, you can choose the suitable bridesmaid dresses for wedding. Besides, evening dresses are also necessary for this big day. Also the beautiful flower girl can also select the flower girl dresses, which is really cute and pretty.

  5. I want to thank you for this site to pay for female. I hope that the website in the upcoming high-grade secondary school to more skills, in fact, your creative skills and encouraged me to let my site has improved. In fact, the blog is spreading its wings rapidly. You write up is a good example.

  6. perfect. thanks

  7. 说的有点模糊,,看不懂

  8. shilong says:

    出现了这个问题。character set ‘#33’ is not a compiled character set and is not specified in the file

  9. heqiang says:

    你好,我的MYSQL是界面版的,没有找到mysql.h文件该怎么破

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image