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", "[email protected]")
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: "[email protected]"}
_, 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: "[email protected]"}
_, 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: "[email protected]"}
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)
}
}