php - 巨大的 sql 查询返回错误 (mysql)

Question

我目前正在尝试更新我的内部搜索引擎以使用多个词。它非常庞大且复杂，但我遇到了以前从未遇到过的错误，我也不知道为什么。

所以问题是，为什么我会收到以下错误消息？

我将把它分成不同的部分以便更好地理解。

这只是我echod，复制并粘贴到 PHPMyAdmin 中的 SQL 查询，粘贴在这里(PHPMyAdmin 格式很好)它在数据库中搜索 2 个词:

SELECT *
FROM (

SELECT p.page_url AS url, COUNT( * ) AS occurrences
FROM PAGE p, word w, occurrence o
WHERE (
(
p.page_id = o.page_id
AND w.page_word_id = o.page_word_id
AND w.word_word LIKE '%' 'test' '%'
GROUP BY p.page_id
)
OR (
p.page_id = o.page_id
AND w.page_word_id = o.page_word_id
AND w.word_word LIKE '%' 'search' '%'
GROUP BY p.page_id
)
UNION (

SELECT f.file_url AS url, COUNT( * ) AS occurrences
FROM files f, filenames fn, fileoccurrence fo
WHERE f.file_id = fo.file_id
AND fn.file_word_id = fo.file_word_id
AND fn.file_word LIKE '%' 'test' '%'
GROUP BY f.file_id
)
OR (

SELECT f.file_url AS url, COUNT( * ) AS occurrences
FROM files f, filenames fn, fileoccurrence fo
WHERE f.file_id = fo.file_id
AND fn.file_word_id = fo.file_word_id
AND fn.file_word LIKE '%' 'search' '%'
GROUP BY f.file_id
)
)t
ORDER BY occurrences DESC 

此代码由下面的 PHP 代码生成，该代码使用搜索输入中的 explode 函数

// Do a little formatting
$keyword = strtolower($keyword);

// Get timestamp for start
$start_time = microtime(true);

$searched_words = explode(' ', $keyword);

foreach ($searched_words as $index => $word) {
    // Set up the stemmer
    $stemmer = new PorterStemmer;
    $stemmed_string = $stemmer->stem($word);
    $searched_words[$index] = $stemmed_string;
}

//  Configure the sql code
$sql = "SELECT * FROM (SELECT p.page_url AS url, COUNT(*) AS occurrences 
    FROM page p, word w, occurrence o WHERE (";

// Add the extra words to the sql
foreach ($searched_words as $index => $word) {  
    $sql .= "(p.page_id = o.page_id AND w.page_word_id = o.page_word_id
        AND w.word_word LIKE '%' '" . $word . "' '%' GROUP BY p.page_id) OR";
}
// Add the union to the sql and then add the second query   
$sql = substr($sql, 0, (strLen($sql)-3)); //this will eat the last OR
$sql .= " UNION ";

// The second set of querys
foreach ($searched_words as $index => $word) {
    $sql .= "(SELECT f.file_url AS url, COUNT(*) AS occurrences FROM files f, filenames fn, fileoccurrence fo
        WHERE f.file_id = fo.file_id AND fn.file_word_id = fo.file_word_id AND fn.file_word
        LIKE '%' '" . $word . "' '%' GROUP BY f.file_id) OR";
}

// Clsoe the sql code
$sql = substr($sql, 0, (strLen($sql)-3)); //this will eat the last OR
$sql .= ") t ORDER BY occurrences DESC"; // LIMIT " . $results . "");

// echo the query for the pure lolz of it
echo $sql . "<br /><br />";

// Search the DB for the results
$results = mysql_query($sql)
    or die("Invalid query: " . mysql_error());

所有这些都会返回错误:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY p.page_id) OR(p.page_id = o.page_id AND w.page_word_id = o.page_word_id' at line 3

为什么我会收到此错误？我以前使用的代码运行良好。我添加的唯一真实的东西是 foreach()。

这是我的原始 SQL 代码:

$result = mysql_query("SELECT * FROM (SELECT p.page_url AS url, COUNT(*) AS occurrences 
    FROM page p, word w, occurrence o WHERE p.page_id = o.page_id AND w.page_word_id = o.page_word_id
    AND w.word_word LIKE '%' '" . $stemmed_string . "' '%' GROUP BY p.page_id UNION  
    SELECT f.file_url AS url, COUNT(*) AS occurrences FROM files f, filenames fn, fileoccurrence fo
    WHERE f.file_id = fo.file_id AND fn.file_word_id = fo.file_word_id AND fn.file_word
    LIKE '%' '" . $stemmed_string . "' '%' GROUP BY f.file_id) t ORDER BY occurrences DESC") // LIMIT " . $results . "")
        or die("Invalid query: " . mysql_error());

---

编辑:修复了上述错误。使用此代码

//  Configure the sql code
$sql = "SELECT * FROM (SELECT p.page_url AS url, COUNT(*) AS occurrences 
    FROM page p, word w, occurrence o WHERE (";

// Add the extra words to the sql
foreach ($searched_words as $index => $word) {  
    $sql .= "(p.page_id = o.page_id AND w.page_word_id = o.page_word_id
        AND w.word_word LIKE CONCAT('%', '" . $word . "', '%'))) OR "; //GROUP BY p.page_id)
}
// Add the union to the sql and then add the second query   
$sql = substr($sql, 0, (strLen($sql)-4)); //this will eat the last OR
$sql .= " GROUP BY p.page_id)";
$sql .= " UNION ";
$sql .= "(SELECT f.file_url AS url, COUNT(*) AS occurrences FROM files f, filenames fn, fileoccurrence fo
    WHERE (";

// The second set of querys
foreach ($searched_words as $index => $word) {
    $sql .= "(f.file_id = fo.file_id AND fn.file_word_id = fo.file_word_id AND fn.file_word
        LIKE CONCAT('%', '" . $word . "', '%'))) OR "; //GROUP BY f.file_id)
}

// Clsoe the sql code
$sql = substr($sql, 0, (strLen($sql)-4)); //this will eat the last OR
$sql .= " GROUP BY f.file_id)";
$sql .= ") t ORDER BY occurrences DESC"; // LIMIT " . $results . "");

这现在会产生错误:

Invalid query: Every derived table must have its own alias

这与联合有关(我不是很擅长联合……或者一般的 SQL)

Answer 1

您不能仅通过将字符串并排放置来连接 SQL 中的字符串。

AND w.word_word LIKE '%' 'test' '%'

应该是

AND w.word_word LIKE CONCAT('%', 'test', '%')

或者，如果您使用 SET SQL_MODE='PIPES_AS_CONCAT' 来获得标准的 ANSI SQL 语法，您可以使用:

AND w.word_word LIKE '%' || 'test' || '%'

---

回复你的评论，我看到另一个问题:

在 SQL 中，WHERE 子句必须完整，然后才能添加 GROUP BY 子句。语法是:

WHERE ( <conditions...> )
GROUP BY <expressions>

而你有:

WHERE ( <conditions...> GROUP BY <expressions> ) 
   OR ( <conditions...> GROUP BY <expressions> )

你的语法不合法。

真的，这是您应该能够在任何有关 SQL 的初学者引用资料中自行查找的内容。

---

Every derived table must have its own alias

这意味着您在 FROM 子句中使用了一个子查询，但没有给它一个别名。例如:

SELECT ... FROM (SELECT ... FROM table) AS x WHERE ...etc... 

如果您遗漏了 AS x 则这是一个错误(x 只是在这种情况下的一个示例，您可以选择一个更有意义的别名)。