Go语言数据库操作与ORM实践指南
引言
数据库操作是后端开发的核心任务之一。Go语言提供了标准库database/sql用于数据库操作，同时社区也有丰富的ORM框架。本文将深入探讨Go语言的数据库操作实践、ORM使用技巧，以及如何构建高效的数据访问层。
一、database/sql基础
1.1 连接数据库
import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" ) func main() { dsn := "user:password@tcp(localhost:3306)/database?charset=utf8mb4&parseTime=True&loc=Local" db, err := sql.Open("mysql", dsn) if err != nil { panic(err) } defer db.Close() // 验证连接 if err := db.Ping(); err != nil { panic(err) } fmt.Println("Connected to database") }1.2 连接池配置
func createDB(dsn string) (*sql.DB, error) { db, err := sql.Open("mysql", dsn) if err != nil { return nil, err } // 设置连接池参数 db.SetMaxOpenConns(20) db.SetMaxIdleConns(10) db.SetConnMaxLifetime(time.Minute * 30) db.SetConnMaxIdleTime(time.Minute * 10) if err := db.Ping(); err != nil { return nil, err } return db, nil }1.3 执行SQL语句
func createUser(db *sql.DB, name, email string) error { query := `INSERT INTO users (name, email) VALUES (?, ?)` result, err := db.Exec(query, name, email) if err != nil { return err } id, err := result.LastInsertId() if err != nil { return err } fmt.Printf("User created with ID: %d\n", id) return nil }二、查询操作
2.1 查询单行
type User struct { ID int Name string Email string } func getUser(db *sql.DB, id int) (*User, error) { query := `SELECT id, name, email FROM users WHERE id = ?` row := db.QueryRow(query, id) var user User err := row.Scan(&user.ID, &user.Name, &user.Email) if err != nil { if err == sql.ErrNoRows { return nil, nil // 用户不存在 } return nil, err } return &user, nil }2.2 查询多行
func getUsers(db *sql.DB) ([]User, error) { query := `SELECT id, name, email FROM users` rows, err := db.Query(query) if err != nil { return nil, err } defer rows.Close() var users []User for rows.Next() { var user User err := rows.Scan(&user.ID, &user.Name, &user.Email) if err != nil { return nil, err } users = append(users, user) } if err := rows.Err(); err != nil { return nil, err } return users, nil }2.3 预处理语句
func batchInsertUsers(db *sql.DB, users []User) error { query := `INSERT INTO users (name, email) VALUES (?, ?)` stmt, err := db.Prepare(query) if err != nil { return err } defer stmt.Close() tx, err := db.Begin() if err != nil { return err } for _, user := range users { _, err := tx.Stmt(stmt).Exec(user.Name, user.Email) if err != nil { tx.Rollback() return err } } return tx.Commit() }三、事务处理
3.1 基本事务
func transfer(db *sql.DB, fromID, toID int, amount float64) error { tx, err := db.Begin() if err != nil { return err } defer 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() }3.2 保存点
func complexTransaction(db *sql.DB) error { tx, err := db.Begin() if err != nil { return err } defer tx.Rollback() // 第一个操作 _, err = tx.Exec(`INSERT INTO table1 (col1) VALUES (?)`, "value1") if err != nil { return err } // 创建保存点 _, err = tx.Exec("SAVEPOINT sp1") if err != nil { return err } // 第二个操作 _, err = tx.Exec(`INSERT INTO table2 (col1) VALUES (?)`, "value2") if err != nil { // 回滚到保存点 tx.Exec("ROLLBACK TO SAVEPOINT sp1") // 继续其他操作 } return tx.Commit() }四、GORM ORM实践
4.1 GORM配置
go get gorm.io/gorm go get gorm.io/driver/mysqlimport ( "gorm.io/driver/mysql" "gorm.io/gorm" ) func initDB() (*gorm.DB, error) { dsn := "user:password@tcp(localhost:3306)/database?charset=utf8mb4&parseTime=True&loc=Local" db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) if err != nil { return nil, err } return db, nil }4.2 定义模型
type User struct { gorm.Model Name string `gorm:"size:100;not null"` Email string `gorm:"size:100;unique;not null"` Age int `gorm:"default:0"` } // 表名设置 func (u User) TableName() string { return "users" }4.3 创建记录
func createUser(db *gorm.DB, name, email string) error { user := User{Name: name, Email: email} result := db.Create(&user) if result.Error != nil { return result.Error } fmt.Printf("User created with ID: %d\n", user.ID) return nil }4.4 查询记录
func getUser(db *gorm.DB, id uint) (*User, error) { var user User result := db.First(&user, id) if result.Error != nil { if result.Error == gorm.ErrRecordNotFound { return nil, nil } return nil, result.Error } return &user, nil } func getUsersByAge(db *gorm.DB, minAge int) ([]User, error) { var users []User result := db.Where("age >= ?", minAge).Find(&users) if result.Error != nil { return nil, result.Error } return users, nil }4.5 更新记录
func updateUser(db *gorm.DB, id uint, updates map[string]interface{}) error { result := db.Model(&User{}).Where("id = ?", id).Updates(updates) if result.Error != nil { return result.Error } if result.RowsAffected == 0 { return errors.New("user not found") } return nil }4.6 删除记录
func deleteUser(db *gorm.DB, id uint) error { result := db.Delete(&User{}, id) if result.Error != nil { return result.Error } if result.RowsAffected == 0 { return errors.New("user not found") } return nil }五、高级查询
5.1 关联查询
type Order struct { gorm.Model UserID uint Amount float64 User User `gorm:"foreignKey:UserID"` } func getOrdersWithUser(db *gorm.DB) ([]Order, error) { var orders []Order result := db.Preload("User").Find(&orders) if result.Error != nil { return nil, result.Error } return orders, nil }5.2 自定义SQL
func getActiveUsers(db *gorm.DB) ([]User, error) { var users []User result := db.Raw(` SELECT * FROM users WHERE created_at >= ? AND status = 'active' `, time.Now().Add(-30*24*time.Hour)).Scan(&users) if result.Error != nil { return nil, result.Error } return users, nil }5.3 分页查询
func getUsersPage(db *gorm.DB, page, pageSize int) ([]User, error) { var users []User offset := (page - 1) * pageSize result := db.Offset(offset).Limit(pageSize).Find(&users) if result.Error != nil { return nil, result.Error } return users, nil }六、数据库迁移
6.1 使用GORM自动迁移
func migrate(db *gorm.DB) error { err := db.AutoMigrate( &User{}, &Order{}, &Product{}, ) if err != nil { return err } return nil }6.2 使用go-migrate
go install -tags 'mysql' github.com/golang-migrate/migrate/v4/cmd/migrate@latest# 创建迁移文件 migrate create -ext sql -dir migrations -seq create_users_table-- migrations/000001_create_users_table.up.sql CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );-- migrations/000001_create_users_table.down.sql DROP TABLE users;# 执行迁移 migrate -database "mysql://user:password@tcp(localhost:3306)/database" -path ./migrations up七、性能优化
7.1 使用连接池
func createOptimizedDB(dsn string) (*sql.DB, error) { db, err := sql.Open("mysql", dsn) if err != nil { return nil, err } // 根据业务需求调整连接池大小 db.SetMaxOpenConns(50) db.SetMaxIdleConns(25) db.SetConnMaxLifetime(time.Minute * 15) db.SetConnMaxIdleTime(time.Minute * 5) return db, nil }7.2 批量操作
func batchInsert(db *gorm.DB, users []User) error { return db.Create(&users).Error }7.3 索引优化
type User struct { gorm.Model Name string `gorm:"size:100;not null;index"` Email string `gorm:"size:100;unique;not null"` Age int `gorm:"default:0;index"` }八、错误处理
8.1 错误类型判断
func handleDBError(err error) { if err == sql.ErrNoRows { fmt.Println("record not found") return } if err == gorm.ErrRecordNotFound { fmt.Println("record not found") return } fmt.Printf("database error: %v\n", err) }结论
数据库操作是后端开发的核心。Go语言的database/sql提供了灵活的底层操作能力，而GORM则提供了便捷的ORM抽象。通过合理使用连接池、事务、索引等优化手段，可以构建高效、可靠的数据访问层。实践中需要根据业务需求选择合适的数据库操作方式，平衡性能与开发效率。
