mysql IF语句，模糊检索

✿ﾟ卡笨卡

已于 2024-05-08 14:46:39 修改

阅读量278

点赞数 3

分类专栏： java 笔记 sql 文章标签： mysql sql

于 2024-05-07 15:28:14 首次发布

本文链接：https://blog.csdn.net/tian__c/article/details/138533753

版权

笔记同时被 3 个专栏收录

22 篇文章 0 订阅

订阅专栏

java

18 篇文章 1 订阅

订阅专栏

sql

2 篇文章 0 订阅

订阅专栏

使用MySQL IF语句完成条件检索
IF(expr1,expr2,expr3)，expr1如果满足条件就用expr2，否则用expr3

SELECT
	a.*,
	count(*) AS stdSum 
FROM
	idb_std_power_engin_v1 a 
WHERE
	1 = 1 
	AND (
	IF
		( 'KV' IS NOT NULL, a.NAME REGEXP 'KV', 1 = 1 ) 
	OR
	IF
		( 'KV' IS NOT NULL, a.description REGEXP 'KV', 1 = 1 ) 
	) 
AND
IF
	( 138 IS NOT NULL, a.country = 138, 1 = 1 ) 
GROUP BY
NAME 
	LIMIT 0,
	10

REGEXP 正则表达式，用于模糊匹配多个数据

/**
     * @description: 中英互换 正则表达查询
     * @author csb
     * @date: 2022/12/29 11:24
     */
    public static String suggestTermNameByRegular(String keyword,List<String> termName) {
        StringBuffer keywords = new StringBuffer();
        keywords.append(keyword+"|");
        if(null != termName && termName.size() > 0){
            termName.stream().forEach(a -> {
                keywords.append(a+'|');
            });
        }
        //去除最后的 |
        keywords.deleteCharAt(keywords.length() - 1);
        return String.valueOf(keywords);
    }

/**
     * @description: 关键字高亮
     * @author csb
     * @date: 2022/12/23 10:48
     */

    public static String IgnoreCaseReplace(String source, String patternstring) {
        Pattern p = Pattern.compile(patternstring, Pattern.CASE_INSENSITIVE);
        Matcher mc = p.matcher(source);
        StringBuffer sb = new StringBuffer();
        while (mc.find()) {
            mc.appendReplacement(sb, "<em>" + mc.group() + "</em>");
        }
        mc.appendTail(sb);
        return sb.toString();
    }