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

我有两个表：句子，否定句。我想选择Negatives.negphrase中不包含任何记录的Sentences.sentence列。句子中有20万条记录，否定词中有5万条记录。Sentences.sentence Sample Data============================= - university lab on campus - laboratory designs - lab coats - math lab - methane production - meth labNegatives.negphrase Sample Data====================================== - coats - math lab - methDesired Result Set================== - university lab on campus - laboratory designs - methane production我尝试使用另一个问题的结果，但是数据库超时：SELECT Sentences.sentenceFROM Sentences, NegativesGROUP BY Sentences.sentenceHAVING (((Max(InStr(" " & sentence & " "," " & negphrase & " ")))=0));我的答案因此，我将为Richard b / c提供正确的答案，他的解决方案适用于较小的记录集，但不适用于较大的记录集。这是我用来将所有否定关键字放入数组中的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);？> 最佳答案 使用否定左联接，这将仅根据规则返回Senteces表中与否定表不匹配的行select * from Sentences sleft join Negatives non (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');关于php - MySQL PHP如果语句表中不存在来自表的关键字短语显示结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32954383/