re: VC++连MySQL小记 *Alacky 2007-12-13 22:54
@lmss
回复[lmss]关于乱码:
首先要设置正确的编码类型
MySql安装的时候会有设置数据库那项,默认是latin1,这里可以改成utf8,用来支持中文,不过latin1也可以存储中文,只不过用MySqlGUI工具检索数据时还是乱码,但程序里能正确显示.还可以在创建表的时候指定编码类型为utf8.创建字段时也可以指定编码类型.
在程序中要对输入输出的字符串做正确的编码转换.
查询数据时要设置返回数据的编码类型,即执行SET NAMES 'utf8'
下面是一段测试代码,虽然能够正确的插入和显示中文(程序中和MySql GUI Tools都能够正确显示),但可能还不是正确的方法.曾经看过一些文章说这中间存在多于的转换(和编码设置为latin1,但在程序里还能读取中文的情况差不多),不过我没有深究这个问题,现在主要用.net做东西,链接MySql用的mysql-connector-net-5.0.8.1,它是支持中文的.
CString ConvertMultiByteToUTF8(CString& multiStr)
{
char* buf;
wchar_t* wbuf;
int len;
len = MultiByteToWideChar(CP_ACP, 0, multiStr, -1, 0, 0);
wbuf = new wchar_t[len];
MultiByteToWideChar(CP_ACP, 0, multiStr, -1, wbuf, len);
len = WideCharToMultiByte(CP_UTF8, 0, wbuf, -1, 0, 0, 0, 0);
buf = new char[len];
WideCharToMultiByte(CP_UTF8, 0, wbuf, -1, buf, len, 0, 0);
CString ret(buf);
delete wbuf;
delete buf;
return ret;
}
string ConvertUTF8ToMultiByte(string utf8Str)
{
//char* buf;
//wchar_t* wbuf;
//int len;
//len = MultiByteToWideChar(CP_UTF8, 0, utf8Str, -1, 0, 0);
//wbuf = new wchar_t[len];
//MultiByteToWideChar(CP_UTF8, 0, utf8Str, -1, wbuf, len);
//len = WideCharToMultiByte(CP_ACP, 0, wbuf, -1, 0, 0, 0, 0);
//buf = new char[len];
//WideCharToMultiByte(CP_ACP, 0, wbuf, -1, buf, len, 0, 0);
//CString ret(buf);
//delete wbuf;
//delete buf;
//return ret;
//测试用智能指针
if(utf8Str.empty())
return utf8Str;
int length = MultiByteToWideChar(CP_UTF8, 0, utf8Str.c_str(), -1, 0, 0);
auto_ptr<wchar_t> widechar(new wchar_t[length]);
MultiByteToWideChar(CP_UTF8, 0, utf8Str.c_str(), -1, widechar.get(), length);
length = WideCharToMultiByte(CP_ACP, 0, widechar.get(), -1, 0, 0, 0, 0);
auto_ptr<char> multichar(new char[length]);
WideCharToMultiByte(CP_ACP, 0, widechar.get(), -1, multichar.get(), length, 0, 0);
return multichar.get();
}
void CMySQLTestDlg::OnBnClickedOk()
{
// TODO: 在此添加控件通知处理程序代码
//OnOK();
MYSQL* mysql;
mysql = mysql_init(NULL);
unsigned long long ptr;
stringstream ss;
CString version, hostInfo, serverVersion, colStr, characterSet, queryStr, introStr;
if(!(ptr = (unsigned long long)mysql_real_connect(mysql, "127.0.0.1", "root", "alacky", "hala", 3306, 0, 0)))
{
AfxMessageBox(mysql_error(mysql), 0, 0);
mysql_close(mysql);
return;
}
ss << ptr << " " << (unsigned long long)mysql;
string str = ss.str();
MessageBox(str.c_str());
// 获取链接信息
version = mysql_get_client_info();
hostInfo = mysql_get_host_info(mysql);
serverVersion = mysql_get_server_info(mysql);
characterSet = mysql_character_set_name(mysql);
// 操作数据
queryStr = "UPDATE lala1 SET intro = X'"; // 这里加X表示后面的字符串是十六进制编码的
this->edit_msg.GetWindowText(introStr);
introStr = ConvertMultiByteToUTF8(introStr);
char* buf = new char[introStr.GetLength()*2+1];
mysql_hex_string(buf, introStr, introStr.GetLength()); // 对字符进行16进制编码
queryStr += CString(buf);
delete[] buf;
queryStr += "'";
if(mysql_query(mysql, queryStr))
{
AfxMessageBox(mysql_error(mysql), 0, 0);
mysql_close(mysql);
return;
}
mysql_query(mysql, "SET NAMES 'utf8'");
if(mysql_query(mysql, "SELECT intro FROM lala1 ORDER BY id DESC LIMIT 1"))
{
AfxMessageBox(mysql_error(mysql), 0, 0);
mysql_close(mysql);
return;
}
MYSQL_RES* result = mysql_store_result(mysql);
ULONG columnLen = mysql_num_fields(result);
MYSQL_FIELD* columns = mysql_fetch_fields(result);
colStr = "";
MYSQL_ROW row = mysql_fetch_row(result);
colStr = row[0];
colStr = ConvertUTF8ToMultiByte(string((LPCSTR)colStr)).c_str();
// 清理
mysql_free_result(result);
MessageBox("MySQL Version:" + version + " Host:" + hostInfo + " v" + serverVersion + " " + characterSet);
MessageBox(colStr);
mysql_close(mysql);
}