Golang连接&操作mysql数据库-蒲公英云

文章目录

一、Golang连接&操作mysql数据库
- 1.1 前置步骤
- 1.2 测试查询和插入数据
- 1.3 查询一个可空的列值
- 1.4 处理多结果集
- 1.5 Prepared statement

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

1.1 前置步骤

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

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
}