Go操作MySQL数据库

2019独角兽企业重金招聘Python工程师标准>>>

Go操作MySQL数据库

主要的结构体DB和基于该结构体的方法

type DB
func (db DB) Begin() (Tx, error)
func (db *DB) Close() error
func (db *DB) Driver() driver.Driver
func (db *DB) Exec(query string, args ...interface{}) (Result, error)
func (db *DB) Prepare(query string) (*Stmt, error)
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
func (db *DB) QueryRow(query string, args ...interface{}) *Row

Query && QueryRow

如下代码示例，

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
)

func main() {

	//完整的数据库连接字符串
	//username:password@protocol(address)/dbname?param=value
	db, err := sql.Open("mysql", "root:034039@tcp(127.0.0.1:3306)/account")
	defer db.Close()

	if err != nil {
		log.Println(err)
	}

	rows, err := db.Query("SELECT vin,no FROM vehicle_gps where type= ?", 2)

	if err != nil {
		log.Println(err)
	}

	var vin, no string
	for rows.Next() {
		err := rows.Scan(&vin, &no) //获取字段,select几个字段就传入几个变量
		if err != nil {
			log.Println(err)
		}
		fmt.Println(vin, no)
	}

	// 查询单条记录
	singledRow := db.QueryRow("SELECT id,type FROM vehicle_gps where id = ?", 3901)
	var id, kind int
	singledRow.Scan(&id, &kind)
	fmt.Println(id, kind)
}

Exec

如下代码示例，

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"math/rand"
	"time"
)

func main() {

	//完整的数据库连接字符串
	//username:password@protocol(address)/dbname?param=value
	db, err := sql.Open("mysql", "root:034039@tcp(127.0.0.1:3306)/account")

	if err != nil {
		log.Println(err)
	}

	//在这里进行一些数据库操作
	defer db.Close()

	//db.Exec 执行查询
	//rand.Intn(100) 返回100 以内随机数
	rand.Seed(time.Now().Unix()) //设置随机数的种子
	random := rand.Intn(100)

	result, err := db.Exec("update vehicle_gps set type = ? where id = ?", random, 8033)
	if err != nil {
		fmt.Println("db.Exec error")
		log.Println(err)
	}
	n, _ := result.RowsAffected()
	fmt.Println(n)
}

Prepare

如下代码示例

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"math/rand"
	"time"
)

func main() {
	db, err := sql.Open("mysql", "root:034039@tcp(127.0.0.1:3306)/account")
	defer db.Close()

	if err != nil {
		log.Println(err)
	}

	stmt, err := db.Prepare("update vehicle_gps set type = ? where vin = ?")
	defer stmt.Close()

	if err != nil {
		log.Println(err)
	}
	//rand.Intn(10) 返回10 以内随机数
	rand.Seed(time.Now().Unix()) //设置随机数的种子
	random := rand.Intn(100)

	//Exec方法返回的是sql.Result对象
	result, err := stmt.Exec(random, "JM7CW09FXC0106372")

	if err != nil {
		log.Println(err)
	}

	rows, err := db.Query("select type from vehicle_gps where vin = ?", "JM7CW09FXC0106372")

	if err != nil {
		log.Println(err)
	}

	var t int
	for rows.Next() {
		rows.Scan(&t)
		fmt.Println(t)
		if t != random {
			panic("update type error")
		}
	}

	n, _ := result.RowsAffected()
	fmt.Println(n)
}

事务的支持

如下代码示例

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"math/rand"
	"time"
)

func main() {
	db, err := sql.Open("mysql", "root:034039@tcp(127.0.0.1:3306)/account")
	defer db.Close()

	if err != nil {
		log.Println(err)
	}

	tx, err := db.Begin() //开启一个事物
	defer tx.Commit()     //一定要commit

	if err != nil {
		log.Println(err)
	}

	stmt, err := tx.Prepare("update vehicle_gps set type = ? where vin = ?")
	defer stmt.Close()

	if err != nil {
		log.Println(err)
	}
	//rand.Intn(10) 返回10 以内随机数
	rand.Seed(time.Now().Unix()) //设置随机数的种子
	random := rand.Intn(100)
	result, err := stmt.Exec(random, "JM7CW09FXC0106372")

	if err != nil {
		log.Println(err)
	}
	fmt.Println(random)

	if n, _ := result.RowsAffected(); n == 2 {
		fmt.Println("rollback")
		tx.Rollback() //回滚事务后同时也要提交
	}
}

=========END=========

转载于:https://my.oschina.net/xinxingegeya/blog/732188