C++ sqlite3解决中文排序问题

导言：sqlite3默认的编码方式为UTF8编码，而在UTF8编码下，中文不是按照拼音顺序编码的，所以想解决中文排序问题，必须自定义排序规则，将UTF8编码转换成GB2312编码（GB2312编码中文是按照拼音顺序编码的），然后再进行比较大小，就可以得到正确的排序结果了。

#include "sqlite3.h"
#include <Windows.h>

#include <string>
#include <vector>
using std::vector;
using std::string;
#pragma comment(lib, "sqlite3.lib")
//UTF-8到GB2312的转换
void U2G(const char* utf8, string& gb2312)
{
    int len = MultiByteToWideChar(CP_UTF8, 0, utf8, -1, NULL, 0);
    wchar_t* wstr = new wchar_t[len+1];
    memset(wstr, 0, len+1);
    MultiByteToWideChar(CP_UTF8, 0, utf8, -1, wstr, len);
    len = WideCharToMultiByte(CP_ACP, 0, wstr, -1, NULL, 0, NULL, NULL);
    char* str = new char[len+1];
    memset(str, 0, len+1);
    WideCharToMultiByte(CP_ACP, 0, wstr, -1, str, len, NULL, NULL);
    if(wstr) delete[] wstr;
    gb2312 = str;
    delete[] str;
}
 
//GB2312到UTF-8的转换
void G2U(const char* gb2312, string& utf8)
{
    int len = MultiByteToWideChar(CP_ACP, 0, gb2312, -1, NULL, 0);
    wchar_t* wstr = new wchar_t[len+1];
    memset(wstr, 0, len+1);
    MultiByteToWideChar(CP_ACP, 0, gb2312, -1, wstr, len);
    len = WideCharToMultiByte(CP_UTF8, 0, wstr, -1, NULL, 0, NULL, NULL);
    char* str = new char[len+1];
    memset(str, 0, len+1);
    WideCharToMultiByte(CP_UTF8, 0, wstr, -1, str, len, NULL, NULL);
    if(wstr) delete[] wstr;
    utf8 = str;
    delete[] str;
}

int chinese_cmp(void *NotUsed, int nKey1, const void *pKey1, int nKey2, const void *pKey2)
{
    //int n = nKey1 < nKey1 ? nKey1 : nKey2;
    string key1 = "";
    string key2 = "";
    U2G((char*)pKey1,key1);
    U2G((char*)pKey2,key2);
    //return strncmp(key1.c_str(), key2.c_str(), n + 1);
    return strcmp(key1.c_str(), key2.c_str());
}

void main()
{
    sqlite3* conn;
    int ret = sqlite3_open("C:\\Users\\Administrator\\Desktop\\testsort.db", &conn);
    sqlite3_create_collation(conn, "Chinese", SQLITE_UTF8, 0, chinese_cmp);

    string sql = "";
    G2U("select * from mzhrd order by xm collate Chinese", sql);
    sqlite3_stmt *stmt;
    ret = sqlite3_prepare_v2(conn, sql.c_str(), sql.length(), &stmt,0);

    while(sqlite3_step(stmt) != SQLITE_DONE)
    {
        string data = "";
        U2G((char*)sqlite3_column_text(stmt,0),data);
        int afsd =1;
    }
    int dsaf = 1;
}