Golang数据库CRUD实现与实战教程

package main

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

var db *sql.DB

func initDB() {
    var err error
    dsn := "user:password@tcp(127.0.0.1:3306)/testdb"
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal("打开数据库失败:", err)
    }
    if err = db.Ping(); err != nil {
        log.Fatal("连接数据库失败:", err)
    }
    db.SetMaxOpenConns(10)
    db.SetMaxIdleConns(5)
}

type User struct {
    ID    int    `json:"id"`
    Name  string `json:"name"`
    Email string `json:"email"`
}

func createUser(user User) (int64, error) {
    sqlStr := "INSERT INTO users(name, email) VALUES(?, ?)"
    result, err := db.Exec(sqlStr, user.Name, user.Email)
    if err != nil {
        return 0, err
    }
    return result.LastInsertId()
}

func getUser(id int) (*User, error) {
    user := &User{}
    sqlStr := "SELECT id, name, email FROM users WHERE id = ?"
    row := db.QueryRow(sqlStr, id)
    if err := row.Scan(&user.ID, &user.Name, &user.Email); err != nil {
        return nil, err
    }
    return user, nil
}

func getAllUsers() ([]User, error) {
    sqlStr := "SELECT id, name, email FROM users"
    rows, err := db.Query(sqlStr)
    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.Email); err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    return users, nil
}

func updateUser(user User) error {
    sqlStr := "UPDATE users SET name=?, email=? WHERE id=?"
    _, err := db.Exec(sqlStr, user.Name, user.Email, user.ID)
    return err
}

func deleteUser(id int) error {
    sqlStr := "DELETE FROM users WHERE id = ?"
    _, err := db.Exec(sqlStr, id)
    return err
}

func main() {
    initDB()
    defer db.Close()

    // 示例：插入用户
    newUser := User{Name: "Alice", Email: "alice@example.com"}
    id, _ := createUser(newUser)
    log.Printf("新增用户，ID: %d\n", id)

    // 查询用户
    user, _ := getUser(int(id))
    log.Printf("查得用户: %+v\n", user)
}