☰

检查 sqlite 中不存在的模式

tag-icon • tag-icon shell performance efficiency sql sqlite
检查 sqlite 中不存在的模式

我用纯文本文件解释了类似的情况从巨大的文件中 Grep 大量的模式。那里的很多人说我应该这样做，所以现在我正在将数据迁移到 sqlite 数据库：

我有一个文件，从中提取了大约 10,000 个模式。然后我检查数据库是否不包含此类模式。如果没有，我需要将它们保存在外部file以进行进一步处理：

for id in $(grep ^[0-9] keys); do
  if [[ -z $(sqlite3 db.sqlite "select id from main where id = $id") ]]; then
    echo $id >>file
  fi
done

由于我是 SQL 新手，所以我找不到简单的方法来执行此操作。另外，这个循环是无用的，因为它比我awk在上述 URL 上实现的慢 20 倍。

由于数据库很大，并且不断增长，并且我非常频繁地运行这个循环，是否有可能使其更快？

答案1

sqlite对于每种模式，您都将调用重新连接到数据库的程序的新实例。那太浪费了。您应该构建一个查找任何键的查询，然后执行该查询。数据库客户端擅长执行大型查询。

如果文件中的匹配行keys仅包含数字，则可以按如下方式构建查询：

{
  echo 'select id from main where id in (';
  <keys grep -x '[0-9][0-9]*' |     # retain only lines containing only digits
  sed -e '1! s/^/, /' |             # add ", " at the beginning of every line except the first
  echo ');'
} | sqlite3 db.sqlite

对于更一般的输入数据，您会得到这样的想法：使用文本转换来构建单个大型查询。小心验证您的输入;在这里，我们确保注入到查询中的内容在语法上是有效的。上面的例子实际上存在一个极端情况：如果文件中没有匹配项，那么 SQL 语法无效；如果文件中没有匹配项，则 SQL 语法无效；如果可能发生这种情况，您需要特殊对待这种情况。这是处理空情况的更复杂的代码：

<keys grep -x '[0-9][0-9]*' |
if read first; then {
    echo 'select id from main where id in (' "$first"
    sed -e 's/^/, /'
    echo ');'
  } | sqlite3 db.sqlite
fi

答案2

首先，您确实if用列表替换了。实际上我什至会[[]]用 s代替[]s，然后再运行dash或其他打火机sh。这甚至看起来很简单，可以放弃整个for，并运行xargs（总是我的偏好，更好的性能）所以例如，也许像这样......

grep ^[0-9] keys | xargs -P0 -I '{id}' \
sh -c '[ -z "$(sqlite3 db.sqlite =\"select id from main where id = '{id}'\")" ] && \
echo '{id}' >> file'

我的逃跑很可能失败了，但这应该会为你指明正确的方向。我怀疑这会执行得更快，至少因为你会通过并行运行-P。

如果由于某种原因，即使这是爬行，您也可以随时查看转储 sqlite 数据库的内容。如果您采用这种方法，您很可能正在编写脚本。如果有必要，我只会考虑它。

答案3

将您的 ID 导入到某个新表中，然后使用该表来查询您的main表。用完后把桌子扔掉。

{ echo id; grep '^[0-9]' keys; } |
sqlite3 database.db \
    'CREATE TABLE ids ( id INTEGER UNIQUE )' \
    '.import /dev/stdin ids' \
    'SELECT * FROM main NATURAL JOIN ids' \
    'DROP TABLE ids'

测试：

sqlite> .mode box
sqlite> SELECT * FROM main;
┌────┬─────────────────┐
│ id │      word       │
├────┼─────────────────┤
│ 1  │ concessionaire  │
│ 2  │ goniometrically │
│ 3  │ meshed          │
│ 4  │ Celtic          │
│ 5  │ guiltless       │
│ 6  │ sclerodactylia  │
│ 7  │ spiritism       │
│ 8  │ ratchel         │
│ 9  │ Bajau           │
│ 10 │ semimineral     │
└────┴─────────────────┘

$ cat keys
3
7
78
190

运行给定的命令将输出以下内容（选择id而不是*仅输出 ID）：

3|meshed
7|spiritism

相关内容