Python sqlite3 自定义排序规则如何参与 ORDER BY
如果 SQLite 的默认文本排序不符合业务规则,可以在 Python 连接上调用 sqlite3.Connection.create_collation() 注册一个比较回调,再在查询的 ORDER BY 后写 COLLATE。这样只改变指定查询的比较方式,不会改写表里的原始文本。
官方文档:https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_collation
最小思路是:回调接收两个字符串,返回负数、零或正数表示前者应排在后者之前、相等或之后;规则名称属于当前连接,查询结束后可以用同名规则和 None 将它移除。
把比较回调注册到 SQLite 连接
create_collation(name, callable) 的第一个参数是 SQL 中使用的规则名,第二个参数是 Python 可调用对象。回调最好只做确定性的字符串比较,不访问网络、不读取可变的全局状态,也不要在里面执行 SQL。
import sqlite3
def casefold_order(left, right):
# 统一大小写,避免同一文本因大小写不同被拆成两段。
left_key = left.casefold()
right_key = right.casefold()
if left_key == right_key:
return 0
# 返回负数表示 left 在前,正数表示 right 在前。
return -1 if left_key
这里的规则只是演示大小写折叠,并不等同于中文拼音、自然排序或 locale 排序。真正的业务规则应先明确输入字符集和相等条件,再决定比较键。

在 ORDER BY 中只对目标列启用规则
注册完成后,使用 COLLATE 规则名 指定本次比较。它可以跟在排序表达式后面,也可以和 ASC、DESC 一起使用;没有写 COLLATE 的列仍使用自己的默认比较方式。
# 只让 name 使用自定义规则,created_at 仍按数值倒序排列。 sql = """ SELECT name, created_at FROM item_log ORDER BY name COLLATE CASEFOLD_ORDER ASC, created_at DESC """ # 参数绑定仍然只负责值,规则名应由程序固定,不要拼接外部输入。 rows = con.execute(sql).fetchall()
如果排序需要“未命中优先级后再按文本比较”,可以在 SQL 中先写一个稳定的 CASE 表达式,再让自定义规则处理同一优先级内的文本。例如先按状态权重排序,状态相同时再按名称比较。这样职责更清晰,也便于索引和排查。
| 位置 | 负责内容 | 建议 |
|---|---|---|
| SQL 的 CASE | 固定优先级、空值分组 | 优先写成可读的显式表达式 |
| COLLATE 回调 | 两个文本值之间的相对顺序 | 保持纯函数和结果稳定 |
| 应用层 | 需要外部词典或复杂业务状态的排序 | 考虑预计算 sort_key |

回调返回值、清理方式和版本边界
比较回调至少要满足三个条件:同一对输入反复比较时结果一致;相等时返回 0;不相等时返回负数或正数。不要把“比较结果”误写成差值,尤其是输入并非整数时。若折叠后两个字符串相同,还可以增加一个原文比较作为稳定的次级规则。
规则是连接级注册,不是数据库文件永久保存的 schema。关闭连接后,新的连接需要重新注册。如果连接还要服务其他查询,可以保留规则;确定不再使用时调用 con.create_collation("CASEFOLD_ORDER", None) 清理。Python 官方文档还说明,规则名可以包含 Unicode 字符的行为从 Python 3.11 起得到支持;需要兼容旧版本时使用 ASCII 规则名更稳妥。
自定义 collation 只解决“两个文本值如何排序”。它不负责分词、模糊匹配、全文检索、分页稳定性或跨连接共享。数据量较大、排序频繁且规则固定时,可以把标准化后的排序键存进列并建立索引,避免每次查询都进入 Python 回调。
常见问题
为什么注册成功但查询没有变化?检查 ORDER BY 是否确实写了 COLLATE 规则名,以及查询是否使用了注册规则的同一个连接。
可以把用户输入直接当规则名吗?不建议。规则名应由程序固定并白名单化;用户输入只作为参数绑定的值参与查询。
什么时候应该不用 create_collation?如果排序依赖频繁变化的外部词典、需要全文检索,或数据量大到 Python 回调成为瓶颈,优先考虑预计算排序键、专用 SQL 表达式或应用层排序。
Python json.JSONEncoder.iterencode 怎样分段生成 JSON 文本
- 上一篇
- Python json.JSONEncoder.iterencode 怎样分段生成 JSON 文本
- 下一篇
- Python sqlite3.Connection.total_changes 怎样统计连接累计修改行数
-
- 文章 · python教程 | 13分钟前 | python · Python SQLite sqlite3 SQL脚本 Connection iterdump
- Python sqlite3.Connection.iterdump 怎样生成可恢复的 SQL 脚本
- 342浏览 收藏
-
- 文章 · python教程 | 18分钟前 | python · SQLite · Python SQLite sqlite3 Connection total_changes
- Python sqlite3.Connection.total_changes 怎样统计连接累计修改行数
- 422浏览 收藏
-
- 文章 · python教程 | 23分钟前 | JSON · python · Python JSON JSONEncoder iterencode JSON文本
- Python json.JSONEncoder.iterencode 怎样分段生成 JSON 文本
- 211浏览 收藏
-
- 文章 · python教程 | 35分钟前 | python · pathlib · Python pathlib PurePath with_segments 路径派生
- Python pathlib.PurePath.with_segments 怎样让派生路径保留自定义状态
- 255浏览 收藏
-
- 文章 · python教程 | 40分钟前 | python · Python Calendar itermonthdays4 月历
- Python Calendar.itermonthdays4 怎样保留月历补齐日期的年月日
- 369浏览 收藏
-
- 文章 · python教程 | 22小时前 | python · Python 十六进制 二进制调试 memoryview.hex bytes_per_sep
- Python memoryview.hex 怎样为十六进制输出设置分组分隔符
- 480浏览 收藏
-
- 文章 · python教程 | 23小时前 | python · Python decimal context same_quantum
- Python decimal.Context.same_quantum 怎样判断两个十进制数的指数是否相同
- 336浏览 收藏
-
- 文章 · python教程 | 1天前 |
- Python textwrap.TextWrapper.drop_whitespace 怎样控制换行边缘的空白保留
- 238浏览 收藏
-
- 文章 · python教程 | 1天前 | python · 文件系统 · Python 文件比较 mtime filecmp.clear_cache filecmp.cmp 比较缓存
- Python filecmp.clear_cache 怎样清除文件比较结果缓存
- 290浏览 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 543次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 516次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 500次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 485次学习
-
- PubMedQA
- 深入了解PubMedQA生物医学问答数据集,涵盖其核心功能、使用方法及在临床决策、药物研发等场景的应用,助力提升NLP模型性能。
- 1123次使用
-
- H2O EvalGPT
- H2O EvalGPT是H2O.ai推出的开源LLM评估平台,提供详细的大模型性能排行榜、行业特定基准测试及A/B测试功能,助您快速选择最适合项目的高性能大语言模型。
- 1110次使用
-
- LMArena
- LMArena是加州大学伯克利分校推出的AI模型匿名评测平台。通过盲测投票机制,用户可对比不同大模型回答并生成实时排行榜,助力开发者优化模型及用户选择最佳AI工具。
- 1282次使用
-
- HELM
- 深入了解斯坦福推出的HELM(Holistic Evaluation of Language Models)大模型评测体系。本文解析其核心功能、安装配置步骤及应用场景,涵盖准确性、公平性、鲁棒性等多维度指标,助力开发者全面优化语言模型性能。
- 1224次使用
-
- MMBench
- MMBench是由上海人工智能实验室等机构联合推出的多模态基准测试平台,提供细粒度能力评估、大规模数据集及VLMEvalKit工具。本文详细介绍其核心功能、安装使用方法及应用场景,助力开发者全面评估多模态模型性能。
- 1075次使用
-
- go格式“占位符”输入输出 类似python的input
- 2023-01-19 346浏览
-
- Golang如何调用Python代码详解
- 2023-01-07 235浏览
-
- HTTP 的 response 中的响应体和头部是分开发送的吗?
- 2023-01-28 387浏览
-
- B站等视频网站的弹幕用的是 websocket 还是轮询?
- 2023-02-16 447浏览
-
- Linux 下有什么命令行工具以时序显示 CPU 占用率?
- 2023-01-13 360浏览
