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模糊查询。欢迎点赞、关注、收藏，如果你还有其他问题，欢迎评论区交流。