mysql中文插入和查询过程

备注：此字符集设置在LINUX下可行但WINDOWS不可行，应为gb2312

mysql下添加表

mysql> create table POI ( ID int(6) unsigned zerofill auto_increment
primary key not null, DISTRICT char(50) character set utf8, TYPE int(6)
unsigned zerofill, TYPENAME char(50) character set utf8, ADDRESS char(50)
character set utf8 );

/************************************
**
** insert
**
************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
//#include <sys/iconv.h>
//#include "/usr/src/sys/sys/iconv.h"
//#include "/usr/src/bin/csh/iconv.h"
#include "/usr/local/mysql/include/mysql/mysql.h"

int
main(int argc,char **argv)
{
    MYSQL           mysql;
    FILE            *fp;

    char *filename = "/usr/local/cplusproject/data";

     fp=fopen(filename,"r");
    fprintf(stdout,"file %s opend.../n",filename);

    if(fp == NULL){
      printf("open file fail/n");
        return -1;
        }

    mysql_init(&mysql);

    if(NULL==mysql_real_connect(&mysql,"localhost","root","todaylxp","test",0,NULL,0))
    {
        fprintf(stderr,"Failed to connect to database: Error:%s/n",mysql_error(&mysql));
        exit(-1);
    }

    fprintf(stdout,"DataBase Connected.../n");
   
    // 设定字符集为"utf-8"
   
    if( 0 != mysql_options(&mysql,MYSQL_SET_CHARSET_NAME,"utf8")){
        fprintf(stderr,"mysql_options Error:%s/n",mysql_error(&mysql));
    }
    if( 0 != mysql_set_character_set(&mysql,"utf8") ){
        fprintf(stderr,"mysql_set_character_set Error:%s/n",mysql_error(&mysql));
    }

    // 读取文件

    for(char buf[4096]; NULL != fgets(buf,4096,fp); ){
        /*
         * | DISTRICT  | char(30)
         * | TYPE | int(6) unsigned zerofill
         * | TYPENAME | char(30)
         * | ADDRESS | char(30)
         */
        printf("%s/n",buf);
       
        //SQL语句
       
        char query[1024]="INSERT INTO POI(DISTRICT,TYPE,TYPENAME,ADDRESS) VALUES('";
        char *iter_b,*iter_e;

        for(iter_b=buf; 0 < *iter_b; ++iter_b);
        for(iter_e=iter_b; 0 > *iter_e; iter_e+=2);
        strncat(query,iter_b,iter_e-iter_b);
        strcat(query,"',");
        for(iter_b=iter_e; ' '==*iter_b; ++iter_b);
        for(iter_e=iter_b; ' '!=*iter_e; ++iter_e);
        strncat(query,iter_b,iter_e-iter_b);
        strcat(query,",'");
        for(iter_b=iter_e; 0 < *iter_b; ++iter_b);
        for(iter_e=iter_b; 0 > *iter_e; iter_e+=2);
        strncat(query,iter_b,iter_e-iter_b);
        strcat(query,"','");
        for(iter_b=iter_e; 0 < *iter_b; ++iter_b);
        for(iter_e=iter_b; 0 > *iter_e; iter_e+=2);
        strncat(query,iter_b,iter_e-iter_b);
        strcat(query,"')");

        if( mysql_real_query(&mysql,query,strlen(query)) ){
            fprintf(stderr,"Error making query:%s/n",mysql_error(&mysql));
        }

    }

    fclose(fp);
    mysql_close(&mysql);

    exit(0);

}

gcc -std=c99 -L /usr/local/mysql/lib/mysql/ -lmysqlclient -I /usr/local/include/mysql/ mysql++.c -o sql

//如果编译出错，试试加上-lz选项

错误1
MySQL Connection Failed: Can't connect to local MySQL server through socket /var/lib/mysql.sock （2）
sock文件在 '/tmp/mysql.sock'
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock 作一个软链接

错误2
cannot restore segment prot after reloc: Permission denied

在linux上安装有些东西时会出现 Permission denied 的情况：以下就是解决它的办法之一
编辑/etc/selinux/config，找到这段：
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - SELinux is fully disabled.
SELINUX=enforcing

把 SELINUX=enforcing 注释掉：#SELINUX=enforcing ，然后新加一行为：
SELINUX=disabled
保存，关闭。

不过结果还是不行，使用以下命令:

Summary
SELinux is preventing /usr/local/cplusproject/sql from loading /usr/lib/libmysqlclient.so.15.0.0 which requires text relocation.

Detailed Description
The /usr/local/cplusproject/sql application attempted to load /usr/lib/libmysqlclient.so.15.0.0 which requires text relocation. This is a potential security problem. Most libraries do not need this permission. Libraries are sometimes coded incorrectly and request this permission. The SELinux Memory Protection Tests web page explains how to remove this requirement. You can configure SELinux temporarily to allow /usr/lib/libmysqlclient.so.15.0.0 to use relocation as a workaround, until the library is fixed. Please file a bug report against this package.

Allowing Access
If you trust /usr/lib/libmysqlclient.so.15.0.0 to run correctly, you can change the file context to textrel_shlib_t. "chcon -t textrel_shlib_t /usr/lib/libmysqlclient.so.15.0.0"The following command will allow this access:chcon -t textrel_shlib_t /usr/lib/libmysqlclient.so.15.0.0
Additional Information
Source Context:  root:system_r:unconfined_t:SystemLow-SystemHigh
Target Context:  system_u:object_r:lib_t
Target Objects:  /usr/lib/libmysqlclient.so.15.0.0 [ file ]
Affected RPM Packages:  MySQL-shared-5.0.22-0 [target]
Policy RPM:  selinux-policy-2.4.6-104.el5
Selinux Enabled:  True
Policy Type:  targeted
MLS Enabled:  True
Enforcing Mode:  Enforcing
Plugin Name:  plugins.allow_execmod
Host Name:  localhost.localdomain
Platform:  Linux localhost.localdomain 2.6.18-53.el5 #1 SMP Wed Oct 10 16:34:02 EDT 2007 i686 athlon
Alert Count:  5
Line Numbers:   
Raw Audit Messages :avc: denied { execmod } for comm="sql" dev=dm-0 egid=0 euid=0 exe="/usr/local/cplusproject/sql" exit=-13 fsgid=0 fsuid=0 gid=0 items=0 path="/usr/lib/libmysqlclient.so.15.0.0" pid=3795 scontext=root:system_r:unconfined_t:s0-s0:c0.c1023 sgid=0 subj=root:system_r:unconfined_t:s0-s0:c0.c1023 suid=0 tclass=file tcontext=system_u:object_r:lib_t:s0 tty=pts1 uid=0

chcon -t textrel_shlib_t /usr/lib/libmysqlclient.so.15.0.0

参考文献

http://hi.baidu.com/netpet/blog/item/c7c39acae7803b80c817684f.html
http://www.programfan.com/club/showtxt.asp?id=209923

///

/************************************
**
** query
**
************************************/

DataBase Connected...
got result
the fields number is: 2

    1  #include<stdio.h>
      2   #include "/usr/local/mysql/include/mysql/mysql.h"
      3   int   main()
      4   {
      5     /*declare   structures   and   variables */
      6     MYSQL   mysql;
      7     MYSQL_RES   *result;
      8
      9
     10     //initialize   MYSQL   structure
     11      mysql_init(&mysql);
     12
     13
     14     if(NULL==mysql_real_connect(&mysql,"localhost","root","todaylxp","test",0,NULL,0))
     15     {
     16             fprintf(stderr,"Failed to connect to database: Error:%s/n",mysql_error(&mysql));
     17             return -1;
     18     }
     19
     20     fprintf(stdout,"DataBase Connected.../n");
     21
     22     if( 0 != mysql_options(&mysql,MYSQL_SET_CHARSET_NAME,"utf8"))
     23     {
     24         fprintf(stderr,"mysql_options Error:%s/n",mysql_error(&mysql));
     25     }
     26
     27     if( 0 != mysql_set_character_set(&mysql,"utf8") )
     28     {
     29         fprintf(stderr,"mysql_set_character_set Error:%s/n",mysql_error(&mysql));
     30     }
     31
     32     //execute   query
     33     mysql_query(&mysql,"select   DISTRICT,TYPENAME   from   POI");                                                                                  
     34     //get   result   set
     35     result=mysql_store_result(&mysql);
     36
     37     if(NULL == result)
     38         printf("get result error/n");
     39     else
     40         printf("got result/n");
     41
     42     int nFieldNum = mysql_num_fields(result); //查询多少个字段，取回的是多少个字段，此处等于2( DISTRICT,TYPENAME)
     43
     44     printf("the fields number is: %d/n",nFieldNum);
     45
     46     MYSQL_ROW row;
     47
     48     //process   result   set
     49     while(row=mysql_fetch_row(result))
     50     {
     51        fprintf(stdout,"%d   -   %s/n",atoi(row[0]),row[1]);//  DISTRICT,TYPENAME
     52     }
     53                                                                                                                                                     //          clean   up  
     54   
     55     mysql_free_result(result);
     56   
     57     mysql_close(&mysql);
     58   
     59 }

"/usr/local/cplusproject/sample.c" 59L, 1469C