MySQL SELECT语句WHERE/LIKE子句特殊匹配需求求助：判断table2.selection包含table1.data全部字符

Got it, let's break down your problem and fix this step by step. First, let's clarify your core requirement: you need to find rows where every character in table1.data exists in table2.selection (not the other way around). Your original query was checking the reverse—if table2.selection is a substring of table1.data—which is why it wasn't working.

Why Your Original Query Failed #

Your attempt:

SELECT results FROM table1 WHERE table1.data LIKE concat('%',table2.selection,'%');

This checks if table2.selection is contained within table1.data (e.g., if selection is "ab" and data is "abcd", it matches). But you need the opposite: verify all characters in table1.data are present in table2.selection.

Solution 1: Regular Expression Approach (Cleaner for Most Databases) #

We can build a regex pattern that only allows characters from table2.selection. For example, if selection is "abde", the regex becomes ^[abde]+$—this ensures every character in table1.data is one of a/b/d/e. We'll also handle regex special characters (like ^, ], or -) that need escaping to avoid errors.

MySQL Example

-- To select matching results
SELECT t1.results
FROM table1 t1
JOIN table2 t2 ON t1.wdt_id = t2.wdt_id
WHERE t1.data REGEXP CONCAT(
    '^[',
    REGEXP_REPLACE(t2.selection, '([\\^\\-\\]])', '\\$1'), -- Escape regex special characters
    ']+$'
);

-- For your UPDATE Trigger (to populate selection_results)
UPDATE table2 t2
SET selection_results = (
    SELECT t1.results
    FROM table1 t1
    WHERE t1.data REGEXP CONCAT(
        '^[',
        REGEXP_REPLACE(t2.selection, '([\\^\\-\\]])', '\\$1'),
        ']+$'
    )
    LIMIT 1 -- Add this if you only want one matching result per row
)
WHERE EXISTS (
    SELECT 1
    FROM table1 t1
    WHERE t1.data REGEXP CONCAT(
        '^[',
        REGEXP_REPLACE(t2.selection, '([\\^\\-\\]])', '\\$1'),
        ']+$'
    )
);

PostgreSQL Example

PostgreSQL uses slightly different regex syntax—here's the equivalent:

SELECT t1.results
FROM table1 t1
JOIN table2 t2 ON t1.wdt_id = t2.wdt_id
WHERE t1.data ~ CONCAT(
    '^[',
    regexp_replace(t2.selection, '([\^\-\]])', '\\1', 'g'),
    ']+$'
);

Solution 2: Character-by-Character Validation (No Regex) #

If regex isn't an option, you can validate each character in table1.data individually:

SELECT t1.results
FROM table1 t1
JOIN table2 t2 ON t1.wdt_id = t2.wdt_id
WHERE NOT EXISTS (
    -- Check for any character in t1.data that's NOT in t2.selection
    SELECT 1
    FROM (
        -- Generate a row for each character in t1.data (adjust UNIONs to match max data length)
        SELECT SUBSTRING(t1.data, n, 1) AS single_char
        FROM (
            SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        ) number_list
        WHERE n <= LENGTH(t1.data)
    ) chars
    WHERE LOCATE(chars.single_char, t2.selection) = 0
);

Testing with Your Sample Data #

Using your example data:

• table2 row 1 has selection = 'abde'
• table1 row 2 has data = 'abde' (all characters match)
• The query will return Toyota, which will be inserted into table2.selection_results as you expected.

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