How to connect golang to mysql database

golang operation mysql

Installation

go get "github.com/go-sql-driver/mysql"
go get "github.com/jmoiron/sqlx"

Connect database

var Db *sqlx.DB
db, err := sqlx.Open("mysql","username:password@tcp(ip:port)/database?charset=utf8")
Db = db

Connection 2

package main
import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB  //全局对象db

func initDB() (err error) {
  db, err = sql.Open("mysql","root:admin123@tcp(127.0.0.1:3306)/dududu?charset=utf8")
  if err!=nil{
    return err
  }
  err = db.Ping()  //校验数据库连接
  if err!=nil{
    return err
  }
  return nil
}

type beautiful struct {
	spu_id string
	title string
	price string
}

func queryRowDemo()  {
	sqlStr :="select spu_id,title,price from dududu_shops where id = ?"
	var u beautiful
	err:=db.QueryRow(sqlStr,101).Scan(&u.spu_id,&u.title,&u.price)
	if err!=nil{
		fmt.Println("2",err)
	}
	fmt.Println(u)
}


func main()  {
	err:=initDB()
	if err!=nil{
		fmt.Println("1",err)
		return
	}
	queryRowDemo()
}

Handle Types

The sqlx design and database/sql usage methods are the same. Contains 4 main handle types:

• sqlx.DB - similar to sql.DB, represents the database.

• sqlx.Tx - Similar to sql.Tx, it represents things.

• sqlx.Stmt - Similar to sql.Stmt, it represents prepared statement.

• sqlx.NamedStmt - represents prepared statement (supports named parameters)

All handler types provide compatibility with database/sql, This means that when you call sqlx.DB.Query, you can directly replace it with sql.DB.Query. This makes sqlx easy to add to existing database projects.

In addition, sqlx has two cursor types:

• sqlx.Rows - Similar to sql.Rows, Queryx returns.

• sqlx.Row - Similar to sql.Row, QueryRowx returns.

Compared with the database/sql method, there is a new syntax, which means that the obtained data can be directly converted into a structure.

• Get(dest interface{}, …) error

• Select(dest interface{}, …) error

Create a table

All the following examples use the following table structure as the basis for operation.

CREATE TABLE `userinfo` (
    `uid` INT(10) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(64)  DEFAULT NULL,
    `password` VARCHAR(32)  DEFAULT NULL,
    `department` VARCHAR(64)  DEFAULT NULL,
    `email` varchar(64) DEFAULT NULL,
    PRIMARY KEY (`uid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

Exec uses

Exec and MustExec will take out a connection from the connection pool and then perform the corresponding query operation. For drivers that do not support ad-hoc query execution, a prepared statement is created behind the operation execution. This connection will be returned to the connection pool before the result is returned.

It should be noted that different database types use different placeholders. What does mysql use? as a placeholder symbol.

• MySQL used?

• PostgreSQL uses 1,1,2 etc.

• SQLite uses? Or $1

• ##Oracle use: name

Exec to add or delete this example

Query syntax using Query will be mentioned later

package main

import (
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "fmt"
)

var Db *sqlx.DB

func init()  {
    db, err := sqlx.Open("mysql", "stu:1234qwer@tcp(10.0.0.241:3307)/test?charset=utf8")
    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }
    Db = db
}

func main()  {
    result, err := Db.Exec("INSERT INTO userinfo (username, password, department,email) VALUES (?, ?, ?,?)","wd","123","it","wd@163.com")
    if err != nil{
        fmt.Println("insert failed,error： ", err)
        return
    }
    id,_ := result.LastInsertId()
    fmt.Println("insert id is :",id)
    _, err1 := Db.Exec("update userinfo set username = ? where uid = ?","jack",1)
    if err1 != nil{
        fmt.Println("update failed error:",err1)
    } else {
        fmt.Println("update success!")
    }
    _, err2 := Db.Exec("delete from userinfo where uid = ? ", 1)
    if err2 != nil{
        fmt.Println("delete error:",err2)
    }else{
        fmt.Println("delete success")
    }

}
//insert id is : 1
//update success!
//delete success

sql Prepared Statements

For most databases, when a query is executed, the statement inside the sql statement database has already been declared, and its statement is in the database , we can declare it ahead of time for reuse elsewhere.

stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = stmt.QueryRow(65)
 
tx, err := db.Begin()
txStmt, err := tx.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = txStmt.QueryRow(852)

Of course sqlx also provides Preparex() for expansion, which can be directly used for structure conversion

stmt, err := db.Preparex(`SELECT * FROM place WHERE telcode=?`)
var p Place
err = stmt.Get(&p, 852)

Query

row results are obtained by using Rows in database/sql Obtain. Query returns a sql.Rows object and an error object.

When using it, Rows should be treated as a cursor rather than a series of results. Although the methods of database driver caching are different, one column of results is obtained each time through Next() iteration, which can effectively limit the memory usage when the query results are very large. Scan() uses reflect to map each column of SQL results to go. Language data types such as string, []byte, etc. If you do not iterate through all rows results, be sure to call rows.Close() before returning the connection to the connection pool.

The error returned by Query may occur when the server is preparing the query, or it may occur when the query statement is executed. For example, even if the database tries 10 times to find or create an available connection, it is possible to get a bad connection from the connection pool. Usually, errors mainly originate from errors in SQL statements, similar matching errors, and errors in domain names or table names.

In most cases, Rows.Scan() will make a copy of the data obtained from the driver regardless of how the driver uses the cache. The special type sql.RawBytes can be used to obtain a zero-copy slice byte from the data returned by the driver. The next time Next is called, the value will be invalid because the memory it points to has been overwritten by the driver with other data.

The connection used by Query is released after all rows are traversed through Next() or after rows.Close() is called.

Example:

package main

import (
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "fmt"
)

var Db *sqlx.DB

func init()  {
    db, err := sqlx.Open("mysql", "stu:1234qwer@tcp(10.0.0.241:3307)/test?charset=utf8")
    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }
    Db = db
}

func main()  {
    rows, err := Db.Query("SELECT username,password,email FROM userinfo")
    if err != nil{
        fmt.Println("query failed,error： ", err)
        return
    }
    for rows.Next() {  //循环结果
        var username,password,email string
        err = rows.Scan(&username, &password, &email)
        println(username,password,email)
    }
    
}
//wd 123 wd@163.com
//jack 1222 jack@165.com

Queryx

Queryx behaves very similarly to Query, but returns a sqlx.Rows object, supports extended scan behavior, and can structure the data. body conversion.

Example:

package main

import (
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "fmt"
)

var Db *sqlx.DB

type stu struct {
    Username string   `db:"username"`
    Password string      `db:"password"`
    Department string  `db:"department"`
    Email string        `db:"email"`
}

func init()  {
    db, err := sqlx.Open("mysql", "stu:1234qwer@tcp(10.0.0.241:3307)/test?charset=utf8")
    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }
    Db = db
}

func main()  {
    rows, err := Db.Queryx("SELECT username,password,email FROM userinfo")
    if err != nil{
        fmt.Println("Qeryx failed,error： ", err)
        return
    }
    for rows.Next() {  //循环结果
        var stu1 stu
        err = rows.StructScan(&stu1)// 转换为结构体
        fmt.Println("stuct data：",stu1.Username,stu1.Password)
    }
}
//stuct data： wd 123
//stuct data： jack 1222

QueryRow and QueryRowx

QueryRow and QueryRowx both obtain a piece of data from the database, but QueryRowx provides a scan extension that can directly convert the result into a structure .

package main

import (
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "fmt"
)

var Db *sqlx.DB

type stu struct {
    Username string   `db:"username"`
    Password string      `db:"password"`
    Department string  `db:"department"`
    Email string        `db:"email"`
}

func init()  {
    db, err := sqlx.Open("mysql", "stu:1234qwer@tcp(10.0.0.241:3307)/test?charset=utf8")
    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }
    Db = db
}

func main()  {
    row := Db.QueryRow("SELECT username,password,email FROM userinfo where uid = ?",1) // QueryRow返回错误，错误通过Scan返回
    var username,password,email string
    err :=row.Scan(&username,&password,&email)
    if err != nil{
        fmt.Println(err)
    }
    fmt.Printf("this is QueryRow res:[%s:%s:%s]\n",username,password,email)
    var s stu
    err1 := Db.QueryRowx("SELECT username,password,email FROM userinfo where uid = ?",2).StructScan(&s)
    if err1 != nil{
        fmt.Println("QueryRowx error :",err1)
    }else {
        fmt.Printf("this is QueryRowx res:%v",s)
    }
}
//this is QueryRow res:[wd:123:wd@163.com]
//this is QueryRowx res:{jack 1222  jack@165.com}

Get and Select (very commonly used)

Get and Select is a very time-saving extension, which can directly assign the result to the structure, and encapsulates StructScan internally for conversion. Use the Get method to obtain a single result and then use the Scan method, and use the Select method to obtain slices of results.

Example:

package main

import (
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "fmt"
)

var Db *sqlx.DB

type stu struct {
    Username string   `db:"username"`
    Password string      `db:"password"`
    Department string  `db:"department"`
    Email string        `db:"email"`
}

func init()  {
    db, err := sqlx.Open("mysql", "stu:1234qwer@tcp(10.0.0.241:3307)/test?charset=utf8")
    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }
    Db = db
}

func main()  {
    var stus []stu
    err := Db.Select(&stus,"SELECT username,password,email FROM userinfo")
    if err != nil{
        fmt.Println("Select error",err)
    }
    fmt.Printf("this is Select res:%v\n",stus)
    var s stu
    err1 := Db.Get(&s,"SELECT username,password,email FROM userinfo where uid = ?",2)
    if err1 != nil{
        fmt.Println("GET error :",err1)
    }else {
        fmt.Printf("this is GET res:%v",s)
    }
}
//this is Select res:[{wd 123  wd@163.com} {jack 1222  jack@165.com}]
//this is GET res:{jack 1222  jack@165.com}

Transactions

Transaction operations are implemented through three methods:

Begin(): Open a transaction

Commit(): Commit transaction (execute sql)

Rollback(): Rollback

Usage process:

tx, err := db.Begin()
err = tx.Exec(...)
err = tx.Commit()

//或者使用sqlx扩展的事务
tx := db.MustBegin()
tx.MustExec(...)
err = tx.Commit()

为了维持一个持续的连接状态，Tx对象必须绑定和控制单个连接。在整个生命周期期间，一个Tx将保持连接，直到调用commit或Rollback()方法将其释放。必须非常谨慎地调用这些函数，否则连接将一直被占用，直到被垃圾回收。

使用示例：

package main

import (
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "fmt"
)

var Db *sqlx.DB

func init()  {
    db, err := sqlx.Open("mysql", "stu:1234qwer@tcp(10.0.0.241:3307)/test?charset=utf8")
    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }
    Db = db
}

func main()  {
    tx, err := Db.Beginx()
    _, err = tx.Exec("insert into userinfo(username,password) values(?,?)", "Rose","2223")
    if err != nil {
        tx.Rollback()
    }
    _, err = tx.Exec("insert into userinfo(username,password) values(?,?)", "Mick",222)
    if err != nil {
        fmt.Println("exec sql error:",err)
        tx.Rollback()
    }
    err = tx.Commit()
    if err != nil {
        fmt.Println("commit error")
    }

}

连接池设置

当连接池中没有可用空闲连接时，连接池会自动创建连接，并且其增长是无限制的。最大连接数可以通过调用DB.SetMaxOpenConns来设置。未使用的连接标记为空闲，如果不需要则关闭。为了减少连接的开启和关闭次数，可以利用DB.SetMaxIdleConns方法来设定最大的空闲连接数。

注意：该设置方法golang版本至少为1.2

• DB.SetMaxIdleConns(n int) 设置最大空闲连接数

• DB.SetMaxOpenConns(n int) 设置最大打开的连接数

• DB.SetConnMaxIdleTime(time.Second*10) 间隔时间

示例：

package main

import (
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "fmt"
)

var Db *sqlx.DB

func init()  {
    db, err := sqlx.Open("mysql", "stu:1234qwer@tcp(10.0.0.241:3307)/test?charset=utf8")
    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }
    Db = db
    Db.SetMaxOpenConns(30)
    Db.SetMaxIdleConns(15)

}

案例使用

var Db *sqlx.DB
db, err := sqlx.Open("mysql","root:admin123@tcp(127.0.0.1:3306)/dududu?charset=utf8")
Db = db

rows, err := Db.Query("SELECT spu_id,title,price FROM dududu_shops")

if err != nil{
  fmt.Println("query failed,error： ", err)
  return
}
for rows.Next() {  //循环结果
  var spu_id,title,price string
  err = rows.Scan(&spu_id, &title, &price)
  println(spu_id,title,price)
}

The above is the detailed content of How to connect golang to mysql database. For more information, please follow other related articles on the PHP Chinese website!