文章目录
一、使用形式二、查询操作1、模型2、简单查询3、Select查询4、where查询（简单sql）5、where查询（struct&map）6、not条件查询7、带内联条件的查询8、Or条件查询9、链式查询10、扩展查询选项11、Order语句12、Limit语句13、Offset语句14、Count语句15、Group & Having16、Join语句17、Pluck语句18、Scan语句19、 Scopes语句20、指定表名21、预加载22、自定义预加载SQL23、嵌套预加载 三、修改操作1、更新全部字段2、更新更改字段3、更新选择的字段4、更新更改字段但不进行Callbacks5、Batch Updates 批量更新6、使用SQL表达式更新7、在Callbacks中更改更新值8、额外更新选项9、删除/软删除10、批量删除11、软删除一、使用形式
1、引入gorm包
import "/jinzhu/gorm"
2、导入数据库驱动
import _ "/go-sql-driver/mysql"为了方便记住导入路径，GORM包装了一些驱动：import _ "/jinzhu/gorm/dialects/mysql"// import _ "/jinzhu/gorm/dialects/postgres"// import _ "/jinzhu/gorm/dialects/sqlite"// import _ "/jinzhu/gorm/dialects/mssql"所以可以用以下两种形式来使用mysql:1、import ("/jinzhu/gorm"_ "/jinzhu/gorm/dialects/mysql")2、import ("/jinzhu/gorm"_ "/go-sql-driver/mysql")
3、连接数据库
1、最简单形式func main() {db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local")defer db.Close()}2、公司形式func initDB(info config.MysqlInfo) *gorm.DB {db, err := gorm.Open("mysql", info.DSN)if err != nil {panic(err)}db.SetLogger(gormlog.NewLogger())db.LogMode(true)db.DB().SetMaxOpenConns(info.MaxOpen)db.DB().SetMaxIdleConns(info.MaxIdle)db.DB().SetConnMaxLifetime(time.Second * 300)if err = db.DB().Ping(); err != nil {panic(err)}return db}
二、查询操作
1、模型
表名默认是模型的复数形式，列名默认是字段名的驼峰形式
type User struct {// 默认表名是`users`ID uint // 列名为 `id`Name string // 列名为 `name`Birthday time.Time // 列名为 `birthday`CreatedAt time.Time // 列名为 `created_at`}
基本模型定义如下，表中需要这些字段的，可以直接引入模型即可
// 基本模型的定义type Model struct {ID uint `gorm:"primary_key"`CreatedAt time.TimeUpdatedAt time.TimeDeletedAt *time.Time}//引入模型：type User struct {gorm.ModelName string}//默认id为主键type User struct {ID uint // 字段`ID`为默认主键Name string}// 使用tag`primary_key`用来设置主键type Animal struct {AnimalId int64 `gorm:"primary_key"` // 设置AnimalId为主键NamestringAgeint64}
2、简单查询
// 获取第一条记录，按主键排序db.First(&user);select * from users order by id limit 1;// 获取最后一条记录，按主键排序db.Last(&user);select * from users order by id desc limit 1;// 获取所有记录db.Find(&user);select * from users;// 使用主键获取记录db.First(&user,10);select * from users where id =10;
3、Select查询
指定要从数据库检索的字段，默认情况下，将选择所有字段;
//简单selectdb.Select("name","age").Find(&user);select name,age from users;db.Select([]string{"name","age"}).Find(&user);db.Table("users").Select("COALESCE(age,?)", 42).Rows()SELECT COALESCE(age,'42') FROM users;
4、where查询（简单sql）
// 获取第一个匹配记录db.Where("name=?","jinzhu").First(&user);select * from users where name='jinzhu' limit 1// 获取所有匹配记录db.Where("name=?","jinzhu").Find(&user);select * from users where name='jinzhu';//不等于db.Where("name<>?","jinzhu").Find(&user);//indb.Where("name in (?) ",[]string{"jinzhu1","jinzhu2"}).Find(&user)//likedb.Where("name like ?","%jinzhu%").Find(&user);//anddb.Where("name =? and age=?","jinzhu",16).Find(&user);// Timedb.Where("create_at >= ?",lastWeek).Find(&user);db.Where("create_at between ? and ?",lastWeek,today).Find(&user);
5、where查询（struct&map）
注意：当使用struct查询时，GORM将只查询那些具有值的字段
// Structdb.Where(&user{Name:"jinzhu",Age:16}).First(&user);select * from users where name="jinzhu" and age=16 limit 1;// Mapdb.Where(map[string]interface{}{"name":"jinzhu","age":16}).First(&user);// 主键的Slicedb.Where([]int64{20,30,40}).Find(&user)select * from users where id in(20.30.40)
6、not条件查询
// Notdb.Not("name","jinzhu").First(&user);select * from users where name <>'jinzhu' limit 1;// Not Indb.Not("name",[]string{"jinzhu1","jinzhu2"}).Find(&user);select * from users where name not in ("jinzhu1","jinzhu2");//Not In slice of primary keysdb.Not([]int64{20,30,40}).First(&user);select * from users where id not in(30,40,50);// Plain SQLdb.Not("name=?","jinzhu").Find(&user);select * from users where not(name="jinzhu");// Structdb.Not(User{Name:"jinzhu"}).Find(&user);select * from users where name <> "jinzhu";
7、带内联条件的查询
注意：使用主键查询时，应仔细检查所传递的值是否为有效主键，以避免SQL注入
// 按主键获取db.First(&user,10);select * from users where id=10 limit 1;// 简单SQLdb.Find(&user,"name=?","jinzhu");select * from users where name="jinzhu";db.Find(&user,"name=? and age=?","jinzhu",10);select * from users where name="jinzhu" and age=10;// Structdb.Find(&user,User{Age:20});select * from users where age=20;// Mapdb.Find(&user,map[string]interfice{}{"age":20});
8、Or条件查询
//简单sqldb.Where("name=?","jinzhu")Or("age=?",20).Find(&user)select * from users where name="jinzhu" and age=20;// Structdb.Where(User{Name:"jinzhu"})Or(User{Age:20}).Find(&user);// Mapdb.Where(map[string]interface{}{"name":"jinhzu"})Or(map[string]interface{}{"age":20})
9、链式查询
Gorm有一个可链接的API，可以这样使用：
db.Where("name <>?","jinzhu").Where("age=? and role=?",20,"admin").Find(&user);select * from users where name <>"jinzhu" and age=20 and role="admin";
10、扩展查询选项
// 为Select语句添加扩展SQL选项db.Set("gorm:query_option","FOR UPDATE").Firsr(&user,10);select * from users where id=10 for update;
11、Order语句
从数据库检索记录时指定顺序，将重排序设置为true以覆盖定义的条件
db.Order("age desc,name").Find(&user);select * from users order by age desc,name;//Multiple ordersdb.Order("age desc").Order("name").First(&user);// ReOrderdb.Order("age desc").Find(&user1).Order("age",true).Find(&user2);select * from users order by age desc;(user1)select * from users order by age;(user2)
12、Limit语句
db.Limit(10).Find(&user);select * from users limit 10;//-1就是取消限制db.Limit(10).Find(&user1).Limit(-1).Find(&user2);select * from users limit 10;(user1)select * from users;(user2)
13、Offset语句
指定在开始返回记录之前要跳过的记录数
db.Offset(3).Find(&user);select * from users offset 3;//-1表示取消限制db.Offset(10).Find(&user1).Offset(-1).Find(&user2);select * from users offset 10; (user1)select * from users;(user2)
14、Count语句
db.Where("name=? and age=?","jinzhu",20).Find(&user).Count(&count);select * from users where name="jinzhu" and age=20;(users)select count(*) from users where name="jinzhu" and age=20;(count)db.Model(&User{}).Where("name=?","jinzhu").Count(&count);select count(*) from users where name="jinzhu";db.Table("person_table").Count(&count);select count(*) from person_table;
15、Group & Having
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.TimeTotal int64}db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results);
16、Join语句
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 = ?", "jinzhu@").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)
17、Pluck语句
将模型中的单个列作为地图查询，如果要查询多个列，可以使用Scan
var ages []int64db.Find(&users).Pluck("age", &ages)var names []stringdb.Model(&User{}).Pluck("name", &names)db.Table("deleted_users").Pluck("name", &names)// 要返回多个列，做这样：db.Select("name, age").Find(&users)
18、Scan语句
将结果扫描到另一个结构中
type Result struct {Name stringAge int}var result Resultdb.Table("users").Select("name, age").Where("name = ?", 3).Scan(&result)// Raw SQLdb.Raw("SELECT name, age FROM users WHERE name = ?", 3).Scan(&result)
19、 Scopes语句
将当前数据库连接传递到func(*DB) *DB，可以用于动态添加条件
func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {return db.Where("amount > ?", 1000)}func PaidWithCreditCard(db *gorm.DB) *gorm.DB {return db.Where("pay_mode_sign = ?", "C")}func PaidWithCod(db *gorm.DB) *gorm.DB {return db.Where("pay_mode_sign = ?", "C")}func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {return func (db *gorm.DB) *gorm.DB {return db.Scopes(AmountGreaterThan1000).Where("status in (?)", status)}}db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)// 查找所有信用卡订单和金额大于1000db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)// 查找所有COD订单和金额大于1000db.Scopes(OrderStatus([]string{"paid", "shipped"})).Find(&orders)// 查找所有付费，发货订单
20、指定表名
// 使用User结构定义创建`user_person`表db.Table("user_person").CreateTable(&User{});var my_users []Userdb.Table("my_users").Find(&my_users)SELECT * FROM my_users;db.Table("my_users").Where("name=?","jinzhu").Delete();delete from my_users where name="jinzhu";
21、预加载
db.Preload("Orders").Find(&users) SELECT * FROM users; SELECT * FROM orders WHERE user_id IN (1,2,3,4);db.Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users) SELECT * FROM users; SELECT * FROM orders WHERE user_id IN (1,2,3,4) AND state NOT IN ('cancelled');db.Where("state = ?", "active").Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users) SELECT * FROM users WHERE state = 'active'; SELECT * FROM orders WHERE user_id IN (1,2) AND state NOT IN ('cancelled');db.Preload("Orders").Preload("Profile").Preload("Role").Find(&users) SELECT * FROM users; SELECT * FROM orders WHERE user_id IN (1,2,3,4); // has many SELECT * FROM profiles WHERE user_id IN (1,2,3,4); // has one SELECT * FROM roles WHERE id IN (4,5,6); // belongs to
22、自定义预加载SQL
db.Preload("Orders", func(db *gorm.DB) *gorm.DB {return db.Order("orders.amount DESC")}).Find(&users) SELECT * FROM users; SELECT * FROM orders WHERE user_id IN (1,2,3,4) order by orders.amount DESC;
23、嵌套预加载
db.Preload("Orders.OrderItems").Find(&users)db.Preload("Orders", "state = ?", "paid").Preload("Orders.OrderItems").Find(&users)
三、修改操作
1、更新全部字段
Save将包括执行更新SQL时的所有字段，即使它没有更改
db.First(&user);user.Name="jinzhu";user.Age=18;db.Save(&user);UPDATE users SET name='jinzhu', age=18, birthday='-01-01', updated_at = '-11-17 21:34:10' WHERE id=111;
2、更新更改字段
如果只想更新更改的字段，可以使用Update,Updates
// 更新单个属性（如果更改）db.Model(&user).Update("name","jinzhu1");update users set name="jinzhu1", updated_at='-11-17 21:34:10' where id=111;// 使用组合条件更新单个属性db.Model(&user).Where("active",true).Update("name","jinzhu1");update users set name="jinzhu1", updated_at='-11-17 21:34:10' where active=true and id=111;//使用`map`更新多个属性，只会更新这些更改的字段db.Model(&user).Updates(map[string]interface{}{"name":"jinzhu2","age":30});update users set name="jinzhu1", updated_at='-11-17 21:34:10' ,age=30 where id=111;//使用`struct`更新多个属性，只会更新这些更改的和非空白字段db.Model(&user).Updates(User{Name:"jinzhu1",Age:20});update users set name="jinzhu1", updated_at='-11-17 21:34:10' ,age=20 where id=111;// 警告:当使用struct更新时，FORM将仅更新具有非空值的字段// 对于下面的更新，什么都不会更新为""，0，false是其类型的空白值db.Model(&user).Updates(User{Name:"",Age:0,active:false});
3、更新选择的字段
如果您只想在更新时更新或忽略某些字段，可以使用Select,Omit
//更新选择的字段db.Model(&user).Select("name").Updates(map[string]interface{}{"name":"jinzhu","age":10,"acitive":true});update users set name="jinzhu1",updated_at='-11-17 21:34:10'where id=111;//忽略选择字段db.Model(&user).Omit("name").Updates(map[string]interface{}{"name":"jinzhu","age":10,"acitive":true});update users set age=10,acitive=true,updated_at='-11-17 21:34:10'where id=111;
4、更新更改字段但不进行Callbacks
以上更新操作将执行模型的BeforeUpdate,AfterUpdate方法，更新其UpdatedAt时间戳，在更新时保存它的Associations，如果不想调用它们，可以使用UpdateColumn,UpdateColumns
// 更新单个属性，类似于`Update`db.Model(&user).UpdateColumn("name", "hello") UPDATE users SET name='hello' WHERE id = 111;// 更新多个属性，与“更新”类似db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18}) UPDATE users SET name='hello', age=18 WHERE id = 111;
5、Batch Updates 批量更新
Callbacks在批量更新时不会运行
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);// 使用struct更新仅适用于非零值，或使用map[string]interface{}db.Model(User{}).Updates(User{Name: "hello", Age: 18}) UPDATE users SET name='hello', age=18;// 使用`RowsAffected`获取更新记录计数db.Model(User{}).Updates(User{Name: "hello", Age: 18}).RowsAffected
6、使用SQL表达式更新
DB.Model(&product).Update("price", gorm.Expr("price * ? + ?", 2, 100))UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '-11-17 21:34:10' WHERE "id" = '2';DB.Model(&product).Updates(map[string]interface{}{"price": gorm.Expr("price * ? + ?", 2, 100)})UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '-11-17 21:34:10' WHERE "id" = '2';DB.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2';DB.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2' AND quantity > 1;
7、在Callbacks中更改更新值
如果要使用BeforeUpdate,BeforeSave更改回调中的更新值，可以使用scope.SetColumn，例如
func (user *User) BeforeSave(scope *gorm.Scope) (err error) {if pw, err := bcrypt.GenerateFromPassword(user.Password, 0); err == nil {scope.SetColumn("EncryptedPassword", pw)}}
8、额外更新选项
// 为Update语句添加额外的SQL选项db.Model(&user).Set("gorm:update_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Update("name, "hello"); UPDATE users SET name='hello', updated_at = '-11-17 21:34:10' WHERE id=111 OPTION (OPTIMIZE FOR UNKNOWN);
9、删除/软删除
警告删除记录时，需要确保其主要字段具有值，GORM将使用主键删除记录，如果主要字段为空，GORM将删除模型的所有记录
// 删除存在的记录db.Delete(&email);delete from email where id=10;// 为Delete语句添加额外的SQL选项db.Set("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&email);DELETE from emails where id=10 OPTION (OPTIMIZE FOR UNKNOWN);
10、批量删除
删除所有匹配记录
db.Where("name like ?","%jin%").Delete(Email{});delete emails where name like '%jinzhu%';db.Delete(Email{},"name like ?","%jin%");delete emails where name like '%jinzhu%';
11、软删除
如果模型有DeletedAt字段，它将自动获得软删除功能！ 那么在调用Delete时不会从数据库中永久删除，而是只将字段DeletedAt的值设置为当前时间。
db.Delete(&user);UPDATE users SET deleted_at="-10-29 10:23" WHERE id = 111;// 批量删除db.Where("age =?",20).Delete(&user);UPDATE users SET deleted_at="-10-29 10:23" WHERE age = 20;//软删除的记录将在查询时被忽略db.Where("age = 20").Find(&user)SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;// 使用Unscoped查找软删除的记录db.Unscoped().Where("age=?",20).Find(&user);SELECT * FROM users WHERE age = 20 ;// 使用Unscoped永久删除记录db.Unscoped().Delete(&user);DELETE FROM orders WHERE id=10;
备注
