Golang Conn.Query方法代码示例

func mysqlVersion(conn mysqlc.Conn) (uint, uint, uint, error) {
	rows, _, err := conn.Query("SELECT VERSION()")
	if err != nil {
		return 0, 0, 0, err
	}
	if len(rows) == 0 {
		return 0, 0, 0, fmt.Errorf("SELECT VERSION() returned an empty set")
	}

	versionString := rows[0].Str(0)
	version := strings.Split(versionString, ".")
	invalidVersionErr := fmt.Errorf("Invalid major.minor.patch in %q", versionString)
	if len(version) != 3 {
		return 0, 0, 0, invalidVersionErr
	}

	major, err := strconv.ParseUint(version[0], 10, 32)
	if err != nil {
		return 0, 0, 0, invalidVersionErr
	}

	minor, err := strconv.ParseUint(version[1], 10, 32)
	if err != nil {
		return 0, 0, 0, invalidVersionErr
	}

	patch, err := strconv.ParseUint(version[2], 10, 32)
	if err != nil {
		return 0, 0, 0, invalidVersionErr
	}

	return uint(major), uint(minor), uint(patch), nil
}

func (m MySQLPlugin) fetchProcesslist(db mysql.Conn, stat map[string]float64) error {
	rows, _, err := db.Query("SHOW PROCESSLIST")
	if err != nil {
		log.Fatalln("FetchMetrics (Processlist): ", err)
		return err
	}

	for k := range processState {
		stat[k] = 0
	}

	for _, row := range rows {
		if len(row) > 1 {
			var state string
			if row[6] == nil {
				state = "NULL"
			} else {
				state = string(row[6].([]byte))
			}
			parseProcesslist(state, &stat)
		} else {
			log.Fatalln("FetchMetrics (Processlist): row length is too small: ", len(row))
		}
	}

	return nil
}

func (m MySQLPlugin) fetchShowStatus(db mysql.Conn, stat map[string]float64) error {
	rows, _, err := db.Query("show /*!50002 global */ status")
	if err != nil {
		log.Fatalln("FetchMetrics (Status): ", err)
		return err
	}

	for _, row := range rows {
		if len(row) > 1 {
			variableName := string(row[0].([]byte))
			if err != nil {
				log.Fatalln("FetchMetrics (Status Fetch): ", err)
				return err
			}
			stat[variableName], _ = atof(string(row[1].([]byte)))
		} else {
			log.Fatalln("FetchMetrics (Status): row length is too small: ", len(row))
		}
	}
	if m.EnableExtended {
		err = fetchShowStatusBackwardCompatibile(stat)
		if err != nil {
			log.Fatalln("FetchExtendedMetrics (Status Fetch): ", err)
		}
	}
	return nil
}

func (mu *MysqlUser) GrantExists(db mysql.Conn) (bool, error) {
	rows, _, err := db.Query(
		"SHOW GRANTS FOR '%v'@'%v'",
		mu.Username,
		mu.Hostname,
	)
	if err != nil {
		return false, err
	}

	if len(rows) == 0 {
		return false, nil
	}

	for _, row := range rows {
		grant := row.Str(0)
		// This regex hasn't been extensively tested but works in my local tests
		regex := fmt.Sprintf("GRANT ALL (.+) ON .%v.(.+) TO .%[email protected]%v.", mu.Database, mu.Username, mu.Hostname)
		matched, err := regexp.MatchString(regex, grant)
		if err != nil {
			return false, err
		}
		if matched {
			return true, nil
		}
	}
	return false, nil
}

func udpateMySqlCdrImportStatus(db mysql.Conn, uniqueid string, status int) (err error) {
	var query = fmt.Sprintf("UPDATE cdr SET import = %d WHERE uniqueid = '%s'", status, uniqueid)
	log.Tracef("update cdr status [%s].\n", query)
	_, _, err = db.Query(query)
	//
	return err
}

func (mydb *MysqlDatabase) CreateDatabase(db mysql.Conn) error {
	// IF NOT EXISTS isn't strictly necessary since we are checking
	// to make sure it doesn't exist first but it doesn't hurt.
	_, _, err := db.Query("CREATE DATABASE IF NOT EXISTS %v", mydb.Name)
	if err != nil {
		return err
	}
	mydb.Result.Changed = true
	return nil
}

func serverVersion(conn mysqlc.Conn) (*version.Version, error) {
	rows, _, err := conn.Query("SELECT VERSION()")
	if err != nil {
		return nil, err
	}
	if len(rows) == 0 {
		return nil, fmt.Errorf("SELECT VERSION() returned an empty set")
	}

	return version.NewVersion(rows[0].Str(0))
}

func del(db mysql.Conn, bag_id int, stuff_id int) {
	_, _, err := db.Query("delete from player_bag where bag_id=%d", bag_id)
	if err != nil {
		panic(err)
	}
	_, _, err = db.Query("delete from paladin where paladin_id=%d", stuff_id)
	if err != nil {
		panic(err)
	}
	fmt.Printf("delete %d, %d\n", bag_id, stuff_id)
}

// Get list of existing tables in database
func getTables(db mysql.Conn) (tables []string) {
	tables = make([]string, 0)
	rows, _, err := db.Query("SHOW TABLES")
	checkError(err)
	for _, row := range rows {
		for k, _ := range row {
			tables = append(tables, row.Str(k))
		}
	}
	return
}

func QueryCheck(db mysql.Conn, sql string, params ...interface{}) ([]mysql.Row, mysql.Result) {
	rows, res, err := db.Query(sql)
	if err != nil {
		errlog.Error("QueryCheck =%s", sql)
		errlog.Error("QueryCheck err=%s", err.Error())
		fmt.Println(sql)
		fmt.Println(err)
		os.Exit(1)
	}

	return rows, res
}

func ProcessNum(db mysql.Conn, cfg *base.Cfg) (*model.MetaData, error) {
	sql := "SHOW PROCESSLIST"
	rows, _, err := db.Query(sql)
	if err != nil {
		return nil, err
	}
	monitorName := "process_nums"
	threadNum := len(rows) - 1
	data := model.NewMetric(monitorName, cfg)
	data.SetValue(threadNum)
	return data, nil
}

func queryByPath(w http.ResponseWriter, db mysql.Conn, pt string) {
	rows, res := checkedResult(db.Query("select full_result from risks_check_history where apk='%s' ORDER BY id DESC Limit 1", pt))
	full_result := res.Map("full_result")
	for _, row := range rows {
		//fmt.Ffmt.Printlnf(w,
		fmt.Fprintf(w,
			"------[ %s 包含以下风险]---------\n%s\n",
			pt,
			row.Str(full_result),
		)
		RightLine()
	}
}

// Get the column list for the SELECT, applying the select map
// from config file.
func getColumnListForSelect(db mysql.Conn, table string) string {
	columns := make([]string, 0)
	rows, res, err := db.Query("SHOW COLUMNS FROM `%s`", table)
	checkError(err)
	for _, row := range rows {
		column := row.Str(res.Map("Field"))
		replacement, ok := selectMap[table][column]
		if ok {
			column = fmt.Sprintf("%s AS `%s`", replacement, column)
		}
		columns = append(columns, column)
	}
	return strings.Join(columns, ", ")
}

func (m MySQLPlugin) fetchShowSlaveStatus(db mysql.Conn, stat map[string]float64) error {
	rows, res, err := db.Query("show slave status")
	if err != nil {
		log.Fatalln("FetchMetrics (Slave Status): ", err)
		return err
	}

	for _, row := range rows {
		idx := res.Map("Seconds_Behind_Master")
		Value := row.Int(idx)
		stat["Seconds_Behind_Master"] = float64(Value)
	}
	return nil
}

func deleteMySqlCelRecord(db mysql.Conn, uniqueid string) (err error) {

	if config.PurgeCelEvents == false {
		return nil
	}

	var query = fmt.Sprintf("DELETE FROM cel WHERE uniqueid = '%s' OR linkedid = '%s'", uniqueid, uniqueid)
	_, _, err = db.Query(query)

	log.Debugf("getMySqlCallDetails execute : [%s] .\n", query)

	if err != nil {
		log.Errorf("deleteMySqlCelRecord Failed delete record into cel table for uniqueid [%s].\n", uniqueid)
	}
	return err
}