设计模式之Database/SQL 与GORM实践

这是我参与「第三届青训营 -后端场」笔记创作活动的的第5篇笔记

字节跳动青训营系列文章

「设计模式之 Database/SQL 与 GORM 实践」第三届字节跳动青训营

同时这也是课表的第5天课程

PC端阅读效果更佳，点击文末：阅读原文即可。

讲师介绍

Jinzhu / 张金柱字节跳动@基础架构-语言团队 Github：github.com/jinzhu

GORM作者 / Golang Contributor

01.理解database/sql

基本用法，设计原理，基础概念

1.1基本用法-Quick Start

package main

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"

)

func main() {
	db, err := sql.Open ("mysqL", "user:password@tcp(127.0.0.1:3306])/hello")
	rows, err := db.Query("select id, name from users where id = ?", 1)
	if err == nil {
		//xxx
	}
	defer rows.Close()
	var users []User
	for rows.Next() {
		var user User
		err := rows.Scan(&user. ID, &user,Name)
		if err!=nil{
		//..
		}
	}
		users = append(users, user)
		if rows.Err() != nil {
		}
}

了解 DSN 是什么：

1.2设计原理

查看go的源码

type DB struct {
	// Atomic access only. At top of struct to prevent mis-alignment
	// on 32-bit platforms. Of type time.Duration.
	waitDuration int64 // Total time waited for new connections.

	connector driver.Connector
	// numClosed is an atomic counter which represents a total number of
	// closed connections. Stmt.openStmt checks it before cleaning closed
	// connections in Stmt.css.
	numClosed uint64

	mu           sync.Mutex // protects following fields
	freeConn     []*driverConn
	connRequests map[uint64]chan connRequest
	nextRequest  uint64 // Next key to use in connRequests.
	numOpen      int    // number of opened and pending open connections
	// Used to signal the need for new connections
	// a goroutine running connectionOpener() reads on this chan and
	// maybeOpenNewConnections sends on the chan (one send per needed connection)
	// It is closed during db.Close(). The close tells the connectionOpener
	// goroutine to exit.
	openerCh          chan struct{}
	resetterCh        chan *driverConn
	closed            bool
	dep               map[finalCloser]depSet
	lastPut           map[*driverConn]string // stacktrace of last conn's put; debug only
	maxIdle           int                    // zero means defaultMaxIdleConns; negative means 0
	maxOpen           int                    // <= 0 means unlimited
	maxLifetime       time.Duration          // maximum amount of time a connection may be reused
	cleanerCh         chan struct{}
	waitCount         int64 // Total number of connections waited for.
	maxIdleClosed     int64 // Total number of connections closed due to idle.
	maxLifetimeClosed int64 // Total number of connections closed due to max free limit.

	stop func() // stop cancels the connection opener and the session resetter.
}

接下来看连接池

连接池配置

func (db *DB) SetConnMaxIdleTime(d time.Duration)
func (db *DB) SetConnMaxLifetime(d time.Duration)
func (db *DB) SetMaxIdleConns(n int)
func (db *DB) SetMaxOpenConns(n int)

连接池状态

func (db *DB) Stats() DBStats

Driver 连接接口

	for i := 0; i < maxBadConnRetries; i++ {
		// 从连接池获取连接或通过driver新建连接
		dc, err := db.conn(ctx, strategy)
		//有空闲连接-> reuse -> max life time
		//新建连接-> max open.. .
		//将连接放回连接池
		defer dc.db.putConn(dc, err, true)
		/// validateConnection有无错误
		// max life time, max idle conns检查
		//连接实现driver.Queryer; driver.Execer等interface
		if err == nil{
			err = dc.ci.Query(sql, args...)
		}
		isBadConn = errors.Is(err, driver. ErrBadConn)
		if !isBadConn {
			break
		}

// Open new Connection.
// See https://github.com/go-sql-driver/mysql#dsn-data-source-name for how
// the DSN string is formatted
func (d MySQLDriver) Open(dsn string) (driver.Conn, error) {
	cfg, err := ParseDSN(dsn)
	if err != nil {
		return nil, err
	}
	c := &connector{
		cfg: cfg,
	}
	return c.Connect(context.Background())
}
// github.com/go-sql-driver/mysql/driver.go
// 注册驱动
func init() {
	sql.Register("mysql", &MySQLDriver{})
}

dev.mysql.com/doc/interna…

02.GORM基础使用

基本用法，Model定义，惯例约定，关联操作

2.1背景知识

设计简洁、功能强大、自由扩展的全功能ORM

设计原则： API精简、测试优先、最小惊讶、灵活扩展、无依赖可信赖
功能完善：
- 关联：一对一、一对多、单表自关联、多态；Preload、 Joins 预加载、级联删除；关联模式；自定义关联表
- 事务：事务代码块、嵌套事务、Save Point
- 多数据库、读写分离、命名参数、Map、子查询、分组条件、代码共享、SQL表达式(查询、创建、更新)、自动选字段、查询优化器
- 字段权限、软删除、批量数据处理、Prepared Stmt、自定义类型、命名策略、虚拟字段、自动track时间、SQL Builder、Logger
- 代码生成、复合主键、Constraint、 Prometheus、 Auto Migration、真跨数据库兼容...
- 多模式灵活自由扩展
- Developer Friendly

2.2基本用法

先install

go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlite

package main

import (
  "gorm.io/gorm"
  "gorm.io/driver/sqlite"
)

type Product struct {
  gorm.Model
  Code  string
  Price uint
}

func main() {
  db, err := gorm.Open(sqlite.Open("deme.db"), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }

  // Migrate the schema
  db.AutoMigrate(&Product{})

  // Create
  db.Create(&Product{Code: "D42", Price: 100})

  // Read
  var product Product
  db.First(&product, 1) // find product with integer primary key
  db.First(&product, "code = ?", "D42") // find product with code D42

  // Update - update product's price to 200
  db.Model(&product).Update("Price", 200)
  // Update - update multiple fields
  db.Model(&product).Updates(Product{Price: 200, Code: "F42"}) // non-zero fields
  db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})

  // Delete - delete product
  db.Delete(&product, 1)
}

2.2基本用法-CRUD

github：github.com/nateshao/gi…

package main

// gorm demo1

import (
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

// UserInfo --> 数据表
type UserInfo struct {
	ID     uint
	Name   string
	Gender string
	Hobby  string
}

func main() {
	// 连接MySQL数据库
	db, err := gorm.Open("mysql", "root:root1234@(127.0.0.1:13306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// 创建表 自动迁移（把结构体和数据表进行对应）
	db.AutoMigrate(&UserInfo{})

	// 创建数据行
	//u1 := UserInfo{1, "七米", "男", "蛙泳"}
	//db.Create(&u1)
	// 查询
	var u UserInfo
	db.First(&u) // 查询表中第一天数据保存到u中
	fmt.Printf("u:%#v\n", u)
	// 更新
	db.Model(&u).Update("hobby", "双色球")
	// 删除
	db.Delete(&u)
}

新建

package main

import (
	"database/sql"
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

// gorm demo03

// 1. 定义模型
type User struct {
	ID   int64
	Name sql.NullString `gorm:"default:'小王子'"`
	Age  int64
}

func main() {
	// 连接MySQL数据库
	db, err := gorm.Open("mysql", "root:root1234@(127.0.0.1:13306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
	if err != nil {
		panic(err)
	}
	defer db.Close()
	// 2. 把模型与数据库中的表对应起来
	db.AutoMigrate(&User{})

	// 3. 创建
	u := User{Name: sql.NullString{String: "", Valid: true}, Age: 98} // 在代码层面创建一个User对象
	fmt.Println(db.NewRecord(&u))                                     // 判断主键是否为空 true
	db.Debug().Create(&u)                                             // 在数据库中创建了一条q1mi 18的记录
	fmt.Println(db.NewRecord(&u))                                     // 判断主键是否为空 false
}

2.3模型定义-惯例约定

约定优于配置

表名为struct name的snake_ cases复数格式
字段名为field name的snake_ case单数格式
ID/ ld字段为主键，如果为数字，则为自增主键
CreatedAt字段，创建时，保存当前时间
UpdatedAt字段，创建、更新时，保存当前时间
gorm.DeletedAt字段，默认开启soft delete模式

一切皆可配置：gorm.io/docs/conven…

package main

// gorm demo2
import (
	"database/sql"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
	"time"
)

// 定义模型
type User struct {
	gorm.Model   // 内嵌gorm.Model
	Name         string
	Age          sql.NullInt64 `gorm:"column:user_age"` // 零值类型
	Birthday     *time.Time
	Email        string  `gorm:"type:varchar(120);unique_index"`
	Role         string  `gorm:"size:255"`        // 设置字段大小为255
	MemberNumber *string `gorm:"unique;not null"` // 设置会员号（member number）唯一并且不为空
	Num          int     `gorm:"AUTO_INCREMENT"`  // 设置 num 为自增类型
	Address      string  `gorm:"index:addr"`      // 给address字段创建名为addr的索引
	IgnoreMe     int     `gorm:"-"`               // 忽略本字段
}

// 使用`AnimalID`作为主键
type Animal struct {
	AnimalID int64 `gorm:"primary_key"`
	Name     string
	Age      int64
}

// 唯一指定表名
func (Animal) TableName() string {
	return "qimi"
}

func main() {
	// 修改默认的表明规则
	gorm.DefaultTableNameHandler = func(db *gorm.DB, defaultTableName string) string {
		return "SMS_" + defaultTableName
	}
	// 连接MySQL数据库
	db, err := gorm.Open("mysql", "root:root1234@(127.0.0.1:13306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
	if err != nil {
		panic(err)
	}
	defer db.Close()
	db.SingularTable(true) // 禁用复数

	db.AutoMigrate(&User{})
	db.AutoMigrate(&Animal{})

	// 使用User结构体创建名叫 xiaowangzi 的表
	//db.Table("xiaowangzi").CreateTable(&User{})
}