导入相关的包
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
_ "github.com/mattn/go-oci8"
)
初始化数据库连接
func initIni() {
ip = "127.0.0.1"
port = "3306"
username = "root"
password = "123456"
database = "test01"
}
func InitDB() (db *sql.DB, err error) {
initIni()
mysqldsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", username, password, ip, port, database)
db, err = sql.Open("mysql", mysqldsn)
if err != nil {
fmt.Println(`验证格式失败`, mysqldsn, err)
return nil, err
}
err = db.Ping()
if err != nil {
fmt.Println(`连接数据库失败`, err)
return nil, err
}
db.SetMaxOpenConns(5)
db.SetMaxIdleConns(0)
db.SetConnMaxIdleTime(60 * time.Minute)
db.SetConnMaxLifetime(60 * time.Minute)
return db, nil
}
操作_新增
func Insertdb(db *sql.DB, sqlStr string, args ...interface{}) (int64, error) {
stmt, err := db.Prepare(sqlStr)
res, err := stmt.Exec(args...)
id, err := res.LastInsertId()
fmt.Println("插入数据成功:", id)
return id, err
}
操作_查询
func Querydb(db *sql.DB, sqlStr string, args ...interface{}) ([]map[string]string, error) {
rows, err := db.Query(sqlStr, args...)
if err != nil {
return nil, err
}
defer rows.Close()
cols, _ := rows.Columns()
values := make([][]byte, len(cols))
scans := make([]interface{}, len(cols))
for i := range values {
scans[i] = &values[i]
}
results := make([]map[string]string, 0)
for rows.Next() {
err := rows.Scan(scans...)
if err != nil {
return nil, err
}
row := make(map[string]string, 10)
for k, v := range values {
key := cols[k]
row[key] = string(v)
}
results = append(results, row)
}
return results, nil
}
操作_修改
func UpdateDB(db *sql.DB, sqlStr string, args ...interface{}) (int64, error) {
stmt, err := db.Prepare(sqlStr)
res, err := stmt.Exec(args...)
fmt.Println(err)
affect, err := res.RowsAffected()
fmt.Println("更新数据:", affect)
return affect, err
}
操作_删除
func DeleteDB(db *sql.DB, sqlStr string, args ...interface{}) (int64, error) {
stmt, err := db.Prepare(sqlStr)
res, err := stmt.Exec(args...)
affect, err := res.RowsAffected()
fmt.Println("删除数据:", affect)
return affect, err
}
测试
func main() {
defer func() {
fmt.Println("d")
if err := recover(); err != nil {
fmt.Println(err)
}
fmt.Println("e")
}()
db, err := InitDB()
if err != nil {
fmt.Println("错误:", err)
}
defer db.Close()
iStr := `INSERT INTO test(uid, email,phone) VALUES (?, ?, ?);`
pStr1 := `3`
pStr2 := `32`
iresult, err2 := Insertdb(db, iStr, pStr1, pStr2, `321`)
if err2 != nil {
fmt.Print(`新增条数:`)
fmt.Println(iresult)
}
qStr := `select email,phone from test where uid = ? and phone = ?`
pStr3 := `3`
pStr4 := `321`
qresult, err1 := Querydb(db, qStr, pStr3, pStr4)
if err1 != nil {
} else {
lresult := len(qresult)
fmt.Println(lresult)
for _, value := range qresult {
for k, v := range value {
fmt.Println(`列名:` + k + ` 值:` + v)
}
fmt.Println(`另起一行:`)
}
}
uString := `update test set phone = ? where uid = ? and email = ?`
pStr5 := `123`
UpdateDB(db, uString, pStr5, pStr1, pStr2)
dStr := "Delete from test where uid = ? and phone = ?"
pStr7 := `3`
dresult, err3 := DeleteDB(db, dStr, pStr7, pStr5)
if err3 != nil {
fmt.Print(`删除条数:`)
fmt.Println(dresult)
}
}