1.1 前置步骤
github.com/go-sql-driver/mysqlDROP 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 查询一个可空的列值
使用下图这种可以用来表示一个可空的值。
sql.NullFloat64price.Valid// 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/mysqlMultiStatements: 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
}