搜索表的多列MySQL

Question

下面的代码在续集Pro中正确工作，但是当我试图使用Pro运行它时，它什么也不做。这是解决我在多列上搜索一个查询问题的最佳方法吗？

非常感谢！

SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE company_name LIKE '%hudd%'
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE additional_contacts LIKE '%hudd%' 
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE company_number LIKE '%hudd%' 
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE city LIKE '%lee%' 
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE email LIKE '%miln.co%' 
UNION
SELECT * FROM suppliers JOIN users on suppliers.user_id = users.id WHERE CONCAT_WS(" ", 'first_name', 'last_name') LIKE '%hudd%'
ORDER BY company_name

Answer 1

你可以试试这个：

SELECT 
    DISTINCT *
FROM
    suppliers
        JOIN
    users ON suppliers.user_id = users.id
WHERE
    company_name LIKE '%hudd%'
        OR additional_contacts LIKE '%hudd%'
        OR company_number LIKE '%hudd%'
        OR city LIKE '%lee%'
        OR email LIKE '%miln.co%'
        OR CONCAT_WS(' ', 'first_name', 'last_name') LIKE '%hudd%'
ORDER BY company_name

Answer 2

使用布尔模式

SELECT * FROM test WHERE MATCH (column1,column2) 
     AGAINST ('+value1 +value2 +value3' IN BOOLEAN MODE);