当名字和姓氏存储在不同的列中时如何搜索全名 [英] How to search for a full name when first name and last name are stored in a different columns

问题描述

如果我的名字存储在名为first_name"的列中，而姓氏存储在名为last_name"的列中，我如何搜索一个人的全名

How can I search for a person full name if I have first name stored in a column called "first_name" and the last name is stored in a column called "last_name"

请注意，该表有几百万条记录，因此我需要一种有效的方法来进行搜索.我正在使用 MySQL 服务器

Note that this table has couple million records so I need an efficient way to do the search. and I am using MySQL Server

first_name 列和 last_name 列都是 VARCHAR(80) 类型.

the column first_name and the column last_name are both type VARCHAR(80).

到目前为止，我已经尝试了以下方法，但速度很慢，因为它由于 concat 函数而忽略了索引

I have tried the following so far which works but slow because it ignores the indexes because of the concat function

SELECT first_name, phone FROM people
WHERE CONCAT_WS(' ',first_name, last_name) like '%John Smith%'

我也尝试在(first_name, last_name)上添加索引全文索引然后这个查询

I also have tried to add index full text index on( first_name, last_name) and then this query

SELECT *
FROM people
WHERE MATCH(first_name, last_name) AGAINST('John Smith')

但这不是最快的查询.有没有更好的方法来解决这个问题?

but it is not the fastest query. is there a better approach to this problem?

谢谢

推荐答案

我会在 (first_name, last_name) 上创建一个 FULLTEXT 索引并使用 MATCH()

I would create a FULLTEXT index on (first_name, last_name) and use MATCH()

WHERE MATCH(first_name, last_name) AGAINST ('John Smith')

这篇关于当名字和姓氏存储在不同的列中时如何搜索全名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！