php - MySQL PHP 如果表中的关键字短语不存在于句子表中显示结果

Question

我有两个表:句子、否定。

我想选择在 Negatives.negphrase 中不包含任何记录的 Sentences.sentence 列。

句子中有 200k 条记录，否定句有 50k 条记录。

Sentences.sentence Sample Data
=============================

 - university lab on campus
 - laboratory designs
 - lab coats
 - math lab
 - methane production
 - meth lab

Negatives.negphrase Sample Data
======================================

 - coats
 - math lab
 - meth

Desired Result Set
==================

 - university lab on campus
 - laboratory designs
 - methane production

我尝试使用我的另一个问题的结果，但数据库超时:

SELECT Sentences.sentence
FROM Sentences, Negatives
GROUP BY Sentences.sentence
HAVING (((Max(InStr(" " & sentence & " "," " & negphrase & " ")))=0));

我的答案

因此，我将向理查德提供正确的答案，因为他的解决方案适用于较小的记录集，但不适用于较大的记录集。下面是我用来将所有否定关键字放入数组中的 PHP 代码，然后使用 UPDATE 子句循环遍历该数组，以在 Sentences 表中标记新列“negmatch”。我将在另一个 WHERE 子句中使用它来选择 Sentences.sentence WHERE negmatch <> 1。

我只需对所有否定词运行一次此代码，然后当我添加其他关键字时，我使用相同的代码，但没有循环再次搜索句子(下面未显示代码)。该代码需要 6.5 分钟来循环 2800 个 UPDATE 子句，因此初始加载相当长，但一旦完成，就不必再次执行。

<?php
$mysqli = new mysqli("localhost", "myuser", "myuserpassword", "database");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

if ($result = $mysqli->query("SELECT negphrase FROM negatives")) {  
    $row_cnt = $result->num_rows;
    printf("Negative keywords have %d rows.\n", $row_cnt); //print count of rows

    while($row = $result->fetch_assoc()){ //loop through all results by row
        foreach( $row  AS $value ) {
        $negative[] = $value;
    }
}


    /* free result set */
    $result->close();

    $data = array_values($negative); // get only values
    $data = array_filter($data);
    $datacount = 1;
    foreach($data as $val) { //loop through array to build MySQL WHERE clause


            $updatequery = "UPDATE Sentences SET negmatch=1 WHERE sentence REGEXP '[[:<:]]" . trim($val) . "[[:>:]]'";
            echo $updatequery  . "<br />";

            mysqli_query($mysqli,$updatequery) or die (mysqli_error($mysqli));
            echo $datacount . " " . trim($val) ."<br />";
            $datacount++;

        }

}
$mysqli->close();


    unset($result, $row, $mysqli,$value,$negative,$data,$val,$updatequery,$datacount,$row_cnt);

？>

Answer 1

使用否定左连接，这将仅返回 Senteces 表中与基于规则的 Negatives 表不匹配的行

select * from Sentences s 
left join Negatives n 
on (concat(" ",s.sentence," ") like concat("% ",n.negphrase," %"))
where n.negphrase is null

根据以下数据进行测试

CREATE TABLE IF NOT EXISTS `Negatives` (
  `negphrase` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Negatives` (`negphrase`) VALUES
('coats'),
('math lab'),
('meth');

CREATE TABLE IF NOT EXISTS `Sentences` (
  `sentence` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Sentences` (`sentence`) VALUES
('university lab on campus'),
('laboratory designs'),
('lab coats'),
('math lab'),
('methane production'),
('meth lab'),
('testing sentence');