查询MySQL数据库
上面我演示了如何连接数据库以及获得一个叫做conn的连接点。
接下来我们建立SQL查询：
| mysql_query(conn, "SET NAMES 'utf8'"); if(forAutocomplete){ if(search == NULL || wcslen(search) ==0){ WideCharToMultiByte(CP_UTF8,0, L"SELECT Title from Document LIMIT 20",-1,sql,1000,NULL,NULL); }else{ wsprintf(lbuffer, L"SELECT Title, match(Title) against('%ls' IN BOOLEAN MODE) as Score from Document where match(Title) against('%ls' IN BOOLEAN MODE) > 0.001 order by Score Desc LIMIT 20", search, search); WideCharToMultiByte(CP_UTF8,0,lbuffer,-1,sql,1000,NULL,NULL); } }else if(search == NULL || wcslen(search) ==0){ WideCharToMultiByte(CP_UTF8,0,L"SELECT DocumentID, Title, Text from Document",-1,sql,1000,NULL,NULL); }else{ wsprintf(lbuffer, L"SELECT DocumentID, Title, Text, match(Title, Text) against('%ls' IN BOOLEAN MODE) as Score from Document where match(Title, Text) against('%ls' IN BOOLEAN MODE) > 0.001 order by Score Desc", search, search); WideCharToMultiByte(CP_UTF8,0,lbuffer,-1,sql,1000,NULL,NULL); } |
查询match(Title, Text) against('%ls' IN BOOLEAN MODE)在列Title和Text中查询要搜索的字符串，并返回一个反馈查询匹配情况的值。只有分数大于0.001的文档将显示，输出结果按评分排序。
IN BOOLEAN MODE时多个单词的搜索分别进行。
在搜索字符串中，你可以使用“*”作为通配符，它匹配0到n个字符。例如“as*”会匹配ASP。搜索不区分大小写。在SQL server中有些例外，“as**”不匹配任何内容，“*SP”也不匹配，你可以在字符串的开头匹配通配符。
获得数据
| if(mysql_query(conn, sql)) { fprintf(stderr, "%sn", mysql_error(conn)); fprintf(stderr, "%sn", sql); return NULL; } // Process results result = mysql_store_result(conn); ... while((row = mysql_fetch_row(result)) != NULL) { if(format == FORMAT_TEXT){ MultiByteToWideChar(CP_UTF8,0,row[0], -1,buffer, 255); wsprintf(resultBufferp,L"%st", buffer); resultBufferp+=wcslen(buffer)+1; MultiByteToWideChar(CP_UTF8,0,row[1], -1,buffer, 255); wsprintf(resultBufferp,L"%st", buffer); resultBufferp+=wcslen(buffer)+1; MultiByteToWideChar(CP_UTF8,0,row[2], -1,buffer, 32000); wsprintf(resultBufferp,L"%sn", buffer); resultBufferp+=wcslen(buffer)+1; }else if(format == FORMAT_JSON){ if(!forAutocomplete){ MultiByteToWideChar(CP_UTF8,0,row[0], -1,buffer, 255); wsprintf(resultBufferp,L"{"DocumentID": %s, ", buffer); resultBufferp+=wcslen(buffer)+wcslen(L"{"DocumentID": , "); MultiByteToWideChar(CP_UTF8,0,row[1], -1,buffer, 255); wsprintf(resultBufferp,L""Title": "%s", ", buffer); resultBufferp+=wcslen(buffer)+wcslen(L""Title": "", "); MultiByteToWideChar(CP_UTF8,0,row[2], -1,buffer, 32000); wsprintf(resultBufferp,L""Text": "%s"},", buffer); resultBufferp+=wcslen(buffer)+wcslen(L""Text": ""},"); }else{ MultiByteToWideChar(CP_UTF8,0,row[0], -1,buffer, 255); wsprintf(resultBufferp,L""%s",", buffer); resultBufferp+=wcslen(buffer)+wcslen(L""","); } } } |
