Golang mysql.New函数代码示例

func get_id() {
	db := mysql.New("tcp", "", "183.60.126.26:3306", user, pass, dbname)
	db1 := mysql.New("tcp", "", "183.60.126.26:3306", user, pass, dbname)

	err := db.Connect()
	if err != nil {
		panic(err)
	}
	defer db.Close()

	err = db1.Connect()
	defer db1.Close()

	rows, _, err := db.Query("select bag_id,stuff_id from player_bag b, paladin p where player_id = %d and b.stuff_id=p.paladin_id and is_active=0", 26559)
	if err != nil {
		panic(err)
	}

	for _, row := range rows {
		for _, col := range row {
			if col == nil {
				// col has NULL value
			} else {
				// Do something with text in col (type []byte)
			}
		}

		bag_id := row.Int(0)
		stuff_id := row.Int(1)
		fmt.Printf("%d, %d\n", bag_id, stuff_id)

		//del(db, bag_id, stuff_id);
	}
}

// Open new connection. The uri need to have the following syntax:
//
//   [PROTOCOL_SPECFIIC*]DBNAME/USER/PASSWD
//
// where protocol spercific part may be empty (this means connection to
// local server using default protocol). Currently possible forms:
//   DBNAME/USER/PASSWD
//   unix:SOCKPATH*DBNAME/USER/PASSWD
//   tcp:ADDR*DBNAME/USER/PASSWD
func (d *Driver) Open(uri string) (driver.Conn, error) {
	pd := strings.SplitN(uri, "*", 2)
	if len(pd) == 2 {
		// Parse protocol part of URI
		p := strings.SplitN(pd[0], ":", 2)
		if len(p) != 2 {
			return nil, errors.New("Wrong protocol part of URI")
		}
		d.proto = p[0]
		d.raddr = p[1]
		// Remove protocol part
		pd = pd[1:]
	}
	// Parse database part of URI
	dup := strings.SplitN(pd[0], "/", 3)
	if len(dup) != 3 {
		return nil, errors.New("Wrong database part of URI")
	}
	d.db = dup[0]
	d.user = dup[1]
	d.passwd = dup[2]

	// Establish the connection
	c := conn{mysql.New(d.proto, d.laddr, d.raddr, d.user, d.passwd, d.db)}
	for _, q := range d.initCmds {
		c.my.Register(q) // Register initialisation commands
	}
	if err := c.my.Connect(); err != nil {
		return nil, errFilter(err)
	}
	return &c, nil
}

func show_count(w http.ResponseWriter, r *http.Request) {
	log.Println("process count request")
	srv1.TimeNow = time.Now()

	db1 := mysql.New("tcp", "", *(srv1.addr), *(srv1.user), *(srv1.pass), *(srv1.dbname))
	err := db1.Connect()
	defer db1.Close()
	if err != nil {
		panic(err)
	}
	_, _, _ = db1.Query("set wait_timeout = 3600")
	_, _, _ = db1.Query("set interactive_timeout = 3600")

	//	     fmt.Fprintf(w, player_page,
	//	                 get_all_reg(&db1),
	//	                 pve_online_5(&db1),
	//	                 get_login_30(&db1));

	srv1.D帐户数 = get_all_reg(&db1)
	srv1.D角色数 = get_player(&db1)
	srv1.D活跃数 = pve_online_5(&db1)
	srv1.D登录_30 = get_login_30(&db1)
	srv1.D登录_60 = get_login_60(&db1)
	get_level_count(&db1, &srv1)

	log.Println("parse file: ", *cssFile)
	tmp, _ := template.ParseFiles(*cssFile)
	tmp.Execute(w, srv1)
}

// FetchMetrics interface for mackerelplugin
func (m MySQLPlugin) FetchMetrics() (map[string]interface{}, error) {
	db := mysql.New("tcp", "", m.Target, m.Username, m.Password, "")
	err := db.Connect()
	if err != nil {
		log.Fatalln("FetchMetrics (DB Connect): ", err)
		return nil, err
	}
	defer db.Close()

	stat := make(map[string]float64)
	m.fetchShowStatus(db, stat)

	if m.DisableInnoDB != true {
		m.fetchShowInnodbStatus(db, stat)
		m.fetchShowVariables(db, stat)
	}

	m.fetchShowSlaveStatus(db, stat)

	statRet := make(map[string]interface{})
	for key, value := range stat {
		statRet[key] = value
	}

	return statRet, err
}

func main() {
	user := "root"
	pass := "root"
	dbname := "test"

	db := mysql.New("tcp", "", "127.0.0.1:3306", user, pass, dbname)

	err := db.Connect()
	if err != nil {
		panic(err)
	}

	rows, res, err := db.Query("select 'hello world' as  FirstColumn,22 as SecondColumn limit  %d", 20)
	if err != nil {
		panic(err)
	}

	for _, row := range rows {
		for _, col := range row {
			if col == nil {
				// col has NULL value
			} else {
				// Do something with text in col (type []byte)
			}
		}

		// You may get values by column name
		first := res.Map("FirstColumn")
		//second := res.Map("SecondColumn")
		fmt.Printf(row.Str(first))
	}
}

func getAdmin(adminid int) (string, string) {
	db := mysql.New("tcp", "", "127.0.0.1:3306", dbusername, dbpassowrd, dbname)

	err := db.Connect()
	if err != nil {
		log.Panic(err)
	}
	defer db.Close()

	rows, res, err := db.Query("select * from user where uid=%d", adminid)
	if err != nil {
		log.Panic(err)
	}

	if len(rows) < 1 {
		log.Panic("rows error")
	}

	row := rows[0]

	first := res.Map("username")
	second := res.Map("password")

	username, password := row.Str(first), row.Str(second)
	return username, password
}

func get_reg() (int, int) {
	db := mysql.New("tcp", "", "117.135.154.58:3306", user, pass, dbname)
	err := db.Connect()
	var ret_all = 0
	var ret_player = 0
	if err != nil {
		panic(err)
		return ret_all, ret_player
	}
	defer db.Close()
	_, _, _ = db.Query("set wait_timeout = 3600")
	_, _, _ = db.Query("set interactive_timeout = 3600")

	rows, _, err := db.Query("select count(customer_id)-54 from customer.customer")
	if err != nil {
		panic(err)
	}
	for _, row := range rows {
		ret_all = row.Int(0)
	}
	rows, _, err = db.Query("select count(player_id) from player where vip>=1 and player_id>27048")
	if err != nil {
		panic(err)
	}
	for _, row := range rows {
		ret_player = row.Int(0)
	}
	return ret_all, ret_player
}

func FetchData(m *model.MysqlIns, socketfile string, cfg *base.Cfg) (err error) {
	defer func() {
		MysqlAlive(cfg, m, err == nil)
	}()

	//db := mysql.New("tcp", "", fmt.Sprintf("%s:%d", m.Host, m.Port), cfg.User, cfg.Pass)
	db := mysql.New("unix", "", socketfile, cfg.User, cfg.Pass)
	db.SetTimeout(500 * time.Millisecond)
	if err = db.Connect(); err != nil {
		return
	}
	defer db.Close()

	data := make([]*model.MetaData, 0)
	threadNums, err := collect.ProcessNum(db, cfg)
	if err != nil {
		return
	}
	data = append(data, threadNums)
	slaveState, err := collect.SlaveStatus(db, cfg)
	if err != nil {
		return
	}
	data = append(data, slaveState...)

	msg, err := SendData(data, cfg)
	if err != nil {
		return
	}
	log.Infof("Send response %v: %s", m, string(msg))
	return
}

func show_level(w http.ResponseWriter, r *http.Request) {
	log.Println("process level request")
	nav(w)
	db1 := mysql.New("tcp", "", *(srv1.addr), *(srv1.user), *(srv1.pass), *(srv1.dbname))
	err := db1.Connect()
	defer db1.Close()
	if err != nil {
		panic(err)
	}
	_, _, _ = db1.Query("set wait_timeout = 3600")
	_, _, _ = db1.Query("set interactive_timeout = 3600")

	get_level_count(&db1, &srv1)

	fmt.Fprintf(w, `<br>`)
	level_tmp := template.New("")
	level_tmp.Parse(`
	  <table width="307" height="414" border="1" cellspacing="0" bordercolor="#333366">
	  <tr>
		  <td><span class="style3">等级</span></td>
		  <td width="88"><span class="style5">  人数 &nbsp;</span></td>
		</tr>
	  {{range $k,$v := .D级别}}
	  <tr>
		  <td><span class="style3">级别 {{$k}}0-{{$k}}9 级</span></td>
		  <td width="88"><span class="style5">  {{$v}} &nbsp;</span></td>
		</tr>
		{{end}}
	  </table>
	`)
	level_tmp.Execute(w, srv1)
}

// MAIN
func main() {
	var err error
	var w io.Writer

	parseCommandLine()
	readConfigFile()

	raddr := fmt.Sprintf("%s:%d", hostname, port)
	db := mysql.New("tcp", "", raddr, username, password, database)
	db.Register("SET NAMES utf8")
	err = db.Connect()
	checkError(err)

	if *output == "" {
		w = os.Stdout
	} else {
		w, err = os.Create(*output)
		checkError(err)
	}

	fmt.Fprintf(w, "SET NAMES utf8;\n")
	fmt.Fprintf(w, "SET FOREIGN_KEY_CHECKS = 0;\n")

	tables := getTables(db)
	for _, table := range tables {
		dumpCreateTable(w, db, table)
		dumpTableData(w, db, table)
	}

	fmt.Fprintf(w, "SET FOREIGN_KEY_CHECKS = 1;\n")
}

func dbConn(obj_feed chan PerformanceStamp) {
	user := "root"
	pass := ""
	dbname := "multicdn"
	//proto  := "unix"
	//addr   := "/var/run/mysqld/mysqld.sock"
	proto := "tcp"
	addr := "127.0.0.1:3306"

	db := mysql.New(proto, "", addr, user, pass, dbname)

	fmt.Printf("Connect to %s:%s... ", proto, addr)

	checkError(db.Connect())
	printOK()

	go func() {
		idx := 0
		for obj := range obj_feed {
			db.Query("insert cdn_logs values ('%s', '%s', '%s', '%s')", obj.ts, obj.cdn, obj.ip, obj.url)
			idx++

			if idx%1000 == 0 {
				fmt.Printf("%d. records saved\n", idx)
			}
		}
	}()
}

func zip_around_10_miles(cur_zip string, limit int) []string {
	if cur_zip == "" {
		return nil
	}
	config, _ := toml.LoadFile("config.toml")
	mysql_user_name, _ := config.Get("mysql_user_name").(string)
	mysql_password, _ := config.Get("mysql_password").(string)
	mysql_db_name, _ := config.Get("mysql_db_name").(string)
	mysql_url, _ := config.Get("mysql_url").(string)

	db := mysql.New("tcp", "", mysql_url, mysql_user_name, mysql_password, mysql_db_name)
	err := db.Connect()
	if err != nil {
		panic(err)
	}
	rows, _, err := db.Query("SELECT `zip_distance`.* FROM `zip_distance` WHERE `zip_distance`.`PrimaryZip` = " + cur_zip + " LIMIT 1")
	if err != nil {
		panic(err)
	}
	var zips string
	for _, row := range rows {
		val1 := row[2].([]byte)
		row = row
		zips = string(val1)
	}
	return strings.Split(zips, ",")
}

// Establish a connection using URI of following syntax:
//   DBNAME/USER/PASSWD
//   unix:SOCKPATH*DBNAME/USER/PASSWD
//   tcp:ADDR*DBNAME/USER/PASSWD
func (d *drv) Open(uri string) (driver.Conn, error) {
	pd := strings.SplitN(uri, "*", 2)
	if len(pd) == 2 {
		// Parse protocol part of URI
		p := strings.SplitN(pd[0], ":", 2)
		if len(p) != 2 {
			return nil, errors.New("Wrong protocol part of URI")
		}
		d.proto = p[0]
		d.raddr = p[1]
		// Remove protocol part
		pd = pd[1:]
	}
	// Parse database part of URI
	dup := strings.SplitN(pd[0], "/", 3)
	if len(dup) != 3 {
		return nil, errors.New("Wrong database part of URI")
	}
	d.db = dup[0]
	d.user = dup[1]
	d.passwd = dup[2]

	// Establish the connection
	mc := mysql.New(d.proto, d.laddr, d.raddr, d.user, d.passwd, d.db)
	c := conn{mc, make(map[string]driver.Stmt)}
	if err := c.my.Connect(); err != nil {
		return nil, err
	}
	return &c, nil
}

func checkUptime() *checkers.Checker {
	opts := connectionOpts{}
	psr := flags.NewParser(&opts, flags.Default)
	_, err := psr.Parse()
	if err != nil {
		os.Exit(1)
	}

	db := mysql.New("tcp", "", fmt.Sprintf("%s:%s", opts.mysqlSetting.Host, opts.mysqlSetting.Port), opts.mysqlSetting.User, opts.mysqlSetting.Pass, "")
	err = db.Connect()
	if err != nil {
		return checkers.Critical("couldn't connect DB")
	}
	defer db.Close()

	rows, res, err := db.Query("SHOW GLOBAL STATUS LIKE 'Uptime'")
	if err != nil {
		return checkers.Critical("couldn't execute query")
	}

	idxValue := res.Map("Value")
	Uptime := rows[0].Int64(idxValue)

	if opts.Crit > 0 && Uptime < opts.Crit {
		return checkers.Critical(fmt.Sprintf("up %s < %s", uptime2str(Uptime), uptime2str(opts.Crit)))
	} else if opts.Warn > 0 && Uptime < opts.Warn {
		return checkers.Warning(fmt.Sprintf("up %s < %s", uptime2str(Uptime), uptime2str(opts.Warn)))
	}
	return checkers.Ok(fmt.Sprintf("up %s", uptime2str(Uptime)))
}

func CreateCon(addr string, user string, passwd string, dbname string) (con mysql.Conn) {
	proto := "tcp"
	con = mysql.New(proto, "", addr, user, passwd, dbname)
	fmt.Printf("Connect to %s:%s... ", proto, addr)
	CheckError(con.Connect())
	PrintOK()
	return
}