C#中SQL LIKE操作与参数化查询的实践指南

2025年12月6日 互联网

一、SQL LIKE操作符在C#中的核心应用场景

LIKE操作符是SQL中实现模糊匹配的关键工具，在C#开发中广泛应用于用户搜索、数据过滤等场景。其核心语法包含两个通配符：%（匹配任意数量字符）和_（匹配单个字符）。例如，WHERE Name LIKE '张%'可检索所有以”张”开头的姓名记录。

1.1 基础语法实现

在ADO.NET中，LIKE操作需通过字符串拼接构建SQL语句：

  1. string keyword = "张";
  2. string sql = $"SELECT * FROM Users WHERE Name LIKE '{keyword}%'";

这种硬编码方式存在严重安全隐患，易引发SQL注入攻击。攻击者可能通过构造特殊输入（如张' OR '1'='1）篡改查询逻辑。

1.2 性能优化策略

LIKE查询的性能受通配符位置影响显著：

  • 前导通配符（LIKE '%张'）：需全表扫描，性能最差
  • 中段通配符（LIKE '张%明'）：仍需扫描匹配字段
  • 后缀匹配（LIKE '张%'）：可利用B树索引优化

建议对高频查询字段建立函数索引（如SQL Server的CREATE INDEX idx_name ON Users(Name) INCLUDE (Name)），或考虑使用全文索引（FULLTEXT INDEX）替代LIKE操作。

二、参数化查询的防御性编程实践

参数化查询是防止SQL注入的核心手段，通过将用户输入与SQL语句分离，确保输入内容仅作为数据处理而非代码执行。

2.1 SqlParameter的标准用法

  1. using (SqlConnection conn = new SqlConnection(connectionString))
  2. {
  3.     string keyword = "张";
  4.     string sql = "SELECT * FROM Users WHERE Name LIKE @NamePattern";
  6.     SqlCommand cmd = new SqlCommand(sql, conn);
  7.     cmd.Parameters.Add("@NamePattern", SqlDbType.NVarChar).Value = keyword + "%";
  9.     conn.Open();
  10.     SqlDataReader reader = cmd.ExecuteReader();
  11.     // 处理结果...
  12. }

此方式确保%等特殊字符被正确转义，同时保持查询计划可重用性。

2.2 动态LIKE模式的参数化处理

处理复杂模式（如同时支持前缀和后缀模糊匹配）时，可采用以下模式：

  1. string searchTerm = "test";
  2. bool prefixSearch = true;
  3. bool suffixSearch = false;
  5. string pattern = (prefixSearch ? "%" : "") + searchTerm + (suffixSearch ? "%" : "");
  6. cmd.Parameters.Add("@Pattern", SqlDbType.NVarChar).Value = pattern;
  7. string sql = "SELECT * FROM Products WHERE Name LIKE @Pattern";

三、ORM框架中的高级实现

Entity Framework Core等ORM框架提供了更优雅的解决方案：

3.1 EF Core的Like扩展方法

通过自定义扩展方法实现类型安全的LIKE查询：

  1. public static class QueryExtensions
  2. {
  3.     public static IQueryable<T> WhereLike<T>(
  4.         this IQueryable<T> query,
  5.         Expression<Func<T, string>> selector,
  6.         string value)
  7.     {
  8.         var likePattern = $"%{value}%";
  9.         var parameter = selector.Parameters[0];
  10.         var body = Expression.Call(
  11.             typeof(string),
  12.             "Contains",
  13.             null,
  14.             selector.Body,
  15.             Expression.Constant(value));
  17.         return query.Where(Expression.Lambda<Func<T, bool>>(body, parameter));
  18.     }
  19. }
  21. // 使用示例
  22. var results = dbContext.Products
  23.     .WhereLike(p => p.Name, "手机")
  24.     .ToList();

3.2 Dapper的参数化支持

Dapper作为轻量级ORM，通过DynamicParameters实现灵活参数绑定：

  1. var parameters = new DynamicParameters();
  2. parameters.Add("pattern", "%" + searchTerm + "%");
  4. var results = connection.Query<Product>(
  5.     "SELECT * FROM Products WHERE Name LIKE @pattern",
  6.     parameters);

四、安全防护的深度实践

4.1 输入验证三原则

  1. 白名单验证：限制输入字符集（如仅允许中文、字母、数字）
  2. 长度限制：根据字段最大长度设置输入上限
  3. 编码转换：对特殊字符进行HTML/URL编码

4.2 存储过程封装

将LIKE查询封装在存储过程中，通过参数化接口暴露功能：

  1. CREATE PROCEDURE SearchUsers
  2.     @NamePattern NVARCHAR(100)
  3. AS
  4. BEGIN
  5.     SELECT * FROM Users WHERE Name LIKE @NamePattern
  6. END

C#调用代码：

  1. using (SqlCommand cmd = new SqlCommand("SearchUsers", conn))
  2. {
  3.     cmd.CommandType = CommandType.StoredProcedure;
  4.     cmd.Parameters.AddWithValue("@NamePattern", "张%");
  5.     // 执行查询...
  6. }

五、性能调优实战技巧

5.1 查询计划重用

参数化查询允许数据库缓存执行计划，对OLTP系统性能提升显著。可通过SQL Server Profiler监控SQL:BatchPrepared事件验证计划重用情况。

5.2 索引优化策略

针对LIKE查询创建计算列索引：

  1. ALTER TABLE Users ADD NameSearch AS (Name + '%') PERSISTED;
  2. CREATE INDEX IX_Users_NameSearch ON Users(NameSearch);

查询时调整为：

  1. cmd.Parameters.Add("@Pattern", SqlDbType.NVarChar).Value = "张";
  2. string sql = "SELECT * FROM Users WHERE NameSearch LIKE @Pattern + '%'";

5.3 分页处理方案

结合LIKE查询实现高效分页：

  1. int pageSize = 20;
  2. int pageNumber = 2;
  3. string sql = @"
  4.     SELECT * FROM (
  5.         SELECT *, ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS RowNum
  6.         FROM Products WHERE Name LIKE @Pattern
  7.     ) AS PagedResults
  8.     WHERE RowNum BETWEEN @Start AND @End";
  10. cmd.Parameters.AddWithValue("@Pattern", "%手机%");
  11. cmd.Parameters.AddWithValue("@Start", (pageNumber - 1) * pageSize + 1);
  12. cmd.Parameters.AddWithValue("@End", pageNumber * pageSize);

六、异常处理与日志记录

完善的错误处理机制应包含：

  1. 结构化异常处理：区分SQL异常与业务异常
  2. 敏感信息脱敏：日志中避免记录完整SQL语句
  3. 重试机制：对瞬时故障实施指数退避重试
  1. try
  2. {
  3.     // 数据库操作代码
  4. }
  5. catch (SqlException ex) when (ex.Number == 1205) // 死锁错误
  6. {
  7.     logger.LogWarning(ex, "发生数据库死锁，执行重试逻辑");
  8.     // 实现重试机制
  9. }
  10. catch (Exception ex)
  11. {
  12.     logger.LogCritical(ex, "数据库操作发生严重错误");
  13.     throw;
  14. }

通过系统化的LIKE操作符应用和参数化查询实践，开发者能够构建出既安全又高效的数据库交互层。建议在实际项目中结合具体业务场景，综合运用本文介绍的各项技术，持续监控查询性能指标，通过索引优化、查询重写等手段实现系统性能的渐进式提升。

最新文章