MySQL specific sorting of the result based on multiply 'LIKE' and better matching of the f-CodePudding

I'm trying to build search engine and wondering is there a way to sort the results based on more precise matching?

I have a simple table library with the user names stored there:

userName
john hampton
smith horne
john smith junior
kevin spencer
john white smith
luke junior
alan smith junior

based on search field inputs. For example if I want to search for john smith junior I would like not only to check for the full name matching, also want to check for the matching part of the name. so expected result should be:

userName
john smith junior
john white smith
alan smith junior
john hampton
smith horne
luke junior

First line need to be top rated as we have three matches, second and third line have two matches and rest there is just one match, but ordered by the input field submission.

I started to build following query:

SELECT `userName`
FROM `library`
WHERE `userName` like '%john%smith%junior'
  OR `userName` like '%john%smith%'
  OR `userName` like '%john%junior%'
  OR `userName` like '%smith%junior%'
  OR `userName` like '%john%'
  OR `userName` like '%smith%'
  OR `userName` like '%junior%'
ORDER BY ???

but I blocked how to sort of the results, so my question is - is it possible to add some special clauses in ORDER BY to get results sorted as required (priority based on where clause adding order)?

First all results where '%john%smith%junior' sorted ASC
Second all results where userName like '%john%smith%' sorted ASC
etc.
Last is all results where userName like '%junior%' sorted ASC

CodePudding user response：

...
ORDER BY (`userName` like '%john%smith%junior') * @weight1  
         (`userName` like '%john%smith%')       * @weight2  
         (`userName` like '%john%junior%')      * @weight3  
         (`userName` like '%smith%junior%')     * @weight4  
         (`userName` like '%john%')             * @weight5  
         (`userName` like '%smith%')            * @weight6  
         (`userName` like '%junior%')           * @weight7

Take into account that real weight of the condition is a sum of weights for it and all its subconditions.

For example, a row which matches condition like '%john%smith%' also matches like '%john%' and like '%smith%', i.e. actual weight of this pattern is @weight2 @weight5 @weight6.