如何检查字符串列表中的至少一个字符串是否包含在另一个字符串中?

Question

我给出了一个标签列表和一个名为tags的字段表。这些标记组合为一个字符串存储。在连接之前，在每个标签的开头添加一个hashtag。

例子:

标签列表:{'summer', 'winter', 'autumn', 'spring'}

表中存储的字符串:'#summer#winter#autumn#spring'

现在给定一个标签列表，我如何查询包含tags字符串中至少一个给定标签的条目?是否有任何特定的功能或单行指令来实现这一点?

我目前正在获取该表中包含的所有数据集，在应用程序中为每个数据集创建对象，然后根据给定的标记列表查询这些对象。这是非常不方便和低效的，因为该表中有大约500.000个数据集。而且总是查询所有这些数据集会花费太多时间。接收包含至少一个给定标记的所有数据集的SQL查询可能如下所示:

SELECT *
FROM table
WHERE tag_string LIKE '%tag1%' OR tag_string LIKE '%tag2%' OR tag_string LIKE '%tag3%' OR LIKE....

但是这也是低效的，特别是对于更多的给定标签。

我可以遍历每个标签并检查LOCATE的子字符串吗?或者有更有效的方法吗?

我正在使用MariaDB服务器版本10.3.21。

Answer 1

如果您继续将数据存储在"非规范化"的格式中，

样式(即分隔的字符串)，您将为此付出查询复杂性的代价。

解决这个问题的一种方法是将分隔的字符串分成多行，每行只有一个标记。然后，可以在where子句中使用更简单、更有效的相等条件。例如

CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY
, post_body TEXT
, tags TEXT );
INSERT INTO posts (post_body, tags) 
VALUES
('This is a post about summer.', '#summer#winter#autumn#spring') 
, ('Some arbitrary fruity but text wintery here.', '#fruit#winter#irrelevant') 
, ('untagged text here.', NULL) 
; 

，然后在递归CTE中生成额外的行并提取标记:

WITH RECURSIVE cte AS (
SELECT 
id
, post_body
, SUBSTRING_INDEX(tags,'#',1) AS tag
, SUBSTRING(tags, LOCATE('#', tags) + 1) AS rest
, tags
FROM posts
UNION ALL
SELECT
id
, post_body
, SUBSTRING_INDEX(rest,'#',1)
, SUBSTRING(rest, LOCATE('#', rest) + 1)
, tags
FROM cte
WHERE LOCATE('#', rest) > 0
)
SELECT id, post_body, tag, tags
FROM cte
WHERE tag IN('summer','winter')
ORDER BY id, tag;

结果(示例):

id  post_body                                       tag     tags
1   This is a post about summer.                    summer  #summer#winter#autumn#spring
1   This is a post about summer.                    winter  #summer#winter#autumn#spring
2   Some arbitrary fruity but text wintery here.    winter  #fruit#winter#irrelevant

见:https://dbfiddle.uk/QPhgV2O6

注意:WHERE tag IN('summer','winter')相当于(tag = 'summer' or tag = 'winter')，避免通配符和LIKE

的需求

---

另一种方法是将当前标签数据视为JSON，这允许使用JSON_SEARCH(注意"one")你可以看到它匹配JSON的第1个元素)

SELECT *
FROM (
SELECT post_body,
CONCAT('["', REPLACE(TRIM(LEADING '#' FROM tags), '#', '","'), '"]') AS tags_json
FROM posts
) AS subq
WHERE JSON_SEARCH(tags_json, 'one', 'summer') IS NOT NULL 
OR JSON_SEARCH(tags_json, 'one', 'winter') IS NOT NULL;

或者使用JSON来存储标记，这里我为示例添加另一个列json_tag(类型为longtext):

UPDATE posts
SET json_tags = CONCAT('["', REPLACE(TRIM(LEADING '#' FROM tags), '#', '","'), '"]');

现在可以像这样使用JSON_SEARCH:

SELECT *
FROM posts
WHERE JSON_SEARCH(json_tags, 'one', 'summer') IS NOT NULL 
OR JSON_SEARCH(json_tags, 'one', 'winter') IS NOT NULL;

参见此变体:https://dbfiddle.uk/H7fGkHFH

注:我不知道这对你的数据有多好，也不知道用JSON代替你现在做的有多困难。