如何从同一列中包含3个以上所需单词的MySQL数据库行中进行选择？

Question

I have a large database (over 2 million rows) where I store statistics from users. Users have Unique IDs which contain parts (for example hashed MAC address). The example Unique ID string is:

"AAAAAA-BBBBBB-CCCCCC-DDDDDD-EEEEEE-FFFFFFF"

Sometimes a part of the Unique ID changes and then it's for example:

"ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX"

I want to identify users and select all rows, where at least 3 (or different value) Unique ID parts match, using PHP. Is there any useful syntax which I could use to do that? I know that I can select all rows containing at least 1 Unique ID part, then explode to an array by the "-" character and check if contains at least 3 same words, but I believe that it isn't the fastest possible way.

Basically, I would like to use something like:

WHERE `UniqueID` REGEXP 'AAAAAA|BBBBBB|CCCCCC|DDDDDD|EEEEEE|FFFFFFF';

But selecting only if 3 of 6 words match.

Answer 1

YOU MIGHT WANT TO CHECK PERFORMANCE

Consider this query which uses inStr to check if a string is available within your id string. i used substring_index to extract the parts. just in case if you are interested in splitting or extracting part of the id string to create a temporary table but for this answer you can ignore it.

Ideally create your own stored procedure with keyword1, keyword2 & keyword3 as in parameter and then you can perform the search and return results.

Where condition checks whether the keyword1, 2 & 3 are found in your id string.

select 
    'ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX' as Id,
    substring_index('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX','-', 1) as part1,
    substring_index(substring_index('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX','-', 2),'-', -1) as part2,
    substring_index(substring_index('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX','-', 3),'-', -1) as part3,
    substring_index(substring_index('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX','-', 4),'-', -1) as part4,
    substring_index(substring_index('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX','-', 5),'-', -1) as part5,
    substring_index(substring_index('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX','-', 6),'-', -1) as part6
from dual
WHERE

    instr('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX','BBBBBB') >= 1 -- keyword1
    and instr('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX','CCCCCC') >= 1 -- keyword2
    and instr('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX','DDDDDD') >= 1 -- keyword3
;

EDIT if the above query is working you can add your logic.

select 
    'ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX' as Id


from dual
WHERE
    (
    -- below logic gives true when 3 or more keywords are found. change this accordingly
        (instr('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX',ik1) >0)+
        (instr('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX',ik2) >0)+
        (instr('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX',ik3) >0)+
        (instr('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX',ik4) >0)+
        (instr('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX',ik5) >0)+
        (instr('ZZZZZZ-BBBBBB-CCCCCC-DDDDDD-EEEEEE-XXXXXXX',ik6) >0)
    ) >=3

;