golang mysql的连接池的具体使用

.
+--- Go.mod
+--- go.sum
+--- main.go
+--- pool
|   +--- sql-pool.go

package pool

import (
	"database/sql"
	"fmt"
	_ "GitHub.com/go-sql-driver/mysql"
	"log"
	"time"
)

var DB *sql.DB

func init()  {
	DB, _ = sql.Open("mysql", "root:root@tcp(localhost:3306)/test?charset=utf8&parseTime=True&loc=Local") // 使用本地时间，即东八区，北京时间
	 // set pool params
	DB.SetMaxOpenConns(2000)
	DB.SetMaxIdleConns(1000)
	DB.SetConnMaxLifetime(time.Minute * 60) // mysql default conn timeout=8h, should < mysql_timeout
	 err := DB.Ping()
	 if err != nil {
	 	log.Fatalf("database init failed, err: ", err)
	 }
	log.Println("mysql conn pool has initiated.")
}

func checkErr(err error)  {
	if err != nil {
		log.Println(err)
		panic(err)
	}
}

func createTable() {
	db := DB
	table := `CREATE TABLE IF NOT EXISTS test.user (
	 user_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
	 user_name VARCHAR(45) NOT NULL COMMENT '用户名称',
	 user_age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户年龄',
	 user_sex TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户性别',
	 PRIMARY KEY (user_id))
	 ENGINE = InnoDB
	 AUTO_INCREMENT = 1
	 DEFAULT CHARACTER SET = utf8
	 COLLATE = utf8_general_ci
	 COMMENT = '用户表'`
	if _, err := db.Exec(table); err != nil {
		checkErr(err)
	}
}

func insert() {
	db := DB
	stmt, err := db.Prepare(`INSERT user (user, age) values (?, ?)`)
	checkErr(err)
	res, err := stmt.Exec("Elvis", 26)
	checkErr(err)
	id, err := res.LastInsertId()
	checkErr(err)
	log.Println(id)
}

func query() {
	db := DB
	rows, err := db.Query("SELECT * FROM user")
	checkErr(err)
	for rows.Next() {
		var userId int
		var userName string
		var userAge int
		var userSex int
		rows.Columns()
		err = rows.Scan(&userId, &userName, &userAge, &userSex)
		checkErr(err)
		fmt.Println(userId)
		fmt.Println(userName)
		fmt.Println(userAge)
		fmt.Println(userSex)
	}
}

func queryToMap() {
	db := DB
	rows, err := db.Query("SELECT * FROM user")
	checkErr(err)
	//字典类型
	//构造scanArgs、values两个数组，scanArgs的每个值指向values相应值的地址
	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)
		record := make(map[string]string)
		for i, col := range values {
			if col != nil {
				record[columns[i]] = string(col.([]byte))
			}
		}
		fmt.Println(record)
	}
}

func update() {
	db := DB
	stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
	checkErr(err)
	res, err := stmt.Exec(21, 2, 1)
	checkErr(err)
	num, err := res.RowsAffected()
	checkErr(err)
	fmt.Println(num)
}

func remove() {
	db := DB
	stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
	checkErr(err)
	res, err := stmt.Exec(1)
	checkErr(err)
	num, err := res.RowsAffected()
	checkErr(err)
	fmt.Println(num)
}

package main

import (
	"fmt"
	"log"
	"net/Http"

	. "go-mysql-pool-v1/pool"
)

func main()  {
	http.HandleFunc("/pool", pool)
	log.Println("server is up now...")
	http.ListenAndServe(":8080", nil)
}

func pool(w http.ResponseWriter, r *http.Request)  {
	rows, err := DB.Query(`select * from user limit 1`)
	defer rows.Close()
	checkErr(err)

	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))
	for j := range values {
		scanArgs[j] = &values[j]
	}

	record := make(map[string]string)
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		for i, col := range values {
			if col != nil {
				record[columns[i]] = string(col.([]byte))
			}
		}
	}
	log.Println(record)
	fmt.Fprintf(w, "finish")
}

func checkErr(err error)  {
	if err != nil {
		log.Println(err)
		panic(err)
	}
}

.
+--- config
|   +--- config.go
|   +--- config.JSON
+--- go.mod
+--- go.sum
+--- main.go
+--- pool
|   +--- gorm-pool.go
+--- test.db

{
  "database": {
    "name": "test",
    "type": "mysql",
    "host": "localhost",
    "port": "3306",
    "user": "root",
    "passWord": "root",
    "table_prefix": ""
  }
}

package config

import (
	"encoding/json"
	"os"
)

type Database struct {
	Type        string `json:"type"`
	Host        string `json:"host"`
	Port        string `json:"port"`
	User        string `json:"user"`
	Password    string `json:"password"`
	Name        string `json:"name"`
	TablePrefix string `json:"table_prefix"`
}

var DatabaseSetting = &Database{}


type Config struct {
	Database *Database `json:"database"`
}

var GlobalConfigSetting = &Config{}

func init()  {
	// win path is abs-path, linux -> config.json
	filePtr, err := os.Open("D:\\demo1\\src\\demo\\demo06\\go-mysql-pool-v2\\config\\config.json")
	if err != nil {
		return
	}
	defer filePtr.Close()

	// json decode
	decoder := json.NewDecoder(filePtr)
	err = decoder.Decode(GlobalConfigSetting)
	DatabaseSetting = GlobalConfigSetting.Database
}

package pool

import (
	"fmt"
	"go-mysql-pool-v2/config"
	"gorm.io/driver/mysql"
	"gorm.io/driver/postgres"
	"gorm.io/driver/SQLite"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
	"log"
	"time"
)

var db *gorm.DB

// gorm v2
func init()  {
	var dbURi string
	var dialector gorm.Dialector
	if config.DatabaseSetting.Type == "mysql" {
		dbURi = fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8&parseTime=true",
			config.DatabaseSetting.User,
			config.DatabaseSetting.Password,
			config.DatabaseSetting.Host,
			config.DatabaseSetting.Port,
			config.DatabaseSetting.Name)
		dialector = mysql.New(mysql.Config{
			DSN:                       dbURi, // data source name
			DefaultStringSize:         256,   // default size for string fields
			DisableDatetimePrecision:  true,  // disable datetime precision, which not supported before MySQL 5.6
			DontSupportRenameIndex:    true,  // drop & create when rename index, rename index not supported before MySQL 5.7, mariadb
			DontSupportRenameColumn:   true,  // `change` when rename column, rename column not supported before MySQL 8, MariaDB
			SkipInitializeWithVersion: false, // auto configure based on currently MySQL version
		})
	} else if config.DatabaseSetting.Type == "postgres" {
		dbURi = fmt.Sprintf("host=%s port=%s user=%s dbname=%s sslmode=disable password=%s",
			config.DatabaseSetting.Host,
			config.DatabaseSetting.Port,
			config.DatabaseSetting.User,
			config.DatabaseSetting.Name,
			config.DatabaseSetting.Password)
		dialector = postgres.New(postgres.Config{
			DSN:                  "user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai",
			PreferSimpleProtocol: true, // disables implicit prepared statement usage
		})
	} else { // sqlite3
		dbURi = fmt.Sprintf("test.db")
		dialector = sqlite.Open("test.db")
	}

	conn, err := gorm.Open(dialector, &gorm.Config{ // 如果不用设置表前缀及复数，nil
		NamingStrategy: schema.NamingStrategy{
			TablePrefix: config.DatabaseSetting.TablePrefix, // set table prefix
			SingularTable: true, // set table singular
		},
	})
	if err != nil {
		log.Println(err.Error())
	}
	sqlDB, err := conn.DB()
	if err != nil {
		log.Println("connect db server failed.")
	}
	sqlDB.SetMaxOpenConns(100)
	sqlDB.SetMaxIdleConns(10)
	sqlDB.SetConnMaxLifetime(600*time.Second)

	db = conn
}

// open api
func GetDB() *gorm.DB {
	sqlDB, err := db.DB()
	if err != nil {
		log.Println("connect db server failed.")
	}
	if err = sqlDB.Ping(); err != nil {
		sqlDB.Close()
	}

	return db
}

package main

import (
	"go-mysql-pool-v2/pool"
	"gorm.io/gorm"
	"log"
)

type Product struct {
	gorm.Model // default add id stats time, id as primary key
	Code string
	Price uint
}

func main()  {
	log.Println("gorm init...")
	SetupModel()
}

func SetupModel()  {
	db := pool.GetDB()
	// auto migrate
	db.AutoMigrate(&Product{})
	// create record
	db.Create(&Product{Code: "L1212", Price: 1000})
}

module go-mysql-pool-v2

go 1.16

replace go-mysql-pool-v2 => ../go-mysql-pool-v2

require (
	gorm.io/driver/mysql v1.3.4
	gorm.io/driver/postgres v1.3.4
	gorm.io/driver/sqlite v1.3.4
	gorm.io/gorm v1.23.5
)

ubuntu安装ab工具，apt-get install apache2-utils
// get 一般请求
ab -n 1000 -c 1000 http://xxx

// post json请求
ab -n 100000 -c 400 -p tempPara.txt -T application/json http://xxx

tempPara.txt内容：
{"driverId": 17,"pageNo": 1,"pageSize": 20,"status": 1}