同时搜索2个字段

I have a table of first and last names

我有一个名字和姓氏的表

firstname    lastname
---------    ---------
Joe          Robertson
Sally        Robert
Jim          Green
Sandra       Jordan

I'm trying to search this table based on an input that consists of the full name. For example:

我正在尝试根据包含全名的输入搜索此表。例如：

input: Joe Robert

I thought about using

我想过要用

SELECT * FROM tablename WHERE firstname LIKE

BUT the table stores the first and last name separately, so I'm not sure how to do the search in this case

但是表分别存储了名字和姓氏，所以在这种情况下我不确定如何进行搜索

2 个解决方案

#1

In MyISAM:

在MyISAM中：

SELECT  *
FROM    mytable
WHERE   MATCH(firstname, lastname) AGAINST ("Joe* Robert*" IN BOOLEAN MODE);

This will run much faster if you create a FULLTEXT index:

如果您创建FULLTEXT索引，这将运行得更快：

CREATE FULLTEXT INDEX ON mytable (firstname, lastname)

For the query to be able to search the short names (like Joe in your case), you'll need to set the variable @@ft_min_word_len to 1 before creating the index.

为了使查询能够搜索短名称（例如在您的情况下为Joe），您需要在创建索引之前将变量@@ ft_min_word_len设置为1。

In InnoDB you'll need to split the search string:

在InnoDB中，您需要拆分搜索字符串：

SELECT  *
FROM    mytable
WHERE   firstname LIKE 'Joe%'
        AND lastname LIKE 'Robert%'

#2

An alternative to Quassnoi's method:

Quassnoi方法的替代方案：

SELECT  *
FROM    mytable
WHERE   CONCAT(firstname, " ", lastname) = "Joe Robert"

#1