没有名字/姓氏顺序的全名的SQL条件

Question

给定一个名为fullname的列，如何无顺序地筛选firstname/lastname？

在下面的javascript示例中，当按姓氏搜索时，查询无效

function getQuery(searchWord){
return `SELECT * FROM user WHERE fullname like '%${searchWord}%' `
}
// trying to search fullname "Elon Musk"
getQuery("Elon M") // okay
getQuery("Musk E") // no result

什么是让我找到"；埃隆·马斯克；通过按关键字"进行搜索；马斯克·埃隆？

注意：列firstname和lastname也存在

Answer 1

最后我使用一些javascript解决了这个问题。

function getQuery(searchTerm = '') {
// " Musk  E " => ["Musk", "E"]
const arr = searchTerm.split(/s+/).filter((e) => e)
// ["Musk", "E"] => (first_name like '%Musk%' or  last_name like '%Musk%') AND (first_name like '%E%' or  last_name like '%E%')
const conditions = arr
.map((e) => `(first_name like '%${e}%' or  last_name like '%${e}%')`)
.join(` AND `)
return `use SNOW select distinct sys_id, name from dbo.sys_user where ${conditions}`
}

Answer 2

您可以使用MATCHAGAINST:进行FULLTEXT搜索

SELECT * FROM user WHERE (MATCH (fullname) AGAINST ('${searchWord} IN NATURAL LANGUAGE MODE'))

您必须在列fullname上创建一个FULLTEXT search index

或者你可以按空间分割你的searchWord，然后进行LIKE '%$(searchWordPart1)%' AND LIKE '%$(searchWordPart2)%'等