时间戳

首页 > 解决方案 > MySQL搜索多个表并在一个表中搜索多个列

mysql - MySQL搜索多个表并在一个表中搜索多个列

问题描述

我构建了一个查询，用于在多个表中搜索一个值。它按原样工作得很好，但我想在“客户”表中搜索多个字段（另外还有“last_name”和“company_name”）。

$sql = "SELECT first_name as name FROM customers WHERE first_name LIKE '%" . $keyword . "%'
        UNION
        SELECT name as name FROM events WHERE name LIKE '%" . $keyword . "%'
        UNION
        SELECT product_name as name FROM products WHERE product_name LIKE '%" . $keyword . "%'";

我是否只是像这样为每个附加字段添加更多单独的行？

"SELECT first_name as name FROM customers WHERE first_name LIKE '%" . $keyword . "%'
    UNION
SELECT last_name as name FROM customers WHERE last_name LIKE '%" . $keyword . "%'
    UNION
SELECT company_name as name FROM customers WHERE company_name LIKE '%" . $keyword . "%'

它似乎不是最有效的，所以想检查一下。谢谢！

标签： mysqlsql

解决方案

有一个有效的解决方案。由于您只收集名称，因此您将它们收集到三个不同的变量中。并使用后端语言，合并这 3 个数组。

    example for php, 
    $sql1 = "SELECT first_name as name FROM customers WHERE first_name LIKE '%" . $keyword . "%'; 
    //getting first array result by this query
    $sql2 = "SELECT name as name FROM events WHERE name LIKE '%" . $keyword . "%'";
    //getting second array result by this query
    $sql3 = "SELECT product_name as name FROM products WHERE product_name LIKE '%" . $keyword . "%'"; 
    //getting third array result by this query

    $result = array_merge($sql1, $sql2, $sql3)

如果您可以通过后端语言管理数据，则此解决方案将适用。

推荐阅读