萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

Golang 連接 MySQL 實現 CRUD 操作完整教程

Go 語言操作 MySQL 實戰:database/sql + go-sql-driver/mysql 最佳實踐

Go 標準庫 database/sql 定義統一接口,配合 go-sql-driver/mysql 驅動實現高性能、類型安全的數據庫操作。本文提供從驅動安裝到增刪改查的完整代碼範例。

一、環境準備


# 下載驅動
go get github.com/go-sql-driver/mysql
# 若報 GOPATH 未設置
export GOPATH=$HOME/go
go get github.com/go-sql-driver/mysql

二、建表腳本


CREATE TABLE IF NOT EXISTS `test`.`user` (
  `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用戶編號',
  `user_name` VARCHAR(45) NOT NULL COMMENT '用戶名稱',
  `user_age` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用戶年齡',
  `user_sex` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用戶性別',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用戶表';

三、數據庫連接


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

dsn := "user:password@tcp(localhost:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
db, err := sql.Open("mysql", dsn)
if err != nil { panic(err) }
defer db.Close()

// 連接池調優
db.SetMaxOpenConns(100)
db.SetMaxIdleConns(20)
db.SetConnMaxLifetime(time.Hour)

DSN 格式變體:

  • user@unix(/path/to/socket)/dbname
  • user:pass@tcp(host:port)/dbname?charset=utf8mb4(最常用)
  • user:pass@tcp([ipv6:addr]:port)/dbname

四、CRUD 操作封裝

1. 插入


func insertUser(db *sql.DB, name string, age, sex int) (int64, error) {
    stmt, err := db.Prepare(`INSERT INTO user (user_name, user_age, user_sex) VALUES (?, ?, ?)`)
    if err != nil { return 0, err }
    defer stmt.Close()

    res, err := stmt.Exec(name, age, sex)
    if err != nil { return 0, err }
    return res.LastInsertId()
}

2. 查詢(映射到 Struct/Map)


type User struct {
    ID   int    `json:"user_id"`
    Name string `json:"user_name"`
    Age  int    `json:"user_age"`
    Sex  int    `json:"user_sex"`
}

func queryUsers(db *sql.DB) ([]User, error) {
    rows, err := db.Query("SELECT user_id, user_name, user_age, user_sex FROM user")
    if err != nil { return nil, err }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Age, &u.Sex); err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    return users, rows.Err()
}

// 動態列映射到 Map(適合未知結構)
func queryToMap(db *sql.DB) ([]map[string]string, error) {
    rows, err := db.Query("SELECT * FROM user")
    if err != nil { return nil, err }
    defer rows.Close()

    cols, _ := rows.Columns()
    scanArgs := make([]interface{}, len(cols))
    values := make([]interface{}, len(cols))
    for i := range values { scanArgs[i] = &values[i] }

    var result []map[string]string
    for rows.Next() {
        if err := rows.Scan(scanArgs...); err != nil { return nil, err }
        record := make(map[string]string)
        for i, col := range values {
            if col != nil {
                record[cols[i]] = string(col.([]byte))
            }
        }
        result = append(result, record)
    }
    return result, rows.Err()
}

3. 更新


func updateUser(db *sql.DB, id, age, sex int) (int64, error) {
    stmt, err := db.Prepare(`UPDATE user SET user_age=?, user_sex=? WHERE user_id=?`)
    if err != nil { return 0, err }
    defer stmt.Close()
    res, err := stmt.Exec(age, sex, id)
    if err != nil { return 0, err }
    return res.RowsAffected()
}

4. 刪除


func deleteUser(db *sql.DB, id int) (int64, error) {
    stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
    if err != nil { return 0, err }
    defer stmt.Close()
    res, err := stmt.Exec(id)
    if err != nil { return 0, err }
    return res.RowsAffected()
}

五、錯誤處理模式


func checkErr(err error) {
    if err != nil { panic(err) }  // 示例用 panic,生產建議返回 error 由上層處理
}

六、完整可運行示例


package main
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)
func main() {
    db, _ := sql.Open("mysql", "root:@/test?charset=utf8mb4")
    defer db.Close()

    id, _ := insertUser(db, "tony", 20, 1)
    fmt.Println("Insert ID:", id)

    users, _ := queryUsers(db)
    for _, u := range users { fmt.Printf("%+v\n", u) }

    affected, _ := updateUser(db, id, 21, 2)
    fmt.Println("Updated rows:", affected)

    affected, _ = deleteUser(db, id)
    fmt.Println("Deleted rows:", affected)
}

七、生產環境進階主題

  • 連接池:SetMaxOpenConns/SetMaxIdleConns 根據 QPS 壓測調優
  • 預處理語句緩存:驅動自動緩存 Prepare,複用減少解析開銷
  • 事務:tx, _ := db.Begin() → 操作 → tx.Commit()/Rollback()
  • Context 超時控制:db.QueryContext(ctx, ...) 防止慢查詢阻塞
  • ORM 選型:GORM、sqlx、ent 等構建於 database/sql 之上,提升開發效率
copyright © 萬盛學電腦網 all rights reserved