时间戳

首页 > 解决方案 > 将子字符串与存储的字符串列表匹配

sql - 将子字符串与存储的字符串列表匹配

问题描述

因此，在最近的 Twitch 泄露事件之后，每个人都在讨论他们最喜欢的错误代码。突出的一点是一个用于发现“非法术语”的怪物 SQL 语句。这让我开始思考如何“正确”地实现这一点。

所以你有一个字符串表，你想匹配子字符串，你如何在 PL/pgSQL 中编写它？我假设任何 SQL 实现都应该具有为此类元编程创建函数/过程的过程能力，基本上创建和执行 SQL，如下所示：

admin@localhost:words> SELECT 'XabcY' LIKE '%abc%' OR 'XabcY' LIKE '%xyz%' as matches;
+-----------+
| matches   |
|-----------|
| True      |
+-----------+

所以更具体地说，给定 table 中的字符串列表disallowed：

| illegal_string |
|----------------|
| stupid         |
| witless        |
| moron          |
| commie-lover   |

如果其中任何一个与给定字符串匹配，您将如何在 PL/pgSQL 中创建一个在执行时返回 true 的动态查询？它不需要ILIKE在 Twitch 中使用来检查给定的单词是否包含子字符串，所以使用position也很好，但它应该是使用gin索引和诸如此类的高性能/可调整的。

相关问题。

标签： sqlpostgresqlplpgsql

解决方案

有两种可能：

  1. 你可以使用LIKE ANY(array)运算符
postgres=# select 'Ahoj' like any (ARRAY['Ah%', 'Na%']);
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

postgres=# select 'Nazdar' like any (ARRAY['Ah%', 'Na%']);
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)
  1. 你可以使用正则表达式：
postgres=# select 'Ahoj' ~ '^(Ah|Na)';
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

postgres=# select 'Nazdar' ~ '^(Ah|Na)';
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

所以最后你不需要动态SQL。

也有 ANSI/SQL 语法：

postgres=# select 'Nazdar' similar to '(Ah|Na)%';
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

因此，您可以编写如下内容：

DECLARE pw text[];
BEGIN
  pw := (SELECT array_agg('%' || disallowed || '%'
            FROM disallowed);
  IF EXISTS(SELECT * FROM foo WHERE c LIKE ANY (pw)) THEN
    RAISE NOTICE 'there are some disallowed words';
  END IF;
  ...

我不确定表演。在较大的表上，您需要三元索引，或者使用全文而不是子字符串搜索可能更好。

推荐阅读