MySQL模糊查询二三事

1、源数据信息

源数据字段：Student表字段Names 存储内容为"111,222,333,444,555,666,777,888"

2、查询请求入参

{
    "Names": "666,888"
}

3、具体需求

需要拿到既包含666又包含888，直接模糊查询或者Contains之类的无法满足

4、方法实现

方法一：

// 纯SQL语句

var nameSql = string.Empty;
for (int i = 0; i < namesList.Count; i++)
{
    nameSql += $"FIND_IN_SET('{namesList[i]}', REPLACE(names, ',', ',')) > 0";
    if (i != namesList.Count - 1)
        nameSql += " OR ";
}
var list = _student.AsQueryable()
    .WhereIF(args.Names.IsNotNullAndEmpty(), $"({nameSql})")
    .Where(x => x.Status > 0)
    .OrderBy(x => x.ID, OrderByType.Desc)
    .ToPageList(page.CurrenetPageIndex, page.PageSize, ref counts, ref pageCounts);

生成SQL如下：

SELECT
	*  
FROM
	`Student`  
WHERE
	(
  FIND_IN_SET(
  	'666',
  REPLACE ( Names, ',', ',' )) > 0  
  OR FIND_IN_SET(
  	'888',
  REPLACE ( Names, ',', ',' )) > 0  
	)  
	AND ( `Status` > 0 )  
ORDER BY
	`ID` DESC  
	LIMIT 0,10

方法二：

// 动态表达式 （推荐）

var namesList = new List<string>();
if (args.Names.IsNotNullAndEmpty())
    namesList = args.Names.Split(',').ToList();
Expressionable<WashRulegroup> exp_names = Expressionable.Create<WashRulegroup>();
foreach (var name in namesList)
{
    exp_names.Or(t => t.names.Contains(name));
}
var list = _rulegroup.AsQueryable()
    .WhereIF(args.Names.IsNotNullAndEmpty(), exp_names.ToExpression())
    .Where(x => x.Status > 0)
    .OrderBy(x => x.ID, OrderByType.Desc)
    .ToPageList(page.CurrenetPageIndex, page.PageSize, ref counts, ref pageCounts);

生成SQL如下：

SELECT
	*  
FROM
	`Student`  
WHERE
	(( `Names` LIKE concat( '%', '666', '%' )) OR ( `Names` LIKE concat( '%', '888', '%' )) )  
	AND ( `Status` > 0 )  
ORDER BY
	`ID` DESC  
	LIMIT 0,10

注：本文使用orm为sqlsugar

希望以上内容能够帮助你理解和学习MySQL模糊查询。欢迎点赞、关注、收藏，如果你还有其他问题，欢迎评论区交流。