t,_:=time.Parse("2006-01-02","1998-07-12") var users = []User{{Name: "彭于晏",Birthday: t}, {Name: "刘清政",Birthday: t}, {Name: "古天乐",Birthday: t}} db.Create(&users) for _, user := range users { fmt.Println(user.ID) } 因为Mysql5.7版本及以上版本的datetime值不能为'0000-00-00 00:00:00', //处理方法： 修改mysql.ini 在[mysqld]添加一项： sql_mode=NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO
使用 CreateInBatches 分批创建时，你可以指定每批的数量，例如：
1 2 3 4 5 6 7 8 9
var users [20]User t, _ := time.Parse("2006-01-02", "1999-07-12") for i, _ := range users { users[i].Name = fmt.Sprintf("刘清政%d号", i) users[i].Birthday = t } fmt.Println(users)
type User struct { ID string`gorm:"default:uuid_generate_v3()"`// db func FirstName string LastName string Age uint8 FullName string`gorm:"->;type:GENERATED ALWAYS AS (concat(firstname,' ',lastname));default:(-);"` }
// 在`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 // 因为user自动和User彪啊关联了 db.First(&user) // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// works because model is specified using `db.Model()` // 使用map，需要指定跟那个表关联 result := map[string]interface{}{} db.Model(&User{}).First(&result) // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// doesn't work-->没指定Model，无效 result := map[string]interface{}{} db.Table("users").First(&result)
// works with Take，使用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 = ?", "jinzhu").First(&user) // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// Get all matched records 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);
NOTE When querying with struct, GORM will only query with non-zero fields, that means if your field’s value is 0, '', false or other zero values, it won’t be used to build query conditions, for example:
1 2 3
// 当使用结构体作为查询条件，gorm只会查询非0字段，如果字段是`0`, `''`, `false` or other zero values，该字段不会被用于构建查询条件 db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu";
To include zero values in the query conditions, you can use a map, which will include all key-values as query conditions, for example:
1 2 3
// 如果想要在查询中包含0的字段，可以使用map来做 db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
When searching with struct, you can specify which particular values from the struct to use in the query conditions by passing in the relevant field name or the dbname to Where(), for example:
1 2 3 4 5 6
//在使用struct进行搜索时，可以通过将相关字段名或数据库名传递给`Where（），来指定在查询条件中使用struct中的哪些特定值` 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;
内联条件
查询条件可以以类似于Where的方式内联到’First’和’Find’等方法中
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
// 如果是非整形主键，根据主键获取记录 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 条件
Build NOT conditions, works similar to Where
构建NOT条件，工作原理同Where
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);
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("sum(amount) > ?", 100).Scan(&results)
2.8 Distinct
Selecting distinct values from the model
从模型中选择不同的值
1
db.Distinct("name", "age").Order("name, age desc").Find(&results)
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 = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)
Joins 预加载
You can use Joins eager loading associations with a single SQL, for example:
可以使用’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`;
Join with conditions
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 `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;
type Order struct { UserId int FinishedAt *time.Time }
query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id") db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results) // SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latest
2.10 Scan
Scanning results into a struct works similarly to the way we use Find–
将结果扫描到结构体中的工作方式，与“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)
三 高级查询(了解)
3.1 智能选择字段
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 // 假设后面还有几百个字段... }
db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders) // SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");
subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users") db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results) // SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")
From 子查询
GORM 允许您在 Table 方法中通过 FROM 子句使用子查询，例如：
1 2 3 4 5 6 7
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18}).Find(&User{}) // SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18
subQuery1 := db.Model(&User{}).Select("name") subQuery2 := db.Model(&Pet{}).Select("name") db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{}) // SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p
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": "jinzhu"}).First(&user) // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu" ORDER BY `users`.`id` LIMIT 1
// 未找到 user，则根据给定的条件以及 Attrs 初始化 user db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1; // user -> User{Name: "non_existing", Age: 20}
// 未找到 user，则根据给定的条件以及 Attrs 初始化 user db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1; // user -> User{Name: "non_existing", Age: 20}
// 找到了 `name` = `jinzhu` 的 user，则忽略 Attrs db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1; // user -> User{ID: 111, Name: "Jinzhu", Age: 18}
// 找到 `name` = `jinzhu` 的记录，依然会更新 Assign 相关的属性 db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 20}).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1; // user -> User{ID: 111, Name: "Jinzhu", Age: 20}
3.9 FirstOrCreate
Get first matched record or create a new one with given conditions (only works with struct, map conditions), RowsAffected returns created/updated record’s count
1 2 3 4 5 6 7 8 9 10
// User not found, create a new record with give conditions result := db.FirstOrCreate(&user, User{Name: "non_existing"}) // INSERT INTO "users" (name) VALUES ("non_existing"); // user -> User{ID: 112, Name: "non_existing"} // result.RowsAffected // => 0
// Found user with `name` = `jinzhu` result := db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user) // user -> User{ID: 111, Name: "jinzhu", "Age": 18} // result.RowsAffected // => 0
// 未找到 user，根据条件和 Assign 属性创建记录 db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user) // SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1; // INSERT INTO "users" (name, age) VALUES ("non_existing", 20); // user -> User{ID: 112, Name: "non_existing", Age: 20}
// 找到了 `name` = `jinzhu` 的 user，则忽略 Attrs db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user) // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1; // user -> User{ID: 111, Name: "jinzhu", Age: 18}
不管是否找到记录，Assign 都会将属性赋值给 struct，并将结果写回数据库
1 2 3 4 5 6 7 8 9 10 11
// 未找到 user，根据条件和 Assign 属性创建记录 db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user) // SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1; // INSERT INTO "users" (name, age) VALUES ("non_existing", 20); // user -> User{ID: 112, Name: "non_existing", Age: 20}
// 找到了 `name` = `jinzhu` 的 user，依然会根据 Assign 更新记录 db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user) // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1; // UPDATE users SET age=20 WHERE id = 111; // user -> User{ID: 111, Name: "jinzhu", Age: 20}
db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{}) // SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`
索引提示允许传递索引提示到数据库，以防查询计划器出现混乱。
1 2 3 4 5 6 7
import"gorm.io/hints"
db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{}) // SELECT * FROM `users` USE INDEX (`idx_user_name`)
db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{}) // SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"
var count int64 db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count) // SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'
db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count) // SELECT count(1) FROM users WHERE name = 'jinzhu'; (count)
db.Table("deleted_users").Count(&count) // SELECT count(1) FROM deleted_users;
// Count with Distinct db.Model(&User{}).Distinct("name").Count(&count) // SELECT COUNT(DISTINCT(`name`)) FROM `users`
db.Table("deleted_users").Select("count(distinct(name))").Count(&count) // SELECT count(distinct(name)) FROM deleted_users
// Count with Group users := []User{ {Name: "name1"}, {Name: "name2"}, {Name: "name3"}, {Name: "name3"}, }
// 表模型变成 type User struct { gorm.Model Name string Age uint8 Birthday time.Time Active bool } // 删除表，重新迁移 db.AutoMigrate(&User{}) // 插入记录 var user =User{Name: "lqz",Age: 18,Birthday: time.Now(),Active: true} db.Create(&user)
当使用 Update 更新单个列时，你需要指定条件，否则会返回 ErrMissingWhereClause 错误，查看 Block Global Updates 获取详情。当使用了 Model 方法，且该对象主键有值，该值会被用于构建条件，例如：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
// 条件更新(如果有多条，全更新) 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 是 `1` var user User db.First(&user) db.Model(&user).Update("name", "lqz_nb") // UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;
// 根据条件和 model 的值进行更新 var user User db.First(&user) db.Model(&user).Where("active = ?", true).Update("name", "lqz_hello") // UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;
// 根据 `struct` 更新属性，只会更新非零值的字段 var user User db.First(&user) db.Model(&user).Updates(User{Name: "hello", Age: 99, Active: false}) // UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;
// 根据 `map` 更新属性 var user User db.First(&user) db.Model(&user).Updates(map[string]interface{}{"name": "lqz_01", "age": 88, "active": false}) // UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;
// 使用 Map 进行 Select // User's ID is `111`: var user User db.First(&user) // 只更新name db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "lqz_002", "age": 66, "active": false}) // UPDATE users SET name='hello' WHERE id=111;
var user User db.First(&user) // 除了name都更新 db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": true}) } // UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;
// 使用 Struct 进行 Select（会 select 零值的字段），零值字段也会更新 // 注意 user的id不能为0 var user User db.First(&user) 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 所有字段（查询包括零值字段的所有字段） var user User db.First(&user) fmt.Println(user) db.Model(&user).Select("*").Updates(User{Name: "lqz_004", Birthday:time.Now(), Age: 33,Active: false, Model:gorm.Model{ID:3,CreatedAt: time.Now(),UpdatedAt: time.Now()}})
func(u *User) BeforeUpdate(tx *gorm.DB) (err error) { if u.Role == "admin" { return errors.New("admin user not allowed to update") } return } // 测试 func(u *User) BeforeUpdate(tx *gorm.DB) (err error) { fmt.Println("修改了") return }
4.6 批量更新
如果您尚未通过 Model 指定记录的主键，则 GORM 会执行批量更新
1 2 3 4 5 6 7 8 9
// 根据 struct 更新--->批量更新不能用Hook db.Model(User{}).Where("active = ?", true).Updates(User{Name: "hello", Age: 18}) // UPDATE `users` SET `updated_at`='2022-05-05 01:01:53.001',`name`='hello',`age`=18 WHERE active = true AND `users`.`deleted_at` IS NULL
// user 的 ID 是 `1` var user User db.First(&user) db.Model(&user).Update("age", gorm.Expr("age * ? + ?", 2, 100)) // UPDATE `users` SET `age`=age * 2 + 100,`updated_at`='2022-05-05 01:11:16.242' WHERE `users`.`deleted_at` IS NULL AND `id` = 1
var user User db.First(&user) db.Model(&user).Updates(map[string]interface{}{"age": gorm.Expr("age - ? + ?", 2, 100)}) // UPDATE `users` SET `age`=age - 2 + 100,`updated_at`='2022-05-05 01:13:13.302' WHERE `users`.`deleted_at` IS NULL AND `id` = 1
db.First(&user) db.Model(&user).UpdateColumn("age", gorm.Expr("age - ?", 10)) // UPDATE `users` SET `age`=age - 10 WHERE `users`.`deleted_at` IS NULL AND `id` = 1
var user User db.First(&user) db.Model(&user).Where("age > 100").UpdateColumn("age", gorm.Expr("age - ?", 100)) // UPDATE `users` SET `age`=age - 100 WHERE age > 100 AND `users`.`deleted_at` IS NULL AND `id` = 1
db.Model(&User{ID: 1}).Updates(User{ Name: "jinzhu", Location: Location{X: 100, Y: 100}, }) // UPDATE `user_with_points` SET `name`="jinzhu",`location`=ST_PointFromText("POINT(100 100)") WHERE `id` = 1
根据子查询进行更新
使用子查询更新表
1 2 3 4 5 6 7 8 9 10
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 = ?", "jinzhu").Update("company_name", db.Table("companies as c").Select("name").Where("c.id = u.company_id")) //UPDATE users as u SET `company_name`=(SELECT name FROM companies as c WHERE c.id = u.company_id) WHERE name = 'jinzhu'
db.Table("users as u").Where("name = ?", "jinzhu").Updates(map[string]interface{}{"company_name": db.Table("companies as c").Select("name").Where("c.id = u.company_id")}) //UPDATE users as u SET `company_name`=(SELECT name FROM companies as c WHERE c.id = u.company_id) WHERE name = 'jinzhu'
// user 的 ID 是 `1`--->软删除 var user User db.First(&user) db.Delete(&user)
// UPDATE `users` SET `deleted_at`='2022-05-05 02:05:16.7' WHERE `users`.`id` = 1 AND `users`.`deleted_at` IS NULL
// 带额外条件的删除 var user User db.First(&user) db.Where("name = ?", "hello").Delete(&user)
//db.Where("name = ?", "hello").Delete(&User{}) // UPDATE `users` SET `deleted_at`='2022-05-05 02:08:30.857' WHERE name = 'hello' AND `users`.`deleted_at` IS NULL
// 测试 var user []User3 db.Where("name = @name OR age = @age", sql.Named("name", "lqz"),sql.Named("age", "12")).Find(&user) fmt.Println(user) var users []User3 db.Where("name = @name OR age = @age", sql.Named("name", "lqz"),sql.Named("age", "12")).Find(&users) fmt.Println(users)
// 其他一样 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"
var users []User3 sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB { return tx.Model(&User3{}).Where("id = ?", 100).Limit(10).Order("age desc").Find(&users) }) fmt.Println(sql) //SELECT * FROM `user3` WHERE id = 100 ORDER BY age desc LIMIT 10
6.5 Row & Rows
获取 *sql.Row 结果
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
// 使用 GORM API 构建 SQL var name string var age int row := db.Table("user3").Where("name = ?", "lqz").Select("name", "age").Row() row.Scan(&name, &age) fmt.Println(name) fmt.Println(age)
// 使用原生 SQL var name string var age int row := db.Raw("select name, age from user3 where name = ?", "lqz").Row() row.Scan(&name, &age) fmt.Println(name) fmt.Println(age)
获取 *sql.Rows 结果
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
// 使用 GORM API 构建 SQL var name string var age int rows, _ := db.Model(&User3{}).Where("name like ?", "%lqz%").Select("name, age").Rows() defer rows.Close() for rows.Next() { rows.Scan(&name, &age) fmt.Printf("name是：%s，age是：%d\n",name,age) }
// 原生 SQL var name string var age int rows, err := db.Raw("select name, age from user3 where name like ?", "%lqz%").Rows() defer rows.Close() for rows.Next() { rows.Scan(&name, &age) fmt.Printf("name是：%s，age是：%d\n",name,age) }
rows, err := db.Model(&User3{}).Where("name like ?", "%lqz%").Select("name, age").Rows() defer rows.Close() var user User3 for rows.Next() { // ScanRows 将一行扫描至 user db.ScanRows(rows, &user) fmt.Println(user) }
6.7 Connection
Run mutliple SQL in same db tcp connection (not in a transaction)
GORM uses SQL builder generates SQL internally, for each operation, GORM creates a *gorm.Statement object, all GORM APIs add/change Clause for the Statement, at last, GORM generated SQL based on those clauses
For example, when querying with First, it adds the following clauses to the Statement
For different databases, Clauses may generate different SQL, for example:
1 2 3 4 5
db.Offset(10).Limit(5).Find(&users) // Generated for SQL Server // SELECT * FROM "users" OFFSET 10 ROW FETCH NEXT 5 ROWS ONLY // Generated for MySQL // SELECT * FROM `users` LIMIT 5 OFFSET 10
Which is supported because GORM allows database driver register Clause Builder to replace the default one, take the Limit as example
子句选项
GORM defined Many Clauses, and some clauses provide advanced options can be used for your application
Although most of them are rarely used, if you find GORM public API can’t match your requirements, may be good to check them out, for example:
1 2
db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user) // INSERT IGNORE INTO users (name,age...) VALUES ("jinzhu",18...);
StatementModifier
GORM provides interface StatementModifier allows you modify statement to match your requirements, take Hints as example
1 2 3 4
import"gorm.io/hints"
db.Clauses(hints.New("hint")).Find(&User{}) // SELECT * /*+ hint */ FROM `users`
