能否结合自定义表列的正则表达式检索另一张表？（SQL）

Great question! Since you're working with a non-mainstream SQL dialect, the exact syntax might vary a bit, but the core idea is totally doable—here's how to approach it:

Core Concept #

The goal is to convert your list of keywords in custom_keywords into a regex pattern where each keyword is an alternative (separated by |), then use that pattern to match against fields in main_table. For example, keywords white, green, blue, yellow become the regex white|green|blue|yellow.

Option 1: Your SQL Supports String Aggregation #

If your dialect has a string aggregation function (like GROUP_CONCAT in MySQL, STRING_AGG in PostgreSQL, or similar), you can generate the regex pattern directly in SQL.

Step 1: Generate the Regex Pattern #

First, concatenate all keywords into a single regex string. Important: Escape any regex special characters (like ., *, +, ?, [, ]) in your keywords to avoid unintended matching or errors:

SELECT GROUP_CONCAT(
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(keyword, '.', '\\.'),
        '*', '\\*'
      ),
      '+', '\\+'
    ),
    '[', '\\['
  ) SEPARATOR '|'
) AS regex_pattern
FROM custom_keywords;

Step 2: Use the Pattern in Your Main Query #

Embed the pattern generation as a subquery in your REGEXP check:

SELECT main_table_field
FROM main_table
WHERE REGEXP(
  main_table_field,
  (
    SELECT GROUP_CONCAT(
      REPLACE(REPLACE(REPLACE(REPLACE(keyword, '.', '\\.'), '*', '\\*'), '+', '\\+'), '[', '\\[')
      SEPARATOR '|'
    ) FROM custom_keywords
  )
);

Option 2: Your SQL Doesn't Support String Aggregation #

If your dialect lacks built-in string aggregation, handle the pattern generation in your application layer (e.g., Python, Java, Node.js) instead:

Example (Python Pseudocode) #

1. Fetch all keywords from custom_keywords:

import re
import your_db_driver

# Connect to your database
db = your_db_driver.connect(...)

# Fetch all keywords
cursor = db.cursor()
cursor.execute("SELECT keyword FROM custom_keywords")
keywords = [row[0] for row in cursor.fetchall()]

# Escape regex special characters and build the pattern
escaped_keywords = [re.escape(keyword) for keyword in keywords]
regex_pattern = '|'.join(escaped_keywords)

# Run the main query with the generated pattern
cursor.execute("SELECT main_table_field FROM main_table WHERE REGEXP(main_table_field, %s)", (regex_pattern,))
results = cursor.fetchall()

Key Notes to Avoid Issues #

• String Length Limits: 2000 keywords concatenated could create a very long string. Check your SQL dialect's maximum string length for parameters—if you hit limits, you might need to split the query into smaller batches (e.g., match 500 keywords at a time) or adjust database settings.
• Performance: Regex with 2000 alternatives can be slow on large main_table datasets. If possible, explore alternatives like full-text search (if your dialect supports it) or pre-filtering rows to reduce the data volume before running the regex check.
• Testing: Always test with a small subset of keywords first to ensure the regex works as expected, especially after escaping special characters.

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