Golang连接&操作mysql数据库

ゝ一纸荒年。 2023-09-29 18:30 6阅读 0赞

#### 文章目录 ####

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

## 一、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 查询一个可空的列值 ###

使用下图这种可以用来表示一个可空的值。  
![在这里插入图片描述][e27854c1039e4d7ba894bc04184bc62d.png]

这里写了一个函数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
    }

[e27854c1039e4d7ba894bc04184bc62d.png]: https://img-blog.csdnimg.cn/e27854c1039e4d7ba894bc04184bc62d.png