Sqlite中文排序研究

最新推荐文章于 2024-10-22 23:21:32 发布
转载 最新推荐文章于 2024-10-22 23:21:32 发布 · 830 阅读 · 0
文章标签：

#sqlite #collation #insert #table #架构设计 #测试

本文介绍了如何在SQLite数据库中实现中文排序功能，特别是拼音排序。通过分析SQLite架构并自定义比较函数，成功实现了按拼音排序的需求。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

出处和作者联系方式：http://blog.youkuaiyun.com/absurd  

作者联系方式：Li XianJing <xianjimli at hotmail dot com>

更新时间：2006-12-19

 

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的架构设计非常优秀，接口定义得也比较合理，支持中文排序变得非常简单。

 

(关于pinyin_strncmp的实现，将在下一篇文章中介绍)

 

~~end~~

SQLite FTS3 和 FTS4 扩展(三十二)
04-20 1420
SQLite拥有别人无法比拟的装机量，究竟什么成就了SQLite呢，本文将SQLite的历史版本记录粗列一下，供各位朋友参详如何做一个高质量的程序的引文。SQLite拥有超过225个API，本系列文章将深入介绍 SQLite C/C++ 数据库接口的相关知识，包括如何连接和打开 SQLite 数据库、执行查询和事务管理、提取查询结果集等等，帮助开发者更好地了解和使用 SQLite 数据库接口。
SQLite 中文排序（C# 版）
自由建客
08-12 4652
SQLite 默认排序为 UNICODE 编码序，与汉语拼音没有任何联系，而人们常习惯拼音序。 解决方法在 C# 中其实很简单，但网上尚未见有任何中文资料说这事，只有 C 方法。以下我是通过一英文网页学到的。 首先，定义和注入规则 示例代码如下，很简单无须多解释。只有一点必须注意，对同一个函数只能注入一次，不得重复，所以注入语句写在主函数中再合适不过。 static class
Sqlite中文排序
code life
01-07 2271
Sqlite是一个用C语言实现的小型SQL数据库引擎。它体积小巧但功能强大，对硬件资源要求很低而且性能表现卓越，非常适合于嵌入式应用环境。 最近发现sqlite并不支持中文(拼音/笔画)排序，而这个功能又是我们必需的，所以花了些时间去研究。我对Sqlite的了解只能算是业余级，在研究 的过程或许走了些弯路，或许已经有现存的算法可利用，不管怎么样，在研究过程中还是有不少收获，写出来和大家探讨一下
[转]Sqlite中文排序研究
heiyeluren的blog（黑夜路人的开源世界）
10-11 3558
 Sqlite中文排序研究 转载时请注明出处：http://blog.youkuaiyun.com/absurd Sqlite是一个用C语言实现的小型SQL数据库引擎。它体积小巧但功能强大，对硬件资源要求很低而且性能表现卓越，非常适合于嵌入式应用环境。最近发现sqlite并不支持中文(拼音/笔画)排序，而这个功能又是我们必需的，所以花了些时间去研究。我对Sqlite的了解只能算是业余级，在
易语言源码sqlite操作演示.rar
02-15
易语言是一种专为中国人设计的编程语言，它以简明直观的中文编程语法著称，降低了编程的门槛。SQLite则是一种轻量级的、自包含的、无服务器的数据库引擎，广泛应用于移动设备和嵌入式系统。这个压缩包"易语言源码...
基于Python和thulac的网络小说词义分析及sqlite3数据库存储源码
10-11
本项目正是基于这样的背景，利用Python编程语言与thulac中文分词工具，实现了对网络小说文本的词义分析，并将分析结果存储于SQLite3数据库中，以便于进一步的数据分析和研究。 项目共包含75个文件，这些文件是项目...
delphi Sqlite
dkopg24406的专栏
08-13 1136
Delphi中SQLite如何读写二进制字段(Blob类型) 在Delphi中,有大量的组件可以操作SQLite数据库,如UniDAC就是其中一个比较优秀的,当然还有ASQLite3Components,也有SQLite3版的ODBC驱动,可直接用ADO操作。本文简要说明SynopseSQLite3读写二进制字段,先说下SynopseSQLite3的优点,静态编译集成SQLite3引擎...
解决Sqlite中文排序问题（网页）
03-31
一种非常简单的方式解决Sqlite中文排序问题（网页）
MySQL按照汉字的拼音排序简单实例
12-16
如果存储姓名的字段采用的是GBK字符集，那就好办了，因为GBK内码编码时本身就采用了拼音排序的方法（常用一级汉字3755个采用拼音排序，二级汉字就不是了，但考虑到人名等都是常用汉字，因此只是针对一级汉字能正确排序也够用了）。 直接在查询语句后面 添加 order by name asc; 查询结果按照姓氏的升序排序； 如果存储姓名的字段采用的是 utf8字符集，需要在排序的时候对字段进行转码；对于的代码是  order by convert(name using gbk) asc; 同样，查询的结果也是按照姓氏的升序排序； 以上这篇MySQL按照汉字的拼音排序简单实例就是小编分享给大家的全部内
汉字笔画顺序数据库sqlite
08-20
汉字笔画顺序数据库sqlite
Sqlite中文排序方法
天高任鸟飞
05-17 3500
sqlite 的默认编码是unicode,但是没有排序编码，只提供了实现排序的接口。已经验证C#版本 可以正常按照中文的拼音进行排序 androird版本和Swift版本未涉及，作者貌似已经实验成功。c#版：https://blog.youkuaiyun.com/ch_fb/article/details/6682791#commentseditandroid版https://my.oschina.net/l...
sqlite排序规则
简约不简单
05-23 4391
sqlite 排序规则 BINARY - 使用 memcmp() 对字符串数据进行比较，无论文本编码。 NOCASE - 与 BINARY 相同，除了在比较执行之前会将 ASCII 码的 26 个大写字母被折换为与其等值的小写字母。注意，只有 ASCII 码的字符进行大小写折换。由于所需表的大小，SQLite 不会尝试对完整 UTF 的大小写进行折换。 RTRIM - 与 BINA...
SQLITE排序
qq_25194681的博客
10-22 605
最终实现的效果：先查询第一层2列开始的1、4、2、3排，再查询第三列、四列...,然后第二层...排序优先级：层>列>排(1>2,4>3)最终排的优先级 1>4>2>3。
按照名称中文字符和英文字符排序，给出对应sql，以及详细解释
最新发布
06-06
<think>我们正在讨论SQL中的中文排序问题。根据之前的对话，用户现在想了解SQL查询中如何同时按照中文和英文字符进行排序，包括原理和示例。此外，用户提供了三个引用，主要关于SQLite中文排序的研究，指出SQLite默认使用Unicode排序（即按码点排序），没有专门的中文排序支持。结合用户引用[1][2][3]的信息：-SQLite在默认情况下使用Unicode码点排序（即按字符的编码值排序），这对于中文来说并不是按拼音或笔画排序。-如果需要在SQLite中实现中文排序（如按拼音），需要自己实现排序规则。用户当前的请求是：获取SQL查询中按照中文和英文字符排序的SQL语句及详细解释，并询问排序原理、示例和解释。因此，我们需要分两部分回答：1.**排序原理**：解释默认排序（按码点）和自定义排序（如拼音）的原理。2.**示例**：提供在SQLite中如何实现按拼音排序的示例（自定义函数），以及其他数据库（如MySQL）的内置支持示例。另外，由于用户提到了中英文混合排序，我们还需要考虑如何同时处理中文和英文，确保英文按字母顺序，中文按拼音顺序。##回答结构1.**排序原理**-默认排序（Unicode码点排序）的原理和问题-中文排序的常见方式（拼音、笔画等）-中英文混合排序的挑战2.**在SQLite中实现中文排序（按拼音）**-自定义排序规则函数（使用Python或其它语言扩展）-示例SQL语句3.**在支持中文排序的数据库中的示例（如MySQL）**4.**总结**##详细内容###1.排序原理-**默认排序（按码点）**：在大多数数据库系统中，字符串排序默认按照字符的编码值（如Unicode码点）进行比较。例如：-英文字符：'A'(U+0041)< 'B'(U+0042)< 'a'(U+0061)（注意：大写字母的码点小于小写字母）-中文字符：'一'(U+4E00)< '丁'(U+4E01)< '丈'(U+4E08)...这种顺序与拼音或笔画无关。在SQLite中，默认排序规则为`BINARY`，即按字节值（对于UTF-8，按编码后的字节序列）排序，实际上等同于按Unicode码点排序。-**中文排序的常见方式**：-**拼音排序**：将汉字转换为拼音，然后按拼音字母顺序排序。多音字需要额外处理。-**笔画排序**：根据笔画数和笔顺排序。在支持中文排序的数据库中（如MySQL，SQLServer），通常内置了按拼音排序的规则。但SQLite没有内置支持，需要自定义。-**中英文混合排序**：目标是将英文和中文混合的字符串按照英文按字母顺序、中文按拼音顺序排序。常见的做法是：-将整个字符串转换为一个统一的排序键（如将中文部分转换为拼音，英文部分不变，然后整体比较）。###2.在SQLite中实现中文排序（按拼音）由于SQLite本身不支持中文拼音排序，需要用户自定义排序规则。这里以Python的sqlite3模块为例，展示如何注册一个自定义排序函数来实现按拼音排序。**步骤**：1.使用第三方库（如`pypinyin`）将中文字符串转换为拼音字符串。2.在SQLite连接上注册一个自定义排序函数。3.在SQL查询中使用该函数进行排序。**示例代码（Python+SQLite）**：```pythonimportsqlite3frompypinyinimportpinyin,Style#将中文转换为拼音字符串（不带声调，空格分隔）defchinese_to_pinyin(text):#如果是英文字母或数字，直接返回原字符串小写（或大写）以保持一致性#但为了区分中英文，我们只转换中文部分，这里简单处理：整个字符串转为拼音#注意：这里没有处理非中文字符，实际应用需要更复杂的处理pinyin_list=pinyin(text,style=Style.NORMAL)return''.join([item[0]foriteminpinyin_list])#自定义排序函数：将中文字符串转换为拼音后进行比较defcollate_pinyin(str1,str2):pinyin1=chinese_to_pinyin(str1)pinyin2=chinese_to_pinyin(str2)ifpinyin1< pinyin2:return-1elifpinyin1>pinyin2:return1else:return0#连接到SQLite数据库conn=sqlite3.connect(':memory:')#注册排序函数conn.create_collation('PINYIN',collate_pinyin)#创建测试表cursor=conn.cursor()cursor.execute('''CREATETABLEtest(nameTEXT)''')#插入测试数据data=['张三','李四','王五','Alice','Bob','陈六']fornameindata:cursor.execute("INSERTINTOtestVALUES(?)",(name,))#按拼音排序查询cursor.execute("SELECTnameFROMtestORDERBYnameCOLLATEPINYIN")results=cursor.fetchall()print("按拼音排序结果:")forrowinresults:print(row[0])```**输出结果**（示例）：```AliceBob陈六(ChenLiu)李四(LiSi)王五(WangWu)张三(ZhangSan)```**解释**：-我们使用`pypinyin`库将中文字符串转换为拼音字符串（不带声调）。-自定义排序函数`collate_pinyin`将两个字符串分别转换为拼音后比较。-在SQLite中，使用`CREATECOLLATION`注册了一个名为`PINYIN`的排序规则。-查询时，在`ORDERBY`子句中指定`COLLATEPINYIN`即可使用这个规则。**注意**：-这个示例简单地将整个字符串转换为拼音，实际应用中可能需要更复杂的逻辑来处理中英混合字符串（如只转换中文部分，保留英文部分，并统一大小写）。-对于非中文部分，我们在这里直接按原字符串比较（但转换为拼音后中文部分变成了英文，所以整个字符串就是英文字符串）。这可能会导致大小写问题，一般我们会统一转换为小写或大写再比较。###3.在MySQL中实现中文拼音排序MySQL提供了内置的中文排序规则，比如`utf8mb4_zh_0900_as_cs`（MySQL8.0以上）或`utf8mb4_chinese_ci`（较旧版本）。**示例**：```sqlCREATETABLEtest(nameVARCHAR(100))CHARACTERSETutf8mb4COLLATEutf8mb4_zh_0900_as_cs;INSERTINTOtest(name)VALUES('张三'),('李四'),('王五'),('Alice'),('Bob'),('陈六');SELECTnameFROMtestORDERBYname;```**结果**：```AliceBob陈六李四王五张三```**解释**：-在MySQL中，我们只需在创建表时指定支持中文拼音排序的collation，排序时就会自动按拼音排序。-`utf8mb4_zh_0900_as_cs`是MySQL8.0引入的针对中文的排序规则，其中`zh`代表中文，`as`表示区分重音（对中文影响不大），`cs`表示区分大小写。还有`utf8mb4_zh_0900_as_ci`（不区分大小写）。###4.总结-**原理**：SQL中的排序依赖于排序规则（Collation）。默认情况下，按字符的编码值（码点）排序，这不符合中文的拼音顺序。要实现中文排序，需要按拼音（或笔画）的规则进行。-**SQLite**：无内置支持，需要自定义排序规则（利用编程语言扩展）。-**MySQL等**：有内置的中文排序规则，可直接使用。##相关问题§§相关问题§§1.SQLite中如何实现按中文笔画排序？2.MySQL中中文排序规则有哪些，如何选择？3.在SQLite中自定义排序规则会影响性能吗？如何优化？</think> ### SQL 中文字符排序原理与实现 #### 一、排序原理分析 1. **默认排序规则（按码点排序）** SQL 数据库默认使用字符的 **Unicode 码点（Code Point）** 进行排序，即依据字符在 Unicode 编码表中的二进制值比较： ```sql -- 示例：默认按码点排序的结果 SELECT '苹果' AS fruit UNION ALL SELECT 'Banana' UNION ALL SELECT '橙子' ORDER BY fruit; ``` **排序结果**：`Banana` → `橙子` → `苹果` 原因：英文字符 `B`(U+0042) < 汉字 `橙`(U+6A59) < `苹`(U+82F9)[^3]。 2. **中文排序的挑战** - 汉字码点顺序与拼音/笔画顺序无直接关联（如 "张"(U+5F20) > "李"(U+674E)，但拼音 Li < Zhang） - GB2312 编码部分遵循拼音序，但 GBK/Unicode 无此规律[^3] - SQLite 等数据库依赖系统级排序规则（如 glibc 的 `iso14651_t1`），无原生中文支持[^1][^2] #### 二、中文排序实现方案 1. **SQLite 自定义排序（Python 扩展）** 需注册拼音转换函数实现： ```python # Python + SQLite 扩展示例 import sqlite3 from pypinyin import lazy_pinyin def chinese_sort(text): return ''.join(lazy_pinyin(text)) # 转为拼音字符串 conn = sqlite3.connect(':memory:') conn.create_function('PINYIN', 1, chinese_sort) # 注册函数 # 使用示例 cursor = conn.execute(""" SELECT name FROM users ORDER BY PINYIN(name) COLLATE NOCASE """) ``` **执行效果**： | 原数据 | 排序后 | |--------|--------| | 王五 | → 李四 | | 李四 | → 王五 | | Adam | → Adam | 2. **MySQL 内置拼音排序** 直接指定中文排序规则： ```sql -- 创建表时指定排序规则 CREATE TABLE employees ( name VARCHAR(50) COLLATE utf8mb4_zh_0900_as_cs ); -- 查询时动态指定 SELECT name FROM employees ORDER BY name COLLATE utf8mb4_chinese_ci; ``` 3. **跨数据库中英文混合排序** 需统一转换为排序键： ```sql /* 通用方案：将中文转拼音 + 英文小写 */ SELECT name, CASE WHEN name REGEXP '[\u4e00-\u9fff]' THEN CONVERT(name USING GBK) -- 中文按GBK拼音序 ELSE LOWER(name) -- 英文小写 END AS sort_key FROM products ORDER BY sort_key; ``` #### 三、排序原理对比 | 排序类型 | 原理 | 特点 | |----------------|-------------------------------|--------------------------| | 默认码点排序 | 按 Unicode 二进制值比较 | 中文顺序不符合人类认知 | | 拼音排序 | 汉字 → 拼音 → 字母序比较 | 符合常用习惯，需额外处理 | | 笔画排序 | 计算笔画数 + 笔顺权重 | 专业场景使用 | | 部首排序 | 按康熙部首分类后排序 | 古籍整理等特殊用途 | > **特殊字符处理**： > - 数字：`"10斤" > "2斤"`（按字符比较），需用 `CAST(col AS UNSIGNED)` 转数值 > - 符号：`#`(U+0023) < `中`(U+4E2D) < `¥`(U+00A5) > $$ \text{排序权重} = \begin{cases} 0 & \text{非中文字符} \\ \text{pinyin\_weight}(c) & \text{汉字} \end{cases} $$ ### 总结与建议 1. **优先方案**： - MySQL：直接使用 `utf8mb4_zh_0900_as_cs` 规则 - SQLite：扩展拼音排序函数（推荐 `pypinyin` 库） - 混合数据：先分离中英文再统一转换 2. **性能优化**： ```sql -- 为排序键建索引 CREATE INDEX idx_sort ON products ( CASE WHEN name REGEXP '[\u4e00-\u9fff]' THEN CONVERT(name USING GBK) ELSE LOWER(name) END ); ``` 3. **避坑指南**： - SQLite 的 `COLLATE LOCALIZED` 在部分系统仍按码点排序[^1] - 多音字需人工映射（如 "重庆" → `chongqing`） - 繁体字使用 `zh_unicode_ci` 与简体规则不同 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值