测试表:
CREATE TABLE `users` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`password` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`nickname` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`email` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
`birthday` VARCHAR(10) NOT NULL DEFAULT '0000-00-00' COLLATE 'utf8mb4_unicode_ci',
`age` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`level` TINYINT(3) NOT NULL DEFAULT '0',
`disabled` TINYINT(1) NOT NULL DEFAULT '0',
`created_at` DATETIME NOT NULL DEFAULT current_timestamp(),
`updated_at` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `username` (`username`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;
连接
package main
import (
"database/sql"
"fmt"
"time"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB //全局变量client
func initMySQL() (err error) {
dsn := "root:123456@tcp(127.0.0.1:3306)/test"
db, err = sql.Open("mysql", dsn)
if err != nil {
panic(err)
}
err = db.Ping() //检测是否连接成功
if err != nil {
return
}
db.SetMaxOpenConns(200) //最大连接数
db.SetMaxIdleConns(10) //连接池里最大空闲连接数。必须要比maxOpenConns小
db.SetConnMaxLifetime(time.Second * 10) //最大存活保持时间
db.SetConnMaxIdleTime(time.Second * 10) //最大空闲保持时间
return
}
func main() {
if err := initMySQL(); err != nil {
fmt.Printf("connect to db failed,err:%v\n", err)
} else {
fmt.Println("connect to db success")
}
sqlStr := "SELECT id, name FROM sys_user WHERE id=?"
var u user
//非常重要:确保QueryRow之后调用Scan方法,否则持有的数据库链接不会被释放
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name)
if err != nil {
fmt.Printf("scan failed, err: %v\n", err)
return
}
fmt.Printf("id:%d,name:%s,age:%d\n", u.id, u.name)
defer db.Close()
}
//user结构体
type user struct {
id int
name string
}