多列名称搜索MySQL [英] 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:
用户搜索
John Doe
生成的SQL查询
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.
注意: 我希望能够进行部分匹配.因此，如果我寻找"John Do"，我应该可以找到John Doe.
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"
解决方案 但是，在Rolando的帮助下，我确实提出了一个解决方案，该解决方案已发布给其他发现此问题的人.按照他的说明如何在下面创建索引，然后运行它:
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))
推荐答案
您最好的选择是创建一个包含两个字段的FULLTEXT索引
Your best bet here is create a FULLTEXT index that encompasses the two fields
第1步)创建仅包含三个单词的停用词文件
Step 1) Create a stop word file with just three word
回显"a">/var/lib/mysql/stopwords.txt
回声一个" >>/var/lib/mysql/stopwords.txt
回声的" >>/var/lib/mysql/stopwords.txt
echo "a" > /var/lib/mysql/stopwords.txt
echo "an" >> /var/lib/mysql/stopwords.txt
echo "the" >> /var/lib/mysql/stopwords.txt
第2步)将这些选项添加到/etc/my.cnf
Step 2) Add these options to /etc/my.cnf
ft_min_word_len = 2
ft_stopword_file =/var/lib/mysql/stopwords.txt
ft_min_word_len=2
ft_stopword_file=/var/lib/mysql/stopwords.txt
第3步)在名字和姓氏列上创建FULLTEXT索引
Step 3) Create FULLTEXT index on the first and last name columns
ALTER TABLE用户添加完整文本first_last_name_index(first，last);
ALTER TABLE users ADD FULLTEXT first_last_name_index (first,last);
第4步)在搜索中实现MATCH功能
Step 4) Implement the MATCH function in your search
类似这样的东西:
选择*从用户那里(对(MATHO(first，last)反对('John'在BOOLEAN模式下))和(对(MATH(first，last))反对(DOE在BOOLEAN模式下));
SELECT * FROM users WHERE (MATCH(first,last) AGAINST ('John' IN BOOLEAN MODE)) AND (MATCH(first,last) AGAINST ('Doe' IN BOOLEAN MODE));
这篇关于多列名称搜索MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
