如何高效查询item_name后缀匹配指定标签列表的SQL数据行？

我有一张包含item_name、value两列的数据表，item_name格式为"前缀.tag_name"。现在需要筛选出tag_name属于指定标签列表的数据行，示例标签列表：

tag_names = ['f1', 'k500', '23_g']

输入表：

期望输出表：

我之前尝试循环拼接SQL语句：

SELECT * FROM table WHERE item_name LIKE '%f1' OR item_name LIKE '%k500' OR item_name LIKE '%23_g';

但标签数量可能在1到200个之间，大量标签会让查询语句过于复杂，想找更高效的方案。

方法1：提取tag_name后用IN子句 #

利用字符串函数提取item_name中.后面的部分，再用IN匹配标签列表，比多个LIKE更简洁高效。

MySQL/MariaDB #

SELECT * 
FROM your_table 
WHERE SUBSTRING_INDEX(item_name, '.', -1) IN ('f1', 'k500', '23_g');

PostgreSQL #

SELECT * 
FROM your_table 
WHERE SPLIT_PART(item_name, '.', 2) IN ('f1', 'k500', '23_g');

SQL Server #

SELECT * 
FROM your_table 
WHERE RIGHT(item_name, CHARINDEX('.', REVERSE(item_name)) - 1) IN ('f1', 'k500', '23_g');

方法2：使用临时表（适合标签数量极多的场景） #

如果标签数量接近200，或者后续可能扩展，可以先创建临时表存储标签，再通过JOIN关联查询，维护性更好。

MySQL示例 #

-- 创建临时表并插入标签
CREATE TEMPORARY TABLE target_tags (tag_name VARCHAR(50));
INSERT INTO target_tags VALUES ('f1'), ('k500'), ('23_g');

-- 关联查询
SELECT t.* 
FROM your_table t
JOIN target_tags tt ON SUBSTRING_INDEX(t.item_name, '.', -1) = tt.tag_name;

这种方式的好处是标签列表可以单独维护，不需要修改主查询语句，而且JOIN的性能在数据量较大时通常优于多个OR条件。

优势说明 #

• LIKE '%xxx'会触发全表扫描（无法利用索引），而提取后缀后用IN，如果给item_name创建对应的函数索引，能大幅提升查询速度。
• 语句结构更简洁，避免了大量OR拼接的冗余代码。

内容的提问来源于stack exchange，提问作者A_ruby