实例：搜索历史记录（SearchHistory）｜技术：去重插入、LIMIT 保留 10 条、时间排序

一、三个查询难题的回顾

搜索历史的数据层要解决三个问题（7-1 文章已建立认知），本篇文章深入实现细节：

1. 去重插入：同词不新增记录，而是刷新时间 + 次数累计；
2. 超限淘汰：超过 10 条时删除最久未搜的；
3. 时间排序：最近搜的排最前。

这三个问题恰好覆盖了 SQL 的三种能力：条件分支（查重）、子查询 + LIMIT（淘汰）、ORDER BY（排序）。逐个拆解。

二、去重插入：UPSERT 语义的两种实现

「存在则更新、不存在则插入」在数据库术语里叫 UPSERT（UPDATE + INSERT）。RDB 没有直接的 UPSERT 语法，落地版用「先查后改」实现（7-1 已展示完整代码），这里对比另一种方案：INSERT OR REPLACE。

方案 A：先查后改（落地版采用）

const pred = new relationalStore.RdbPredicates(SearchHistoryDao.TABLE);
pred.equalTo('keyword', kw);
const result = await store.query(pred);
if (result.goToNextRow()) {
  // 存在 → UPDATE search_time + count
  const id = result.getLong(result.getColumnIndex('id'));
  const count = result.getLong(result.getColumnIndex('count'));
  result.close();
  const values: relationalStore.ValuesBucket = { search_time: Date.now(), count: count + 1 };
  const updatePred = new relationalStore.RdbPredicates(SearchHistoryDao.TABLE);
  updatePred.equalTo('id', id);
  await store.update(values, updatePred);
} else {
  result.close();
  const values: relationalStore.ValuesBucket = { keyword: kw, search_time: Date.now(), count: 1 };
  await store.insert(SearchHistoryDao.TABLE, values);
}

优点：次数累计逻辑自然（count + 1 在代码里完成）；缺点：两次数据库交互（查 + 写）。

方案 B：INSERT OR REPLACE

INSERT OR REPLACE INTO search_history (id, keyword, search_time, count)
VALUES (?, ?, ?, ?);

问题：REPLACE 的语义是「删除旧行、插入新行」——如果旧行存在，它的 id 会变化（新 id），且必须提供全部字段。我们想保留 count 累加值就得先查出来，等于还是要先查。更糟的是 id 变化会导致外键引用失效（本实例无外键，影响小）。

结论

先查后改是搜索历史的正确方案——因为「次数累计」需要读旧值，无论怎么优化都要先查。INSERT OR REPLACE 更适合「无状态覆盖」场景（如用户设置）。方案选择取决于业务是否需要读旧值。

三、超限淘汰：LIMIT 子查询删除的三种写法

「保留最近 N 条」的删除，SQLite 有几种写法，逐一对比：

写法一：子查询取 id（落地版采用）

DELETE FROM search_history
WHERE id IN (
  SELECT id FROM search_history
  ORDER BY search_time ASC
  LIMIT ${overflow}
);

优点：语义清晰、通用（任何 SQLite 版本可用）；缺点：两层级联查询。

写法二：ROWID 直接删（SQLite 特性）

DELETE FROM search_history
WHERE rowid IN (
  SELECT rowid FROM search_history
  ORDER BY search_time ASC
  LIMIT ${overflow}
);

用内置 rowid 替代 id，避免子查询里读 id 列——性能略优（rowid 是主键索引），语义相同。如果表有 INTEGER PRIMARY KEY，id 就是 rowid 的别名，两种写法等价。

写法三：先查后删（两段式）

// 先查出最旧的 overflow 个 id
const result = await store.querySql(
  `SELECT id FROM ${SearchHistoryDao.TABLE} ORDER BY search_time ASC LIMIT ${overflow}`
);
// 再逐个删除
for (const id of ids) { await store.delete(...); }

缺点：代码多、多次数据库交互。不如写法一/二一条 SQL 干净。

结论

落地版用写法一（id IN 子查询），兼顾通用性与可读性。记住这个模式：SQLite 删除 TOP-N 记录 = DELETE … WHERE id IN (SELECT id … ORDER BY … LIMIT N)，它是「保留最近 N 条」这类需求的标准答案。

四、时间排序与「上浮」行为

历史列表按 search_time DESC 排序，这里有一个值得展开的交互细节：重复搜索的词会「上浮」到最前。

场景：用户先搜「SQLite」，再搜「ArkTS」，再搜「SQLite」——第二次搜 SQLite 时，它的 search_time 被刷新为最新，排序时排到第一位。历史列表的最终顺序：SQLite（最新）、ArkTS、其他。

这个「上浮」行为是怎么来的？ 完全由「去重插入时刷新 search_time」驱动——不需要额外的排序逻辑，ORDER BY search_time DESC 自然呈现「最近用的词在上」。数据层的行为设计决定了交互体验：刷新时间戳 → 自动上浮，这是搜索历史的行业惯例（浏览器、App 都这样）。

五、完整的方法矩阵

SearchHistoryDao 的完整方法清单：

方法	作用	关键 SQL
getStore	单例 + 建表	CREATE TABLE IF NOT EXISTS
queryAll	全部历史	ORDER BY search_time DESC
addKeyword	去重插入 + 刷新 + 淘汰	查重 → UPDATE/INSERT → trim
trim	超限淘汰	DELETE … IN (SELECT … LIMIT)
deleteOne	删单条	DELETE WHERE id = ?
clearAll	一键清空	DELETE（无条件）
initSeedData	12 条种子	见 7-4 文章

每个方法职责单一、签名简洁（context + 业务参数），页面调用零心智负担。

六、技术要点对照表

技术点	实现方式	生产价值
UPSERT	先查后改（存在 UPDATE/不存在 INSERT）	需读旧值的场景必备
INSERT OR REPLACE	不适用的场景分析	无状态覆盖才用
TOP-N 删除	DELETE … WHERE id IN (SELECT … LIMIT)	保留最近 N 条的标准答案
上浮排序	刷新 search_time + ORDER BY DESC	最近使用的词排最前
唯一约束	keyword UNIQUE	去重的数据库级保证
次数累计	count + 1	热度徽标

七、常见问题 FAQ

Q1：为什么不用 keyword 做主键？
A：可以但不好。用 keyword TEXT PRIMARY KEY 也能去重，但主键是字符串索引，且一旦改词（如大小写统一）会牵动引用。用「id 自增主键 + keyword 唯一约束」分离「标识」与「业务唯一」，更灵活。

Q2：大小写敏感吗？搜「harmonyos」和「HarmonyOS」算重复吗？
A：默认算不重复（SQLite 的 UNIQUE 区分大小写）。如果要忽略大小写去重，需要 COLLATE NOCASE 约束（keyword TEXT UNIQUE COLLATE NOCASE）或插入前统一 toLowerCase()。本实例保持默认，中文搜索无此问题。

Q3：空格和空串怎么处理？
A：addKeyword 开头 kw.trim() 去首尾空格，空串直接 return 不记录。避免「 」这种纯空格关键词污染历史。输入卫生是数据层的第一道防线。

Q4：MAX_KEEP 是 10，能改成 20 吗？
A：改 const MAX_KEEP = 10 为 20 即可，页面副标题「保留最近 N 条」自动跟随（绑定 histories.length）。常量单一来源让调整零风险。

Q5：清空历史后重新搜索会怎样？
A：正常重新记录——clearAll 删空表，再搜索时表为空，addKeyword 走 INSERT 分支新建。历史从零开始，符合预期。

Q6：trim 在每次 addKeyword 都执行，频繁吗？
A：trim 内部先判断 total <= MAX_KEEP 直接 return，只有超限才执行删除。未超限时只是一次 COUNT 查询，开销可忽略。带守卫的判断让高频调用的成本最低。

Q7：并发场景（两个搜索同时进来）会怎样？
A：单用户 App 的搜索是串行事件（用户一次输入一个词），并发风险极低。即使并发，keyword 唯一约束兜底（第二次 INSERT 冲突报错，被异常捕获）。本实例未做并发控制，符合场景复杂度。

八、文章小结

本篇文章深入讲解了搜索历史数据层的三个核心实现：先查后改的 UPSERT（需读旧值场景）、DELETE … IN (SELECT … LIMIT) 的 TOP-N 删除（SQLite 标准写法）、刷新时间戳驱动的上浮排序。这三个模式组合起来，就是「有上限的最近记录」这类场景的完整数据层答案——搜索历史、最近播放、浏览足迹全部适用。

下一篇（7-4）展示 12 条热词种子数据，让标签流一开屏就「撑起榜单」。

九、增删查三件套：查重、热度自增、关键词搜索

前三节讲了「去重插入 + 超限淘汰 + 排序」三个设计难点，本节把增删查的每个原子操作补齐：插入前查重、热度自增 UPDATE、LIKE 关键词搜索。

9.1 插入前查重：两种判定的等价写法

先查后改的第一步是查重。除了 7-1 的「query + goToNextRow」写法，也可以用 COUNT 判定：

-- 查重：统计同名记录数
SELECT COUNT(*) AS cnt FROM search_history WHERE keyword = ?;

const pred = new relationalStore.RdbPredicates(SearchHistoryDao.TABLE);
pred.equalTo('keyword', kw);
const result = await store.query(pred);
const has = result.goToNextRow(); // true = 已存在
result.close();

两种写法等价：goToNextRow 判断「有没有下一行」，COUNT 判断「行数是否大于 0」。落地版选前者——它顺便把 id、count 取出来，省一次查询。真正的双保险来自数据库层的 keyword UNIQUE 唯一约束：即使代码查重漏判（如极端并发），重复 INSERT 也会被约束拒绝，不会产生脏数据。

9.2 热度自增 UPDATE：count 在 SQL 里加还是代码里加

热度 = 搜索次数。加一次热度有两种写法：

-- 写法 A：SQL 层自增
UPDATE search_history SET count = count + 1, search_time = ? WHERE id = ?;

-- 写法 B：代码层自增（落地版）
-- count + 1 在 ArkTS 里完成，UPDATE 只写回结果

写法	读旧值	原子性	落地版选型
A：count = count + 1	SQL 内部完成	一条语句原子完成	查重用 COUNT 时推荐
B：代码自增	需先 getLong 读出	读-改-写有中间态	查重已拿到旧值，顺手

落地版选 B 的原因：查重时已经拿到了 count（不拿白不拿），且 B 的语义一目了然。选型标准是「旧值是否顺手可得」——旧值已在手，B 少写一条 SQL；旧值没查到，A 更简洁。注意 UPDATE 的谓词用 equalTo('id', id) 而不是 equalTo('keyword', kw)——用主键定位行，命中唯一、绝不误更新多条。

9.3 LIKE 搜索：历史词联想

搜索框输入时可以联想历史里的相似词：

SELECT keyword, count FROM search_history
WHERE keyword LIKE '%?%'
ORDER BY count DESC, search_time DESC;

const pred = new relationalStore.RdbPredicates(SearchHistoryDao.TABLE);
pred.like('keyword', `%${kw}%`);   // 模糊匹配
pred.orderByDesc('count');          // 热度优先
pred.orderByDesc('search_time');    // 同热度按时间

LIKE '%关键词%' 是包含匹配：搜「ark」能命中「ArkTS 语法」「元服务与 ArkTS」。LIKE 的百分号必须自己拼——RdbPredicates.like 不会自动加，漏写就退化成等值查询，这是最隐蔽的坑。中文搜索同样适用，默认按字符匹配。

十、热门榜单：GROUP BY + ORDER BY 的热度聚合

搜索历史除了「最近记录」，还能产出「热门榜单」——按热度给关键词排名。榜单的 SQL 有两条路径：

10.1 已有热度列：ORDER BY 直接排序

本表的 count 字段就是热度，直接排序即可：

SELECT keyword, count FROM search_history
ORDER BY count DESC, search_time DESC
LIMIT 5;

ORDER BY 多列时先按第一列、相同时再按第二列：count 相同（并列热度）时，最近搜的排前面。这就是「热门榜 + 时效」的经典双排序。

10.2 无热度列：GROUP BY 现算热度

如果表里没有 count 列（只有 keyword + search_time），榜单要靠分组聚合现算：

SELECT keyword, COUNT(*) AS hot, MAX(search_time) AS last_time
FROM search_history
GROUP BY keyword
ORDER BY hot DESC, last_time DESC
LIMIT 5;

子句	作用	本例
GROUP BY keyword	同词归一组	每个词一组
COUNT(*)	组内行数 = 热度	搜 3 次 → hot = 3
MAX(search_time)	组内最近时间	并列热度时用
ORDER BY hot DESC	热度降序	最热在前

GROUP BY 的经典坑：SELECT 里除了聚合函数，只能出现 GROUP BY 的列——想带出别的字段必须套 MAX/MIN 聚合，否则 SQLite 取哪一行不确定。记住：落地版有 count 列走 10.1，GROUP BY 是「没有热度列」时的补救方案。

十一、清空历史：delete 谓词的空条件艺术

clearAll 是一键清空的实现，它揭示了 RdbPredicates 的一个特性：不挂任何条件的谓词 = 无条件删除。

static async clearAll(context: common.Context): Promise<void> {
  const store = await SearchHistoryDao.getStore(context);
  // 谓词不带 equalTo —— 没有 WHERE 条件，删全表
  const pred = new relationalStore.RdbPredicates(SearchHistoryDao.TABLE);
  await store.delete(pred);
}

生成的 SQL 只有一句：

DELETE FROM search_history;

对比 deleteOne：

const pred = new relationalStore.RdbPredicates(SearchHistoryDao.TABLE);
pred.equalTo('id', id);   // 带条件 → WHERE id = ?
await store.delete(pred);

方法	谓词	生成 SQL
deleteOne	equalTo(‘id’, id)	DELETE FROM search_history WHERE id = ?
clearAll	空谓词	DELETE FROM search_history

两个 delete 共用同一个 API，区别只在谓词是否挂条件——这是 RdbPredicates 的优雅之处：条件是可选参数，空条件即全表操作。页面层「清空」按钮调 clearAll 前弹确认框（promptAction.showDialog），防止误触不可恢复。

十二、完整 SQL 生成解读：方法 → SQL 对照

把整个 SearchHistoryDao 的每个方法展开成它实际执行的 SQL，一览全貌：

方法	RdbPredicates 链 / 行为	生成 SQL
queryAll	orderByDesc(‘search_time’)	SELECT * FROM search_history ORDER BY search_time DESC
addKeyword 查重	equalTo(‘keyword’, kw)	SELECT * FROM search_history WHERE keyword = ?
addKeyword 更新	equalTo(‘id’, id)	UPDATE search_history SET search_time = ?, count = ? WHERE id = ?
addKeyword 插入	insert 直接传表名	INSERT INTO search_history (keyword, search_time, count) VALUES (?, ?, ?)
trim 淘汰	IN 子查询	DELETE FROM search_history WHERE id IN (SELECT id … ORDER BY search_time ASC LIMIT ?)
deleteOne	equalTo(‘id’, id)	DELETE FROM search_history WHERE id = ?
clearAll	空谓词	DELETE FROM search_history
hotList	orderByDesc(‘count’)	SELECT … ORDER BY count DESC, search_time DESC LIMIT ?

读这张表的方式：每个方法 = 一条谓词链 + 一个 SQL 模板。RdbPredicates 链的每个方法调用（equalTo / orderByDesc / like）对应 SQL 的一个子句，链的顺序就是子句的顺序。读懂这张表，就等于读懂了 SearchHistoryDao 的全部数据库行为——以后调优（加索引、改排序、加条件）都能在 SQL 层面直接推演，无需翻代码。

十三、FAQ 补充

Q1：count 自增用 SQL 写（count = count + 1）会不会更好？
A：更原子——一条 SQL 完成「读-加-写」，没有中间态。落地版选代码自增纯粹因为查重时旧值已到手。如果查重用 COUNT 写法，建议改回 SQL 自增，两种选型各有适用前提。

Q2：LIKE 搜索会不会很慢？
A：历史表最多几十行，全表扫描也无感。若表很大可给 keyword 建索引，但 LIKE '%词%' 的前置百分号会导致索引失效（SQLite 只有前缀匹配才走索引）——本场景规模下无需优化。

Q3：清空历史能恢复吗？
A：不能。DELETE 是物理删除，没有回收站。页面已用确认框兜底，数据层不需要额外设计。需要「恢复」的业务应设计软删除（加 deleted 标记列），搜索历史不需要——需求匹配复杂度，不过度设计。

Q4：热门榜单和最近记录能用同一张表吗？
A：能，而且应该——一张 search_history 表同时服务「时间线」（ORDER BY search_time）和「热度榜」（ORDER BY count），只是排序维度不同。这就是一表多读：表结构不变，查询视角决定输出。

Q5：为什么 trim 不用 DELETE ... ORDER BY ... LIMIT 直接写？
A：SQLite 的 DELETE 语法不支持 ORDER BY / LIMIT 直接跟——必须用子查询包一层（见第三节写法一）。这是 SQLite 的语法限制，不是设计取舍，写法二/三只是它的变体。

Q6：唯一约束和先查后改，哪个才是真正的去重保证？
A：唯一约束是最终防线，先查后改是主流程。先查后改负责「正常路径」（存在则更新），唯一约束负责「异常兜底」（并发漏判时拒绝重复插入）。两者缺一不可——只靠查改会漏，只靠约束会报错。