创建连接:
首先,创建了connect包
connect.go
package connectdb
import (
"fmt"
_ "github.com/go-sql-driver/mysql" // mysql
"github.com/jmoiron/sqlx"
)
// Connect 连接对象
func Connect(url string, database string, user string, pwd string) (db *sqlx.DB, err error) {
dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s", user, pwd, url, database)
db, err = sqlx.Connect("mysql", dsn)
if err != nil {
fmt.Println("数据库连接失败==>", err)
return nil, err
}
db.SetMaxOpenConns(100) // 设置连接池最大连接数
db.SetMaxIdleConns(20) // 设置连接池最大空闲连接数
fmt.Println("数据库已连接!")
return
}
插入数据
数据库结构是:
表名: userinfo
字段包含:username, departname,都是string
package simplecurd
import (
"fmt"
db "go_my_demo/mysql_demo/sqlx/connectdb"
"github.com/jmoiron/sqlx"
)
var DB *sqlx.DB
func normalInsert() {
DB, err := db.Connect("127.0.0.1:3306", "my_db", "user", "root123456")
if err != nil {
fmt.Println("connect error: " + err.Error())
return
}
sql := `INSERT INTO userinfo (username, departname) values (?, ?)`
res, err := DB.Exec(sql, "zhilong", "develop")
if err != nil {
fmt.Println("Insert Error: " + err.Error())
return
}
id, _ := res.LastInsertId()
fmt.Println("id: ", id)
}
type UserInfo struct {
// 用NamedExec时的变量名,只跟db:有关,无论Name还是json,都没关系
Name string `json:"name" db:"username"`
Departname string `json:"departname" db:"departname"`
}
func useStructToInsert() {
DB, err := db.Connect("127.0.0.1:3306", "my_db", "user", "root123456")
if err != nil {
fmt.Println("connect error: " + err.Error())
return
}
sql := `INSERT INTO userinfo (username, departname) values (:username, :departname)` // 用冒号加结构体里的变量名代替了
userInfo := UserInfo{
Name: "your name",
Departname: "Oh yeah",
}
res, err := DB.NamedExec(sql, userInfo)
if err != nil {
fmt.Println("Insert Error" + err.Error())
return
}
id, _ := res.LastInsertId()
fmt.Println("id is: ", id)
}