字节跳动青训营系列文章
这是我参与「第三届青训营 -后端场」笔记创作活动的的第5篇笔记
同时这也是课表的第5天课程
PC端阅读效果更佳,点击文末:阅读原文即可。
基本用法,设计原理,基础概念
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 是什么:
查看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{})
}
https://dev.mysql.com/doc/internals/en/client-server-protocol.html
基本用法,Model定义,惯例约定,关联操作
设计简洁、功能强大、自由扩展的全功能ORM
先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)
}
github:https://github.com/nateshao/gin-demo/blob/main/gin-demo-17-gorm-mysql/main.go
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
}
约定优于配置
一切皆可配置:https://gorm.io/docs/conventions.html
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{})
}
SQL生成,插件扩展,ConnPool,Dialector
为什么这样处理呢?
https://gorm.io/docs/security.html