数据库操作

Go 语言数据库操作最佳实践

2 课时

SQL 基础

Go 标准库 database/sql 提供了通用的 SQL 数据库接口，配合驱动可连接各种数据库。

数据库连接与查询

1package main
2 
3import (
4    "database/sql"
5    "fmt"
6    _ "github.com/lib/pq"
7)
8 
9type User struct {
10    ID    int
11    Name  string
12    Email string
13}
14 
15func main() {
16    db, err := sql.Open("postgres", "postgres://localhost/mydb?sslmode=disable")
17    if err != nil {
18        panic(err)
19    }
20    defer db.Close()
21    
22    // 查询单行
23    var user User
24    err = db.QueryRow("SELECT id, name, email FROM users WHERE id = $1", 1).
25        Scan(&user.ID, &user.Name, &user.Email)
26    if err != nil {
27        panic(err)
28    }
29    
30    // 查询多行
31    rows, _ := db.Query("SELECT id, name, email FROM users")
32    defer rows.Close()
33    
34    for rows.Next() {
35        var u User
36        rows.Scan(&u.ID, &u.Name, &u.Email)
37        fmt.Println(u)
38    }
39}

GORM ORM

GORM 是 Go 语言最流行的 ORM 库，提供了丰富的功能和良好的开发体验。

GORM 基本操作

1package main
2 
3import (
4    "gorm.io/driver/postgres"
5    "gorm.io/gorm"
6)
7 
8type User struct {
9    gorm.Model
10    Name  string
11    Email string `gorm:"uniqueIndex"`
12}
13 
14func main() {
15    db, err := gorm.Open(postgres.Open("host=localhost dbname=mydb"), &gorm.Config{})
16    if err != nil {
17        panic(err)
18    }
19    
20    // 自动迁移
21    db.AutoMigrate(&User{})
22    
23    // 创建
24    db.Create(&User{Name: "Alice", Email: "alice@example.com"})
25    
26    // 查询
27    var user User
28    db.First(&user, 1)
29    db.Where("name = ?", "Alice").First(&user)
30    
31    // 更新
32    db.Model(&user).Update("Name", "Bob")
33    
34    // 删除 (软删除)
35    db.Delete(&user)
36}

1package main 2 3import ( 4 "database/sql" 5 "fmt" 6 _ "github.com/lib/pq" 7) 8 9type User struct { 10 ID int 11 Name string 12 Email string 13} 14 15func main() { 16 db, err := sql.Open("postgres", "postgres://localhost/mydb?sslmode=disable") 17 if err != nil { 18 panic(err) 19 } 20 defer db.Close() 21 22 // 查询单行 23 var user User 24 err = db.QueryRow("SELECT id, name, email FROM users WHERE id = $1", 1). 25 Scan(&user.ID, &user.Name, &user.Email) 26 if err != nil { 27 panic(err) 28 } 29 30 // 查询多行 31 rows, _ := db.Query("SELECT id, name, email FROM users") 32 defer rows.Close() 33 34 for rows.Next() { 35 var u User 36 rows.Scan(&u.ID, &u.Name, &u.Email) 37 fmt.Println(u) 38 } 39}

1package main 2 3import ( 4 "gorm.io/driver/postgres" 5 "gorm.io/gorm" 6) 7 8type User struct { 9 gorm.Model 10 Name string 11 Email string `gorm:"uniqueIndex"` 12} 13 14func main() { 15 db, err := gorm.Open(postgres.Open("host=localhost dbname=mydb"), &gorm.Config{}) 16 if err != nil { 17 panic(err) 18 } 19 20 // 自动迁移 21 db.AutoMigrate(&User{}) 22 23 // 创建 24 db.Create(&User{Name: "Alice", Email: "alice@example.com"}) 25 26 // 查询 27 var user User 28 db.First(&user, 1) 29 db.Where("name = ?", "Alice").First(&user) 30 31 // 更新 32 db.Model(&user).Update("Name", "Bob") 33 34 // 删除 (软删除) 35 db.Delete(&user) 36}