## 数据库连接
[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()
```
