补充:字段中包含(符号,中文,字母,数字)排序问题处理(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博客
#创建前删除已经创建的自定义函数
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
再总结一下常用的正在表达式。