使用排列在MySQL中搜索

Question

I need help.
I have a table where only two columns are: ID and NAME and these data:

ID | NAME
1    HOME
2    GAME
3    LINK

And I want show e.g. row with name: HOME if user search: HOME or OMEH or EMOH or HMEO, etc... - all permutations from word HOME.

I can't save to mysql all these permutations and search in this columns, because some words will be a too big (9-10 chars) and more than 40 MB for each 9 chars words.

Answer 1

One way to solve this problem is to store the sorted set of characters in each name in your database as an additional column and then sort the string the user inputs before searching e.g. database has

ID   NAME   CHARS
1    HOME   EHMO
2    GAME   AEGM
3    LINK   IKLN

Then when searching in PHP you would do this:

$search = 'MEHO';                // user input = MEHO
$chars = str_split($search);
sort($chars);
$search = implode('', $chars);   // now contains EHMO
$sql = "SELECT ID, NAME FROM table1 WHERE CHARS = '$search'";
// perform query etc.

Output

ID   NAME
1    HOME