数据库概述 #

go get github.com/go-sql-driver/mysql

go get github.com/lib/pq

go get github.com/mattn/go-sqlite3

go get gorm.io/gorm
go get gorm.io/driver/mysql

go get github.com/jmoiron/sqlx

go get entgo.io/ent

package main

import (
    "database/sql"
    "fmt"
    "github.com/go-sql-driver/mysql"
    "github.com/labstack/echo/v4"
)

var db *sql.DB

func initDB() error {
    cfg := mysql.Config{
        User:   "root",
        Passwd: "password",
        Net:    "tcp",
        Addr:   "localhost:3306",
        DBName: "myapp",
    }
    
    var err error
    db, err = sql.Open("mysql", cfg.FormatDSN())
    if err != nil {
        return err
    }
    
    return db.Ping()
}

func main() {
    if err := initDB(); err != nil {
        panic(err)
    }
    defer db.Close()
    
    e := echo.New()
    e.GET("/users", getUsers)
    e.Start(":8080")
}

func getUsers(c echo.Context) error {
    rows, err := db.Query("SELECT id, name FROM users")
    if err != nil {
        return err
    }
    defer rows.Close()
    
    var users []map[string]interface{}
    for rows.Next() {
        var id int
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            return err
        }
        users = append(users, map[string]interface{}{
            "id":   id,
            "name": name,
        })
    }
    
    return c.JSON(http.StatusOK, users)
}

func initDB() error {
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        return err
    }
    
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(5 * time.Minute)
    
    return db.Ping()
}

database:
  driver: mysql
  host: localhost
  port: 3306
  name: myapp
  user: root
  password: secret
  max_open_conns: 25
  max_idle_conns: 5
  conn_max_lifetime: 5m

type DatabaseConfig struct {
    Driver          string
    Host            string
    Port            int
    Name            string
    User            string
    Password        string
    MaxOpenConns    int
    MaxIdleConns    int
    ConnMaxLifetime time.Duration
}

func loadConfig() *DatabaseConfig {
    return &DatabaseConfig{
        Driver:          viper.GetString("database.driver"),
        Host:            viper.GetString("database.host"),
        Port:            viper.GetInt("database.port"),
        Name:            viper.GetString("database.name"),
        User:            viper.GetString("database.user"),
        Password:        viper.GetString("database.password"),
        MaxOpenConns:    viper.GetInt("database.max_open_conns"),
        MaxIdleConns:    viper.GetInt("database.max_idle_conns"),
        ConnMaxLifetime: viper.GetDuration("database.conn_max_lifetime"),
    }
}

func autoMigrate() error {
    return db.AutoMigrate(
        &User{},
        &Post{},
        &Comment{},
    )
}

go install -tags 'mysql' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

migrate create -ext sql -dir migrations create_users_table

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

func transferMoney(fromID, toID int64, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    
    defer func() {
        if err != nil {
            tx.Rollback()
        }
    }()
    
    _, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID)
    if err != nil {
        return err
    }
    
    _, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID)
    if err != nil {
        return err
    }
    
    return tx.Commit()
}

func transferMoney(db *gorm.DB, fromID, toID uint, amount float64) error {
    return db.Transaction(func(tx *gorm.DB) error {
        var from Account
        if err := tx.First(&from, fromID).Error; err != nil {
            return err
        }
        
        var to Account
        if err := tx.First(&to, toID).Error; err != nil {
            return err
        }
        
        if from.Balance < amount {
            return errors.New("余额不足")
        }
        
        if err := tx.Model(&from).Update("balance", from.Balance-amount).Error; err != nil {
            return err
        }
        
        if err := tx.Model(&to).Update("balance", to.Balance+amount).Error; err != nil {
            return err
        }
        
        return nil
    })
}

func setupTestDB() *gorm.DB {
    db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})
    if err != nil {
        panic(err)
    }
    
    db.AutoMigrate(&User{})
    
    return db
}

func TestGetUsers(t *testing.T) {
    db := setupTestDB()
    
    db.Create(&User{Name: "张三", Email: "zhangsan@example.com"})
    
    var users []User
    db.Find(&users)
    
    assert.Equal(t, 1, len(users))
}