如何缩短并优化多关键词过滤的查询语句？

Got it, let's tackle this problem head-on. When you're dealing with a long (and growing) list of keywords to match against a field and want to avoid messy, endless OR clauses in your query, there are several clean, scalable approaches—depending on your database system. Here are the most practical ones:

This is my top recommendation if you plan to keep adding keywords regularly. Instead of hardcoding keywords into your query, store them in a dedicated table. This way, you never have to modify your core query logic—just insert new keywords into the table when needed.

Example Setup: #

First, create a simple keywords table:

CREATE TABLE search_keywords (
    id INT AUTO_INCREMENT PRIMARY KEY,
    keyword VARCHAR(255) NOT NULL UNIQUE
);

Insert your initial keywords:

INSERT INTO search_keywords (keyword) VALUES ('foo'), ('bar'), ('baz');

Query to Match Any Keyword: #

Use JOIN or EXISTS to find records where the target field contains any of the keywords. For case-insensitive matching, adjust the collation or use LOWER():

-- Using JOIN (works in most databases)
SELECT DISTINCT t.*
FROM your_target_table t
JOIN search_keywords k 
    ON t.your_field LIKE CONCAT('%', k.keyword, '%');

-- Using EXISTS (often more efficient for large datasets)
SELECT t.*
FROM your_target_table t
WHERE EXISTS (
    SELECT 1 
    FROM search_keywords k 
    WHERE t.your_field LIKE CONCAT('%', k.keyword, '%')
);

Pro tip: If you need exact word matches instead of partial contains, use word boundary operators (like REGEXP '[[:<:]]keyword[[:>:]]' in MySQL) instead of LIKE.

If you don't want to create a separate table and prefer to pass keywords as a single string (like a comma-separated list), most modern databases have functions to split this into a set and match against it.

MySQL Example: #

Use a generated numbers table to split the keyword string and match:

SELECT *
FROM your_target_table
WHERE EXISTS (
    SELECT 1 
    FROM (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('foo,bar,baz', ',', n), ',', -1) AS keyword
          FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers
          WHERE n <= LENGTH('foo,bar,baz') - LENGTH(REPLACE('foo,bar,baz', ',', '')) + 1) keywords
    WHERE your_field LIKE CONCAT('%', keyword, '%')
);

PostgreSQL Example: #

Use string_to_array() and ANY() for cleaner syntax:

SELECT *
FROM your_target_table
WHERE your_field LIKE ANY (ARRAY['%foo%', '%bar%', '%baz%']);

-- Or pass a comma-separated string directly:
SELECT *
FROM your_target_table
WHERE your_field LIKE ANY (string_to_array('foo,bar,baz', ',')::text[] || '%');

If you're dealing with large text fields or need fast searches, full-text indexing is the way to go. It's optimized for text matching and handles keyword expansion seamlessly.

MySQL Example: #

First, add a full-text index to your field:

ALTER TABLE your_target_table ADD FULLTEXT INDEX idx_your_field (your_field);

Then query using MATCH AGAINST:

SELECT *
FROM your_target_table
WHERE MATCH(your_field) AGAINST('foo bar baz' IN BOOLEAN MODE);

Note: To add new keywords later, just update the AGAINST clause or pass the keywords as a parameter.

PostgreSQL Example: #

Use tsvector and tsquery with a GIN index:

-- Create index for fast searches
CREATE INDEX idx_your_field_fts ON your_target_table USING GIN (to_tsvector('english', your_field));

-- Query for matching keywords
SELECT *
FROM your_target_table
WHERE to_tsvector('english', your_field) @@ to_tsquery('english', 'foo | bar | baz');

This is blazingly fast for large datasets, and adding new keywords just means updating the tsquery string.

Final Recommendation #

If you’re going to keep adding keywords over time, the keyword table + JOIN/exists method is hands-down the best—it keeps your query static, avoids hardcoding, and makes maintenance a breeze. Full-text search is ideal if performance is a concern with large data volumes.

内容的提问来源于stack exchange，提问作者Hee Do Park