连接数据库
为了连接数据库，你首先要导入数据库驱动程序。例如：
import _ "github.com/go-sql-driver/mysql" 复制代码
GORM 已经包含了一些驱动程序，为了方便的去记住它们的导入路径，你可以像下面这样导入 mysql 驱动程序
import _ "github.com/jinzhu/gorm/dialects/mysql" // import _ "github.com/jinzhu/gorm/dialects/postgres" // import _ "github.com/jinzhu/gorm/dialects/sqlite" // import _ "github.com/jinzhu/gorm/dialects/mssql" 复制代码
支持的数据库
MySQL
time.TimeparseTimeimport ( "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/mysql" ) func main() { db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local") defer db.Close() } 复制代码PostgreSQL
import ( "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/postgres" ) func main() { db, err := gorm.Open("postgres", "host=myhost port=myport user=gorm dbname=gorm password=mypassword") defer db.Close() } 复制代码Sqlite3
import ( "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/sqlite" ) func main() { db, err := gorm.Open("sqlite3", "/tmp/gorm.db") defer db.Close() } 复制代码SQL Server
Get started with SQL Server，它可以通过 Docker 运行在你的 Mac， Linux 上。
import ( "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/mssql" ) func main() { db, err := gorm.Open("mssql", "sqlserver://username:password@localhost:1433?database=dbname") defer db.Close() } 复制代码CRUD 接口
创建记录
user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()} db.NewRecord(user) // => 返回 `true` ，因为主键为空 db.Create(&user) db.NewRecord(user) // => 在 `user` 之后创建返回 `false` 复制代码默认值
你可以通过标签定义字段的默认值，例如：
type Animal struct { ID int64 Name string `gorm:"default:'galeone'"` Age int64 } 复制代码然后 SQL 会排除那些没有值或者有 零值 的字段，在记录插入数据库之后，gorm将从数据库中加载这些字段的值。
var animal = Animal{Age: 99, Name: ""} db.Create(&animal) // INSERT INTO animals("age") values('99'); // SELECT name from animals WHERE ID=111; // 返回的主键是 111 // animal.Name => 'galeone' 复制代码0''false// Use pointer value type User struct { gorm.Model Name string Age *int `gorm:"default:18"` } // Use scanner/valuer type User struct { gorm.Model Name string Age sql.NullInt64 `gorm:"default:18"` } 复制代码在钩子中设置字段值
BeforeCreatescope.SetColumnfunc (user *User) BeforeCreate(scope *gorm.Scope) error { scope.SetColumn("ID", uuid.New()) return nil } 复制代码创建额外选项
// 为插入 SQL 语句添加额外选项 db.Set("gorm:insert_option", "ON CONFLICT").Create(&product) // INSERT INTO products (name, code) VALUES ("name", "code") ON CONFLICT; 复制代码查询
// 获取第一条记录，按主键排序 db.First(&user) //// SELECT * FROM users ORDER BY id LIMIT 1; // 获取一条记录，不指定排序 db.Take(&user) //// SELECT * FROM users LIMIT 1; // 获取最后一条记录，按主键排序 db.Last(&user) //// SELECT * FROM users ORDER BY id DESC LIMIT 1; // 获取所有的记录 db.Find(&users) //// SELECT * FROM users; // 通过主键进行查询 (仅适用于主键是数字类型) db.First(&user, 10) //// SELECT * FROM users WHERE id = 10; 复制代码
Where
原生 SQL
// 获取第一条匹配的记录 db.Where("name = ?", "jinzhu").First(&user) //// SELECT * FROM users WHERE name = 'jinzhu' limit 1; // 获取所有匹配的记录 db.Where("name = ?", "jinzhu").Find(&users) //// SELECT * FROM users WHERE name = 'jinzhu'; // <> db.Where("name <> ?", "jinzhu").Find(&users) // IN db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users) // LIKE db.Where("name LIKE ?", "%jin%").Find(&users) // AND db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users) // Time db.Where("updated_at > ?", lastWeek).Find(&users) // BETWEEN db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users) 复制代码Struct & Map
// Struct db.Where(&User{Name: "jinzhu", Age: 20}).First(&user) //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1; // Map db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users) //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20; // 多主键 slice 查询 db.Where([]int64{20, 21, 22}).Find(&users) //// SELECT * FROM users WHERE id IN (20, 21, 22); 复制代码0''falsedb.Where(&User{Name: "jinzhu", Age: 0}).Find(&users) //// SELECT * FROM users WHERE name = "jinzhu"; 复制代码你可以考虑适用指针类型或者 scanner/valuer 来避免这种情况。
// 使用指针类型 type User struct { gorm.Model Name string Age *int } // 使用 scanner/valuer type User struct { gorm.Model Name string Age sql.NullInt64 } 复制代码Not
Wheredb.Not("name", "jinzhu").First(&user) //// SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1; // 不包含 db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users) //// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2"); //不在主键 slice 中 db.Not([]int64{1,2,3}).First(&user) //// SELECT * FROM users WHERE id NOT IN (1,2,3); db.Not([]int64{}).First(&user) //// SELECT * FROM users; // 原生 SQL db.Not("name = ?", "jinzhu").First(&user) //// SELECT * FROM users WHERE NOT(name = "jinzhu"); // Struct db.Not(User{Name: "jinzhu"}).First(&user) //// SELECT * FROM users WHERE name <> "jinzhu"; 复制代码Or
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"}).Find(&users) //// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; // Map db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users) //// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; 复制代码行内条件查询
Where需要注意的是，当使用链式调用传入行内条件查询时，这些查询不会被传参给后续的中间方法。
// 通过主键进行查询 (仅适用于主键是数字类型) db.First(&user, 23) //// SELECT * FROM users WHERE id = 23 LIMIT 1; // 非数字类型的主键查询 db.First(&user, "id = ?", "string_primary_key") //// SELECT * FROM users WHERE id = 'string_primary_key' LIMIT 1; // 原生 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; 复制代码额外的查询选项
// 为查询 SQL 添加额外的选项 db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10) //// SELECT * FROM users WHERE id = 10 FOR UPDATE; 复制代码FirstOrInit
获取第一条匹配的记录，或者通过给定的条件下初始一条新的记录（仅适用与于 struct 和 map 条件）。
// 未查询到 db.FirstOrInit(&user, User{Name: "non_existing"}) //// user -> User{Name: "non_existing"} // 查询到 db.Where(User{Name: "Jinzhu"}).FirstOrInit(&user) //// user -> User{Id: 111, Name: "Jinzhu", Age: 20} db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"}) //// user -> User{Id: 111, Name: "Jinzhu", Age: 20} 复制代码Attrs
如果未找到记录，则使用参数初始化 struct
// 未查询到 db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = 'non_existing'; //// user -> User{Name: "non_existing", Age: 20} db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = 'non_existing'; //// user -> User{Name: "non_existing", Age: 20} // 查询到 db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = jinzhu'; //// user -> User{Id: 111, Name: "Jinzhu", Age: 20} 复制代码Assign
无论是否查询到数据，都将参数赋值给 struct
// 未查询到 db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user) //// user -> User{Name: "non_existing", Age: 20} // 查询到 db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = jinzhu'; //// user -> User{Id: 111, Name: "Jinzhu", Age: 30} 复制代码FirstOrCreate
获取第一条匹配的记录，或者通过给定的条件创建一条记录 （仅适用与于 struct 和 map 条件）。
// 未查询到 db.FirstOrCreate(&user, User{Name: "non_existing"}) //// INSERT INTO "users" (name) VALUES ("non_existing"); //// user -> User{Id: 112, Name: "non_existing"} // 查询到 db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user) //// user -> User{Id: 111, Name: "Jinzhu"} 复制代码Attrs
如果未查询到记录，通过给定的参数赋值给 struct ，然后使用这些值添加一条记录。
// 未查询到 db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user) //// SELECT * FROM users WHERE name = 'non_existing'; //// INSERT INTO "users" (name, age) VALUES ("non_existing", 20); //// user -> User{Id: 112, Name: "non_existing", Age: 20} // 查询到 db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user) //// SELECT * FROM users WHERE name = 'jinzhu'; //// user -> User{Id: 111, Name: "jinzhu", Age: 20} 复制代码Assign
无论是否查询到，都将其分配给记录，并保存到数据库中。
// 未查询到 db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user) //// SELECT * FROM users WHERE name = 'non_existing'; //// INSERT INTO "users" (name, age) VALUES ("non_existing", 20); //// user -> User{Id: 112, Name: "non_existing", Age: 20} // 查询到 db.Where(User{Name: "jinzhu"}).Assign(User{Age: 30}).FirstOrCreate(&user) //// SELECT * FROM users WHERE name = 'jinzhu'; //// UPDATE users SET age=30 WHERE id = 111; //// user -> User{Id: 111, Name: "jinzhu", Age: 30} 复制代码高级查询
子查询
*gorm.exprdb.Where("amount > ?", DB.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").QueryExpr()).Find(&orders) // SELECT * FROM "orders" WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders" WHERE (state = 'paid'))); 复制代码查询
指定要从数据库检索的字段，默认情况下，将选择所有字段。
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
truedb.Order("age desc, name").Find(&users) //// SELECT * FROM users ORDER BY age desc, name; // 多个排序条件 db.Order("age desc").Order("name").Find(&users) //// SELECT * FROM users ORDER BY age desc, name; // 重新排序 db.Order("age desc").Find(&users1).Order("age", true).Find(&users2) //// SELECT * FROM users ORDER BY age desc; (users1) //// SELECT * FROM users ORDER BY age; (users2) 复制代码Limit
指定要查询的最大记录数
db.Limit(3).Find(&users) //// SELECT * FROM users LIMIT 3; // 用 -1 取消 LIMIT 限制条件 db.Limit(10).Find(&users1).Limit(-1).Find(&users2) //// SELECT * FROM users LIMIT 10; (users1) //// SELECT * FROM users; (users2) 复制代码
Offset
指定在开始返回记录之前要跳过的记录数。
db.Offset(3).Find(&users) //// SELECT * FROM users OFFSET 3; // 用 -1 取消 OFFSET 限制条件 db.Offset(10).Find(&users1).Offset(-1).Find(&users2) //// SELECT * FROM users OFFSET 10; (users1) //// SELECT * FROM users; (users2) 复制代码
Count
获取模型记录数
db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count) //// SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users) //// SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count) db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count) //// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count) db.Table("deleted_users").Count(&count) //// SELECT count(*) FROM deleted_users; 复制代码CountSELECTGroup 和 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.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) 复制代码Joins
指定关联条件
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@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user) 复制代码Pluck
Scanvar ages []int64 db.Find(&users).Pluck("age", &ages) var names []string db.Model(&User{}).Pluck("name", &names) db.Table("deleted_users").Pluck("name", &names) // Requesting more than one column? Do it like this: db.Select("name, age").Find(&users) 复制代码Scan
将 Scan 查询结果放入另一个结构体中。
type Result struct { Name string Age int } var result Result db.Table("users").Select("name, age").Where("name = ?", 3).Scan(&result) // Raw SQL db.Raw("SELECT name, age FROM users WHERE name = ?", 3).Scan(&result) 复制代码更新
更新所有字段
Savedb.First(&user) user.Name = "jinzhu 2" user.Age = 100 db.Save(&user) //// UPDATE users SET name='jinzhu 2', age=100, birthday='2016-01-01', updated_at = '2013-11-17 21:34:10' WHERE id=111; 复制代码
更新已更改的字段
UpdateUpdates// 如果单个属性被更改了，更新它 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; // 使用 `map` 更新多个属性，只会更新那些被更改了的字段 db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false}) //// UPDATE users SET name='hello', age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111; // 使用 `struct` 更新多个属性，只会更新那些被修改了的和非空的字段 db.Model(&user).Updates(User{Name: "hello", Age: 18}) //// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111; // 警告： 当使用结构体更新的时候, GORM 只会更新那些非空的字段 // 例如下面的更新，没有东西会被更新，因为像 "", 0, false 是这些字段类型的空值 db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false}) 复制代码更新选中的字段
SelectOmitdb.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false}) //// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111; db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false}) //// UPDATE users SET age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111; 复制代码更新列钩子方法
BeforeUpdateAfterUpdateUpdatedAt关联UpdateColumnUpdateColumns// Update single attribute, similar with `Update` db.Model(&user).UpdateColumn("name", "hello") //// UPDATE users SET name='hello' WHERE id = 111; // Update multiple attributes, similar with `Updates` db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18}) //// UPDATE users SET name='hello', age=18 WHERE id = 111; 复制代码批量更新
批量更新时，钩子函数不会执行
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); // 使用结构体更新将只适用于非零值，或者使用 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 复制代码带有表达式的 SQL 更新
DB.Model(&product).Update("price", gorm.Expr("price * ? + ?", 2, 100)) //// UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '2013-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" = '2013-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; 复制代码在钩子函数中更新值
BeforeUpdateBeforeSavescope.SetColumnfunc (user *User) BeforeSave(scope *gorm.Scope) (err error) { if pw, err := bcrypt.GenerateFromPassword(user.Password, 0); err == nil { scope.SetColumn("EncryptedPassword", pw) } } 复制代码额外的更新选项
// 在更新 SQL 语句中添加额外的 SQL 选项 db.Model(&user).Set("gorm:update_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Update("name", "hello") //// UPDATE users SET name='hello', updated_at = '2013-11-17 21:34:10' WHERE id=111 OPTION (OPTIMIZE FOR UNKNOWN); 复制代码删除
删除记录
警告：当删除一条记录的时候，你需要确定这条记录的主键有值，GORM会使用主键来删除这条记录。如果主键字段为空，GORM会删除模型中所有的记录。
// 删除一条存在的记录 db.Delete(&email) //// DELETE from emails where id=10; // 为删除 SQL 语句添加额外选项 db.Set("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&email) //// DELETE from emails where id=10 OPTION (OPTIMIZE FOR UNKNOWN); 复制代码批量删除
删除所有匹配的记录
db.Where("email LIKE ?", "%jinzhu%").Delete(Email{}) //// DELETE from emails where email LIKE "%jinzhu%"; db.Delete(Email{}, "email LIKE ?", "%jinzhu%") //// DELETE from emails where email LIKE "%jinzhu%"; 复制代码软删除
DeletedAtDeletedAtdb.Delete(&user) //// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111; // 批量删除 db.Where("age = ?", 20).Delete(&User{}) //// UPDATE users SET deleted_at="2013-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(&users) //// SELECT * FROM users WHERE age = 20; // 使用 Unscoped 方法永久删除记录 db.Unscoped().Delete(&order) //// DELETE FROM orders WHERE id=10; 复制代码事务
createupdatedeletecreateupdatedeleteTransaction要在事务中执行一组操作，正常的流程如下所示。
// 开启事务 tx := db.Begin() // 在事务中执行一些数据库操作 （从这里开始使用 'tx'，而不是 'db'） tx.Create(...) // ... // 发生错误回滚事务 tx.Rollback() // 或者提交这个事务 tx.Commit() 复制代码
具体例子
func CreateAnimals(db *gorm.DB) err { // 注意在事务中要使用 tx 作为数据库句柄 tx := db.Begin() defer func() { if r := recover(); r != nil { tx.Rollback() } }() if tx.Error != nil { return err } if err := tx.Create(&Animal{Name: "Giraffe"}).Error; err != nil { tx.Rollback() return err } if err := tx.Create(&Animal{Name: "Lion"}).Error; err != nil { tx.Rollback() return err } return tx.Commit().Error } 复制代码