MySQL的顺序是多少就像一个字符串

Question

I have the following MySQL query:

SELECT * FROM users WHERE name LIKE '%jack%'

I want it to order by how much it's like jack so

1. jack
2. jacker
3. majack

I can also use PHP.

Answer 1

I think you can accomplish what you want by using full text search function in mysql. Your query will be like this:

SELECT name, MATCH (name) AGAINST ('jack') as score 
FROM users ORDER BY score DESC;

There are some conditions you need to take into consideration when using full search text:

• The mysql engine should support this functions. I think InnoDB 5.6 and MYISAM 5.5 support that.
• You have to add a FULLTEXT index in your table definition.

You can see a working demo here: http://sqlfiddle.com/#!9/72bf5/1

More info about full search text in MySQL here: http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

Also, here is a working example I wrote on PHP using similar_text and sorting array functions: http://ideone.com/UQpBFk

Hope it helps!