golang实现MySQL操作

golang实现MySQL操作

database/sql

引入数据库驱动

$ go get -u github.com/go-sql-driver/mysql

//引用MySQL驱动
improt _"github.com/go-sql-driver/mysql"

连接MySQL数据库

import (
	"database/sql"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

db, err := sql.Open("mysql", "user:password@/dbname")
if err != nil {
	panic(err)
}

//连接设置
//设置连接可重用的最大时间
db.SetConnMaxLifetime(time.Minute * 3)
//设置与数据库建立连接的最大数目，默认0，无限制
db.SetMaxOpenConns(10)
//设置连接池中的最大闲置连接数
db.SetMaxIdleConns(10)

sql语句

id := 2
var name string
sql := "update tables set name = tony where id = ?"
//Exec一般执行不需要返回数据结果的sql语句
db.Exec(sql, id)
//返回一行数据结果
db.QueryRow("select name from user where id=?",id).Scan(&name)
fmt.Println(name)

//返回多行数据结果
names := make([]string,0)
rows,err := db.Query("select name from user where id>?",id)
if err != nil{
    fmt.Prrintln(err)
    return
}
for rows.Next{
    err = rows.Scan(&name)
    if err != nil{
		continue
    }
    names = append(names,name)
}

处理事务

	//开启一个事务
	tx, err := db.Begin()
	if err != nil {
		panic(err)
	}
	//执行sql语句
	_, err = tx.Exec("insert into tom (name,score) value (?,?)", "qq", 89)
	if err != nil {
		fmt.Println(err)
        //遇到错误，事务回滚
		tx.Rollback()
	}
    //执行sql语句
	_, err = tx.Exec("delete from tom1 where id = ?", 9)
	if err != nil {
		fmt.Println(err)
        //遇到错误，事务回滚
		tx.Rollback()
	}
	//提交事务
	err = tx.Commit()
	if err != nil {
		fmt.Println("err:======", err)
		return
	}

处理where in

func main(){
    //要执行where in 的sql语句，in内的"?"的数量需要与切片的元素个数相等
    
    //注意。in内不再是?而是格式化符号%s。其他条件仍要?
    sql := "delete from user where id in(%s)"
    ids := []int{2,3,4}
    sqlin,args,err := In(sql,ids)
    if err != nil{
        fmt.Println(err)
        return
    }
    db.Exec(sqlin,args...)
}

//处理where in的sql语句
func In(sql string, ss interface{}) (string, []interface{}, error) {
    //判断sql语句中是否有in关键字
	ok := strings.Contains(sql, "in")
	err := errors.New("sql not found in")
	if !ok {
		return "", nil, err
	}
	args := make([]interface{}, 0)
	var count string
    //断言，判断数值是那种类型切片
	switch v := ss.(type) {
        //根据不同类型切片的长度，添加相应数量的"?"
        //将原切片的数据转为interface切片
	case []string:
		for i := 0; i < len(v); i++ {
			if i == 0 {
				count = "?"
			} else {
				count += ",?"
			}
			args = append(args, v[i])
		}
	case []int:
		for i := 0; i < len(v); i++ {
			if i == 0 {
				count = "?"
			} else {
				count += ",?"
			}
			args = append(args, v[i])
		}
	case []int64:
		for i := 0; i < len(v); i++ {
			if i == 0 {
				count = "?"
			} else {
				count += ",?"
			}
			args = append(args, v[i])
		}
	default:
		fmt.Println("err,no type")
	}
    //生成新的sql语句
	newsql := fmt.Sprintf(sql, count)
	return newsql, args, nil

}