在存储值为CSV的列上使用多个标记的最佳结果搜索

I have tags stored in a column that are separated by commas. I'm trying to create a best match search.

I do not want to match partial strings, so I've added a comma at the beginning and end of each string like so ,apple,orange,banana, so that I can look up using LIKE '%,apple,% and it doesn't care what the position is.

how do I get the list of ids in order based on the best match compared to the search tags

is there a pure mySQL way of doing this?

Right now I'm dealing with it in PHP by looping through each tag and building out an array that counts the number of matches. 99% of time there will be a max of up to 5 tags being searched.

also in my case I'm using two tag fields/columns that have separate tag categories

SQL FIDDLE DEMO

Without a schema I imagine you need something like this.

SELECT 
    t.tagID, 
    t.tagText, 
    num_match.num_appear,
    ((LENGTH(`tagText`) - LENGTH(REPLACE(`tagText`, ',', '')))/LENGTH(',')) - 1 as wordnumber,
    (num_match.num_appear / (((LENGTH(`tagText`) - LENGTH(REPLACE(`tagText`, ',', '')))*1.0/LENGTH(',')) - 1)) *100 as porcentage

FROM 
    tblTAG t left join
    (SELECT tagID, count(tagID) as num_appear
     FROM
         (( SELECT tagID
            FROM  tblTAG
            WHERE tagText LIKE '%,apple,%'
         ) union all
         (  SELECT tagID
            FROM  tblTAG
            WHERE tagText LIKE '%,banana,%'
         )) as result
     GROUP BY tagID
    ) as num_match 
    ON t.tagID = num_match.tagID