7.3 PostgreSQL操作与特色功能¶

作为有三十年Go语言开发经验的老师，我很高兴带你深入学习PostgreSQL在Go中的使用。PostgreSQL是一个功能强大的开源关系型数据库，与Go语言的结合能够构建出高性能、可靠的应用程序。

学习目标¶

掌握Go连接PostgreSQL的基本方法
学会执行常见的增删改查操作
了解PostgreSQL常用数据类型的处理
初步了解PostgreSQL的特色功能
建立PostgreSQL开发的基础能力

核心内容¶

1. PostgreSQL环境准备¶

1.1 PostgreSQL安装与配置¶

首先，你需要安装PostgreSQL数据库。根据你的操作系统选择合适的方式：

Ubuntu/Debian: sudo apt-get install postgresql postgresql-contrib
CentOS/RHEL: sudo yum install postgresql-server
macOS: brew install postgresql
Windows: 从官网下载安装包安装

安装完成后，启动PostgreSQL服务并设置开机自启。

1.2 数据库和用户创建¶

-- 创建新用户
CREATE USER myuser WITH PASSWORD 'mypassword';

-- 创建数据库
CREATE DATABASE mydb WITH OWNER myuser;

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

1.3 Go驱动选择与安装¶

Go语言中常用的PostgreSQL驱动有：

github.com/lib/pq - 最常用的驱动，稳定可靠
github.com/jackc/pgx - 高性能驱动，支持更多特性

安装lib/pq驱动：

go get github.com/lib/pq

2. 连接PostgreSQL数据库¶

2.1 使用pq驱动连接数据库¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    // 连接字符串
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"

    // 打开数据库连接
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 测试连接
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("成功连接到PostgreSQL数据库!")
}

2.2 连接字符串配置详解¶

PostgreSQL连接字符串可以包含以下参数：

user - 数据库用户名
password - 用户密码
dbname - 数据库名称
host - 主机地址（默认为localhost）
port - 端口号（默认为5432）
sslmode - SSL模式（disable、require、verify-full等）

示例：

"user=myuser password=mypass dbname=mydb host=localhost port=5432 sslmode=disable"

2.3 连接测试与错误处理¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal("打开数据库连接失败:", err)
    }
    defer db.Close()

    // 设置连接池参数
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)

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

    fmt.Println("数据库连接成功!")
}

3. 基础CRUD操作¶

3.1 创建表和插入数据¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 创建users表
    createTable := `
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )`

    _, err = db.Exec(createTable)
    if err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 插入数据
    insertUser := `
    INSERT INTO users (username, password, email) 
    VALUES ($1, $2, $3)
    RETURNING id`

    var userID int
    err = db.QueryRow(insertUser, "john_doe", "secret123", "john@example.com").Scan(&userID)
    if err != nil {
        log.Fatal("插入数据失败:", err)
    }

    fmt.Printf("用户创建成功，ID: %d\n", userID)
}

3.2 查询数据的多种方式¶

package main

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

    _ "github.com/lib/pq"
)

type User struct {
    ID        int
    Username  string
    Password  string
    Email     string
    CreatedAt sql.NullTime
}

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 查询单个用户
    var user User
    err = db.QueryRow("SELECT id, username, password, email, created_at FROM users WHERE id = $1", 1).Scan(
        &user.ID, &user.Username, &user.Password, &user.Email, &user.CreatedAt)
    if err != nil {
        log.Fatal("查询用户失败:", err)
    }
    fmt.Printf("单个用户: %+v\n", user)

    // 查询多个用户
    rows, err := db.Query("SELECT id, username, password, email, created_at FROM users WHERE username LIKE $1", "%john%")
    if err != nil {
        log.Fatal("查询用户列表失败:", err)
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        err := rows.Scan(&u.ID, &u.Username, &u.Password, &u.Email, &u.CreatedAt)
        if err != nil {
            log.Fatal("扫描行失败:", err)
        }
        users = append(users, u)
    }

    if err = rows.Err(); err != nil {
        log.Fatal("遍历行时发生错误:", err)
    }

    fmt.Println("用户列表:")
    for _, u := range users {
        fmt.Printf("%+v\n", u)
    }
}

3.3 更新和删除操作¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 更新数据
    updateStmt := "UPDATE users SET email = $1 WHERE id = $2"
    res, err := db.Exec(updateStmt, "new_email@example.com", 1)
    if err != nil {
        log.Fatal("更新失败:", err)
    }

    rowsAffected, err := res.RowsAffected()
    if err != nil {
        log.Fatal("获取影响行数失败:", err)
    }
    fmt.Printf("更新了 %d 行数据\n", rowsAffected)

    // 删除数据
    deleteStmt := "DELETE FROM users WHERE id = $1"
    res, err = db.Exec(deleteStmt, 1)
    if err != nil {
        log.Fatal("删除失败:", err)
    }

    rowsAffected, err = res.RowsAffected()
    if err != nil {
        log.Fatal("获取影响行数失败:", err)
    }
    fmt.Printf("删除了 %d 行数据\n", rowsAffected)
}

3.4 事务处理基础¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 开始事务
    tx, err := db.Begin()
    if err != nil {
        log.Fatal("开始事务失败:", err)
    }

    // 在事务中执行多个操作
    _, err = tx.Exec("INSERT INTO users (username, password, email) VALUES ($1, $2, $3)", 
        "alice", "alice123", "alice@example.com")
    if err != nil {
        tx.Rollback()
        log.Fatal("插入失败，回滚事务:", err)
    }

    _, err = tx.Exec("UPDATE users SET email = $1 WHERE username = $2", 
        "alice_new@example.com", "alice")
    if err != nil {
        tx.Rollback()
        log.Fatal("更新失败，回滚事务:", err)
    }

    // 提交事务
    err = tx.Commit()
    if err != nil {
        log.Fatal("提交事务失败:", err)
    }

    fmt.Println("事务执行成功!")
}

4. 常用数据类型处理¶

4.1 基本数据类型映射¶

PostgreSQL与Go的数据类型映射关系：

PostgreSQL类型	Go类型
INTEGER, SERIAL	int, int64
BIGINT, BIGSERIAL	int64
REAL, DOUBLE PRECISION	float32, float64
BOOLEAN	bool
VARCHAR, TEXT	string
TIMESTAMP, DATE	time.Time
JSON, JSONB	[]byte, 自定义结构体

4.2 时间日期类型处理¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 创建带时间字段的表
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS events (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            event_time TIMESTAMP NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `)
    if err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 插入带时间的数据
    eventTime := time.Now().Add(24 * time.Hour) // 明天此时
    _, err = db.Exec(`
        INSERT INTO events (name, event_time) 
        VALUES ($1, $2)
    `, "产品发布会", eventTime)
    if err != nil {
        log.Fatal("插入数据失败:", err)
    }

    // 查询时间数据
    rows, err := db.Query(`
        SELECT id, name, event_time, created_at 
        FROM events 
        WHERE event_time > $1
    `, time.Now())
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    defer rows.Close()

    for rows.Next() {
        var (
            id        int
            name      string
            eventTime time.Time
            createdAt time.Time
        )
        err := rows.Scan(&id, &name, &eventTime, &createdAt)
        if err != nil {
            log.Fatal("扫描行失败:", err)
        }
        fmt.Printf("事件: %s, 时间: %v\n", name, eventTime.Format("2006-01-02 15:04:05"))
    }
}

4.3 NULL值处理技巧¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 创建带NULL字段的表
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS products (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            description TEXT,
            price DECIMAL(10,2),
            in_stock BOOLEAN
        )
    `)
    if err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 插入包含NULL值的数据
    _, err = db.Exec(`
        INSERT INTO products (name, description, price, in_stock) 
        VALUES ($1, $2, $3, $4)
    `, "笔记本电脑", nil, 5999.99, true)
    if err != nil {
        log.Fatal("插入数据失败:", err)
    }

    // 查询包含NULL值的数据
    rows, err := db.Query(`
        SELECT id, name, description, price, in_stock 
        FROM products
    `)
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    defer rows.Close()

    for rows.Next() {
        var (
            id          int
            name        string
            description sql.NullString
            price       sql.NullFloat64
            inStock     sql.NullBool
        )
        err := rows.Scan(&id, &name, &description, &price, &inStock)
        if err != nil {
            log.Fatal("扫描行失败:", err)
        }

        desc := "无描述"
        if description.Valid {
            desc = description.String
        }

        prc := "价格未知"
        if price.Valid {
            prc = fmt.Sprintf("¥%.2f", price.Float64)
        }

        stock := "库存未知"
        if inStock.Valid {
            if inStock.Bool {
                stock = "有货"
            } else {
                stock = "缺货"
            }
        }

        fmt.Printf("产品: %s, 描述: %s, 价格: %s, 库存: %s\n", name, desc, prc, stock)
    }
}

4.4 字符串与文本处理¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // PostgreSQL字符串函数示例
    var result string

    // 字符串连接
    err = db.QueryRow("SELECT CONCAT('Hello', ' ', 'World')").Scan(&result)
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    fmt.Println("字符串连接:", result)

    // 字符串长度
    var length int
    err = db.QueryRow("SELECT LENGTH('PostgreSQL')").Scan(&length)
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    fmt.Println("字符串长度:", length)

    // 子字符串
    err = db.QueryRow("SELECT SUBSTRING('Hello PostgreSQL' FROM 7 FOR 10)").Scan(&result)
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    fmt.Println("子字符串:", result)

    // 在Go中处理字符串
    names := []string{"Alice", "Bob", "Charlie"}
    placeholders := make([]string, len(names))
    args := make([]interface{}, len(names))
    for i, name := range names {
        placeholders[i] = fmt.Sprintf("$%d", i+1)
        args[i] = name
    }

    query := fmt.Sprintf("SELECT * FROM users WHERE username IN (%s)", 
        strings.Join(placeholders, ","))

    fmt.Println("生成的查询:", query)
    // 实际执行查询...
}

5. PostgreSQL特色功能入门¶

5.1 JSON数据类型基础操作¶

package main

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

    _ "github.com/lib/pq"
)

type Product struct {
    ID          int             `json:"id"`
    Name        string          `json:"name"`
    Attributes  json.RawMessage `json:"attributes"` // 存储JSON数据
    CreatedAt   sql.NullTime    `json:"created_at"`
}

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 创建带JSON字段的表
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS products_json (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            attributes JSONB NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `)
    if err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 插入JSON数据
    attributes := `{"color": "black", "weight": 1.5, "dimensions": {"width": 30, "height": 20, "depth": 15}}`
    _, err = db.Exec(`
        INSERT INTO products_json (name, attributes) 
        VALUES ($1, $2)
    `, "笔记本电脑", attributes)
    if err != nil {
        log.Fatal("插入数据失败:", err)
    }

    // 查询JSON数据
    rows, err := db.Query(`
        SELECT id, name, attributes, created_at 
        FROM products_json 
        WHERE attributes @> $1
    `, `{"color": "black"}`)
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    defer rows.Close()

    var products []Product
    for rows.Next() {
        var p Product
        err := rows.Scan(&p.ID, &p.Name, &p.Attributes, &p.CreatedAt)
        if err != nil {
            log.Fatal("扫描行失败:", err)
        }
        products = append(products, p)
    }

    fmt.Println("查询结果:")
    for _, p := range products {
        fmt.Printf("ID: %d, Name: %s, Attributes: %s\n", p.ID, p.Name, string(p.Attributes))

        // 解析JSON属性
        var attrs map[string]interface{}
        if err := json.Unmarshal(p.Attributes, &attrs); err != nil {
            log.Printf("解析JSON失败: %v", err)
            continue
        }
        if color, exists := attrs["color"]; exists {
            fmt.Printf("  颜色: %s\n", color)
        }
    }
}

5.2 数组类型的简单使用¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 创建带数组字段的表
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS posts (
            id SERIAL PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            tags TEXT[] NOT NULL,
            ratings INTEGER[],
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `)
    if err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 插入数组数据
    _, err = db.Exec(`
        INSERT INTO posts (title, tags, ratings) 
        VALUES ($1, $2, $3)
    `, "Go语言入门教程", `{"编程", "Go", "教程"}`, `{5, 4, 5, 4, 5}`)
    if err != nil {
        log.Fatal("插入数据失败:", err)
    }

    // 查询包含特定标签的文章
    rows, err := db.Query(`
        SELECT id, title, tags, ratings 
        FROM posts 
        WHERE $1 = ANY(tags)
    `, "Go")
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    defer rows.Close()

    for rows.Next() {
        var (
            id     int
            title  string
            tags   []string
            ratings []int64
        )
        err := rows.Scan(&id, &title, &tags, &ratings)
        if err != nil {
            log.Fatal("扫描行失败:", err)
        }

        fmt.Printf("文章: %s\n", title)
        fmt.Printf("  标签: %v\n", tags)

        // 计算平均评分
        if len(ratings) > 0 {
            var sum int64
            for _, r := range ratings {
                sum += r
            }
            avg := float64(sum) / float64(len(ratings))
            fmt.Printf("  平均评分: %.1f\n", avg)
        }
    }
}

5.3 序列(SERIAL)和UUID¶

package main

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

    _ "github.com/lib/pq"
    "github.com/google/uuid"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 启用UUID扩展
    _, err = db.Exec("CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"")
    if err != nil {
        log.Fatal("启用UUID扩展失败:", err)
    }

    // 创建使用SERIAL和UUID的表
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS items (
            id SERIAL PRIMARY KEY,
            item_uuid UUID DEFAULT uuid_generate_v4(),
            name VARCHAR(100) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `)
    if err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 插入数据，使用默认的UUID
    _, err = db.Exec(`
        INSERT INTO items (name) 
        VALUES ($1)
    `, "测试商品")
    if err != nil {
        log.Fatal("插入数据失败:", err)
    }

    // 插入数据，使用Go生成的UUID
    newUUID := uuid.New()
    _, err = db.Exec(`
        INSERT INTO items (item_uuid, name) 
        VALUES ($1, $2)
    `, newUUID, "另一个商品")
    if err != nil {
        log.Fatal("插入数据失败:", err)
    }

    // 查询数据
    rows, err := db.Query(`
        SELECT id, item_uuid, name, created_at 
        FROM items
    `)
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    defer rows.Close()

    for rows.Next() {
        var (
            id       int
            itemUUID uuid.UUID
            name     string
            createdAt sql.NullTime
        )
        err := rows.Scan(&id, &itemUUID, &name, &createdAt)
        if err != nil {
            log.Fatal("扫描行失败:", err)
        }
        fmt.Printf("ID: %d, UUID: %s, Name: %s\n", id, itemUUID.String(), name)
    }
}

5.4 基础索引优化¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 创建测试表
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS orders (
            id SERIAL PRIMARY KEY,
            user_id INTEGER NOT NULL,
            amount DECIMAL(10,2) NOT NULL,
            status VARCHAR(20) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `)
    if err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 创建索引
    _, err = db.Exec(`
        CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
        CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
        CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);
    `)
    if err != nil {
        log.Fatal("创建索引失败:", err)
    }

    // 分析查询性能
    start := time.Now()

    rows, err := db.Query(`
        EXPLAIN ANALYZE 
        SELECT * FROM orders 
        WHERE user_id = $1 AND status = $2
    `, 1, "completed")
    if err != nil {
        log.Fatal("查询失败:", err)
    }
    defer rows.Close()

    var explanation string
    for rows.Next() {
        var line string
        err := rows.Scan(&line)
        if err != nil {
            log.Fatal("扫描行失败:", err)
        }
        explanation += line + "\n"
    }

    elapsed := time.Since(start)
    fmt.Printf("查询执行时间: %v\n", elapsed)
    fmt.Println("执行计划:")
    fmt.Println(explanation)

    // 监控索引使用情况
    indexRows, err := db.Query(`
        SELECT 
            tablename,
            indexname,
            idx_scan as index_scans
        FROM 
            pg_stat_all_indexes 
        WHERE 
            schemaname = 'public'
        ORDER BY 
            idx_scan DESC
    `)
    if err != nil {
        log.Fatal("查询索引统计失败:", err)
    }
    defer indexRows.Close()

    fmt.Println("索引使用统计:")
    for indexRows.Next() {
        var (
            tableName  string
            indexName  string
            indexScans int64
        )
        err := indexRows.Scan(&tableName, &indexName, &indexScans)
        if err != nil {
            log.Fatal("扫描行失败:", err)
        }
        fmt.Printf("表: %s, 索引: %s, 扫描次数: %d\n", tableName, indexName, indexScans)
    }
}

6. 实用技巧与最佳实践¶

6.1 SQL注入防护¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 不安全的方式 - 容易受到SQL注入攻击
    unsafeQuery := func(username string) {
        query := fmt.Sprintf("SELECT * FROM users WHERE username = '%s'", username)
        fmt.Println("不安全查询:", query)
        // 不要在实际代码中这样使用!
    }

    // 安全的方式 - 使用参数化查询
    safeQuery := func(username string) {
        rows, err := db.Query("SELECT * FROM users WHERE username = $1", username)
        if err != nil {
            log.Fatal("查询失败:", err)
        }
        defer rows.Close()

        // 处理结果...
        fmt.Println("安全查询执行成功")
    }

    // 示例
    username := "admin'; DROP TABLE users; --"
    unsafeQuery(username) // 危险!
    safeQuery(username)   // 安全
}

6.2 连接池的基础配置¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"

    // 打开数据库连接
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 配置连接池
    db.SetMaxOpenConns(25)           // 最大打开连接数
    db.SetMaxIdleConns(25)           // 最大空闲连接数
    db.SetConnMaxLifetime(5 * time.Minute) // 连接最大生命周期
    db.SetConnMaxIdleTime(2 * time.Minute) // 连接最大空闲时间

    // 测试连接池
    for i := 0; i < 30; i++ {
        go func(id int) {
            rows, err := db.Query("SELECT pg_sleep(0.1), $1", id)
            if err != nil {
                log.Printf("协程 %d 查询失败: %v", id, err)
                return
            }
            rows.Close()
            fmt.Printf("协程 %d 完成\n", id)
        }(i)
    }

    time.Sleep(2 * time.Second) // 等待所有协程完成

    // 查看连接池状态
    fmt.Printf("当前打开连接数: %d\n", db.Stats().OpenConnections)
    fmt.Printf("正在使用连接数: %d\n", db.Stats().InUse)
    fmt.Printf("空闲连接数: %d\n", db.Stats().Idle)
}

6.3 错误处理与调试技巧¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 高级错误处理
    _, err = db.Exec("INSERT INTO non_existent_table (name) VALUES ($1)", "test")
    if err != nil {
        // 检查特定类型的错误
        if err == sql.ErrNoRows {
            fmt.Println("没有找到行")
        } else if err.Error() == "pq: relation \"non_existent_table\" does not exist" {
            fmt.Println("表不存在错误:", err)
            // 这里可以添加特定的恢复逻辑
        } else {
            log.Printf("未知错误: %v", err)
        }
    }

    // 使用事务的错误处理
    tx, err := db.Begin()
    if err != nil {
        log.Fatal("开始事务失败:", err)
    }

    // 使用defer进行事务回滚(在函数返回时检查并决定是回滚还是提交)
    defer func() {
        if p := recover(); p != nil {
            // 发生panic，回滚事务
            tx.Rollback()
            panic(p) // 重新抛出panic
        } else if err != nil {
            // 发生错误，回滚事务
            tx.Rollback()
        } else {
            // 没有错误，提交事务
            err = tx.Commit()
            if err != nil {
                log.Fatal("提交事务失败:", err)
            }
        }
    }()

    // 在事务中执行操作
    _, err = tx.Exec("INSERT INTO users (username, password, email) VALUES ($1, $2, $3)", 
        "test_user", "password123", "test@example.com")
    if err != nil {
        return // 触发defer中的回滚
    }

    // 另一个操作
    _, err = tx.Exec("UPDATE users SET email = $1 WHERE username = $2", 
        "new_email@example.com", "test_user")
    if err != nil {
        return // 触发defer中的回滚
    }

    fmt.Println("事务执行成功")
}

6.4 性能监控入门¶

package main

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

    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 监控数据库性能
    go monitorDBStats(db)

    // 模拟一些数据库操作
    for i := 0; i < 100; i++ {
        start := time.Now()

        _, err := db.Exec("INSERT INTO test_perf (data) VALUES ($1)", fmt.Sprintf("data_%d", i))
        if err != nil {
            log.Printf("插入失败: %v", err)
        }

        elapsed := time.Since(start)
        if elapsed > 100*time.Millisecond {
            log.Printf("慢查询: %v", elapsed)
        }

        time.Sleep(10 * time.Millisecond)
    }

    time.Sleep(2 * time.Second)
}

func monitorDBStats(db *sql.DB) {
    for {
        stats := db.Stats()
        fmt.Printf("连接池统计: 打开=%d, 使用中=%d, 空闲=%d, 等待=%d\n",
            stats.OpenConnections, stats.InUse, stats.Idle, stats.WaitCount)

        if stats.WaitCount > 0 {
            fmt.Printf("警告: 有 %d 个操作在等待连接\n", stats.WaitCount)
        }

        time.Sleep(1 * time.Second)
    }
}

实战练习¶

练习1：用户管理系统¶

构建一个简单的用户注册、登录、信息更新系统

package main

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

    _ "github.com/lib/pq"
    "golang.org/x/crypto/bcrypt"
)

type User struct {
    ID       int
    Username string
    Password string
    Email    string
}

type UserManager struct {
    db *sql.DB
}

func NewUserManager(db *sql.DB) *UserManager {
    return &UserManager{db: db}
}

func (um *UserManager) CreateTable() error {
    _, err := um.db.Exec(`
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            password VARCHAR(100) NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `)
    return err
}

func (um *UserManager) Register(username, password, email string) error {
    // 加密密码
    hashedPassword, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
    if err != nil {
        return err
    }

    _, err = um.db.Exec(`
        INSERT INTO users (username, password, email) 
        VALUES ($1, $2, $3)
    `, username, string(hashedPassword), email)

    return err
}

func (um *UserManager) Login(username, password string) (*User, error) {
    var user User
    err := um.db.QueryRow(`
        SELECT id, username, password, email 
        FROM users 
        WHERE username = $1
    `, username).Scan(&user.ID, &user.Username, &user.Password, &user.Email)

    if err != nil {
        return nil, err
    }

    // 验证密码
    err = bcrypt.CompareHashAndPassword([]byte(user.Password), []byte(password))
    if err != nil {
        return nil, fmt.Errorf("密码错误")
    }

    return &user, nil
}

func (um *UserManager) UpdateEmail(userID int, newEmail string) error {
    _, err := um.db.Exec(`
        UPDATE users 
        SET email = $1 
        WHERE id = $2
    `, newEmail, userID)

    return err
}

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    um := NewUserManager(db)

    // 创建表
    if err := um.CreateTable(); err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 注册用户
    if err := um.Register("alice", "password123", "alice@example.com"); err != nil {
        log.Fatal("注册失败:", err)
    }
    fmt.Println("用户注册成功")

    // 登录
    user, err := um.Login("alice", "password123")
    if err != nil {
        log.Fatal("登录失败:", err)
    }
    fmt.Printf("用户登录成功: %s\n", user.Username)

    // 更新邮箱
    if err := um.UpdateEmail(user.ID, "alice_new@example.com"); err != nil {
        log.Fatal("更新邮箱失败:", err)
    }
    fmt.Println("邮箱更新成功")
}

练习2：博客文章管理¶

实现文章的发布、查询、分类管理功能

package main

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

    _ "github.com/lib/pq"
)

type Article struct {
    ID        int
    Title     string
    Content   string
    AuthorID  int
    Category  string
    CreatedAt time.Time
    UpdatedAt sql.NullTime
}

type ArticleManager struct {
    db *sql.DB
}

func NewArticleManager(db *sql.DB) *ArticleManager {
    return &ArticleManager{db: db}
}

func (am *ArticleManager) CreateTable() error {
    _, err := am.db.Exec(`
        CREATE TABLE IF NOT EXISTS articles (
            id SERIAL PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            content TEXT NOT NULL,
            author_id INTEGER NOT NULL,
            category VARCHAR(50) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP
        );

        CREATE INDEX IF NOT EXISTS idx_articles_category ON articles(category);
        CREATE INDEX IF NOT EXISTS idx_articles_author ON articles(author_id);
    `)
    return err
}

func (am *ArticleManager) CreateArticle(title, content string, authorID int, category string) (int, error) {
    var id int
    err := am.db.QueryRow(`
        INSERT INTO articles (title, content, author_id, category) 
        VALUES ($1, $2, $3, $4)
        RETURNING id
    `, title, content, authorID, category).Scan(&id)

    return id, err
}

func (am *ArticleManager) GetArticleByID(id int) (*Article, error) {
    var article Article
    err := am.db.QueryRow(`
        SELECT id, title, content, author_id, category, created_at, updated_at
        FROM articles 
        WHERE id = $1
    `, id).Scan(
        &article.ID, &article.Title, &article.Content, &article.AuthorID,
        &article.Category, &article.CreatedAt, &article.UpdatedAt,
    )

    if err != nil {
        return nil, err
    }

    return &article, nil
}

func (am *ArticleManager) GetArticlesByCategory(category string, limit, offset int) ([]Article, error) {
    rows, err := am.db.Query(`
        SELECT id, title, content, author_id, category, created_at, updated_at
        FROM articles 
        WHERE category = $1
        ORDER BY created_at DESC
        LIMIT $2 OFFSET $3
    `, category, limit, offset)

    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var articles []Article
    for rows.Next() {
        var article Article
        err := rows.Scan(
            &article.ID, &article.Title, &article.Content, &article.AuthorID,
            &article.Category, &article.CreatedAt, &article.UpdatedAt,
        )
        if err != nil {
            return nil, err
        }
        articles = append(articles, article)
    }

    return articles, nil
}

func (am *ArticleManager) UpdateArticle(id int, title, content, category string) error {
    _, err := am.db.Exec(`
        UPDATE articles 
        SET title = $1, content = $2, category = $3, updated_at = CURRENT_TIMESTAMP
        WHERE id = $4
    `, title, content, category, id)

    return err
}

func (am *ArticleManager) DeleteArticle(id int) error {
    _, err := am.db.Exec(`
        DELETE FROM articles 
        WHERE id = $1
    `, id)

    return err
}

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    am := NewArticleManager(db)

    // 创建表
    if err := am.CreateTable(); err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 创建文章
    articleID, err := am.CreateArticle(
        "Go语言入门",
        "这是一篇关于Go语言入门的文章...",
        1,
        "编程",
    )
    if err != nil {
        log.Fatal("创建文章失败:", err)
    }
    fmt.Printf("文章创建成功，ID: %d\n", articleID)

    // 查询文章
    article, err := am.GetArticleByID(articleID)
    if err != nil {
        log.Fatal("查询文章失败:", err)
    }
    fmt.Printf("文章标题: %s\n", article.Title)

    // 按分类查询文章
    articles, err := am.GetArticlesByCategory("编程", 10, 0)
    if err != nil {
        log.Fatal("查询文章列表失败:", err)
    }
    fmt.Printf("找到 %d 篇文章\n", len(articles))

    // 更新文章
    if err := am.UpdateArticle(articleID, "Go语言入门(修订版)", "更新后的内容...", "编程"); err != nil {
        log.Fatal("更新文章失败:", err)
    }
    fmt.Println("文章更新成功")
}

练习3：商品库存管理¶

使用事务处理库存增减，确保数据一致性

package main

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

    _ "github.com/lib/pq"
)

type Product struct {
    ID       int
    Name     string
    Price    float64
    Stock    int
    Category string
}

type InventoryManager struct {
    db *sql.DB
}

func NewInventoryManager(db *sql.DB) *InventoryManager {
    return &InventoryManager{db: db}
}

func (im *InventoryManager) CreateTable() error {
    _, err := im.db.Exec(`
        CREATE TABLE IF NOT EXISTS products (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            price DECIMAL(10,2) NOT NULL,
            stock INTEGER NOT NULL DEFAULT 0,
            category VARCHAR(50) NOT NULL
        );

        CREATE TABLE IF NOT EXISTS inventory_logs (
            id SERIAL PRIMARY KEY,
            product_id INTEGER NOT NULL REFERENCES products(id),
            change_amount INTEGER NOT NULL,
            reason VARCHAR(200) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    `)
    return err
}

func (im *InventoryManager) AddProduct(name string, price float64, stock int, category string) (int, error) {
    var id int
    err := im.db.QueryRow(`
        INSERT INTO products (name, price, stock, category) 
        VALUES ($1, $2, $3, $4)
        RETURNING id
    `, name, price, stock, category).Scan(&id)

    return id, err
}

func (im *InventoryManager) UpdateStock(productID, amount int, reason string) error {
    tx, err := im.db.Begin()
    if err != nil {
        return err
    }

    // 检查库存是否足够（如果是减少库存）
    if amount < 0 {
        var currentStock int
        err := tx.QueryRow("SELECT stock FROM products WHERE id = $1 FOR UPDATE", productID).Scan(&currentStock)
        if err != nil {
            tx.Rollback()
            return err
        }

        if currentStock+amount < 0 {
            tx.Rollback()
            return fmt.Errorf("库存不足，当前库存: %d", currentStock)
        }
    }

    // 更新库存
    _, err = tx.Exec(`
        UPDATE products 
        SET stock = stock + $1 
        WHERE id = $2
    `, amount, productID)
    if err != nil {
        tx.Rollback()
        return err
    }

    // 记录库存变更日志
    _, err = tx.Exec(`
        INSERT INTO inventory_logs (product_id, change_amount, reason) 
        VALUES ($1, $2, $3)
    `, productID, amount, reason)
    if err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit()
}

func (im *InventoryManager) GetProductStock(productID int) (int, error) {
    var stock int
    err := im.db.QueryRow(`
        SELECT stock 
        FROM products 
        WHERE id = $1
    `, productID).Scan(&stock)

    return stock, err
}

func (im *InventoryManager) GetInventoryLogs(productID int, limit int) ([]map[string]interface{}, error) {
    rows, err := im.db.Query(`
        SELECT id, product_id, change_amount, reason, created_at
        FROM inventory_logs 
        WHERE product_id = $1
        ORDER BY created_at DESC
        LIMIT $2
    `, productID, limit)

    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var logs []map[string]interface{}
    for rows.Next() {
        var (
            id           int
            productID    int
            changeAmount int
            reason       string
            createdAt    sql.NullTime
        )
        err := rows.Scan(&id, &productID, &changeAmount, &reason, &createdAt)
        if err != nil {
            return nil, err
        }

        logEntry := map[string]interface{}{
            "id":            id,
            "product_id":    productID,
            "change_amount": changeAmount,
            "reason":        reason,
            "created_at":    createdAt,
        }
        logs = append(logs, logEntry)
    }

    return logs, nil
}

func main() {
    connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    im := NewInventoryManager(db)

    // 创建表
    if err := im.CreateTable(); err != nil {
        log.Fatal("创建表失败:", err)
    }

    // 添加商品
    productID, err := im.AddProduct("笔记本电脑", 5999.99, 10, "电子产品")
    if err != nil {
        log.Fatal("添加商品失败:", err)
    }
    fmt.Printf("商品添加成功，ID: %d\n", productID)

    // 增加库存
    if err := im.UpdateStock(productID, 5, "采购入库"); err != nil {
        log.Fatal("增加库存失败:", err)
    }
    fmt.Println("库存增加成功")

    // 减少库存
    if err := im.UpdateStock(productID, -3, "销售出库"); err != nil {
        log.Fatal("减少库存失败:", err)
    }
    fmt.Println("库存减少成功")

    // 查询库存
    stock, err := im.GetProductStock(productID)
    if err != nil {
        log.Fatal("查询库存失败:", err)
    }
    fmt.Printf("当前库存: %d\n", stock)

    // 尝试减少超过库存数量的商品（应该失败）
    if err := im.UpdateStock(productID, -20, "销售出库"); err != nil {
        fmt.Printf("预期中的错误: %v\n", err)
    } else {
        fmt.Println("库存减少成功")
    }

    // 查询库存变更记录
    logs, err := im.GetInventoryLogs(productID, 10)
    if err != nil {
        log.Fatal("查询库存记录失败:", err)
    }
    fmt.Printf("库存变更记录: %+v\n", logs)
}

这份教程涵盖了PostgreSQL在Go中的基本操作和特色功能，包括环境准备、连接数据库、CRUD操作、数据类型处理、PostgreSQL特色功能以及实用技巧。通过三个实战练习，你可以巩固所学知识并应用到实际项目中。

记得在实际开发中，要根据具体需求调整代码，并始终注意错误处理和安全性问题。祝你学习愉快！

详细内容待补充...