php+mysql多关键词分词搜索查询，mysql多关键词正则匹配查询，搜索结果关键词匹配度排序

柔情只为你懂 2023-02-23 13:29 36阅读 0赞

补充：字段中包含（符号，中文，字母，数字）排序问题处理(1.a,10\_a,11-b,2.a,3-a,20、a)

//先把可能指定字符替换为.，然后截取第一个“.”之前的所有字符,再对这部分内容+0操作，最后再排序
    //regexp_replace(name,"-|_|\&|、",".") AS rename,
    //$by .= "LEFT(name,1) {$oldBy}, MID(name,2)+0 {$oldBy}, LPAD(name,200,0) {$oldBy},";
    //SUBSTRING_INDEX(rename, '.', 1); 截取第一个“.”之前的所有字符
    $by .= "ORDER BY SUBSTRING_INDEX(rename, '.', 1)+0 DESC, name DESC,  ";

创建mysql自定义函数regexp\_replace，实现能够正则替换数据regexp\_replace(name,"-|\_|\\&|、",".")

# mysql5.7实现regex\_replace正则替换功能  来源网址:[mysql5.7实现regex\_replace正则替换功能\_WY\_SHIJI的博客-CSDN博客][mysql5.7_regex_replace_WY_SHIJI_-CSDN] #

#创建前删除已经创建的自定义函数
    DROP FUNCTION IF EXISTS regexp_replace;
    #创建 regexp_replace函数
    DELIMITER $$  
    CREATE FUNCTION  `regexp_replace`(string_a VARCHAR(1000),pattern VARCHAR(1000),string_b VARCHAR(1000))  
      RETURNS VARCHAR(1000)  
      DETERMINISTIC  
    BEGIN  
     DECLARE string_c VARCHAR(1000);  
     DECLARE nub VARCHAR(1);  
     DECLARE i INT;  
     SET i =1;  
     SET string_c ='';  
     IF string_a REGEXP pattern THEN  
        loop_label: LOOP  
          IF i>CHAR_LENGTH(string_a) THEN  
            LEAVE loop_label;  
     END IF;
     SET nub = SUBSTRING(string_a,i,1);  
     IF NOT nub REGEXP pattern THEN  
     SET string_c = CONCAT(string_c,nub);  
          ELSE  
            SET string_c = CONCAT(string_c,string_b);  
          END IF;  
          SET i=i+1;  
        END LOOP;  
      ELSE
        SET string_c = string_a;  
      END IF;  
      RETURN string_c;  
    END$$  
    DELIMITER;

`-----------------------------------------------END-----------------------------------------------------------------`

1、使用mysql的REGEXP正则匹配出相关内容，再使用LOCATE('\{$keywordOld\}',\{$keywordCode\}) DESC 统计出关键词匹配次数进行排序排序，

// LOCATE('$keywordOld',$keywordCode) DESC 排序使用
    $keywordOld = addcslashes($keyword, "'");
    $keywordArr = explode(' ', $keywordOld);
    $kCount = count($keywordArr);
    //关键词转义  ' " * . ? + $ ^ [ ] ( ) { } | \ / ! - : =
    //转义特殊字符 \* \. \?
    $keyword = addcslashes(mb_strtolower($keyword, 'utf-8'), '*.?+$^[](){}|\\/!-:=%@');
    $keyword = preg_replace("/\s+/", "|", $keyword); //把空格替换成 |
    $keywordNew = $keyword;
    $keyword = addcslashes($keyword, "'");
    //mysql正则匹配必须 把 \+ ,替换成\\+
    $keywordNew = addcslashes($keywordNew, '\\');
    //特殊字符 MySQL 语句安全转义 ' "
    $keywordNew = addcslashes($keywordNew, "'");
    $keywordNew = " REGEXP '{$keywordNew}'";
    
    $keywordCode = "LCASE(CONCAT(name,' ',user_name))";
    $code = "id, user_name, FROM_UNIXTIME(insert_time) AS insert_time";
    $where = "WHERE is_show = 1 AND {$keywordCode} REGEXP '{$keywordNew}' ";
    
    $codeLen = mb_strlen ($keywordOld,'utf8'); //关键词长度
    $mysqlLen = "CHAR_LENGTH($keywordCode)";
    //关键词完全匹配排前面，匹配次数多的拍前面
    $by = "(CASE WHEN product_name = '{$keyword}' THEN 1 ELSE 0 END) DESC, ($mysqlLen - CHAR_LENGTH(REPLACE($keywordCode,'$keywordOld','')))/$codeLen DESC, "; //返回计算关键词匹配次数
    if($kCount>1) {
        foreach ($keywordArr as $m => $item) {
            $codeLen = strlen ($item);
            $by .= "($mysqlLen - CHAR_LENGTH(REPLACE($keywordCode,'$item','')))/$codeLen";
            if($m != $kCount -1){
                $by .= " + ";
            }
        }
        $by .= " DESC, ";
    }
    $by .= "insert_time DESC";
    
    $sql = "SELECT {$code} FROM category {$where} UNION ALL SELECT {$code} FROM files {$where} {$by} limit 500";
    
    //替换关键词
    $name = preg_replace("/($keyword)/i", "<font color=\"red\">\\1</font>", $name);
    $userName = preg_replace("/($keyword)/i", "<font color=\"red\">\\1</font>", $userName);

2、最终的SQL语句

SELECT 
      id,
      user_name,
      CONCAT(NAME, ' ', user_name) AS basename,
      FROM_UNIXTIME(insert_time) AS insert_time 
    FROM
      category 
    WHERE is_show = 1 
      AND basename REGEXP 'a|b|c' 
    UNION
    ALL 
    SELECT 
      id,
      CONCAT(NAME, ' ', user_name) AS basename,
      FROM_UNIXTIME(insert_time) AS insert_time 
    FROM
      files 
    WHERE is_show = 1 
      AND basename REGEXP 'a|b|c' 
    ORDER BY (
        CHAR_LENGTH(basename) - CHAR_LENGTH(REPLACE(basename, 'a b c', ''))
      ) / 5 DESC,
      (
        CHAR_LENGTH(basename) - CHAR_LENGTH(REPLACE(basename, 'a', ''))
      ) / 1 + (
        CHAR_LENGTH(basename) - CHAR_LENGTH(REPLACE(basename, 'b', ''))
      ) / 1 + (
        CHAR_LENGTH(basename) - CHAR_LENGTH(REPLACE(basename, 'c', ''))
      ) / 1 DESC,
      insert_time DESC 
    LIMIT 300

再总结一下常用的正在表达式。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0Nzc5MTQ_size_16_color_FFFFFF_t_70][]

[mysql5.7_regex_replace_WY_SHIJI_-CSDN]: https://blog.csdn.net/qq_35861801/article/details/103183360
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0Nzc5MTQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200708150540520.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0Nzc5MTQ=,size_16,color_FFFFFF,t_70