How to operate MySQL database in Go language

The Go language is an open source programming language that makes it easy to construct simple, reliable and efficient software. go language tutorial column will introduce to you how to operate the MySQL database in the go language.

Go language to operate MySQL database:

Install the driver for go to operate MySQL

go get -u -v github.com/go-sql-driver/mysql

go Operating MySQL database

Guide package

import _ "github.com/go-sql-driver/mysql"

The program only needs to use database/sql when operating the database, and does not need to use the database driver directly, so the program imports the database driver Set the name of this package to underscore.

Connect to the database using the sql.Open() method. The first parameter of the open() method is the driver name, and the second parameter is the connection string. The format is: username:password@tcp(ip :port)/database name? Encoding method, the return value is the connection object and error information, for example:

conn,err := sql.Open("mysql","root:123456@tcp(127.0.0.1:3306)/test?charset=utf8")
defer conn.Close()//随手关闭数据库是个好习惯

Perform database operation.

The main methods of the database are:

Query executes the query operation of the database, such as a Select statement, and the return data type is *Rows

QueryRow executes the database to return at most one piece of data. The return data type is *Row

Exec. The execution number does not return any database statement of rows, such as delete operation

PrePare prepares a database query operation and returns a *Stmt for subsequent query or Exec. This Stmt can be executed multiple times, or executed concurrently

Create table

The exec function is as follows:

func (db *DB) Exec(query string, args ...interface{}) (Result, error)

The method of creating a table is also Exec() , the parameter is the SQL statement, and the return value is the result set and error message.

The result contains:

type Result interface {
	LastInsertId() (int64, error)
	RowsAffected() (int64, error)
}

RowsAffected() function, which can obtain the impact on the database after successfully executing SQL number of rows.

res ,err:= conn.Exec("create table user(name VARCHAR(40),pwd VARCHAR(40))")
beego.Info("create table result=",res.,err)

Add, delete, and modify operations

Exec() is used to execute add, delete, and modify operation statements. The parameters are SQL statements. The return value is the result set and error information. By comparing the results Set judgment and obtain execution result information. Taking inserting data as an example, the code is as follows:

res,_:=stmt.Exec("insert user(name,pwd) values (?,?)","tony","tony")
count,_:=res.RowsAffected()
this.Ctx.WriteString(strconv.Itoa(int(count)))

Query operation

The functions used are Query() and QueryRow.

func (db *DB) QueryRow(query string, args ...interface{}) *Row
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

Use QueryRow to implement a single row Query can determine that the query result of the SQL statement is a record. Use the Scan() function to extract the field values ​​in the result in sequence.

Use Query to implement multi-row data query, and the return value is the query result set and error information. Take out the data through the next and Scan functions together

The code is as follows:

Single row query:

row:= conn.QueryRow(`select * from user where userName = "wyj"`)
var name,pwd string
row.Scan(&name,&pwd)
beego.Info(name,"------",pwd)

Multiple row query:

data ,err :=conn.Query("SELECT name from user")
	var userName string
	if err == nil{
		for data.Next(){
			data.Scan(&userName)
			beego.Info(userName)
		}
	}

All codes

//连接数据库
conn,err := sql.Open("mysql","root:123456@tcp(127.0.0.1:3306)/testtest?charset=utf8")
	if err != nil{
		beego.Info("链接失败")
	}
	defer conn.Close()
//建表
	res ,err:= conn.Exec("create table user(userName VARCHAR(40),passwd VARCHAR(40))")
	beego.Info("create table result=",res,err)
//插入数据
    res,err =conn.Exec("insert user(userName,passwd) values(?,?)","itcast","heima")
	beego.Info(res,err)
//单行查询
	row:= conn.QueryRow(`select * from user where userName = "wyj"`)
	var name,pwd string
	row.Scan(&name,&pwd)
	beego.Info(name,"------",pwd)
//多行查询
	data ,err :=conn.Query("SELECT userName from user")
	var userName string
	if err == nil{
		for data.Next(){
			data.Scan(&userName)
			beego.Error(userName)
		}
	}

For more go language knowledge, please pay attention to the go language tutorial column on the php Chinese website.

The above is the detailed content of How to operate MySQL database in Go language. For more information, please follow other related articles on the PHP Chinese website!