演示实例
数据库初始化
package main
import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
type User struct {
ID int64 `gorm:"primary_key"`
Name string `gorm:"default:'靓仔'"`
Age int64
}
func main(){
// 连接数据库
db , err := gorm.Open("mysql",
"root:root@(127.0.0.1:3306)/db1?charset=utf8&mb4&parseTime=True&loc=Local")
if err != nil{
panic( err )
return
}
defer db.Close()
fmt.Printf("连接数据库成功！\n")
// 创建表(table)
db.AutoMigrate(&User{})
fmt.Printf("创建表成功！\n")
// 添加元素
// 01 - 05 : Charles,Mark,Bill,Vincent,William,
// 06 - 10 : Jseph,James,Henry,Gary,Martin
users := []User{
{Name: "Charles", Age: 18},
{Name: "Mark", Age: 40},
{Name: "Bill", Age: 54},
{Name: "Vincent", Age: 17},
{Name: "William", Age: 59},
{Name: "Jseph", Age: 50},
{Name: "James", Age: 30},
{Name: "Henry", Age: 17},
{Name: "Gary", Age: 34},
{Name: "Martin", Age: 95},
}
for _ , user := range users {
db.Debug().Create(&user)
}
}
查询
内联条件
// 查询
// 内联条件
// 作用与Where查询类似，当内联条件与多个立即执行方法一起使用时, 内联条件不会传递给后面的立即执行方法。
// 根据主键获取记录 (只适用于整形主键)
// SELECT * FROM `users` WHERE (`users`.`id` = 4) ORDER BY `users`.`id` ASC LIMIT 1
var user User
db.Debug().First(&user, 4)
fmt.Printf("%#v\n",user)
// User{ID:4, Name:"Vincent", Age:17}
// 根据主键获取记录, 如果它是一个非整形主键
//db.First(&user, "id = ?", "string_primary_key")
//// SELECT * FROM users WHERE id = 'string_primary_key' LIMIT 1;
// Plain SQL
// SELECT * FROM `users` WHERE (name = 'James')
users = []User{}
db.Debug().Find(&users, "name = ?", "James")
fmt.Printf("%#v\n",users)
// User{ID:7, Name:"James", Age:30}
// SELECT * FROM `users` WHERE (name <> 'Charles' AND age < 20)
users = []User{}
db.Debug().Find(&users, "name <> ? AND age < ?", "Charles", 20)
fmt.Printf("%#v\n",users)
// User{ID:4, Name:"Vincent", Age:17},
// User{ID:8, Name:"Henry", Age:17}
// Struct
// SELECT * FROM `users` WHERE (`users`.`age` = 17)
users = []User{}
db.Debug().Find(&users, User{Age: 17})
fmt.Printf("%#v\n",users)
// User{ID:4, Name:"Vincent", Age:17},
// User{ID:8, Name:"Henry", Age:17}
// Map
// SELECT * FROM `users` WHERE (`users`.`age` = 17)
users = []User{}
db.Debug().Find(&users, map[string]interface{}{"age": 17})
fmt.Printf("%#v\n",users)
// User{ID:4, Name:"Vincent", Age:17},
// User{ID:8, Name:"Henry", Age:17}
FirstOrInit
// FirstOrInit
// 如果 找不到相应的行 则 user 接收一个自定义的Default
// 未找到
var user User
db.Debug().FirstOrInit(&user, User{Name: "non_existing",Age:99})
fmt.Printf("user : %#v\n",user)
// user -> User{ID:0, Name:"non_existing", Age:99}
// 找到
// SELECT * FROM `users` WHERE (`users`.`name` = 'Vincent') ORDER BY `users`.`id` ASC LIMIT 1
user = User{}
db.Debug().Where(User{Name: "Vincent"}).FirstOrInit(&user)
fmt.Printf("user : %#v\n",user)
// user -> User{ID:4, Name:"Vincent", Age:17}
// SELECT * FROM `users` WHERE (`users`.`Name` = 'Jseph') ORDER BY `users`.`id` ASC LIMIT 1
user = User{}
db.Debug().FirstOrInit(&user, map[string]interface{}{"Name": "Jseph"})
fmt.Printf("user : %#v\n",user)
// user -> User{ID:6, Name:"Jseph", Age:50}
// Attrs
// 未找到 : 如果记录未找到，将使用参数初始化 struct.
// SELECT * FROM `users` WHERE (`users`.`name` = 'non_existing') ORDER BY `users`.`id` ASC LIMIT 1
user = User{}
db.Debug().Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
fmt.Printf("user : %#v\n",user)
// user -> User{ID:0, Name:"non_existing", Age:20}
user = User{}
db.Debug().Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
fmt.Printf("user : %#v\n",user)
// SELECT * FROM USERS WHERE name = 'non_existing';
// user -> User{ID:0, Name:"non_existing", Age:20}
// 找到
// SELECT * FROM `users` WHERE (`users`.`name` = 'Bill') ORDER BY `users`.`id` ASC LIMIT 1
user = User{}
db.Debug().Where(User{Name: "Bill"}).Attrs(User{Age: 30}).FirstOrInit(&user)
fmt.Printf("user : %#v\n",user)
//// user -> User{ID:3, Name:"Bill", Age:54}
// Assign
// 不管记录是否找到，都将参数赋值给 struct.
// 未找到
//SELECT * FROM `users` WHERE (`users`.`name` = 'non_existing') ORDER BY `users`.`id` ASC LIMIT 1
user = User{}
db.Debug().Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
fmt.Printf("user : %#v\n",user)
// user -> User{ID:0, Name:"non_existing", Age:20}
// 找到
user = User{}
db.Debug().Where(User{Name: "Bill"}).Assign(User{Age: 30}).FirstOrInit(&user)
fmt.Printf("user : %#v\n",user)
// user -> User{ID:3, Name:"Bill", Age:54(origin) -> 30(Assign) }
FirstOrCreate
// FirstOrCreate
// 获取匹配的第一条记录, 否则根据给定的条件创建一个新的记录 (仅支持 struct 和 map 条件)
// 未找到
var user User
// SELECT * FROM `users` WHERE (`users`.`name` = 'non_existing1') ORDER BY `users`.`id` ASC LIMIT 1
// INSERT INTO `users` (`name`) VALUES ('non_existing1')
db.Debug().FirstOrCreate(&user, User{Name: "non_existing1"})
fmt.Printf("user : %#v\n",user)
// user -> User{ID:11, Name:"non_existing1", Age:22(Default)}
// 找到
// SELECT * FROM `users` WHERE (`users`.`name` = 'Mark') ORDER BY `users`.`id` ASC LIMIT 1
user = User{}
db.Debug().Where(User{Name: "Mark"}).FirstOrCreate(&user)
fmt.Printf("user : %#v\n",user)
//// user -> User{ID:2, Name:"Mark", Age:40}
// Attrs
// 未找到 : 如果记录未找到，将使用参数初始化 struct.
// SELECT * FROM `users` WHERE (`users`.`name` = 'non_existing2') ORDER BY `users`.`id` ASC LIMIT 1
// INSERT INTO `users` (`name`,`age`) VALUES ('non_existing2',20)
user = User{}
db.Debug().Where(User{Name: "non_existing2"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
fmt.Printf("user : %#v\n",user)
// user -> User{ID:12, Name:"non_existing2", Age:20}
// 找到
// SELECT * FROM `users` WHERE (`users`.`name` = 'Bill') ORDER BY `users`.`id` ASC LIMIT 1
user = User{}
db.Debug().Where(User{Name: "Bill"}).Attrs(User{Age: 30}).FirstOrCreate(&user)
fmt.Printf("user : %#v\n",user)
//// user -> User{ID:3, Name:"Bill", Age:54}
// Assign
// 不管记录是否找到，都将参数赋值给 struct.
// 未找到
// SELECT * FROM `users` WHERE (`users`.`name` = 'non_existing3') ORDER BY `users`.`id` ASC LIMIT 1
// INSERT INTO `users` (`name`,`age`) VALUES ('non_existing3',20)
user = User{}
db.Debug().Where(User{Name: "non_existing3"}).Assign(User{Age: 20}).FirstOrCreate(&user)
fmt.Printf("user : %#v\n",user)
// user -> User{ID:13, Name:"non_existing3", Age:20}
// 找到
// SELECT * FROM `users` WHERE (`users`.`name` = 'Bill') ORDER BY `users`.`id` ASC LIMIT 1
// UPDATE `users` SET `age` = 30 WHERE `users`.`id` = 3 AND ((`users`.`name` = 'Bill'))
user = User{}
db.Debug().Where(User{Name: "Bill"}).Assign(User{Age: 30}).FirstOrCreate(&user)
fmt.Printf("user : %#v\n",user)
// user -> User{ID:3, Name:"Bill", Age:54(origin) -> 30(Assign) }
/*
执行完table`users`显示如下:
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| .. | ............. | .. |
| 11 | non_existing1 | 22 |
| 12 | non_existing2 | 20 |
| 13 | non_existing3 | 20 |
+----+---------------+------+
*/
