go-orm接口原生到框架

go-orm接口原生到框架

1. 使用 Go 原生数据库 API 连接 MySQL

示例项目结构

myapp/
├── main.go
├── models/
│   └── user.go
└── go.mod

go.mod

module myapp

go 1.20

require (
    github.com/go-sql-driver/mysql v1.6.0
)

main.go

package main

import (
    "database/sql"
    "fmt"
    "log"

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

func main() {
    // 数据库连接字符串
    dsn := "user:password@tcp(127.0.0.1:3306)/mydb"
    
    // 连接数据库
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 测试数据库连接
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }

    fmt.Println("Connected to the database successfully!")

    // 创建表
    createTable := `CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) NOT NULL UNIQUE
    );`
    _, err = db.Exec(createTable)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Table created successfully!")

    // 插入数据
    _, err = db.Exec("INSERT INTO users (name, email) VALUES (?, ?)", "John Doe", "john@example.com")
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Data inserted successfully!")

    // 查询数据
    rows, err := db.Query("SELECT id, name, email FROM users")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name, email string
        if err := rows.Scan(&id, &name, &email); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%d: %s - %s\n", id, name, email)
    }

    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
}

2.使用 SQLX 库连接 MySQL

sqlx 是一个 Go 语言的库，它在 database/sql 基础上扩展了更多功能，提供了更简洁的接口用于处理 SQL 查询和数据映射。以下是一个使用 sqlx 连接和操作 MySQL 数据库的示例。

sqlx 示例项目结构

myapp/
├── main.go
├── models/
│   └── user.go
└── go.mod

go.mod

module myapp

go 1.20

require (
    github.com/go-sql-driver/mysql v1.6.0
    github.com/jmoiron/sqlx v1.4.0
)

models/user.go

package models

// User 定义了 User 结构体
type User struct {
    ID    int64  `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

main.go

package main

import (
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "myapp/models"
)

func main() {
    // 数据库连接字符串
    dsn := "user:password@tcp(127.0.0.1:3306)/mydb?charset=utf8mb4&parseTime=True&loc=Local"
    
    // 连接数据库
    db, err := sqlx.Open("mysql", dsn)
    if err != nil {
        log.Fatal("failed to connect database:", err)
    }
    defer db.Close()

    fmt.Println("Connected to the database successfully!")

    // 创建表
    schema := `
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) NOT NULL UNIQUE
    );`
    _, err = db.Exec(schema)
    if err != nil {
        log.Fatal("failed to create table:", err)
    }

    fmt.Println("Table created successfully!")

    // 插入数据
    user := models.User{Name: "John Doe", Email: "john@example.com"}
    _, err = db.NamedExec(`INSERT INTO users (name, email) VALUES (:name, :email)`, &user)
    if err != nil {
        log.Fatal("failed to insert data:", err)
    }

    fmt.Println("Data inserted successfully!")

    // 查询数据
    var users []models.User
    err = db.Select(&users, "SELECT id, name, email FROM users")
    if err != nil {
        log.Fatal("failed to query data:", err)
    }

    for _, user := range users {
        fmt.Printf("%d: %s - %s\n", user.ID, user.Name, user.Email)
    }
}

3. 使用 XORM 框架连接 MySQL

示例项目结构

myapp/
├── main.go
├── models/
│   └── user.go
└── go.mod

go.mod

module myapp

go 1.20

require (
    github.com/go-sql-driver/mysql v1.6.0
    xorm.io/xorm v0.7.1
)

models/user.go

package models

type User struct {
    Id    int64  `xorm:"pk autoincr"`
    Name  string `xorm:"not null"`
    Email string `xorm:"unique not null"`
}

main.go

package main

import (
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql"
    "xorm.io/xorm"
    "myapp/models"
)

func main() {
    // 数据库连接字符串
    dsn := "user:password@tcp(127.0.0.1:3306)/mydb"
    
    // 连接数据库
    engine, err := xorm.NewEngine("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer engine.Close()

    // 自动同步结构
    if err := engine.Sync2(new(models.User)); err != nil {
        log.Fatal(err)
    }

    fmt.Println("Table created successfully!")

    // 插入数据
    user := &models.User{Name: "John Doe", Email: "john@example.com"}
    _, err = engine.Insert(user)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Data inserted successfully!")

    // 查询数据
    var users []models.User
    if err := engine.Find(&users); err != nil {
        log.Fatal(err)
    }

    for _, user := range users {
        fmt.Printf("%d: %s - %s\n", user.Id, user.Name, user.Email)
    }
}

4.使用 GORM 框架连接 MySQL

以下是一个使用 GORM 框架连接和操作 MySQL 数据库的示例。GORM 是一个流行的 Go 语言 ORM 框架，可以简化与数据库的交互。

GORM 示例项目结构

myapp/
├── main.go
├── models/
│   └── user.go
└── go.mod

go.mod

module myapp

go 1.20

require (
    github.com/go-sql-driver/mysql v1.6.0
    gorm.io/gorm v1.24.0
    gorm.io/driver/mysql v1.5.0
)

models/user.go

package models

import (
    "gorm.io/gorm"
)

type User struct {
    gorm.Model
    Name  string `gorm:"not null"`
    Email string `gorm:"unique;not null"`
}

main.go

package main

import (
    "fmt"
    "log"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "myapp/models"
)

func main() {
    // 数据库连接字符串
    dsn := "user:password@tcp(127.0.0.1:3306)/mydb?charset=utf8mb4&parseTime=True&loc=Local"
    
    // 连接数据库
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        log.Fatal("failed to connect database:", err)
    }

    fmt.Println("Connected to the database successfully!")

    // 自动迁移
    if err := db.AutoMigrate(&models.User{}); err != nil {
        log.Fatal("failed to migrate database:", err)
    }

    fmt.Println("Table created successfully!")

    // 插入数据
    user := models.User{Name: "John Doe", Email: "john@example.com"}
    if err := db.Create(&user).Error; err != nil {
        log.Fatal("failed to insert data:", err)
    }

    fmt.Println("Data inserted successfully!")

    // 查询数据
    var users []models.User
    if err := db.Find(&users).Error; err != nil {
        log.Fatal("failed to query data:", err)
    }

    for _, user := range users {
        fmt.Printf("%d: %s - %s\n", user.ID, user.Name, user.Email)
    }
}