接着上篇文章继续讲字符集的故事。这一篇文章主要讲MYSQL的各个字符集设置,关于基础理论部分,参考于这里。
1. MYSQL的系统变量
– character_set_server:默认的内部操作字符集
– character_set_client:客户端来源数据使用的字符集
– character_set_connection:连接层字符集
– character_set_results:查询结果字符集
– character_set_database:当前选中数据库的默认字符集
– character_set_system:系统元数据(字段名等)字符集
简单来说,对于使用MYSQL C API的我们来说,主要关心的是3个字符集,即character_set_client, character_set_connection和character_set_results。但是从我的使用的角度上来说,总觉得character_set_connection有点多余。
2. MySQL中的字符集转换过程
1) MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;
2) 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:
• 使用每个数据字段的CHARACTER SET设定值;
• 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);
• 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;
• 若上述值不存在,则使用character_set_server设定值。
3) 将操作结果从内部操作字符集转换为character_set_results。
上面从character_set_connection转换到内部操作字符集的过程看起来比较复杂,但是如果我们在MYSQL建表的时候指定了数据表的字符集,就可以简单认为这个“内部操作字符集”就是对应表的字符集。所以说,我比较推荐在建表的时候带上这句话“DEFAULT CHARSET=xxx”,其中的xxx可以通过”select character_set_name from information_schema.CHARACTER_SETS”来获取。建议是”UTF8”。
3. MySQL中的字符集转换实验
我这里的环境是这样的。 main.cpp是utf-8格式的,编译的gcc并没有指定finput-charset和fexec-charset,所以可执行文件中的中文应该也是以utf-8的方式存储的;
linux终端环境是de_DE(export LANG=de_DE)
MYSQL中的建表语句是 CREATE TABLE `tbl_test` (
`id` int ,
name varchar(20000),
uptime date,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
实验一:正确地处理中文的过程
这个实验的大致过程是, 在连接之后,使用set names utf8同时设置了character_set_client, character_set_connection和character_set_results。
通过set character_set_client=gbk设置客户端的字符集。
在代码中硬编码出“insert into tbl_test(id, name, uptime) VALUES (100, ‘你好’, ‘20130101’)
然后调整那个”好“为”饕“。
需要注意的点是,我首先将二进制中的硬编码(utf8格式)的char*串转换成wchar_t*串,然后调整中文。在出去之前再将wchar_t*串调整为gbk的char*串。经过试验,下面的代码运行正常。 #include
#include
#include
#include
#include "common/dbcomm/DbComm.h"
using namespace std;
COMMON::DbLocation dbLocation1;
void InsertBySqlStatmentTest1();
int main()
{
dbLocation1.SetDbId("TEST_DB1");
dbLocation1.SetIp("127.0.0.1");
dbLocation1.SetPort("3306");
dbLocation1.SetUser("cup_dba");
dbLocation1.SetPassword("123456");
InsertBySqlStatmentTest1();
return 0;
}
void InsertBySqlStatmentTest1()
{
try
{
vector<:dblocation> dbLocations_array;
dbLocations_array.push_back(dbLocation1);
dbLocations_array.push_back(dbLocation2);
tr1::shared_ptr<:idbtasks> mysqlTasks( new COMMON::MysqlDbTasks(dbLocations_array, true) );
mysqlTasks->Connect();
cout << "Connect success" << endl;
{
COMMON::DbExecuteAction* char_action = mysqlTasks->Execute();
COMMON::ExecuteFilter char_filter("set names utf8");
char_action->Do(&char_filter, &dbLocation1);
// change the character_set_client to gbk
COMMON::ExecuteFilter char_filter2("SET character_set_client = gbk");
char_action->Do(&char_filter2, &dbLocation1);
char_action->EndAction();
}
COMMON::DbExecuteAction* insert_action = mysqlTasks->Insert(5000);
stringstream ss;
ss << "INSERT INTO tbl_test(id, name, uptime) VALUES" << "(" << 100 << "," << "'你好'," << "'20130101')";
string statement = ss.str();
// use mbstowcs to change the sql statement to wide-char-string
// we use the default value of fexec-charset, which is utf-8, to compile this file with gcc.
setlocale(LC_ALL, "zh_CN.utf8");
size_t wcs_size = mbstowcs(NULL, statement.c_str(), 0);
wchar_t* dest = new wchar_t[wcs_size + 1];
wmemset(dest, L'\0', wcs_size + 1);
mbstowcs(dest, statement.c_str(), statement.size() * sizeof(char));
// change the last '好' to '饕'
wchar_t *tmp = wcsrchr(dest, L'好');
*tmp = L'饕';
// change the sql statement to the charset that corresponds to the character_set_client of mysql
setlocale(LC_ALL, "zh_CN.gbk");
size_t mbs_size = wcstombs(NULL, dest, 0);
char* buf_mbs = new char [mbs_size + 1];
memset(buf_mbs, '\0', mbs_size + 1);
wcstombs(buf_mbs, dest, wcs_size * sizeof(wchar_t));
// try to insert into mysql
COMMON::InsertFilter insertFilter(buf_mbs);
insert_action->Do(&insertFilter);
insert_action->EndAction();
cout << "EndAction success" << endl;
mysqlTasks->Disconnect();
cout << "Disconnect success" << endl;
}
catch (COMMON::ThrowableException& e)
{
cout << e.What() << endl;
}
catch (...)
{
cout << "unknown exception" << std::endl;
}
}
实验二:错误地处理中文的过程
现在来做一些修改,我们先把情况变得简单一些,我们不恶意地去set character_set_client=gbk,而是只运行set names utf8。然后在拿到拼凑好的sql语句的时候,利用string::find方法找到‘你’,然后直接利用结果的数字下标来修改成‘饕’。具体的代码如下
#include
#include
#include
#include
#include "common/dbcomm/DbComm.h"
using namespace std;
COMMON::DbLocation dbLocation1;
void InsertBySqlStatmentTest1();
int main()
{
dbLocation1.SetDbId("TEST_DB1");
dbLocation1.SetIp("127.0.0.1");
dbLocation1.SetPort("3306");
dbLocation1.SetUser("cup_dba");
dbLocation1.SetPassword("123456");
InsertBySqlStatmentTest1();
return 0;
}
void InsertBySqlStatmentTest1()
{
try
{
vector<:dblocation> dbLocations_array;
dbLocations_array.push_back(dbLocation1);
tr1::shared_ptr<:idbtasks> mysqlTasks( new COMMON::MysqlDbTasks(dbLocations_array, true) );
mysqlTasks->Connect();
cout << "Connect success" << endl;
{
// ************这里不再恶作剧地修改character_set_client为gbk**************
COMMON::DbExecuteAction* char_action = mysqlTasks->Execute();
COMMON::ExecuteFilter char_filter("set names utf8");
char_action->Do(&char_filter, &dbLocation1);
char_action->EndAction();
}
COMMON::DbExecuteAction* insert_action = mysqlTasks->Insert(5000);
stringstream ss;
ss << "INSERT INTO tbl_test(id, name, uptime) VALUES" << "(" << 100 << "," << "'你好'," << "'20130101')";
// ************直接修改string**************
string statement = ss.str();
size_t pos = statement.find('你');
statement[pos] = '饕';
// try to insert into mysql
COMMON::InsertFilter insertFilter(statement);
insert_action->Do(&insertFilter);
insert_action->EndAction();
cout << "EndAction success" << endl;
mysqlTasks->Disconnect();
cout << "Disconnect success" << endl;
}
catch (COMMON::ThrowableException& e)
{
cout << e.What() << endl;
}
catch (...)
{
cout << "unknown exception" << std::endl;
}
}
结果是,
为了追寻错误的原因,让我们从十六进制的角度来看。
可以看到,
size_t pos = statement.find('你');
statement[pos] = '饕';
实质只改动了一个字节(utf8编码,从‘你’的E4BDA0到‘何’的E4BC95,我们的改动,就是那个95,他是‘饕’的一个字节。)这个现象也符合我们对于string行为的认识。
4. 总结和建议
建议对于每一张数据表都设置字符集
建议把character_set_client, character_set_connection和character_set_results设置为和数据表的字符集一致
在使用MYSQL C API的时候,初始化数据库句柄后马上用mysql_options设定MYSQL_SET_CHARSET_NAME属性为与数据表的字符集一致的字符集,或者通过发送SQL语句set names xxx来设置字符集。
如果需要处理中文,那么数据表的字符集通常是utf-8或者gbk。
如果要对中文做字符处理,那么就一定要根据实际的情况设置setlocale,使用mbstowcs转换成wcs,然后针对wide-char string进行操作,再使用wcstombs转换为多字节字符串拼成sql语句传递给数据库连接。