type User struct { ID string`gorm:"default:uuid_generate_v3()"`// 数据库函数 FirstName string LastName string Age uint8 FullName string`gorm:"->;type:GENERATED ALWAYS AS (concat(firstname,' ',lastname));default:(-);` }
// 获取第一条匹配的记录 db.Where("name = ?", "linzy").First(&user) // SELECT * FROM users WHERE name = 'linzy' ORDER BY id LIMIT 1;
// 获取全部匹配的记录 db.Where("name <> ?", "linzy").Find(&users) // SELECT * FROM users WHERE name <> 'linzy';
// IN db.Where("name IN ?", []string{"linzy", "linzy2"}).Find(&users) // SELECT * FROM users WHERE name IN ('linzy','linzy2');
// LIKE db.Where("name LIKE ?", "%in%").Find(&users) // SELECT * FROM users WHERE name LIKE '%in%';
// AND db.Where("name = ? AND age >= ?", "linzy", "22").Find(&users) // SELECT * FROM users WHERE name = 'linzy' AND age >= 22;
// Time db.Where("updated_at > ?", lastWeek).Find(&users) // SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';
// BETWEEN db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users) // SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';
2）Struct & Map 条件
1 2 3 4 5 6 7 8 9 10 11
// Struct db.Where(&User{Name: "linzy", Age: 20}).First(&user) // SELECT * FROM users WHERE name = "linzy" AND age = 20 ORDER BY id LIMIT 1;
// Map db.Where(map[string]interface{}{"name": "linzy", "age": 20}).Find(&users) // SELECT * FROM users WHERE name = "linzy" AND age = 20;
// 主键切片条件 db.Where([]int64{20, 21, 22}).Find(&users) // SELECT * FROM users WHERE id IN (20, 21, 22);
db.Where(&User{Name: "linzy", Age: 0}).Find(&users) // SELECT * FROM users WHERE name = "linzy";
您可以使用 map 来构建查询条件，例如：
1 2
db.Where(map[string]interface{}{"Name": "linzy", "Age": 0}).Find(&users) // SELECT * FROM users WHERE name = "linzy" AND age = 0;
3）内联条件
用法跟 Where 方法一样
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
// SELECT * FROM users WHERE id = 23; // 根据主键获取记录，如果是非整型主键 db.First(&user, "id = ?", "string_primary_key") // SELECT * FROM users WHERE id = 'string_primary_key';
// Plain SQL db.Find(&user, "name = ?", "linzy") // SELECT * FROM users WHERE name = "linzy";
db.Find(&users, "name <> ? AND age > ?", "linzy", 20) // SELECT * FROM users WHERE name <> "linzy" AND age > 20;
// Struct db.Find(&users, User{Age: 20}) // SELECT * FROM users WHERE age = 20;
// Map db.Find(&users, map[string]interface{}{"age": 20}) // SELECT * FROM users WHERE age = 20;
4）Not 条件
Not在sql语句中是一个逻辑运算符，取反的用处，真为假，假为真，语句用法跟 Where 方法一样
1 2 3 4 5 6 7 8 9 10 11 12 13 14
db.Not("name = ?", "linzy").First(&user) // SELECT * FROM users WHERE NOT name = "linzy" ORDER BY id LIMIT 1;
// Not In db.Not(map[string]interface{}{"name": []string{"linzy", "linzy 2"}}).Find(&users) // SELECT * FROM users WHERE name NOT IN ("linzy", "linzy 2");
// Struct db.Not(User{Name: "linzy", Age: 18}).First(&user) // SELECT * FROM users WHERE name <> "linzy" AND age <> 18 ORDER BY id LIMIT 1;
// 不在主键切片中的记录 db.Not([]int64{1, 2, 3}).First(&user) // SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
5）Or条件
在 Where 方法和 内联条件 存在多个条件的时候都是用AND联系，表示条件都必须满足的数据，那我们如果只需要满足其中一种条件呢，那就需要 Or条件 了，语句用法跟 Where 方法一样
1 2 3 4 5 6 7 8 9 10
db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users) // SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';
// Struct db.Where("name = 'linzy'").Or(User{Name: "linzy 2", Age: 18}).Find(&users) // SELECT * FROM users WHERE name = 'linzy' OR (name = 'linzy 2' AND age = 18);
// Map db.Where("name = 'linzy'").Or(map[string]interface{}{"name": "linzy 2", "age": 18}).Find(&users) // SELECT * FROM users WHERE name = 'linzy' OR (name = 'linzy 2' AND age = 18);
5、选择特定字段
1）Select
选择您想从数据库中检索的字段，默认情况下会选择全部字段
1 2 3 4 5 6 7 8
db.Select("name", "age").Find(&users) // SELECT name, age FROM users;
db.Select([]string{"name", "age"}).Find(&users) // SELECT name, age FROM users;
db.Table("users").Select("COALESCE(age,?)", 42).Rows() // SELECT COALESCE(age,'42') FROM users;
2）结构体智能选择字段
GORM 允许通过 Select 方法选择特定的字段，如果在应用程序中经常使用Select获取特定的字段，你也可以定义一个较小的结构体，以实现调用 API 时自动选择特定的字段
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
type User struct { ID uint Name string Age int Gender string // 假设后面还有几百个字段... }
db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result) // SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name`
db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result) // SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows() for rows.Next() { ... }
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows() for rows.Next() { ... }
type Result struct { Date time.Time Total int64 } db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)
9、Distinct去重
从模型中选择不相同的值，简而言之，把选定的字段里重复数据去掉，只留下一条
Distinct 也可以配合 Pluck, Count 使用
1
db.Distinct("name", "age").Order("name, age desc").Find(&results)
funcmain() { //左连接 db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{}) // SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id
rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows() for rows.Next() { ... }
db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)
// 带参数的多表连接 db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "linzy@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user) }
11、Joins 预加载
您可以使用 Joins 实现单条 SQL 预加载关联记录，例如：
1 2
db.Joins("Company").Find(&users) // SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;
12、Scan
Scan 结果存储于结构体 struct，用法与 Find 类似
1 2 3 4 5 6 7 8 9 10 11 12
type Result struct { Name string Age int }
funcmain() { var result Result db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)
// 原生 SQL db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result) }
当使用 Update 更新单个列时，你需要指定条件，否则会返回 ErrMissingWhereClause 错误。当使用了 Model 方法，且该对象主键有值，该值会被用于构建条件，例如：
1 2 3 4 5 6 7 8 9 10 11
// 条件更新 db.Model(&User{}).Where("active = ?", true).Update("name", "linzy") // UPDATE users SET name='linzy', updated_at='2013-11-17 21:34:10' WHERE active=true;
// User 的 ID 是 `111` db.Model(&user).Update("name", "linzy") // UPDATE users SET name='linzy', updated_at='2013-11-17 21:34:10' WHERE id=111;
// 根据条件和 model 的值进行更新 db.Model(&user).Where("active = ?", true).Update("name", "linzy") // UPDATE users SET name='linzy', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;
func(u *User) BeforeUpdate(tx *gorm.DB) (err error) { if u.Role == "admin" { return errors.New("admin user not allowed to update") } return }
6、批量更新
如果您尚未通过 Model 指定记录的主键，则 GORM 会执行批量更新
1 2 3 4 5 6 7
// 根据 struct 更新 db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "linzy", Age: 18}) // UPDATE users SET name='linzy', age=18 WHERE role = 'admin;
// 根据 map 更新 db.Table("users").Where("id IN ?", []int{10, 11}).Updates(map[string]interface{}{"name": "linzy", "age": 18}) // UPDATE users SET name='linzy', age=18 WHERE id IN (10, 11);
db.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?", 1)) // UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = 3;
db.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1)) // UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = 3 AND quantity > 1;
2）根据子查询进行更新
使用子查询更新表
1 2 3 4 5 6
db.Model(&user).Update("company_name", db.Model(&Company{}).Select("name").Where("companies.id = users.company_id")) // UPDATE "users" SET "company_name" = (SELECT name FROM companies WHERE companies.id = users.company_id);
db.Table("users as u").Where("name = ?", "linzy").Update("company_name", db.Table("companies as c").Select("name").Where("c.id = u.company_id"))
db.Table("users as u").Where("name = ?", "linzy").Updates(map[string]interface{}{}{"company_name": db.Table("companies as c").Select("name").Where("c.id = u.company_id")})
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "linzy")).Find(&user) // SELECT * FROM `users` WHERE name1 = "linzy" OR name2 = "linzy"
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "linzy2"}).First(&result3) // SELECT * FROM `users` WHERE name1 = "linzy2" OR name2 = "linzy2" ORDER BY `users`.`id` LIMIT 1
// 原生 SQL 及命名参数 db.Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name", sql.Named("name", "linzy1"), sql.Named("name2", "linzy2")).Find(&user) // SELECT * FROM users WHERE name1 = "linzy1" OR name2 = "linzy2" OR name3 = "linzy1"
db.Raw("SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2", map[string]interface{}{"name": "linzy", "name2": "linzy2"}).Find(&user) // SELECT * FROM users WHERE (name1 = "linzy" AND name3 = "linzy") AND name2 = "linzy2"
type NamedArgument struct { Name string Name2 string }
db.Raw("SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2", NamedArgument{Name: "linzy", Name2: "linzy2"}).Find(&user) // SELECT * FROM users WHERE (name1 = "linzy" AND name3 = "linzy") AND name2 = "linzy2"
3、DryRun 模式
在不执行的情况下生成 SQL ，可以用于准备或测试生成的 SQL
1 2 3
stmt := db.Session(&Session{DryRun: true}).First(&user, 1).Statement stmt.SQL.String() //=> SELECT * FROM `users` WHERE `id` = $1 ORDER BY `id` stmt.Vars //=> []interface{}{1}