type server struct { Port string`mapstructure:"port"` AllowOrigins []string`mapstructure:"allow_origins"` } type db struct { UserName string`mapstructure:"user_name"` Password string`mapstructure:"password"` Address string`mapstructure:"address"` DBName string`mapstructure:"db_name"` } type jwt struct { Secret string`mapstructure:"secret"` Expires uint`mapstructure:"expires"` Issuer string`mapstructure:"issuer"` } type email struct { Sender string`mapstructure:"sender"` Pwd string`mapstructure:"pwd"` SmtpAddr string`mapstructure:"smtpaddr"` SmtpPort int`mapstructure:"smtpport"` } type config struct { Dev bool`mapstructure:"dev"` Server server `mapstructure:"server"` DB db `mapstructure:"db"` Jwt jwt `mapstructure:"jwt"` Email email `mapstructure:"email"` }
// works because destination struct is passed in db.First(&user) // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// works because model is specified using `db.Model()` result := map[string]interface{}{} db.Model(&User{}).First(&result) // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// doesn't work result := map[string]interface{}{} db.Table("users").First(&result)
// works with Take result := map[string]interface{}{} db.Table("users").Take(&result)
// no primary key defined, results will be ordered by first field (i.e., `Code`) type Language struct { Code string Name string } db.First(&Language{}) // SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1
再老的版本中也可以写为db.DB.Where("title = ? AND userid = ?", resume_name, userid).First(&user)
内联条件
查询条件也可以被内联到 First 和 Find 之类的方法中,其用法类似于 Where。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
// Get by primary key if it were a non-integer type db.First(&user, "id = ?", "string_primary_key") // SELECT * FROM users WHERE id = 'string_primary_key';
// Plain SQL db.Find(&user, "name = ?", "jinzhu") // SELECT * FROM users WHERE name = "jinzhu";
db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20) // SELECT * FROM users WHERE name <> "jinzhu" 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;
not条件
1 2 3 4 5 6 7 8 9 10 11 12 13 14
db.Not("name = ?", "jinzhu").First(&user) // SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;
// Not In db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users) // SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");
// Struct db.Not(User{Name: "jinzhu", Age: 18}).First(&user) // SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;
// Not In slice of primary keys db.Not([]int64{1,2,3}).First(&user) // SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
or条件
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 = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users) // SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);
// Map db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users) // SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);
4.4.3 高级查询
智能选择字段
GORM 允许通过 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 // 假设后面还有几百个字段... }
当使用 Update 更新单个列时,你需要指定条件,否则会返回 ErrMissingWhereClause 错误,查看 Block Global Updates 获取详情。当使用了 Model 方法,且该对象主键有值,该值会被用于构建条件,例如:
1 2 3 4 5 6 7 8 9 10 11
// 条件更新 db.Model(&User{}).Where("active = ?", true).Update("name", "hello") // UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;
// User 的 ID 是 `111` db.Model(&user).Update("name", "hello") // UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;
// 根据条件和 model 的值进行更新 db.Model(&user).Where("active = ?", true).Update("name", "hello") // UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;
4.4.5 删除
删除一条记录
删除一条记录时,删除对象需要指定主键,否则会触发 批量 Delete,例如:
1 2 3 4 5 6 7
// Email 的 ID 是 `10` db.Delete(&email) // DELETE from emails where id = 10;
// 带额外条件的删除 db.Where("name = ?", "jinzhu").Delete(&email) // DELETE from emails where id = 10 AND name = "jinzhu";
funcGetData()string { now := time.Now() year := utils.ToString(now.Year()) month := now.Format("01") day := utils.ToString(now.Format("02")) return year + "_" + month + "_" + day }