调用mysql

mysql

package db

import (
	"config"
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"logger"
	"strconv"
	"time"
)

// ==========================
// Admin Vvooooooooooo
// Time  2020/1/8
// Explain
// ==========================


var Db *sql.DB

func init()  {
	dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s?charset=utf8",config.Serverconfig.User,config.Serverconfig.Password,config.Serverconfig.DBNetwork,config.Serverconfig.Sqlip,config.Serverconfig.Port,config.Serverconfig.Dataname)
	var err error
	db, err := sql.Open("mysql", dsn)
	//config.Serverconfig.User+":"+config.Serverconfig.Password+"@tcp("+config.Serverconfig.Sqlip+":"+config.Serverconfig.Port+")/"+config.Serverconfig.Dataname+"?charset=utf8"
	//"qijue:qijue@tcp(192.168.77.20:3306)/wxmh5?charset=utf8" )
	if err!=nil {
		logger.Error("init mysql failse ,err:",err.Error())
	}
	Db = db
	logger.Info("init mysql succ ")
	//defer  db.Close()
}


//Insert插入数据

func Insert(query string) bool  {
	r, err := Db.Exec(query)
	if err !=nil{
		logger.Info("mysql Insert failed,err: ",err)
		return false
	}
	id,err := r.LastInsertId()
	if err!=nil{
		logger.Info("mysql Insert failed Id,err: ",err)
		return false
	}
	logger.Info("mysql Insert succ, ",id)
	return true

}

//Delet删除数据

func Delet(query string) bool {
	res, err :=Db.Exec(query)
	if err != nil {
		logger.Info("mysql Delet failed,err: ", err)
		return false
	}
	row, err := res.RowsAffected()
	if err != nil {
		logger.Info("mysql Delet rows failed,err: ",err)
	}
	logger.Info("mysql Delet succ:",row)
	return true
}

//db Update 更新数据

func Update(query string) bool {
	res, err :=Db.Exec(query )
	if err != nil {
		logger.Info("mysql Update failed,err: ", err)
		return false
	}
	row, err := res.RowsAffected()
	if err != nil {
		logger.Info("mysql Update rows failed,err: ",err)
	}
	logger.Info("mysql Update succ:",row)
	return true
}



// get 获取数据

func close_rows(rows *sql.Rows){
	if rows != nil {
		rows.Close()
	}
}


//获取数据处理

func dealRows(rows *sql.Rows) []map[string]interface{}  {
	defer close_rows(rows)
	//获取列名
	Columns,err := rows.Columns()
	ColumnTypes,_ := rows.ColumnTypes()
	//获取每条数据的类型
	ColumnTypeMap := make(map[string]string)
	for _,v:=range ColumnTypes{
		ColumnTypeMap[v.Name()]  = v.DatabaseTypeName()
	}
	if err != nil{
		logger.Info("rows columns failed, err:",err)
	}
	//定义返回参数
	retValue :=make([]sql.RawBytes,len(Columns))
	//定义数据列名
	scandata :=make([]interface{},len(retValue))
	//数据赋值
	for i:=range retValue{
		scandata[i] = &retValue[i]
	}
	//定义返回数据类型
	var List []map[string]interface{}

	for rows.Next(){
		//检测超出获取
		err = rows.Scan(scandata...)
		if err !=nil{
			logger.Info("rows scan failed ,err",err.Error())
		}
		//定义数据格式
		rowMap := make(map[string]interface{})
		for i,colvalue := range retValue{
			if colvalue !=nil{
				key := Columns[i]
				value := string(colvalue)
			//	数据转换
				switch ColumnTypeMap[key] {
				case "INT":
					newValue, _ := strconv.Atoi(value)
					rowMap[key] = newValue
				case "TINYINT":
					newValue, _ := strconv.Atoi(value)
					rowMap[key] = newValue
				case "VARCHAR":
					rowMap[key] = value
				case "DATETIME":
					newValue, _ := time.Parse(value, value)
					rowMap[key] = newValue
				default:
					rowMap[key] = value
				}
			}
		}
		List = append(List,rowMap)
	}
	return List
}

//get_row 获取单独一数据

func get_row(query string) map[string]interface{} {
	var reData map[string]interface{}
	rows, err := Db.Query(query)
	if err != nil {
		logger.Info("mysql select all failed,err: ",err)
	}
	reList := dealRows(rows)
	if len(reList)>0{
		 reData = reList[0]
	}
	return reData
}
//Get_all  获取多条数据
func get_all(query string) []map[string]interface{}  {
	rows, err := Db.Query(query)
	if err != nil {
		logger.Info("mysql select all failed,err: ",err)
	}
	reList := dealRows(rows)
	return reList
}

package db

import (
	"fmt"
	"logger"
	"testing"
)

// ==========================
// Admin Vvooooooooooo
// Time  2020/1/8
// Explain
// ==========================

func TestSql_row(t *testing.T) {
	var info= "SELECT * FROM qj_mind"
	//获取一条数据
	rows := get_row(info)
	logger.Info("one mind_level,",rows["mind_level"])
	logger.Info("one,",rows)
	//获取多条数据
	all := get_all(info)
	logger.Info("all,",all)
	//插入
	sqlquery :="insert into qj_mind(mind_level,mind_stage,mind_star,playerid) values(%v,%v,%v,%v)"
	sqlquery =fmt.Sprintf(sqlquery,1 ,1 ,0 ,9527)
	result := Insert(sqlquery)
	logger.Info("Insert,",result)
	// 更新和删除和插入方式一致
}