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)/dbnameuser:pass@tcp(host:port)/dbname?charset=utf8mb4(最常用)user:pass@tcp([ipv6:addr]:port)/dbname
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()
}
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()
}
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()
}
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()db.QueryContext(ctx, ...) 防止慢查詢阻塞database/sql 之上,提升開發效率