MySQL和PHP -使用“LIKE”和“NOT LIKE”

Question

我需要一些帮助来同时使用LIKE和NOT LIKE ...我有一个查询，我根据来自另一个服务器的请求变量传递WHERE子句。其中一个查询如下所示：

    'CONNECT' =>
    "( detail_head.comment LIKE '%port%'
      or detail_head.comment LIKE '%forward%'
      or detail_head.comment LIKE '%connect%'
      or detail_head.comment LIKE '%router%'
      or detail_head.comment LIKE '%fire%wall%'
      or detail_head.comment LIKE '%sonic%'
      ) AND (
      detail_head.comment NOT LIKE '%report%'
      OR detail_head.comment NOT LIKE '%portal%'
      )",

您可以看到我使用的是LIKE和NOT LIKE。不幸的是，这并不像我希望的那样工作。我猜这是因为我请求的是PORT，而不是REPORT，所以它给了我LIKE。

我在想在这种情况下我该怎么办。我正在考虑制作另一个查询或数组，我将使用它作为“排除列表”。其中的查询类似于语句，我可以在Where子句中使用这些语句来表示“table_uid NOT in (逗号分隔的UID列表)”。

我有我想要排除的LIKE语句：

$exclude_where_clauses = array(
        'CC'            => "(detail_head.comment LIKE '%ccb%') ",
        'CONNECT'       => "(detail_head.comment LIKE '%report%' OR detail_head.comment LIKE '%portal%') ",
        'EO'            => "(detail_head.comment LIKE '%OCU%' AND detail_head.comment LIKE '%KS%' AND detail_head.comment LIKE '%screen%' AND detail_head.comment LIKE '%term%') ",
        'INVENTORY'     => "(detail_head.comment LIKE '%discount%') ",
        'KS'            => "(detail_head.comment LIKE '%panel%' or detail_head.comment LIKE '%PMIX%' or detail_head.comment LIKE '%pmix%') ",
        'OCUS'          => "(detail_head.comment LIKE '%document%') ",
        'SALES'         => "(detail_head.comment LIKE '%point%') ",
        'SECURITY'      => "(detail_head.comment LIKE '%km%') ",
        'TERMS'         => "(detail_head.comment LIKE '%forward%' or detail_head.comment LIKE '%sales%' or detail_head.comment LIKE '%intermittent%' or detail_head.comment LIKE '%print%' or detail_head.comment LIKE '%de%min%' or detail_head.comment LIKE '%reciept%' or detail_head.comment LIKE '%time%') ",
);

因此，最后，我希望将当前的查询数组转换为"(detail_head.comment LIKE '%port%' or detail_head.comment LIKE '%forward%' or detail_head.comment LIKE '%connect%' or detail_head.comment LIKE '%router%' or detail_head.comment LIKE '%fire%wall%' or detail_head.comment LIKE '%sonic%') AND table_uid NOT IN(LIST OF COMMA SEPARATED UIDs) "

Answer 1

试试这个：

'CONNECT' => "
    (  detail_head.comment LIKE '%port%'
    OR detail_head.comment LIKE '%forward%'
    OR detail_head.comment LIKE '%connect%'
    OR detail_head.comment LIKE '%router%'
    OR detail_head.comment LIKE '%fire%wall%'
    OR detail_head.comment LIKE '%sonic%'
    )
    AND NOT (
           detail_head.comment LIKE '%ccb%'
        OR detail_head.comment LIKE '%report%' 
        OR detail_head.comment LIKE '%portal%'
        OR detail_head.comment LIKE '%OCU%'
        OR detail_head.comment LIKE '%KS%'
        OR detail_head.comment LIKE '%screen%'
        OR detail_head.comment LIKE '%term%'
        OR detail_head.comment LIKE '%discount%'
        OR detail_head.comment LIKE '%panel%'
        OR detail_head.comment LIKE '%PMIX%'
        OR detail_head.comment LIKE '%pmix%'
        OR detail_head.comment LIKE '%document%'
        OR detail_head.comment LIKE '%point%'
        OR detail_head.comment LIKE '%km%'
        OR detail_head.comment LIKE '%forward%'
        OR detail_head.comment LIKE '%sales%'
        OR detail_head.comment LIKE '%intermittent%'
        OR detail_head.comment LIKE '%print%'
        OR detail_head.comment LIKE '%de%min%'
        OR detail_head.comment LIKE '%reciept%'
        OR detail_head.comment LIKE '%time%'
    )
",

Answer 2

我不认为这会提高效率(它确实可能效率较低)，但是，它可能是一种更合理地定义规范的方法--在regexp()中使用单词边界。

这将返回0：

SELECT 'foo report bar' REGEXP '[[:<:]]port[[:>:]]';

其中，这将返回1：

SELECT 'foo report bar' REGEXP '[[:<:]]report[[:>:]]';

MySQL has more info in the manual。(5.1手动链接)

根据手头任务的性质，以及这会给数据库服务器带来多大的压力，我可能会考虑添加字段或相关的表来帮助我提前(在插入数据时)进行处理，这样我就可以在事后以较轻的方式运行类似的报告--而不必在全文字段中进行繁重的文本处理。