sqlite 汉字 排序

最新推荐文章于 2023-02-13 11:47:14 发布
最新推荐文章于 2023-02-13 11:47:14 发布
阅读量8.8k 收藏 3
点赞数 1
分类专栏： 工作 文章标签： sqlite collation insert table 架构设计 测试

Sqlite是一个用C语言实现的小型SQL数据库引擎。它体积小巧但功能强大，对硬件资源要求很低而且性能表现卓越，非常适合于嵌入式应用环境。最近发现sqlite并不支持中文(拼音/笔画)排序，而这个功能又是我们必需的，所以花了些时间去研究。我对Sqlite的了解只能算是业余级，在研究的过程或许走了些弯路，或许已经有现存的算法可利用，不管怎么样，在研究过程中还是有不少收获，写出来和大家探讨一下。

我们知道，计算机中的每一个字符都有一个内码。在默认情况下，计算机排序时，比较两个字符的大小就是比较字符内码的大小，这对于英文来说没有问题，因为英文字母的内码是按字母顺序递增的。对于中文来说，就比较麻烦了：首先，中文的排序方式有多种，比如按内码排序、按拼音排序和按笔画排序，要通过参数指定排序的方式，否则计算机就按内码排序了。其次，汉字的内码顺序即不同于拼音顺序，也不同于按笔画顺序。在GB2312编码中，汉字基本上按拼音排序（据说有例外，不太清楚）。在GBK中，它在GB2312基础上进行了扩充，兼容GB2312中的所有字符，所以不是按拼音排序了。在Unicode中，汉字的排列似乎更没有什么规律可言了。

为了解决内码顺序与用户习惯顺序(如拼音顺序)的冲突，在glibc的locale数据里，要求提供排序方式(collate)的描述。我看了一下glibc-2.3.5提供的locale数据，在简体中文(zh_CN)的locale数据描述里，关于排序方式的描述如下：

% ISO 14651 collation sequence

LC_COLLATE

copy "iso14651_t1"

END LC_COLLATE

也就是说，照抄iso14651_t1的排序方式。打开iso14651_t1文件看了一下，也没有发现关于中文的特殊处理，可以推断glibc默认的排序方式就是按unicode排序。

所以不能指望glibc提供中文排序功能，如果SQLite支持了中文排序只能是做了特殊处理。浏览了一下SQLite的代码，这种希望似乎也不大。在网上也没有查到相关的资料和补丁，看来只能靠自己了。

不过，在浏览SQLite代码时还是有些收获，至少知道了它比较数据记录的过程：

1. sqlite3VdbeExec调用sqlite3BtreeInsert把记录插入到适当的位置。

2. sqlite3BtreeInsert调用sqlite3BtreeMoveto找到要插入的位置。

3. sqlite3BtreeMoveto调用sqlite3VdbeRecordCompare比较两条记录的大小。

4. sqlite3VdbeRecordCompare调用sqlite3MemCompare比较字段的大小。

5. sqlite3MemCompare调用binCollFunc去做真正的比较。

6. binCollFunc是一个回调函数，由外层设置的。

进一步研究，知道了binCollFunc的来源：

1. struct CollSeq是一个用来比较的对象，它带有一个比较函数和相关上下文。

2. 通过multiSelectCollSeq找到合适的CollSeq对象。

3. multiSelectCollSeq调用sqlite3ExprCollSeq查找。

4. multiSelectCollSeq调用sqlite3CheckCollSeq查找。

5. 查找标准是SELECT或CREATE TABLE所带的COLLATE子句。

6. 也就是说可以通过SELECT或CREATE TABLE的参数来决定选择哪个比较函数。

基于上面这些认识，我们知道比较函数是可以指定的了。接下来的问题是，我们能否自定义比较函数，如何自定义，以及如何安装到SQLite里。很快发现SQLite已经提供了安装比较函数的接口:

int sqlite3_create_collation16(

sqlite3* db,

const char *zName,

int enc,

void* pCtx,

int(*xCompare)(void*,int,const void*,int,const void*)

)

int sqlite3_create_collation(

sqlite3* db,

const char *zName,

int enc,

void* pCtx,

int(*xCompare)(void*,int,const void*,int,const void*)

)

前者用来安装UTF-16的比较函数，后者用来安装UTF-8的比较函数。我们发现，在main.c里已经安装了一些内置的比较函数：

sqlite3_create_collation(db, "BINARY", SQLITE_UTF8, 0,binCollFunc);

sqlite3_create_collation(db, "BINARY", SQLITE_UTF16, 0,binCollFunc);

sqlite3_create_collation(db, "NOCASE", SQLITE_UTF8, 0, nocaseCollatingFunc);

好了，原理清楚了，我们要做的只是提供一个比较函数，并且安装进去就OK了。为了测试，我写一个按拼音排序的比较函数(按笔画排序类似):

int pinyin_cmp(

void *NotUsed,

int nKey1, const void *pKey1,

int nKey2, const void *pKey2)

{

int n = nKey1 < nKey1 ? nKey1 : nKey2;

return pinyin_strncmp(pKey1, pKey2, n + 1);

}

安装比较函数时要注意，因为我们实现的比较函数是针对UTF-16的，所以名字要用UTF-16编码。但是由于linux下默认的wchar_t是32位的，不能直接用L”pinyin”的方式把ANSI字符串转换成UTF-16字符串，只能按下列方式。

unsigned short zName[] = {'p', 'i', 'n', 'y', 'i', 'n', 0};

sqlite3_create_collation16(db, zName, SQLITE_UTF16, 16, pinyin_cmp);

测试结果正常(红色部分为按拼音排序，蓝色部分为默认排序):

sqlite> create table person(name text, age int);

sqlite> insert into person values("张三", 23);

sqlite> insert into person values("张三丰", 23);

sqlite> insert into person values("李四", 24);

sqlite> insert into person values("李四叔", 24);

sqlite> insert into person values("王五", 25);

sqlite> insert into person values("王五妹", 25);

sqlite> insert into person values("赵七", 26);

sqlite> insert into person values("赵七姑", 26);

sqlite>

sqlite> select * from person order by name collate pinyin;

李四|24

李四叔|24

王五|25

王五妹|25

张三|23

张三丰|23

赵七|26

赵七姑|26

sqlite> select * from person order by name;

张三|23

张三丰|23

李四|24

李四叔|24

王五|25

王五妹|25

赵七|26

赵七姑|26

总结：SQLite的架构设计非常优秀，接口定义得也比较合理，支持中文排序变得非常简单。





最近在做资源管理器的设计，SPEC要求中文文件名按拼音排序。于是花了点时间去研究关于拼音排序的问题，然后又花了两小时写了一个函数库。其实知道了原理，按拼音排序的实现很简单，放到这里供大家参考吧。

  我们知道，计算机中的每一个字符都有一个内码。在默认情况下，计算机排序时，比较两个字符的大小就是比较字符内码的大小，这对于英文来说没有问题，因为英文字母的内码是按字母顺序递增的。对于中文来说，就比较麻烦了：首先，中文的排序方式有多种，比如按内码排序、按拼音排序和按笔画排序，要通过参数指定排序的方式，否则计算机就按内码排序了。其次，汉字的内码顺序即不同于拼音顺序，也不同于按笔画顺序。在GB2312编码中，汉字基本上按拼音排序（据说有例外，不太清楚）。在GBK中，它在GB2312基础上进行了扩充，兼容GB2312中的所有字符，所以不是按拼音排序了。在Unicode中，汉字的排列似乎更没有什么规律可言了。

  为了解决内码顺序与用户习惯顺序(如拼音顺序)的冲突，在glibc的locale数据里，要求提供排序方式(collate)的描述。我看了一下glibc-2.3.5提供的locale数据，在简体中文(zh_CN)的locale数据描述里，关于排序方式的描述如下：

% ISO 14651 collation sequence

LC_COLLATE

copy "iso14651_t1"

END LC_COLLATE

  也就是说，照抄iso14651_t1的排序方式。打开iso14651_t1文件看了一下，也没有发现关于中文的特殊处理，可以推断glibc默认的排序方式就是按unicode排序。由此看来，glibc没有提供拼音排序功能，只能由我们自己去实现了。

  实现拼音排序其实很简单，我们可以先取出汉字的拼音然后再比较。这种方法会不会性能低下呢？实际上不会，看起来取拼音过程可能会有点慢，但是一次比较函数调用只需要一次取拼音函数调用，因为只当它们的内码不同时才调用取拼音的函数。

如果仅仅是为了比较汉字的拼音顺序，其实我们可以用更简单的办法，而不必存储那些拼音数据。我们只要预先把所有汉字按拼音排序，汉字在排序后位置偏移量，就可以用来作为比较的基准值。

  如何找到所有汉字呢？如果需要GBK/Unicode中的汉字，GBK和Unicode中的汉字是一一对应的，在Unicode中，汉字所在的区间为0x4e00 - 0x9FA5，所以一个循环就可以打印出所有的汉字。

  如何按拼音排序呢？很简单，有很多工具可以完成这一功能，像wps/word/pagemaker/excel。Word排序太慢，而且对行数也有限制，还是用excel吧。

  数据如何组织？很简单，建立一张unicode与排序偏移量的映射表就行了。考虑到汉字与非汉字之间的比较，我们需要把偏移量加上0x4e00。考虑到空间问题，0x4e00前面都不是汉字，我们的表只要0x9FA5 - 0x4e00 + 1大小就行了，所以表中的unicode要减去0x4e00。






zhaoweixing1989
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
SQLite 中文排序（C# 版）
自由建客
08-12 4355
SQLite 默认排序为 UNICODE 编码序，与汉语拼音没有任何联系，而人们常习惯拼音序。 解决方法在 C# 中其实很简单，但网上尚未见有任何中文资料说这事，只有 C 方法。以下我是通过一英文网页学到的。 首先，定义和注入规则 示例代码如下，很简单无须多解释。只有一点必须注意，对同一个函数只能注入一次，不得重复，所以注入语句写在主函数中再合适不过。 static class
Sqlite3 For Delphi
01-26
Delphi 使用 Sqlite3 的例子，实现了汉字的拼音排序，可直接调用，希望能帮到大家，发现bug欢迎指正！
SQL实现 模糊查询（转）
WGH100817的博客
06-05 229
在进行数据库查询时，有完整查询和模糊查询之分。 一般模糊查询语句如下： SELECT 字段 FROM 表 WHERE 某字段 Like 条件 其中关于条件，SQL提供了四种匹配模式： 1，% ：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。 比如 SELECT * FROM [user] WHERE u_name LIKE '%三%...
Sqlite中文排序研究
benny5609的专栏
03-17 1916
Sqlite是一个用C语言实现的小型SQL数据库引擎。它体积小巧但功能强大，对硬件资源要求很低而且性能表现卓越，非常适合于嵌入式应用环境。最近发现sqlite并不支持中文(拼音/笔画)排序，而这个功能又是我们必需的，所以花了些时间去研究。我对Sqlite的了解只能算是业余级，在研究的过程或许走了些弯路，或许已经有现存的算法可利用，不管怎么样，在研究过程中还是有不少收获，写出来和大家探讨一下。 
中文汉字笔划数据库
11-14
里面排好了中文对应的汉字、笔画数量、汉字对应的unicode码等，方便大家进行笔画排序。可以参考链接： https://blog.csdn.net/ouyang_peng/article/details/83863693 sqlite> .tables BI_HUA_BEAN CHILD_BEAN CLASSIFY_BEAN android_metadata sqlite> .schema CREATE TABLE android_metadata (locale TEXT); CREATE TABLE IF NOT EXISTS "CHILD_BEAN" ("_id" INTEGER PRIMARY KEY ,"NAME" TEXT,"ADDRESS" TEXT,"BIHUA_COUNT" INTEGER NOT NULL ,"CHILD_ID" INTEGER); CREATE TABLE IF NOT EXISTS "CLASSIFY_BEAN" ("_id" INTEGER PRIMARY KEY ,"NAME" TEXT,"BEI_ZHU" TEXT); CREATE TABLE IF NOT EXISTS "BI_HUA_BEAN" ("_id" INTEGER PRIMARY KEY ,"CHINESE" TEXT,"SUM" TEXT, codePointAt INTEGER); sqlite> 数据类似于： 2410|抨|8|25256 2411|拣|8|25315 2412|拤|8|25316 2413|坫|8|22379 2414|拈|8|25288 2415|坿|8|22399 2416|拊|8|25290 2417|者|8|32773 2418|拍|8|25293 2419|顶|8|39030 2420|坼|8|22396 2421|拆|8|25286 2422|乴|8|20084
易语言模块Sqlite表管理.rar
03-29
易语言模块Sqlite表管理.rar 易语言模块Sqlite表管理.rar 易语言模块Sqlite表管理.rar 易语言模块Sqlite表管理.rar 易语言模块Sqlite表管理.rar 易语言模块Sqlite表管理.rar
解决Sqlite中文排序问题（网页）
03-31
一种非常简单的方式解决Sqlite中文排序问题（网页）
[转]Sqlite中文排序研究
heiyeluren的blog（黑夜路人的开源世界）
10-11 3496
 Sqlite中文排序研究 转载时请注明出处：http://blog.csdn.net/absurd Sqlite是一个用C语言实现的小型SQL数据库引擎。它体积小巧但功能强大，对硬件资源要求很低而且性能表现卓越，非常适合于嵌入式应用环境。最近发现sqlite并不支持中文(拼音/笔画)排序，而这个功能又是我们必需的，所以花了些时间去研究。我对Sqlite的了解只能算是业余级，在
Sqlite中文排序方法
天高任鸟飞
05-17 3335
sqlite 的默认编码是unicode,但是没有排序编码，只提供了实现排序的接口。已经验证C#版本 可以正常按照中文的拼音进行排序 androird版本和Swift版本未涉及，作者貌似已经实验成功。c#版：https://blog.csdn.net/ch_fb/article/details/6682791#commentseditandroid版https://my.oschina.net/l...
java汉字转拼音
VicBay专栏
01-22 1002
package com.navinfo.mygim.admin.util;/* * Pinyin.java * vicbay */public class Pinyin{ private String[] name = { "zuo", "zun", "zui", "zuan", "zu", "zou",
sqlite汉字拼音对照表
04-02
sqlite汉字拼音对照表，有 常用汉字表6986个 和 gbk汉字库21000个 两张表，带汉字的gbk编码
sqlite中英文混合排序的实现方式参考
szdenny的博客
02-13 370
1、如果是flutter开发的，在pubspaec.yaml增加 lpinyin: ^2.0.3。项目中有个文件名需要升序和降序排序，数据存储在android项目的sqltie数据库中，设置了。3、执行sql的order by pinyin asc 来执行按照名称升序。4、执行sql的order by pinyin desc 来执行按照名称降序。备注：如果碰到不支持的字符，可以通过增加自定义字典解决。2、如果是android原生开发：参考。
Sqlite android 中文排序
风一样的男人
07-20 1840
SQLiteDatabase db = helper.getReadableDatabase(); db.setLocale(Locale.CHINESE); String sqlAllContacts = "select "+DBConstants.CONTACTS_DATA_ID +" from " + DBConstants.CONTACTS_DATA +" orde
Sqlite中文排序
code life
01-07 2203
Sqlite是一个用C语言实现的小型SQL数据库引擎。它体积小巧但功能强大，对硬件资源要求很低而且性能表现卓越，非常适合于嵌入式应用环境。 最近发现sqlite并不支持中文(拼音/笔画)排序，而这个功能又是我们必需的，所以花了些时间去研究。我对Sqlite的了解只能算是业余级，在研究 的过程或许走了些弯路，或许已经有现存的算法可利用，不管怎么样，在研究过程中还是有不少收获，写出来和大家探讨一下
sql 汉字按照首字母排序
雷鸣隐隐的博客
04-12 9038
1、mysql    1）按照汉字的拼音排序    如果存储汉字的字段编码使用的是GBK字符集，因为GBK内码编码时本身就采用了拼音排序的方法（常用一级汉字3755个采用拼音排序，二级汉字就不是了，但考虑到人名等都是常用汉字，因此只是针对一级汉字能正确排序也够用了），直接在查询语句后面添加ORDER BY name ASC，查询结果将按照姓氏的升序排序；如果存储姓名的字段采用的是utf8字符集，需...
c语言 中文转拼音,汉字转换拼音解决思路
weixin_39636707的博客
05-19 1385
汉字转换拼音为毛我读取的汉字时输出换行了！是2维数组的问题吗！#include#include#include#includeusingnamespacestd;voidmain(){FILE*cp,*fp;intx=0,y=0,i=0,j=0,n;//charp[3]="屎";chara[7000][9];charb[1000];if((cp=fopen("常用汉字拼音对照表.t...
SQL取汉字拼音首字母的存储过程、函数
bell的专栏
04-06 1056
Create  function fun_getPY (    @str nvarchar(4000) )returns nvarchar(4000)asbegin   declare @word nchar(1),@PY nvarchar(4000)   set @PY=   while len(@str)>0  begin    set @word=left(@str,1)     -
Sqlite中中文排序方式
最新发布
05-28
在 SQLite 中，如果要使用中文排序方式，需要在创建表时指定相应的 collation。SQLite 支持多种不同的 collation，其中包括支持中文排序的 collation。常见的中文排序 collation 包括 `zh_CN`、`zh_TW` 和 `zh_HK` 等，具体选择哪种 collation 取决于你的需求。 例如，在创建一个包含中文字符的表时，可以指定 collation 为 `zh_CN.utf8`，示例如下： ```sql CREATE TABLE my_table ( id INTEGER PRIMARY KEY, name TEXT COLLATE zh_CN.utf8 ); ``` 在这个示例中，我们指定了 `name` 列的 collation 为 `zh_CN.utf8`，这样在进行排序时就会按照中文排序方式进行。 如果需要进行中文排序，可以使用 `ORDER BY` 关键字，并指定相应的 collation，例如： ```sql SELECT * FROM my_table ORDER BY name COLLATE zh_CN.utf8; ``` 在这个示例中，我们对 `my_table` 表进行查询，并按照 `name` 列进行排序，同时指定 collation 为 `zh_CN.utf8`。 需要注意的是，为了支持中文排序，需要确保数据库编码为 UTF-8。如果数据库编码不是 UTF-8，需要进行相应的转换或重新创建数据库。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值