Multi-Column Name Search MySQL

问题:

I am currently working on a live search and I need to be able to find parts of a name in two columns (we need to separate first and last name). I personally would like to keep the command short, however the only way I have been able to get this to work is:

User Searches For

John Doe

Generated SQL Query

SELECT * FROM users WHERE
(first_name LIKE '%john%' OR last_name LIKE '%john%') AND
(last_name LIKE '%doe%' OR last_name LIKE '%doe%');

The search field is one box so I do some simple separation by space. Most cases this won't reach beyond three sets of clauses, was just wondering if there was any better way to do this.

NOTE: I would like to be able to do partial matching. So I should be able to find John Doe if I look for "John Do"

SOLUTION With the help of Rolando, I did however come up with a solution, posted for anyone else who finds this. Follow his instructions on how to create the index below, then run this:

SELECT * FROM users WHERE
(MATCH(first,last) AGAINST ('+john* +do*' IN BOOLEAN MODE))

回答1:

Your best bet here is create a FULLTEXT index that encompasses the two fields

Step 1) Create a stop word file with just three word

echo "a" > /var/lib/mysql/stopwords.txt
echo "an" >> /var/lib/mysql/stopwords.txt
echo "the" >> /var/lib/mysql/stopwords.txt

Step 2) Add these options to /etc/my.cnf

ft_min_word_len=2
ft_stopword_file=/var/lib/mysql/stopwords.txt

Step 3) Create FULLTEXT index on the first and last name columns

ALTER TABLE users ADD FULLTEXT first_last_name_index (first,last);

Step 4) Implement the MATCH function in your search

Something Like This:

SELECT * FROM users WHERE (MATCH(first,last) AGAINST ('John' IN BOOLEAN MODE)) AND (MATCH(first,last) AGAINST ('Doe' IN BOOLEAN MODE));

Click here to Learn More about FULLTEXT indexing