104 lines
3.1 KiB
Go
104 lines
3.1 KiB
Go
|
package db
|
||
|
|
|
||
|
|
import (
|
||
|
|
// "context"
|
||
|
|
"database/sql"
|
||
|
|
"fmt"
|
||
|
|
_ "github.com/go-sql-driver/mysql"
|
||
|
|
"strings"
|
||
|
|
"time"
|
||
|
|
)
|
||
|
|
|
||
|
|
type MysqlConnector struct {
|
||
|
|
}
|
||
|
|
|
||
|
|
func (c MysqlConnector) connect(config *DBConfig) *Connection {
|
||
|
|
/**
|
||
|
|
[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN]
|
||
|
|
// user@unix(/path/to/socket)/dbname
|
||
|
|
// root:pw@unix(/tmp/mysql.sock)/myDatabase?loc=Local
|
||
|
|
// user:password@tcp(localhost:5555)/dbname?tls=skip-verify&autocommit=true
|
||
|
|
// user:password@/dbname?sql_mode=TRADITIONAL
|
||
|
|
// user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname?timeout=90s&collation=utf8mb4_unicode_ci
|
||
|
|
// id:password@tcp(your-amazonaws-uri.com:3306)/dbname
|
||
|
|
// user@cloudsql(project-id:instance-name)/dbname
|
||
|
|
// user@cloudsql(project-id:regionname:instance-name)/dbname
|
||
|
|
// user:password@tcp/dbname?charset=utf8mb4,utf8&sys_var=esc%40ped
|
||
|
|
// user:password@/dbname
|
||
|
|
// user:password@/
|
||
|
|
*/
|
||
|
|
//TODO: Protocol loc readTimeout serverPubKey timeout
|
||
|
|
if config.MaxOpenConns == 0 {
|
||
|
|
config.MaxOpenConns = 15
|
||
|
|
}
|
||
|
|
if config.MaxIdleConns == 0 {
|
||
|
|
config.MaxIdleConns = 5
|
||
|
|
}
|
||
|
|
if config.ConnMaxLifetime == 0 {
|
||
|
|
config.ConnMaxLifetime = 86400
|
||
|
|
}
|
||
|
|
if config.ConnMaxIdleTime == 0 {
|
||
|
|
config.ConnMaxIdleTime = 7200
|
||
|
|
}
|
||
|
|
var params []string
|
||
|
|
|
||
|
|
if len(config.Charset) > 0 {
|
||
|
|
params = append(params, "charset="+config.Charset)
|
||
|
|
}
|
||
|
|
if len(config.Collation) > 0 {
|
||
|
|
params = append(params, "collation="+config.Collation)
|
||
|
|
}
|
||
|
|
if config.MultiStatements {
|
||
|
|
params = append(params, "multiStatements=true")
|
||
|
|
}
|
||
|
|
if config.ParseTime {
|
||
|
|
params = append(params, "parseTime=true")
|
||
|
|
}
|
||
|
|
var dsn string
|
||
|
|
if len(config.Dsn) > 0 {
|
||
|
|
dsn = config.Dsn
|
||
|
|
} else {
|
||
|
|
dsn = fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?%s", config.Username, config.Password, config.Host, config.Port, config.Database, strings.Join(params, "&"))
|
||
|
|
}
|
||
|
|
db, err := sql.Open(DriverMysql, dsn)
|
||
|
|
|
||
|
|
if err != nil {
|
||
|
|
panic(err.Error())
|
||
|
|
} else if err := db.Ping(); err != nil {
|
||
|
|
panic(err)
|
||
|
|
} else {
|
||
|
|
// 最大链接数, 默认0, 不限制
|
||
|
|
db.SetMaxOpenConns(config.MaxOpenConns)
|
||
|
|
// 最大空闲链接数, 默认2
|
||
|
|
db.SetMaxIdleConns(config.MaxIdleConns)
|
||
|
|
// 表示在连接池中连接的最大生存时间, 默认0, 表示不限制。
|
||
|
|
db.SetConnMaxLifetime(time.Duration(config.ConnMaxLifetime) * time.Second)
|
||
|
|
// 连接池中空闲连接的最大生存时间, 默认0, 表示不限制。
|
||
|
|
db.SetConnMaxIdleTime(time.Duration(config.ConnMaxIdleTime) * time.Second)
|
||
|
|
}
|
||
|
|
|
||
|
|
// 优化事物查询速度
|
||
|
|
// Golang 在 MYSQL 事物中不能并发执行查询, 会出现 driver:bad connection,
|
||
|
|
// 为了在事物中执行并发查询, 单独开一个脏读查询链接
|
||
|
|
|
||
|
|
// 处理 driver:bad connection, 需要加锁
|
||
|
|
// 同一事务开启多协程的同时如果有并发读, 那可能会出现 driver:bad connection 错误,
|
||
|
|
// 原因是同一事务同一时间只能有一个可以进行读操作, 读完之后需要将查询得到的Rows关闭.
|
||
|
|
if len(params) > 0 {
|
||
|
|
dsn = dsn + `&`
|
||
|
|
}
|
||
|
|
RxDB, err := sql.Open(DriverMysql, dsn+`transaction_isolation='read-uncommitted'`)
|
||
|
|
if err != nil {
|
||
|
|
panic("开启事物链接失败, " + err.Error())
|
||
|
|
} else {
|
||
|
|
RxDB.SetMaxOpenConns(5)
|
||
|
|
RxDB.SetMaxIdleConns(5)
|
||
|
|
}
|
||
|
|
|
||
|
|
return &Connection{
|
||
|
|
RxDB: RxDB,
|
||
|
|
DB: db,
|
||
|
|
Config: config,
|
||
|
|
}
|
||
|
|
}
|