Golang连接&操作mysql数据库

一、Golang连接&操作mysql数据库

1.1 前置步骤

1. 这里使用github.com/go-sql-driver/mysql驱动包，需要导入
2. 需要有一个数据库，创库创表用于测试。

DROP TABLE IF EXISTS album;
CREATE TABLE album (
  id         INT AUTO_INCREMENT NOT NULL,
  title      VARCHAR(128) NOT NULL,
  artist     VARCHAR(255) NOT NULL,
  price      DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO album 
  (title, artist, price) 
VALUES 
  ('Blue Train', 'John Coltrane', 56.99),
  ('Giant Steps', 'John Coltrane', 63.99),
  ('Jeru', 'Gerry Mulligan', 17.99),
  ('Sarah Vaughan', 'Sarah Vaughan', 34.98);

1.2 测试查询和插入数据

package main

import (
	"database/sql"
	"fmt"
	"log"

	"github.com/go-sql-driver/mysql"
)

var db *sql.DB

type Album struct {
	ID     int64
	Title  string
	Artist string
	Price  float32
}

func main() {
	cfg := mysql.Config{
		User:                 "root",      //os.Getenv("DBUSER"),
		Passwd:               "xwphs1234*", //os.Getenv("DBPASS"),
		Net:                  "tcp",
		Addr:                 "143.30.158.11:3306",
		DBName:               "recordings",
		AllowNativePasswords: true,
	}
	// get a database handle
	var err error
	db, err = sql.Open("mysql", cfg.FormatDSN())
	if err != nil {
		log.Fatal(err)
	}
	pingErr := db.Ping()
	if pingErr != nil {
		log.Fatal(pingErr)
	}
	fmt.Println("Connected!")

	albums, err := albumsByArtist("John Coltrane")
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Albums found: %v\n", albums)

	// Hard-code ID 2 to test the query
	alb, err := albumByID(2)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Album found: %v\n", alb)

	// Album instance
	alb_instance := Album{
		Title:  "The Modern Sound of Betty Carter",
		Artist: "Betty Carter",
		Price:  49.99,
	}
	// Add album data to the database
	albID, err := addAlbum(alb_instance)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("ID of added album: %v\n", albID)
}

// albumsByArtist queries for albums that have specified name.
func albumsByArtist(name string) ([]Album, error) {
	// An albums slice to hold data from returned rows.
	var albums []Album
	rows, err := db.Query("SELECT * FROM album WHERE artist = ?", name)
	if err != nil {
		return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
	}
	defer rows.Close()
	// Loop through rows, using Scan to assign column data to struct fields.
	for rows.Next() {
		var alb Album
		if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
			return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
		}
		albums = append(albums, alb)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
	}
	return albums, nil
}

// albumByID queries for the album with the specified ID
func albumByID(id int64) (Album, error) {
	// An album to hold data from returned row.
	var alb Album
	row := db.QueryRow("SELECT * FROM album where id = ?", id)
	if err := row.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
		if err == sql.ErrNoRows {
			return alb, fmt.Errorf("albumByID %d: no such album", id)
		}
		return alb, err
	}
	return alb, nil
}

// addAlbum adds the specified album to the database
// returning the album ID of the new entry
func addAlbum(alb Album) (int64, error) {
	result, err := db.Exec("INSERT INTO album (title, artist, price) VALUES (?, ?, ?)", alb.Title, alb.Artist, alb.Price)
	if err != nil {
		return 0, fmt.Errorf("addAlbum: %v\n", err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("addAlbum: %v\n", err)
	}
	return id, nil
}

1.3 查询一个可空的列值

使用下图这种可以用来表示一个可空的值。

这里写了一个函数priceByID，返回一个指定ID的price值。如果price值为空则返回-1。
注意：这里price列值可以为空，所以使用sql.NullFloat64来接收。再通过price.Valid判断值是否为null。

// priceByID get the price of album through specified ID.
// if the return value is -1, it represents the column value is null.
func priceByID(id int64) (float64, error) {
	// Price is an nullable column value, so we use sql.NullFloat64 to adopt it.
	var price sql.NullFloat64

	if err := db.QueryRow("SELECT price FROM album WHERE id = ?", id).Scan(&price); err != nil {
		if err == sql.ErrNoRows {
			return -1, fmt.Errorf("priceByID %d: no such album", id)
		}
		return -1, err
	}
	if price.Valid {
		return price.Float64, nil
	}
	return -1, nil
}

然后是调用函数priceByID。这段接在1.2 main函数末尾

	// Get the price of album with ID 2
	price, err := priceByID(2)
	if err != nil {
		log.Fatal(err)
	}
	if price == -1 {
		fmt.Println("This column value is null")
		return
	}
	fmt.Printf("The price of album with ID 2 is %v\n", price)

1.4 处理多结果集

可以一次处理多个查询语句，然后放到结果集中。可以使用Rows.NextResultSet切换到下一个结果集。

这里db.Query一次处理两个查询语句，结果放到两个结果集中。

大坑：github.com/go-sql-driver/mysql 这个驱动的默认多语句执行是关掉的，所以数据库连接参数要指定：
MultiStatements: true

// testMultipleResultSet is a test that query to return multiple result set
func testMultipleResultSet() {
	rows, err := db.Query("SELECT * FROM album; SELECT * FROM song;")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	// Loop through the first result set
	var albums []Album
	for rows.Next() {
		var alb Album
		if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
			log.Fatal(err)
		}
		albums = append(albums, alb)
	}
	fmt.Printf("Album found: %v\n", albums)

	// Advance to next result set
	rows.NextResultSet()

	// Loop through the second result set
	type Song struct {
		id     int64
		name   string
		artist string
	}
	var songs []Song
	for rows.Next() {
		var s Song
		if err := rows.Scan(&s.id, &s.name, &s.artist); err != nil {
			log.Fatal(err)
		}
		songs = append(songs, s)
	}
	fmt.Printf("Song found: %v\n", songs)

}

1.5 Prepared statement

使用prepared statement使代码跑的更快一点。因为它避免了每次执行数据库操作时重复创建statement的开销。

// preparedStatements retrieves the specified album by ID.
// prepared statement run a bit faster by avoiding the overhead of re-creating statement each time performs database operation.
func preparedStatements(id int) (Album, error) {
	// Define a prepared statement
	stmt, err := db.Prepare("SELECT * FROM album WHERE id = ?")
	if err != nil {
		log.Fatal(err)
	}
	var alb Album

	// Execute the parpared statement, passing an id value for the parameter where placeholder is ?.
	err = stmt.QueryRow(id).Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price)
	if err != nil {
		if err == sql.ErrNoRows {
			return alb, fmt.Errorf("preparedStatements %d: no such album", id)
		}
		return alb, err
	}
	return alb, nil
}