type User struct { Name string`gorm:"<-:create"`// allow read and create Name string`gorm:"<-:update"`// allow read and update Name string`gorm:"<-"`// allow read and write (create and update) Name string`gorm:"<-:false"`// allow read, disable write permission Name string`gorm:"->"`// readonly (disable write permission unless it configured) Name string`gorm:"->;<-:create"`// allow read and create Name string`gorm:"->:false;<-:create"`// createonly (disabled read from db) Name string`gorm:"-"`// ignore this field when write and read with struct Name string`gorm:"-:all"`// ignore this field when write, read and migrate with struct Name string`gorm:"-:migration"`// ignore this field when migrate with struct }
如果您想要保存 UNIX（毫/纳）秒时间戳，而不是 time，您只需简单地将 time.Time 修改为 int 即可。
1 2 3 4 5 6 7
type User struct { CreatedAt time.Time // 在创建时，如果该字段值为零值，则使用当前时间填充 UpdatedAt int// 在创建时该字段值为零值或者在更新时，使用当前时间戳秒数填充 Updated int64`gorm:"autoUpdateTime:nano"`// 使用时间戳填纳秒数充更新时间 Updated int64`gorm:"autoUpdateTime:milli"`// 使用时间戳毫秒数填充更新时间 Created int64`gorm:"autoCreateTime"`// 使用时间戳秒数填充创建时间 }
嵌入结构体
对于匿名字段，GORM 会将其字段包含在父结构体中。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
type User struct { gorm.Model Name string } // 等效于 type User struct { // Begin of gorm.Model ID uint`gorm:"primaryKey"` CreatedAt time.Time UpdatedAt time.Time DeletedAt gorm.DeletedAt `gorm:"index"` // End of gorm.Model Name string }
对于正常的结构体字段，你也可以通过标签 embedded 将其嵌入。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
type Author struct { Name string Email string }
type Blog struct { ID int Author Author `gorm:"embedded"` Upvotes int32 } // 等效于 type Blog struct { ID int64 Name string Email string Upvotes int32 }
并且，您可以使用标签 embeddedPrefix 来为 db 中的字段名添加前缀。
1 2 3 4 5 6 7 8 9 10 11 12
type Blog struct { ID int Author Author `gorm:"embedded;embeddedPrefix:author_"` Upvotes int32 } // 等效于 type Blog struct { ID int64 AuthorName string AuthorEmail string Upvotes int32 }
字段标签
声明 model 时，tag 是可选的，GORM 支持以下 tag： tag 名大小写不敏感，但建议使用 camelCase 风格。
// 在`id`冲突时，将列更新为默认值 db.Clauses(clause.OnConflict{ Columns: []clause.Column{{Name: "id"}}, DoUpdates: clause.Assignments(map[string]interface{}{"role": "user"}), }).Create(&users) // MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET ***; SQL Server // INSERT INTO `users` *** ON DUPLICATE KEY UPDATE ***; MySQL
// 在`id`冲突时，将列更新为新值 db.Clauses(clause.OnConflict{ Columns: []clause.Column{{Name: "id"}}, DoUpdates: clause.AssignmentColumns([]string{"name", "age"}), }).Create(&users) // MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET "name"="excluded"."name"; SQL Server // INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age"; PostgreSQL // INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age=VALUES(age); MySQL
// 在冲突时，更新除主键以外的所有列到新值。 db.Clauses(clause.OnConflict{ UpdateAll: true, }).Create(&users) // INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age", ...;
// 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
// Get first matched record db.Where("name = ?", "Lare").First(&user) // SELECT * FROM users WHERE name = 'Lare' ORDER BY id LIMIT 1;
// Get all matched records db.Where("name <> ?", "Lare").Find(&users) // SELECT * FROM users WHERE name <> 'Lare';
// IN db.Where("name IN ?", []string{"Lare", "Lare 2"}).Find(&users) // SELECT * FROM users WHERE name IN ('Lare','Lare 2');
// LIKE db.Where("name LIKE ?", "%La%").Find(&users) // SELECT * FROM users WHERE name LIKE '%La%';
// AND db.Where("name = ? AND age >= ?", "Lare", "22").Find(&users) // SELECT * FROM users WHERE name = 'Lare' 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';
Struct & Map 条件
1 2 3 4 5 6 7 8 9 10 11
// Struct db.Where(&User{Name: "Lare", Age: 20}).First(&user) // SELECT * FROM users WHERE name = "Lare" AND age = 20 ORDER BY id LIMIT 1;
// Map db.Where(map[string]interface{}{"name": "Lare", "age": 20}).Find(&users) // SELECT * FROM users WHERE name = "Lare" AND age = 20;
// Slice of primary keys db.Where([]int64{20, 21, 22}).Find(&users) // SELECT * FROM users WHERE id IN (20, 21, 22);
1 2
db.Where(&User{Name: "Lare", Age: 0}).Find(&users) // SELECT * FROM users WHERE name = "Lare";
要在查询条件中包含零值，您可以使用映射，它将包含所有键值作为查询条件，例如：
1 2
db.Where(map[string]interface{}{"Name": "Lare", "Age": 0}).Find(&users) // SELECT * FROM users WHERE name = "Lare" AND age = 0;
db.Where(&User{Name: "Lare"}, "name", "Age").Find(&users) // SELECT * FROM users WHERE name = "Lare" AND age = 0;
db.Where(&User{Name: "Lare"}, "Age").Find(&users) // SELECT * FROM users WHERE age = 0;
内联条件
查询条件可以以与 Where 类似的方式内联到 First 和 Find 等方法中。
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 = ?", "Lare") // SELECT * FROM users WHERE name = "Lare";
db.Find(&users, "name <> ? AND age > ?", "Lare", 20) // SELECT * FROM users WHERE name <> "Lare" 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 条件
构建 NOT 条件，工作方式类似于 Where
1 2 3 4 5 6 7 8 9 10 11 12 13 14
db.Not("name = ?", "Lare").First(&user) // SELECT * FROM users WHERE NOT name = "Lare" ORDER BY id LIMIT 1;
// Not In db.Not(map[string]interface{}{"name": []string{"Lare", "Lare 2"}}).Find(&users) // SELECT * FROM users WHERE name NOT IN ("Lare", "Lare 2");
// Struct db.Not(User{Name: "Lare", Age: 18}).First(&user) // SELECT * FROM users WHERE name <> "Lare" 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 = 'Lare'").Or(User{Name: "Lare 2", Age: 18}).Find(&users) // SELECT * FROM users WHERE name = 'Lare' OR (name = 'Lare 2' AND age = 18);
// Map db.Where("name = 'Lare'").Or(map[string]interface{}{"name": "Lare 2", "age": 18}).Find(&users) // SELECT * FROM users WHERE name = 'Lare' OR (name = 'Lare 2' AND age = 18);
选择特定字段
Select 允许您指定要从数据库中检索的字段。 否则，GORM 将默认选择所有字段。
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;
Order
从数据库中检索记录时指定顺序。
1 2 3 4 5 6 7 8 9 10 11
db.Order("age desc, name").Find(&users) // SELECT * FROM users ORDER BY age desc, name;
// Multiple orders db.Order("age desc").Order("name").Find(&users) // SELECT * FROM users ORDER BY age desc, name;
db.Clauses(clause.OrderBy{ Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true}, }).Find(&User{}) // SELECT * FROM users ORDER BY FIELD(id,1,2,3)
Limit & Offset
Limit 指定要检索的最大记录数 Offset 指定在开始返回记录之前要跳过的记录数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
db.Limit(3).Find(&users) // SELECT * FROM users LIMIT 3;
// Cancel limit condition with -1 db.Limit(10).Find(&users1).Limit(-1).Find(&users2) // SELECT * FROM users LIMIT 10; (users1) // SELECT * FROM users; (users2)
db.Offset(3).Find(&users) // SELECT * FROM users OFFSET 3;
db.Limit(10).Offset(5).Find(&users) // SELECT * FROM users OFFSET 5 LIMIT 10;
// Cancel offset condition with -1 db.Offset(10).Find(&users1).Offset(-1).Find(&users2) // SELECT * FROM users OFFSET 10; (users1) // SELECT * FROM users; (users2)
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` LIMIT 1
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() defer rows.Close() 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() defer rows.Close() 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("s
Distinct
从模型中选择不同的值
1
db.Distinct("name", "age").Order("name, age desc").Find(&results)
Joins
指定连接条件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
type result struct { Name string Email string }
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)
// multiple joins with parameter db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "Lare@example.org").Joins(
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`;
有条件加入
1 2
db.Joins("Company", db.Where(&Company{Alive: true})).Find(&users) // SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS
Joins a Derived Table
您还可以使用联接来联接派生表。
Scan
将结果扫描到结构中的工作方式类似于我们使用 Find 的方式
1 2 3 4 5 6 7 8 9 10
type Result struct { Name string Age int }
var result Result db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)
// Raw SQL db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)
// 条件更新 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;
func(u *User) BeforeUpdate(tx *gorm.DB) (err error) { if u.Role == "admin" { return errors.New("admin user not allowed to update") } return }
批量更新
如果您尚未通过 Model 指定记录的主键，则 GORM 会执行批量更新
1 2 3 4 5 6 7
// 根据 struct 更新 db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18}) // UPDATE users SET name='hello', age=18 WHERE role = 'admin';
// 根据 map 更新 db.Table("users").Where("id IN ?", []int{10, 11}).Updates(map[string]interface{}{"name": "hello", "age": 18}) // UPDATE users SET name='hello', age=18 WHERE id IN (10, 11);
// Add name field db.Migrator().AddColumn(&User{}, "Name") // Drop name field db.Migrator().DropColumn(&User{}, "Name") // Alter name field db.Migrator().AlterColumn(&User{}, "Name") // Check column exists db.Migrator().HasColumn(&User{}, "Name")
type User struct { Name string NewName string }
// Rename column to new name db.Migrator().RenameColumn(&User{}, "Name", "NewName") db.Migrator().RenameColumn(&User{}, "name", "new_name")