mysql C编程-utf8编码，汉字和blob类型

// t.cpp

#include <stdio.h>
#include <mysql.h>
#include <string.h>
#include <stdlib.h>

int main()
{
 char szHost[] = "localhost";
 char szUser[] = "root";
 char szPassword[] = "";
 char szDatabase[] = "RoleDB";

 MYSQL* pConn = mysql_init(0);

 if (!pConn)
 {
  return -1;
 }

 // 要求客户端必须是utf8的数据
 if (mysql_options(pConn, MYSQL_SET_CHARSET_NAME, "utf8") != 0)
 {
  printf("%s/n", mysql_error(pConn));
  return -1;
 }

 if (NULL == mysql_real_connect(pConn, szHost, szUser, szPassword,
   szDatabase, 0, NULL, 0))
 {
  printf("%s/n", mysql_error(pConn));
  return -1;
 }

 char szNickname[16] = "";
 // 从utf8文件中读出昵称
 FILE* fp = fopen("ee.txt", "r");
 fgets(szNickname, 16, fp);
 szNickname[strlen(szNickname)] = '/0';
 printf("szNickname utf8 from file: %s/n", szNickname);
 fclose(fp);

 char gender = 'M';
 char xx1[1024] = "";
 xx1[2] = '1';
 char xx2[1024] = "12010100000";

 char szQuery[102400], *pszEnd;
 int n;

 // 测试查询一条不存在的记录
 n = sprintf(szQuery, "%s", "select * from DBRoleInfo where FUin=9999");
 pszEnd = szQuery + n;

 if (mysql_real_query(pConn, szQuery, pszEnd - szQuery) != 0)
 {
  printf("%s/n", mysql_error(pConn));
 }

 unsigned int uiNumberRows;

 MYSQL_RES* pRes = mysql_store_result(pConn);
 if (pRes) // select语句执行成功，虽然没有一行匹配，但是pRes不为NULL
 {
  uiNumberRows = mysql_num_rows(pRes);
  if (uiNumberRows == 0)
  {
   printf("no match----------1/n");
  }
  mysql_free_result(pRes);
 }

 // 测试执行一条replace语句，其中对字符串类型和blob类型进行转义处理
 memset(szQuery, 0, 102400);
 n = sprintf(szQuery, "%s", "REPLACE DBRoleInfo(FUin,FSeq,FNickName,"
  "FGender,FSceneID,FX,FY,FCombatPetIndex,"
  "FCompletedQuest,FBagThing) VALUES(123456,1,");
 pszEnd = szQuery + n;
 *pszEnd++ = '/'';
 pszEnd += mysql_real_escape_string(pConn, pszEnd, szNickname, strlen(
   szNickname));
 *pszEnd++ = '/'';
 n = sprintf(pszEnd, ",/'%d/',%d,%d,%d,%d,", gender, 1, 100, 200, 1);
 pszEnd += n;
 *pszEnd++ = '/'';
 pszEnd += mysql_real_escape_string(pConn, pszEnd, xx1, sizeof(xx1));
 *pszEnd++ = '/'';
 *pszEnd++ = ',';
 *pszEnd++ = '/'';
 pszEnd += mysql_real_escape_string(pConn, pszEnd, xx2, sizeof(xx2));
 *pszEnd++ = '/'';
 *pszEnd++ = ')';

 printf("szNickname: %s/n", szNickname);
 if (mysql_real_query(pConn, szQuery, pszEnd - szQuery) != 0)
 {
  printf("replace error: %s/n", mysql_error(pConn));
  //return -1;
 }
 printf("affected rows: %llu/n", mysql_affected_rows(pConn));
 // 本次执行的不是select语句，所以不用处理结果集。select语句必须处理结果集，否则下一条sql语句会执行失败

 // 再来一条replace语句，操作另一张表
 char xx3[1024] = "123456789";
 xx3[100] = 'P';

 memset(szQuery, 0, 102400);
 n = sprintf(szQuery, "REPLACE DBPetInfo(FUin,FSeq,FPetIndex,FPetID,FPetBuffer) VALUES(123456,1,1,10001,");
 pszEnd = szQuery + n;
 *pszEnd++ = '/'';
 pszEnd += mysql_real_escape_string(pConn, pszEnd, xx3, sizeof(xx3));
 *pszEnd++ = '/'';
 *pszEnd++ = ')';

 if (mysql_real_query(pConn, szQuery, pszEnd - szQuery) != 0)
 {
  printf("%s/n", mysql_error(pConn));
 }

 // select语句
 memset(szQuery, 0, 102400);
 n = sprintf(szQuery, "select * from DBRoleInfo where FUin=%u", 123456);
 pszEnd = szQuery + n;

 if (mysql_real_query(pConn, szQuery, pszEnd - szQuery) != 0)
 {
  printf("%s/n", mysql_error(pConn));
  //return -1;
 }

 pRes = mysql_store_result(pConn);
 if (pRes) // select语句执行成功
 {
  uiNumberRows = mysql_num_rows(pRes);
  if (uiNumberRows == 0)
  {
   printf("no match----------2/n");
   mysql_free_result(pRes);
   return 0;
  }

  printf("number of rows: %d/n", uiNumberRows);

  // 假设只有一行记录，下面取出这一行
  unsigned int uiNumberFields = mysql_num_fields(pRes);
  MYSQL_ROW row = mysql_fetch_row(pRes);
  unsigned long* pulFieldLengths = mysql_fetch_lengths(pRes);

  // 注意：如果字段i当前未填入数据，则row[i]等于NULL，pulFieldLengths[i]等于0

  char pszField[10240] = "";
  for (unsigned int i = 0; i < uiNumberFields; i++)
  {
   memset(pszField, 0, 10240);
   memcpy(pszField, row[i], pulFieldLengths[i]);
   if (i < 2)
   {
    printf("%u/n", atoi(pszField));
   }
   else if (i == 2)
   {
    printf("%s/n", pszField);

    // 将utf8编码的字符串写入文件中
    FILE* fp = fopen("./nickname", "w");
    fwrite(pszField, strlen(pszField), 1, fp);
    fclose(fp);
   }
   else if (i == 3)
   {
    printf("%c/n", pszField[0]);
   }
   else if (i < 8)
   {
    printf("%u/n", atoi(pszField));
   }
   else
   {
    char x1[1024];
    memmove(x1, pszField, sizeof(x1));
   }
  }

  mysql_free_result(pRes);
 }
 else // 执行的是insert/update/delete语句或者select语句执行失败
 {
  if (0 == mysql_field_count(pConn)) // 非select语句
  {
   unsigned int uiNumberAffectedRows = mysql_affected_rows(pConn);
   printf("affected rows: %u/n", uiNumberAffectedRows);

   return 0;
  }
  else // select语句执行失败
  {
   printf("%s/n", mysql_error(pConn));
   return -1;
  }
 }

 return 0;
}

//g++ t.cpp -o t -g -Wall -O -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient