// Scan into a single result result, err := gorm.G[Result](db).Raw("SELECT id, name, age FROM users WHERE id = ?", 3).Find(context.Background())
// Scan into a primitive type age, err := gorm.G[int](db).Raw("SELECT SUM(age) FROM users WHERE role = ?", "admin").Find(context.Background())
// Scan into a slice users, err := gorm.G[User](db).Raw("UPDATE users SET name = ? WHERE age = ? RETURNING id, name", "jinzhu", 20).Find(context.Background())
Exec 原生 SQL
// Execute raw SQL result := gorm.WithResult() err := gorm.G[any](db, result).Exec(context.Background(), "DROP TABLE users")
// Execute with parameters err = gorm.G[any](db).Exec(context.Background(), "UPDATE orders SET shipped_at = ? WHERE id IN ?", time.Now(), []int64{1, 2, 3})
// Exec with SQL Expression err = gorm.G[any](db).Exec(context.Background(), "UPDATE users SET money = ? WHERE name = ?", gorm.Expr("money * ? + ?", 10000, 1), "jinzhu")
Traditional API
Query Raw SQL with Scan
type Result struct { ID int Name string Age int }
var result Result db.Raw("SELECT id, name, age FROM users WHERE id = ?", 3).Scan(&result)
db.Raw("SELECT id, name, age FROM users WHERE name = ?", "jinzhu").Scan(&result)
var age int db.Raw("SELECT SUM(age) FROM users WHERE role = ?", "admin").Scan(&age)
var users []User db.Raw("UPDATE users SET name = ? WHERE age = ? RETURNING id, name", "jinzhu", 20).Scan(&users)
Exec with Raw SQL
db.Exec("DROP TABLE users") db.Exec("UPDATE orders SET shipped_at = ? WHERE id IN ?", time.Now(), []int64{1, 2, 3})
// Exec with SQL Expression db.Exec("UPDATE users SET money = ? WHERE name = ?", gorm.Expr("money * ? + ?", 10000, 1), "jinzhu")
users, err := gorm.G[User](db).Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(context.Background()) // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
result3, err := gorm.G[User](db).Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu2"}).First(context.Background()) // SELECT * FROM `users` WHERE name1 = "jinzhu2" OR name2 = "jinzhu2" ORDER BY `users`.`id` LIMIT 1
// Named Argument with Raw SQL users, err := gorm.G[User](db).Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name", sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2")).Find(context.Background()) // SELECT * FROM users WHERE name1 = "jinzhu1" OR name2 = "jinzhu2" OR name3 = "jinzhu1"
users, err := gorm.G[User](db).Raw("SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2", map[string]interface{}{"name": "jinzhu", "name2": "jinzhu2"}).Find(context.Background()) // SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"
type NamedArgument struct { Name string Name2 string }
users, err := gorm.G[User](db).Raw("SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2", NamedArgument{Name: "jinzhu", Name2: "jinzhu2"}).Find(context.Background()) // SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"
Traditional API
GORM supports named arguments with sql.NamedArg, map[string]interface{}{} or struct, for example:
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user) // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu2"}).First(&result3) // SELECT * FROM `users` WHERE name1 = "jinzhu2" OR name2 = "jinzhu2" ORDER BY `users`.`id` LIMIT 1
// 原生 SQL 及命名参数 db.Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name", sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2")).Find(&user) // SELECT * FROM users WHERE name1 = "jinzhu1" OR name2 = "jinzhu2" OR name3 = "jinzhu1"
db.Raw("SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2", map[string]interface{}{"name": "jinzhu", "name2": "jinzhu2"}).Find(&user) // SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"
type NamedArgument struct { Name string Name2 string }
db.Raw("SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2", NamedArgument{Name: "jinzhu", Name2: "jinzhu2"}).Find(&user) // SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"
sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB { return tx.Model(&User{}).Where("id = ?", 100).Limit(10).Order("age desc").Find(&[]User{}) }) sql //=> SELECT * FROM "users" WHERE id = 100 AND "users"."deleted_at" IS NULL ORDER BY age desc LIMIT 10
Row & Rows
Generics API
获取 *sql.Row 结果
// Use GORM API build SQL row := gorm.G[any](db).Table("users").Where("name = ?", "jinzhu").Select("name", "age").Row(context.Background()) row.Scan(&name, &age)
// Use Raw SQL row := gorm.G[any](db).Raw("select name, age, email from users where name = ?", "jinzhu").Row(context.Background()) row.Scan(&name, &age, &email)
获取 *sql.Rows 结果
// Use GORM API build SQL rows, err := gorm.G[User](db).Where("name = ?", "jinzhu").Select("name, age, email").Rows(context.Background()) defer rows.Close() for rows.Next() { rows.Scan(&name, &age, &email)
// do something }
// Raw SQL rows, err := gorm.G[any](db).Raw("select name, age, email from users where name = ?", "jinzhu").Rows(context.Background()) defer rows.Close() for rows.Next() { rows.Scan(&name, &age, &email)
db.Offset(10).Limit(5).Find(&users) // SQL Server 会生成 // SELECT * FROM "users" OFFSET 10 ROW FETCH NEXT 5 ROWS ONLY // MySQL 会生成 // SELECT * FROM `users` LIMIT 5 OFFSET 10