## 數據庫連接
[https://jasperxu.com/gorm-zh/advanced.html#eh](https://jasperxu.com/gorm-zh/advanced.html#eh)
在libs/mysql/mysql.go中連接數據庫
```
package mysql
import (
"fmt"
"project/config"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
var Db *gorm.DB
func init() {
var (
hostname, database, username, password, prefix string
)
hostname = config.MysqlHostName
database = config.MysqlDb
username = config.MysqlUser
password = config.MysqlPassWord
prefix = config.MysqlPrefix
db, err := gorm.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s?parseTime=True&loc=Local",
username, password, hostname, database))
if err != nil {
fmt.Printf("mysql connect error %s", err)
return
}
gorm.DefaultTableNameHandler = func(db *gorm.DB, defaultTableName string) string {
return prefix + defaultTableName
}
db.LogMode(true) // 打印SQL語句
db.SingularTable(true)
db.DB().SetMaxIdleConns(10)
db.DB().SetMaxOpenConns(100)
Db = db
fmt.Printf("mysql connect success.")
}
```
## CURD操作
```
package model
import (
"project/libs/mysql"
)
type User struct {
Id int `json:"id"`
RegIp string `json:"reg_ip"`
Name int `json:"name"`
Age uint8 `json:"age"`
CreateTime int64 `json:"create_time"`
}
```
### 創建
```
user := User{Name: "Jinzhu", RegIp: '127.0.0.1', CreateTime: time.Now().Unix()}
mysql.Db.Create(&user)
```
### 修改
```
user := User{Id: 1}
// 使用主鍵更新單個字段
mysql.Db.Model(&user).Update("name", "hello")
// UPDATE go_user SET name='hello' WHERE id=1;
// 根據條件更新字段
mysql.Db.Model(&user).Where("reg_ip = ?", '127.0.0.1').Update("name", "hello")
// UPDATE go_user SET name='hello' WHERE id=1 AND reg_ip='127.0.0.1';
更新多個字段
mysql.Db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18})
// UPDATE go_user SET name='hello', age=18 WHERE id=1;
// 使用表名更新,需要使用表全名
mysql.Db.Table("go_user").Where("reg_ip = ?", '127.0.0.1').Update("name", "hello")
// UPDATE go_user SET name='hello' WHERE id=1 AND reg_ip='127.0.0.1';
// 使用`struct`更新多個屬性，只會更新這些更改的和非空白字段
mysql.Db.Model(&user).Updates(User{Name: "hello", Age: 18})
//// UPDATE go_user SET name='hello', age=18 WHERE id = 1;
// 警告:當使用struct更新時，FORM將僅更新具有非空值的字段
// 對于下面的更新，什么都不會更新為""，0，false是其類型的空白值
mysql.Db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})
```
### 刪除
```
user := User{Id: 1}
mysql.Db.Delete(&user)
// DELETE from go_user where id=1;
mysql.Db.Delete(User{}, "name = ?", "hello")
// DELETE from go_user where name='hello';
```
### 查詢
```
user := User{}
users := []*User
// 獲取第一條記錄，按主鍵排序
mysql.Db.First(&user)
// SELECT * FROM go_user ORDER BY id LIMIT 1;
// 獲取最后一條記錄，按主鍵排序
mysql.Db.Last(&user)
// SELECT * FROM go_user ORDER BY id DESC LIMIT 1;
// 獲取所有記錄
mysql.Db.Find(&users)
// SELECT * FROM go_user;
// 使用主鍵獲取記錄
mysql.Db.First(&user, 10)
// SELECT * FROM go_user WHERE id = 10;
```
### Where
```
// 獲取第一個匹配記錄
mysql.Db.Where("name = ?", "jinzhu").First(&user)
//// SELECT * FROM user WHERE name = 'jinzhu' limit 1;
// 獲取所有匹配記錄
mysql.Db.Where("name = ?", "jinzhu").Find(&users)
//// SELECT * FROM user WHERE name = 'jinzhu';
mysql.Db.Where("name <> ?", "jinzhu").Find(&users)
// IN
mysql.Db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// LIKE
mysql.Db.Where("name LIKE ?", "%jin%").Find(&users)
// AND
mysql.Db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// Time
mysql.Db.Where("updated_at > ?", lastWeek).Find(&users)
mysql.Db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
// Struct
// 注意：當使用struct查詢時，GORM將只查詢那些具有值的字段
mysql.Db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM user WHERE name = "jinzhu" AND age = 20 LIMIT 1;
// Map
mysql.Db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM user WHERE name = "jinzhu" AND age = 20;
// 主鍵的Slice
mysql.Db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM user WHERE id IN (20, 21, 22);
// 查詢鏈
mysql.Db.Where("name <> ?","hello").Where("age >= ? ",20).Find(&users)
```
#### Or
```
mysql.Db.Where("name = ?", "admin").Or("age = ?", 18).Find(&users)
//// SELECT * FROM user WHERE name = 'admin' OR age = 18;
// Struct
mysql.Db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users)
//// SELECT * FROM user WHERE name = 'jinzhu' OR name = 'jinzhu 2';
// Map
mysql.Db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)
```
### Select
```
mysql.Db.Select("name, age").Find(&users)
// SELECT name, age FROM user;
mysql.Db.Select([]string{"name", "age"}).Find(&users)
// SELECT name, age FROM user;
```
### Order
```go
mysql.Db.Order("age desc, name").Find(&users)
// SELECT * FROM user ORDER BY age desc, name;
// Multiple orders
mysql.Db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM user ORDER BY age desc, name;
```
### Limit
```go
mysql.Db.Limit(3).Find(&users)
// SELECT * FROM user LIMIT 3;
```
### Offset
指定在開始返回記錄之前要跳過的記錄數
```go
mysql.Db.Offset(3).Find(&users)
// SELECT * FROM user OFFSET 3;
```
### Count
```go
var count int
mysql.Db.Model(&User{}).Where("name = ?", "hello").Count(&count)
// SELECT count(*) FROM user WHERE name = 'hello';
```
### Group
```go
type NameCount struct{
Name string `json:"name"`
Total int `json:"total"`
}
var list []*NameCount
mysql.Db.Table("go_user").Select("name, count(name) as total").Group("name").Scan(&list)
```
### Joins
```go
type Result struct{
Name string `json:"name"`
Email string `json:"email"`
}
mysql.Db.Table("go_user as u").Select("u.name, e.email").Joins("left join go_email as e on e.user_id = u.id").Scan(&results)
```
### Pluck
```go
var ages []int64
mysql.Db.Find(&users).Pluck("age", &ages)
var names []string
mysql.Db.Model(&User{}).Pluck("name", &names)
```
### 錯誤處理
```go
if err := mysql.Db.Where("name = ?", "hello").First(&user).Error; err != nil {
// 錯誤處理...
}
```
## 事務
```go
// 開始事務
tx := mysql.Db.Begin()
// 在事務中做一些數據庫操作
tx.Create(...)
// ...
// 發生錯誤時回滾事務
tx.Rollback()
// 或提交事務
tx.Commit()
```
