用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文件该怎么破

  10. minecraft says:

    Today, I went to the beach with my kids. I found a sea shell and gave it to my
    4 year old daughter and said “You can hear the ocean if you put this to your ear.” She placed the shell to her
    ear and screamed. There was a hermit crab inside and it pinched her ear.

    She never wants to go back! LoL I know this is totally off topic but I had to tell someone!

  11. minecraft says:

    Hi, i believe that i noticed you visited my weblog so i got here to go back the choose?.I’m trying to find issues to enhance my site!I
    assume its ok to make use of a few of your concepts!!

  12. Hey there, I think your site might be having browser compatibility issues.
    When I look at your blog site in Ie, it looks fine
    but when opening in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up!
    Other then that, wonderful blog!

  13. Excellent site you have got here.. It’s difficult to find
    high-quality writing like yours nowadays.
    I seriously appreciate individuals like you! Take care!!

  14. Today, I went to the beach with my kids. I found a sea shell and gave
    it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed.
    There was a hermit crab inside and it pinched her ear. She never wants to go back!
    LoL I know this is entirely off topic but I had to tell someone!

  15. This design is spectacular! You obviously know how to keep
    a reader entertained. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Wonderful job.
    I really enjoyed what you had to say, and more than that, how you presented it.
    Too cool!

  16. Do you mind if I quote a few of your articles as long as
    I provide credit and sources back to your site? My blog site is in the exact same niche as yours and my visitors would really benefit
    from a lot of the information you present here. Please let me know if this okay with you.
    Regards!

  17. I enjoy what you guys are up too. This type of clever work and exposure!
    Keep up the good works guys I’ve you guys to my blogroll.

  18. Can I simply say what a relief to discover someone that truly understands
    what they’re talking about over the internet. You actually understand how to bring
    an issue to light and make it important. More people should check this out and understand this
    side of your story. I can’t believe you’re not
    more popular because you certainly possess the gift.

  19. Hey there, I think your blog might be having browser compatibility
    issues. When I look at your blog site in Firefox, it looks fine but when opening in Internet Explorer,
    it has some overlapping. I just wanted to give you a quick heads
    up! Other then that, fantastic blog!

  20. This is very fascinating, You are a very skilled blogger.
    I’ve joined your rss feed and look ahead to looking for extra of
    your fantastic post. Additionally, I’ve shared your web site in my
    social networks

  21. I just like the valuable info you supply for your articles.
    I will bookmark your blog and take a look at again here frequently.
    I’m quite certain I will be informed plenty of new stuff right
    here! Good luck for the following!

  22. I simply could not depart your site before suggesting that I extremely loved the standard information a person provide to your visitors?
    Is gonna be back incessantly in order to check up on new posts

  23. After exploring a number of the articles on your website, I
    really appreciate your way of blogging. I saved it to
    my bookmark webpage list and will be checking back in the near future.
    Take a look at my website too and tell me
    how you feel.

  24. It is truly a great and useful piece of info. I am glad that you simply shared this helpful info with
    us. Please stay us up to date like this. Thanks
    for sharing.

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