sql - 查询跨表的串联字段

Question

我在 PostgreSQL a 和 b 中有 2 个表。

CREATE TABLE a
(
  id serial PRIMARY,
  name character varying(50) NOT NULL,
);

CREATE TABLE b
(
  id serial PRIMARY,
  name character varying(50) NOT NULL,
  a_id integer,
  CONSTRAINT a_id_fk FOREIGN KEY (a_id) REFERENCES a (id) 
);

此外，我在 b 上有这 2 个部分索引，以确保 b.name 在 a 为 null 时是唯一的，否则 b.name 是唯一的 (a.name, b.name) 是唯一的。

  CREATE UNIQUE INDEX idx1 ON b (name, a_id) WHERE a_id IS NOT NULL;
  CREATE UNIQUE INDEX idx2 ON b (name) WHERE a_id IS NULL;

如果我想查询以下形式的字符串:a.name + ' ' + b.name，这是最有效的方法吗？无论如何我可以创建一个索引来确保 (a.name, b.name) 的唯一性并使用它来有效地查询它吗？

SELECT * FROM b
INNER JOIN a on b.a_id = a.id
WHERE CONCAT(a.name, ' ' , b.name) = 'some string';

我需要一个精确的查找，不需要LIKE/CONTAINS。

Answer 1

首先，您需要一个额外的 UNIQUE 约束来满足您的要求:

else the pair (a.name, b.name) is unique.

CREATE TABLE a (
 , id   serial PRIMARY KEY
 , name text <b>UNIQUE</b> NOT NULL
);

MATERIALIZED VIEW

这在任何情况下都可以快速工作:MATERIALIZED VIEW与连接的字符串。由于 a 和 b 是链接的，我们只得到与 b 中一样多的行，没有笛卡尔积。

CREATE MATERIALIZED VIEW ab AS
SELECT b.a_id, b.id, concat_ws(' ', a.name, b.name) AS abname
FROM   b
LEFT   JOIN a ON a.id = b.a_id;

由于您只使用相等性，现在一个简单的 b 树索引就可以解决问题:

CREATE INDEX ab_abname_idx ON ab (abname);

和查询:

SELECT *
FROM   ab
-- optionally (left) join to a and b ...
WHERE  abname = 'some string';

• LEFT JOIN 对于包含来自 b 且 a_id IS NULL 的行至关重要。

• concat_ws()仅在 a.name 不是 NULL 的地方插入空格。

• 根据您的访问模式刷新物化 View 。如果您具有并发写入权限，那可能是棘手的部分。

没有MATERIALIZED VIEW

'some string' LIKE (a.name || '%')

不是sargable .索引支持是不可能的。您将不得不反转表达式:

a.name = left('some string', length(a.name))

这仍然不是可搜索的。你必须一步一步地做:

a.name = left('some string', 1) OR
a.name = left('some string', 2) OR
a.name = left('some string', 3) OR
...

这可以通过索引来支持。 dba.SE 上的相关回答:

• Algorithm for finding the longest prefix

我会使用递归 CTE 来查找所有匹配项......