转 sqlite 后，用 sqlite 的 FTS5 全文搜索，能满足需求吗？

（ Trigram 分词器支持 3 字及以上的 LIKE '%关键词%' 搜索。
或者，有个号称参考微信 sqlite 优化原理写的 simple 开源分词器，也支持中文分词？）

我在那个帖子里的 3 楼，也问了个类似的问题，也给出一点猜测（但没人理我。。）

反正 20 天前，在一个 [帖子]( https://www.v2ex.com/t/882773 ) 里，有很多人反映，MySQL 单表存 1~2 亿（#11 楼 #16 #18 #19 #21 #35 ）、4 亿（#27 ）、10 亿（#36 ）、20 亿（#28 ）都没问题，查询也很快（< 10 ms ，#27 #28 ）

@uil330

> 1.50TB 放在 cloud 的数据我想不出比 sql 更有效率的方法了

这是要取回本地嘛？还是更新 /追加云端的数据？

若取回本地的话，写个脚本应该会更快吧？*（至少，稀疏数据传得会快些？）*



@sun522198558 数据库新手，好奇想问下，连 1~2 MB 的 `SQLite` 功能都开始丰富多样起来了，为啥还坚持旧版本 `MySQL` 呢？

未定义行为，摸透了也确实能用 *（反正我没摸透，不知为啥那个帖子里的赋值是异常的）*

不支持窗口函数，那就用基础 `SQL` 吧：*（感觉会比窗口函数慢）*

```sqlite
WITH
　 data(id, data, time) AS (
　　 VALUES
　　　(1, 10, '00:03'),
　　　(1, 50, '04:23'),
　　　(1, 20, '07:03')
　),

　 ids(id) AS (
　　 SELECT DISTINCT id
　　　 FROM data
　),

　 time(time) AS (
　　 SELECT format('%02d:00', value)
　　　 FROM json_each('[1, 2, 3, 4, 5, 6, 7, 8]')
　)

-- 方法一
SELECT i.id, t.time,
　　　 (SELECT data
　　　　　 FROM data
　　　　 WHERE id = i.id
　　　　　 AND time < t.time
　　　　 ORDER BY time DESC
　　　　 LIMIT 1) data,
　　　 NULL useless
　 FROM ids i
　 JOIN time t

UNION ALL
VALUES ('----', '----', '----', '----')
UNION ALL

-- 方法二
SELECT i.id, t.time, d.data, MAX(d.time) useless
　 FROM ids i
　 JOIN time t
　 LEFT JOIN data d ON i.id = d.id AND t.time > d.time
GROUP BY i.id, t.time;
```

@shiyanfei5 我查了查 `PostgreSQL` 关于窗口函数的 [文档]( https://www.postgresql.org/docs/current/functions-window.html )，pg 也不支持使用 `IGNORE NULLS` 取上一个非 `NULL` 值呀：

> The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, first_value, last_value, and nth_value. This is not implemented in PostgreSQL: the behavior is always the same as the standard's default, namely RESPECT NULLS

你是咋做的呢？

@sun522198558 对于 `MySQL`，官方不建议这样使用 *（两周前就有人 [发帖]( /t/883301 ) 说赋值异常的）*

- 『在同一条语句内赋值和读取一个用户变量』的行为未定义
- 『在 `SELECT` 中赋值用户变量』已被弃用，未来会移除


@uil330 你是啥数据库啊？

我看 `MySQL` 的 `LAG()`、`LAST_VALUE()` 等窗口函数，都还不支持 `IGNORE NULLS`

`SQLite` 也还不支持在 `LAG()` 等窗口函数上使用 `FILTER (WHERE xxx IS NOT NULL)`，但能用在聚合函数上

所以用 `SQLite` 写了试试：

*（ V 站排版原因，开头有全角空格。若要复制运行，记得删除）*

```sqlite
WITH
　 data(id, data, time) AS (
　　 VALUES
　　　(1, 10, '00:03'),
　　　(1, 50, '04:23'),
　　　(1, 20, '07:03')
　),
　
　 ids(id) AS (
　　 SELECT DISTINCT id
　　　 FROM data
　),
　
　 time(hour) AS (
　　 SELECT value
　　　 FROM json_each('[1, 2, 3, 4, 5, 6, 7, 8]')
　),
　
　 formated(id, hour, data) AS (
　　 SELECT id, strftime('%H', time) + 1, data
　　　 FROM data
　)

-- 按 id 分组，hour 为顺序，窗口范围为 [组内第一行, 当前行]，并过滤掉 NULL 值，
-- 剩余值合并成 json 数组，然后取数组最后一位（这个数组至多 24 个数嘛，不算大）
SELECT id,
　　　 json_group_array(data) FILTER(WHERE data IS NOT NULL) OVER win ->> '$[#-1]' data,
　　　 format('%02d:00', hour) time
　 FROM ids
　 JOIN time
　 LEFT JOIN formated USING(id, hour)
WINDOW win AS (PARTITION BY id ORDER BY hour);
```

@sylpha 那 20 多个系统不能配合提供 “当前系统还在用着哪些文件” 的话。。那就留着呗。。

就算删掉一半文件，能快多少呢？

你不会是所有文件，都塞到一个文件夹里吧？？

@sylpha

> 定期清理的话，因为数据库中表示关联关系的信息都删掉了，而且正常还要使用的文件和要删除的文件都混在一起，所以疑惑怎么清理比较好

啥意思。。不是这样么：

遍历当前所有文件 - 数据库内未删除条目所指向的文件集合 = 冗余文件列表？？

做一次也不需要多久吧？

@longmeier90 就算是 8 个 100 万行的表，每行 1KB 的数据，全部缓存也大概需要不到 8 GB 内存而已呀

是内存不够，导致联表慢吗？

如果不依赖数据库的 left join ，而是自己获取主要数据后，再手动查询 8 次表（若已花 8 GB 代价全部缓存至内存中了），最后组装数据，也很慢吗？

@longmeier90

> 加一些冗余字段是为了查询是方便。很多统计都会联表查询数据量大的时候就会慢

好奇问一下，一般会连多少张表？被连的表一般有多大？（比如，一般连 千万级患者表 等类似级别的 5 张表？）

@liuidetmks 可能你说的是 [灾难性回溯]( https://zh.javascript.info/regexp-catastrophic-backtracking ) ？


@blackantt 实在不想改你的正则了，你自己去 regex101.com ，把你的正则、html 丢进去，看看都匹配了 7 个啥吧

帮你把正则改成这样，然后在左边选择 `ECMAScript (JavaScript)` 引擎，可以看到每个匹配 *(淡蓝色)* 及其各自的 6 个 `(.*?)` *(绿橙紫粉红绿)* 都长啥样：

```regex
<div class="css-em857x"><a title="(.*?) profile" href="\/profile\/\d{6,8}">(.*?)<\/a><\/div><\/h4>(.*?)title="(.*?)"(.*?)<\/span><\/div><div class="MuiBox-root css-15ro776"><span aria-hidden="true" title="China" (.*?)1 shared interest: Languages &amp; Cultures<\/p><\/div><div class=
```


写了段用 lxml 库的 xpath 功能抽取结果的 Python 代码：

*（ V 站排版原因，行首加了全角空格，记得删除）*

```python
import lxml

html = lxml.html.parse(r'C:\Users\dengz\Downloads\out8.txt')

for node in html.xpath(
　　'//div[@role="listitem" and '
　　'.//div[@class="MuiBox-root css-k008qs"]/*[2] and '
　　'.//p[contains(text(), "1 shared interest")]]'):
　 n1 = node.xpath('.//a[text()]')[0]
　 n2 = node.xpath('.//div[@class="MuiBox-root css-15ro776"][2]/span')[0]
　 print([n1.get('href'), n1.text, n2.get('title')])
```

为嘛不能用 request 、aiohttp 等库，在 Python 里下载呢？

@blackantt 粗试了一下，Python 的正则是没问题的，因为在 regex101.com 、regexr.com 、Notedpad++ 等，都匹配到了相同的结果

初步认为，你的正则出问题了

@kestrelBright 我觉得当前表结构，足够你用 SQL 实现那俩需求了

不知你说的『当前方案不够好』，是不好在哪儿


@7911364440 这个『逾期表』，是只能统计『当天』哪些用户违约，还是『任意一天』（楼主好像需要后者）

你是不知道怎么写 SQL 实现那俩搜索吗？

还是写了 SQL ，但速度太慢，问怎么提速？


要不放一下你当前的表结构和 SQL ？

怎么下载样例文件呢。。还要注册个账号？会不会积分不够，还得灌个水啥的？

除非搜索 /替换需求特别简单，否则正则不适合用于 html xml 这种数据上

推荐 BeautifulSoup lxml 等库，使用里面的 css selector 或 xpath 功能

@s82kd92l 反正我认为，只是跑点博客、网站、爬虫啥的，安卓手机的性价比，应该比树莓派之类的高

闲鱼上 骁龙 845 的小米 8 都才三四百了，这个价格的树莓派，性能完全比不上

我也有一台 红米 Note 5 ，骁龙 636 ，写了个 Python 爬虫，凌晨爬小说站，峰值可以每秒爬 700 （ json 格式）网页、解析、UTF-16 编码（中文内容这种编码省空间）、全本数据合并、lzma 压缩存档

@s82kd92l 自己 mount /dev/xxx 呢，是不是就不用经过安卓的机制了

（我不是很懂安卓。反正我是自己这样 mount 一张 ext4 的内存卡的，不想用 exFAT ）

为嘛不用 Linux Deploy 呢？

docker 好像安卓的 Linux 内核不支持。需要自己重新编译

安卓有很多机制为了省电的机制。我在 Debian testing 里可以通过 sudo iw dev wlan0 set power_save off 关闭 WIFI 节能模式