Go learning notes - using MySQL database

2017-12-28 15:17:31 

 

At present, there are many drivers supporting MySQL in Go, some of which support database/sql standards, and some of which use their own implementation interfaces. The commonly used ones are as follows:

 

  1. https://github.com/Go-SQL-Driver/MySQL supports database/sql, all written in go.
  2. Https://github.com/ziutek/mysql supports database/sql and also supports user-defined interfaces, all written in go.
  3. Https://github.com/philio/gommysql does not support database/sql. The user-defined interface is written in go.

 

The next example mainly takes the first driver as an example, and recommends you to adopt it. The main reason is that this driver is relatively new and well maintained. It fully supports database/sql interface, keepalive and long connection.

 

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/Go-SQL-Driver/MySQL"
	"os"
)

func checkErr(err error) {
	if err != nil {
		fmt.Println("Error is ", err)
		os.Exit(-1)
	}
}

// The sql.Open() function is used to open the database driver. mysql is registered in the go SQL driver. The second parameter is DNS, which configures the database information
// The db.Prepare() function is used to return the sql operation to be performed and the prepared status
// The db.Query() function is used to directly execute Sql and return Rows results
// The stmt.Exec() function is used to execute the SQL prepared by stmt

func main() {
	// sql.Open("mysql", "user name: password @ tcp(IP: port) / database? charset=utf8")
	db, err := sql.Open("mysql", "root:xxxxx@tcp(xxxxxxxx:3306)/test?charset=utf8")
	checkErr(err)

	fmt.Println("Linked database test")

	//insert
	stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
	checkErr(err)
	res, err := stmt.Exec("XXXXX", "Gopher", "2012-12-09")
	checkErr(err)
	id, err := res.LastInsertId()
	checkErr(err)
	fmt.Println(id)

	//Update data
	stmt, err := db.Prepare("update userinfo set username=? ,departname=? where id=?")
	checkErr(err)
	res, err := stmt.Exec("testusername", "testdepartname", id)
	checkErr(err)
	affect, err := res.RowsAffected()
	checkErr(err)
	fmt.Println(affect)

	//Query data
	rows, err := db.Query("select * from userinfo")
	checkErr(err)

	for rows.Next() {
		var id int
		var username string
		var departname string
		var created string
		err = rows.Scan(&id, &username, &departname, &created)
		checkErr(err)
		fmt.Println(id)
		fmt.Println(username)
		fmt.Println(departname)
		fmt.Println(created)
	}

}

Tags: Go SQL Database MySQL github

Posted on Tue, 05 May 2020 14:55:35 -0400 by rharden