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

// t.cpp

#include

#include

#include

#include

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