SQL 语句中多个占位符
?时，容易搞混顺序。命名参数让 SQL 更清晰，减少出错。
使用 @ 前缀命名参数：
db.Where("name = @name AND age > @age", sql.Named("name", "张三"), sql.Named("age", 20)).Find(&users)
更简洁的写法：
db.Where("name = @name AND age > @age", map[string]interface{}{
"name": "张三",
"age": 20,
}).Find(&users)
db.Raw("SELECT * FROM users WHERE name = @name AND status = @status", map[string]interface{}{
"name": "张三",
"status": "active",
}).Scan(&users)
db.Exec(`
UPDATE users
SET name = @name, age = @age, updated_at = @now
WHERE id = @id
`, map[string]interface{}{
"name": "新名字",
"age": 25,
"now": time.Now(),
"id": 1,
})
用结构体作为参数：
type UserQuery struct {
Name string
Status string
MinAge int
}
query := UserQuery{
Name: "张三",
Status: "active",
MinAge: 20,
}
db.Where("name = @Name AND status = @Status AND age >= @MinAge", query).Find(&users)
结构体字段名作为参数名。
可读性
// 占位符方式
db.Where("name = ? AND age > ? AND status = ? AND city = ?",
"张三", 20, "active", "北京")
// 命名参数方式
db.Where("name = @name AND age > @age AND status = @status AND city = @city", map[string]interface{}{
"name": "张三",
"age": 20,
"status": "active",
"city": "北京",
})
可维护性
参数顺序无关，增删参数不影响其他：
// 占位符方式：插入参数要调整后面所有位置
db.Where("name = ? AND age > ? AND status = ?", "张三", 20, "active")
// 命名参数方式：直接添加
db.Where("name = @name AND age > @age AND status = @status AND city = @city", map[string]interface{}{
"name": "张三",
"age": 20,
"status": "active",
"city": "北京",
})
复用参数
同一个参数多次使用：
db.Where("name = @name OR nickname = @name", sql.Named("name", "张三")).Find(&users)
多条件搜索
func SearchUsers(db *gorm.DB, params map[string]interface{}) []User {
var users []User
db.Where(`
(@name IS NULL OR name LIKE CONCAT('%', @name, '%'))
AND (@status IS NULL OR status = @status)
AND (@min_age IS NULL OR age >= @min_age)
AND (@max_age IS NULL OR age <= @max_age)
AND (@city IS NULL OR city = @city)
`, params).Find(&users)
return users
}
SearchUsers(db, map[string]interface{}{
"name": "张",
"status": "active",
"min_age": 18,
"max_age": nil,
"city": nil,
})
批量更新
db.Exec(`
UPDATE products
SET
price = @price,
stock = @stock,
updated_at = @now
WHERE id = @id
`, []map[string]interface{}{
{"id": 1, "price": 99.9, "stock": 100, "now": time.Now()},
{"id": 2, "price": 199.9, "stock": 50, "now": time.Now()},
{"id": 3, "price": 299.9, "stock": 30, "now": time.Now()},
})
db.Model(&User{}).
Where("status = @status", sql.Named("status", "active")).
Where("age > @age", sql.Named("age", 20)).
Find(&users)
不同数据库的命名参数语法：
| 数据库 | 语法 |
|---|---|
| MySQL | @name |
| PostgreSQL | @name 或 $1 |
| SQLite | @name 或 ? |
| SQL Server | @name |
GORM 会自动处理转换。
命名参数提高 SQL 可读性和可维护性。参数多时推荐使用，避免占位符顺序错误。