type User struct { ID uint Name string Email *string Age uint8 Birthday *time.Time MemberNumber sql.NullString ActivatedAt sql.NullTime CreatedAt time.Time UpdatedAt time.Time }
自动迁移建表
AutoMigrate 会根据结构体帮我们自动创建一个表
1
DB.AutoMigrate(&User)
AutoMigrate 的逻辑是只新增，不删除，不修改（可以修改大小） 例如 User 结构体中将 Name 改成 Username，对应的表中会多出一个 username 的字段，但是原本的 name 字段不会删除
Gorm 约定
GORM 倾向于约定优于配置 默认情况下
Gorm 使用名为 ID 的字段作为主键
如果没有 TableName 函数，使用结构体的蛇形复数作为表名
字段名的蛇形作为列名
使用 CreatedAt、UpdatedAt 字段追踪创建更新时间
1 2 3 4 5 6 7 8 9
// 结构体的蛇形复数作为表名（UserInfo --> user_infos） // 默认列名是字段名的蛇形小写（CreatedAt --> created_at） type UserInfo struct { ID uint// 默认为主键 Name string Age int CreatedAt time.Time // 创建记录时，如果该字段值为零值，则将该字段的值设为当前时间 UpdatedAt time.Time // 更新记录时，将该字段的值设为当前时间，创建记录时，如果该字段值为零值，则将该字段的值设为当前时间 }
type User struct { ID uint`gorm:"column:user_id"`// 将列名设为 `user_id` UserName string`gorm:"column:name"`// 将列名设为 `name` Age int64`gorm:"column:user_age"`// 将列名设为 `user_age` }
// gorm.Model 的定义 type Model struct { ID uint`gorm:"primaryKey"` CreatedAt time.Time UpdatedAt time.Time DeletedAt gorm.DeletedAt `gorm:"index"` }
你可以将它嵌入到你自己的模型中
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
type User struct { gorm.Model Name string Age int }
// 等效于 type User struct { ID uint`gorm:"primaryKey"` CreatedAt time.Time UpdatedAt time.Time DeletedAt gorm.DeletedAt `gorm:"index"` Name string Age int }
创建记录
模型
1 2 3 4 5 6
type User struct { ID uint Name string Age int Sex string }
// 获取第一条匹配的记录 db.Where("name = ?", "jinzhu").First(&user) // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// 获取所有匹配的记录 db.Where("name <> ?", "jinzhu").Find(&users) // SELECT * FROM users WHERE name <> 'jinzhu';
// IN db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users) // SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');
// LIKE db.Where("name LIKE ?", "%jin%").Find(&users) // SELECT * FROM users WHERE name LIKE '%jin%';
// AND db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users) // SELECT * FROM users WHERE name = 'jinzhu' 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: "jinzhu", Age: 20}).First(&user) // SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;
// Map db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 20;
// Slice of primary keys db.Where([]int64{20, 21, 22}).Find(&users) // SELECT * FROM users WHERE id IN (20, 21, 22);
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users) //// SELECT * FROM users WHERE name = "jinzhu";
要在查询条件中包含零值，可以使用 map，它会将所有键值作为查询条件包含在内，例如：
1 2
db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
指定结构体查询字段
在使用 struct 进行搜索时，指定在查询条件中要使用结构体中的哪些特定值，例如：
1 2 3 4 5
db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
db.Where(&User{Name: "jinzhu"}, "Age").Find(&users) // SELECT * FROM users WHERE age = 0;
内联条件
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);
选择特定字段
Select 允许指定要从数据库中检索的字段，没有 Select GORM 将默认选择所有字段
1 2 3 4 5
db.Select("name", "age").Find(&users) // SELECT name, age FROM users;
db.Select([]string{"name", "age"}).Find(&users) // SELECT name, age FROM users;
排序
1 2 3 4 5 6
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;
Limit & Offset
Limit 指定要检索的最大记录数，Offset 指定在开始返回记录之前要跳过的记录数
1 2 3 4 5 6 7 8 9 10
db.Limit(3).Find(&users) // SELECT * FROM users LIMIT 3;
// -1 表示检索所有记录 db.Limit(10).Find(&users1).Limit(-1).Find(&users2) // SELECT * FROM users LIMIT 10; (users1) // SELECT * FROM users; (users2)
db.Limit(10).Offset(5).Find(&users) // SELECT * FROM users OFFSET 5 LIMIT 10;
db.Save(&User{ID: 1, Name: "jinzhu", Age: 100}) // UPDATE `users` SET `name`="jinzhu",`age`=100,`update_at`="0000-00-00 00:00:00" WHERE `id` = 1
更新单个列
需要通过 Model 函数来传入要更新的模型，主要是用来确定表名
当使用该方法并且其值具有主值时，主键将用于构建条件
1 2 3 4 5 6 7 8 9 10 11 12
// 根据条件更新 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's ID is `111`: db.Model(&user).Update("name", "hello") // UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;
// 根据条件和主键更新 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;
更新多列
如果要更新多个字段的话，可以使用 Updates 函数，该函数需要传入一个结构体或 map
在使用结构体时，不会更新零值，如果要更新的话，需要使用 map 或者更新选定字段
1 2 3 4 5 6 7
// 用结构体更新 db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false}) // UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;
// Select with Map // User's ID is `111`: db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false}) // UPDATE users SET name='hello' WHERE id=111;
db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false}) // UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;
// Select with Struct (select zero value fields) db.Model(&user).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0}) // UPDATE users SET name='new_name', age=0 WHERE id=111;
// Select all fields (select all fields include zero value fields) db.Model(&user).Select("*").Updates(User{Name: "jinzhu", Role: "admin", Age: 0})
// Select all fields but omit Role (select all fields include zero value fields) db.Model(&user).Select("*").Omit("Role").Updates(User{Name: "jinzhu", Role: "admin", Age: 0})
批量更新
如果我们没有指定主键值，GORM 将执行批量更新 Model，更新所有符合条件的记录
1 2 3 4 5 6 7
// Update with struct db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18}) // UPDATE users SET name='hello', age=18 WHERE role = 'admin';
// Update with map db.Model(User{}).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);
