您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 程式設計綜合問答

SQLite 多條件模糊查詢的動態 SQL 建構技巧

編輯：程式設計綜合問答

需求場景

使用者介面提供學號、姓名、性別、地址四個查詢欄位，任一欄位可為空。要求：

填寫欄位 → 該欄位模糊比對（LIKE）
未填欄位 → 不加入 WHERE 條件
全部為空 → 查詢所有學生

核心原則：動態建構 WHERE 子句

不要寫死 WHERE name LIKE ? AND gender LIKE ? ...，因為空值會導致 LIKE '%%' 掃描全表且無法使用索引。應在程式碼中根據使用者輸入動態組裝 SQL。

C# 參數化實作範例

public List Search(string id, string name, string gender, string address)
{
    var sql = new StringBuilder("SELECT * FROM Students WHERE 1=1");
    var parameters = new List();

    if (!string.IsNullOrWhiteSpace(id))
    {
        sql.Append(" AND StudentId LIKE @id");
        parameters.Add(new SQLiteParameter("@id", $"%{id}%"));
    }
    if (!string.IsNullOrWhiteSpace(name))
    {
        sql.Append(" AND Name LIKE @name");
        parameters.Add(new SQLiteParameter("@name", $"%{name}%"));
    }
    if (!string.IsNullOrWhiteSpace(gender))
    {
        sql.Append(" AND Gender = @gender"); // 性別通常精確比對
        parameters.Add(new SQLiteParameter("@gender", gender));
    }
    if (!string.IsNullOrWhiteSpace(address))
    {
        sql.Append(" AND Address LIKE @address");
        parameters.Add(new SQLiteParameter("@address", $"%{address}%"));
    }

    using (var conn = new SQLiteConnection(connStr))
    using (var cmd = new SQLiteCommand(sql.ToString(), conn))
    {
        cmd.Parameters.AddRange(parameters.ToArray());
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            var list = new List();
            while (reader.Read())
            {
                list.Add(new Student
                {
                    Id = reader.GetString(0),
                    Name = reader.GetString(1),
                    Gender = reader.GetString(2),
                    Address = reader.GetString(3)
                });
            }
            return list;
        }
    }
}

關鍵優化點

技巧	說明
`WHERE 1=1`	避免第一個條件需判斷是否加 AND，統一 `AND col = ?`
參數化查詢	防 SQL 注入、自動處理引號、提升計畫快取命中率
欄位型別區分	字串用 LIKE、枚舉/布林用 =、數值用 BETWEEN
索引建議	常用查詢欄位建立索引；LIKE '%xxx%' 無法用索引，考慮全文檢索（FTS5）

進階：使用 Dapper 簡化程式碼

using Dapper;

public IEnumerable Search(StudentQuery q)
{
    var sql = @"SELECT * FROM Students WHERE 1=1
                /**if(id)**/ AND StudentId LIKE @id
                /**if(name)**/ AND Name LIKE @name
                /**if(gender)**/ AND Gender = @gender
                /**if(address)**/ AND Address LIKE @address";

    // Dapper 無內建條件編譯，實際上仍需字串拼接或用 SqlBuilder
    var builder = new SqlBuilder();
    var template = builder.AddTemplate("SELECT * FROM Students WHERE 1=1 /**where**/");
    if (!string.IsNullOrEmpty(q.Id)) builder.Where("StudentId LIKE @id", new { id = $"%{q.Id}%" });
    if (!string.IsNullOrEmpty(q.Name)) builder.Where("Name LIKE @name", new { name = $"%{q.Name}%" });
    if (!string.IsNullOrEmpty(q.Gender)) builder.Where("Gender = @gender", new { gender = q.Gender });
    if (!string.IsNullOrEmpty(q.Address)) builder.Where("Address LIKE @address", new { address = $"%{q.Address}%" });

    using (var conn = new SQLiteConnection(connStr))
        return conn.Query(template.RawSql, template.Parameters);
}

效能考量

避免全表掃描：若四個欄位皆為空，直接 SELECT * FROM Students 可能極慢。建議加上 LIMIT 100 或要求至少一個條件。
複合索引：若常同時查姓名+性別，建立 CREATE INDEX idx_name_gender ON Students(Name, Gender)。
大資料量：考慮 SQLite FTS5 虛擬表或遷移至 PostgreSQL/MySQL。