1 介绍
1.1 定义
01.技术概述
a.基本概念
Go SQLite是Go语言中操作SQLite数据库的技术方案，通过database/sql标准接口结合SQLite驱动实现对嵌入式数据库的访问。SQLite是一个轻量级的嵌入式关系型数据库，无需独立服务器进程，数据存储在单个文件中，非常适合桌面应用、移动应用和嵌入式系统使用。
b.核心驱动
a.go-sqlite3驱动
a.功能说明
go-sqlite3是Go语言中最成熟的SQLite驱动，基于CGO实现与SQLite C库的绑定。该驱动完整实现了database/sql接口，支持SQLite的所有特性，包括事务、预编译语句、自定义函数等。由于依赖CGO，编译时需要C编译器环境。
b.代码示例
---
// go-sqlite3基础连接示例
package main
import (
"database/sql"
"fmt"
"log"
// 导入go-sqlite3驱动，使用下划线表示仅执行init函数注册驱动
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 打开或创建数据库文件
// 如果文件不存在，SQLite会自动创建
db, err := sql.Open("sqlite3", "./test.db")
if err != nil {
log.Fatal("打开数据库失败:", err)
}
defer db.Close()
// 验证数据库连接是否有效
if err := db.Ping(); err != nil {
log.Fatal("数据库连接验证失败:", err)
}
fmt.Println("成功连接到SQLite数据库")
}
---
b.modernc-sqlite驱动
a.功能说明
modernc.org/sqlite是纯Go实现的SQLite驱动，无需CGO依赖。该驱动将SQLite的C代码通过c2go工具转换为Go代码，实现了跨平台编译的便利性。虽然性能略低于CGO版本，但在无法使用CGO的环境中是最佳选择。
b.代码示例
---
// modernc-sqlite纯Go驱动连接示例
package main
import (
"database/sql"
"fmt"
"log"
// 导入纯Go实现的SQLite驱动
_ "modernc.org/sqlite"
)
func main() {
// 使用sqlite驱动名称（注意与go-sqlite3的sqlite3不同）
db, err := sql.Open("sqlite", "./app.db")
if err != nil {
log.Fatal("打开数据库失败:", err)
}
defer db.Close()
// 设置连接参数，纯Go驱动支持的参数略有不同
db.SetMaxOpenConns(1) // SQLite单文件建议单连接
if err := db.Ping(); err != nil {
log.Fatal("连接验证失败:", err)
}
fmt.Println("纯Go SQLite驱动连接成功")
}
---
02.标准接口
a.database/sql包
a.设计理念
database/sql是Go标准库提供的通用数据库接口，定义了与关系型数据库交互的标准API。该包采用接口抽象设计，通过驱动注册机制实现对不同数据库的统一访问，开发者无需关心底层数据库差异即可编写可移植的数据库代码。
b.核心类型
---
// database/sql核心类型说明
package main
import (
"context"
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// sql.DB: 数据库连接池，线程安全，应全局复用
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 创建测试表
_, err = db.Exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
if err != nil {
log.Fatal(err)
}
// sql.Tx: 事务对象，用于执行事务操作
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
// sql.Stmt: 预编译语句，可复用提高性能
stmt, err := tx.Prepare("INSERT INTO users (name) VALUES (?)")
if err != nil {
tx.Rollback()
log.Fatal(err)
}
defer stmt.Close()
// 执行预编译语句
_, err = stmt.Exec("Alice")
if err != nil {
tx.Rollback()
log.Fatal(err)
}
tx.Commit()
// sql.Rows: 查询结果集，需要遍历读取
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id int
var name string
if err := rows.Scan(&id, &name); err != nil {
log.Fatal(err)
}
fmt.Printf("用户: ID=%d, Name=%s\n", id, name)
}
// sql.Row: 单行查询结果
var count int
err = db.QueryRow("SELECT COUNT(*) FROM users").Scan(&count)
if err != nil {
log.Fatal(err)
}
fmt.Printf("用户总数: %d\n", count)
}
---
b.驱动注册机制
a.功能说明
SQLite驱动通过sql.Register函数在init阶段自动注册到database/sql包中。注册后，开发者使用sql.Open函数通过驱动名称获取数据库连接。这种设计实现了驱动的即插即用，只需导入驱动包即可使用。
b.代码示例
---
// 驱动注册机制演示
package main
import (
"database/sql"
"fmt"
"log"
// 驱动包的init函数会自动调用sql.Register注册驱动
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 查看已注册的所有驱动
drivers := sql.Drivers()
fmt.Println("已注册的数据库驱动:")
for _, driver := range drivers {
fmt.Printf(" - %s\n", driver)
}
// 使用已注册的驱动名称打开数据库
// 驱动名称由驱动包在注册时指定
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal("打开数据库失败:", err)
}
defer db.Close()
// 验证连接
var version string
err = db.QueryRow("SELECT sqlite_version()").Scan(&version)
if err != nil {
log.Fatal(err)
}
fmt.Printf("SQLite版本: %s\n", version)
}
---
1.2 核心概念
01.嵌入式数据库
a.概念定义
a.嵌入式特性
嵌入式数据库是直接集成到应用程序中运行的数据库系统，无需独立的服务器进程。SQLite作为典型的嵌入式数据库，以库的形式链接到应用程序，数据库引擎与应用共享同一进程空间，消除了进程间通信开销。
b.文件存储
SQLite将整个数据库存储在单个磁盘文件中，包括表结构、索引、数据和元信息。文件格式跨平台兼容，可以在不同操作系统和架构之间直接复制使用。文件扩展名通常为.db、.sqlite或.sqlite3。
b.与服务端数据库对比
a.架构差异
---
// 嵌入式vs服务端数据库架构对比示例
package main
import (
"database/sql"
"fmt"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// SQLite嵌入式数据库特点演示
// 1. 无需网络连接，直接操作文件
dbPath := "./embedded.db"
// 检查数据库文件是否存在
if _, err := os.Stat(dbPath); os.IsNotExist(err) {
fmt.Println("数据库文件不存在，将自动创建")
}
// 2. 连接字符串就是文件路径，无需主机、端口、用户名密码
db, err := sql.Open("sqlite3", dbPath)
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 3. 数据库引擎在应用进程内运行
fmt.Printf("进程ID: %d\n", os.Getpid())
fmt.Println("SQLite引擎运行在同一进程中，无需服务器")
// 4. 获取数据库文件信息
fileInfo, err := os.Stat(dbPath)
if err == nil {
fmt.Printf("数据库文件大小: %d bytes\n", fileInfo.Size())
fmt.Printf("最后修改时间: %v\n", fileInfo.ModTime())
}
// 5. 执行查询验证
var version string
db.QueryRow("SELECT sqlite_version()").Scan(&version)
fmt.Printf("嵌入的SQLite版本: %s\n", version)
}
---
b.适用场景对比
嵌入式数据库适合单用户应用、桌面程序、移动应用、物联网设备等场景，数据量通常在GB级别以下。服务端数据库适合多用户并发访问、分布式系统、高可用要求的场景，支持TB甚至PB级别数据存储。
02.连接字符串
a.基本格式
a.文件路径模式
SQLite连接字符串最基本的形式就是数据库文件路径，可以是相对路径或绝对路径。连接时如果文件不存在会自动创建，如果已存在则直接打开。
b.代码示例
---
// SQLite连接字符串格式示例
package main
import (
"database/sql"
"fmt"
"log"
"os"
"path/filepath"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 相对路径：在当前工作目录创建/打开数据库
relPath := "./data/app.db"
// 确保目录存在
os.MkdirAll(filepath.Dir(relPath), 0755)
db1, err := sql.Open("sqlite3", relPath)
if err != nil {
log.Fatal(err)
}
defer db1.Close()
fmt.Printf("相对路径数据库: %s\n", relPath)
// 绝对路径：明确指定数据库位置
absPath := "/tmp/absolute.db"
db2, err := sql.Open("sqlite3", absPath)
if err != nil {
log.Fatal(err)
}
defer db2.Close()
fmt.Printf("绝对路径数据库: %s\n", absPath)
// 获取实际工作目录
wd, _ := os.Getwd()
fmt.Printf("当前工作目录: %s\n", wd)
}
---
b.特殊模式
a.内存数据库
a.功能说明
使用:memory:作为连接字符串可创建内存数据库，数据完全存储在RAM中，连接关闭后数据消失。内存数据库适合临时数据处理、单元测试、缓存场景，访问速度极快。
b.代码示例
---
// 内存数据库使用示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 创建内存数据库
// :memory: 表示数据库完全在内存中
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 创建表并插入数据
_, err = db.Exec(`
CREATE TABLE cache (
key TEXT PRIMARY KEY,
value TEXT,
expires_at INTEGER
)
`)
if err != nil {
log.Fatal(err)
}
// 插入缓存数据
_, err = db.Exec(`INSERT INTO cache (key, value) VALUES (?, ?)`,
"session:123", `{"user_id": 1, "name": "Alice"}`)
if err != nil {
log.Fatal(err)
}
// 查询数据
var value string
err = db.QueryRow(`SELECT value FROM cache WHERE key = ?`, "session:123").Scan(&value)
if err != nil {
log.Fatal(err)
}
fmt.Printf("缓存值: %s\n", value)
// 获取内存数据库状态
var pageCount, pageSize int
db.QueryRow("PRAGMA page_count").Scan(&pageCount)
db.QueryRow("PRAGMA page_size").Scan(&pageSize)
fmt.Printf("内存占用: 约 %d KB\n", pageCount*pageSize/1024)
fmt.Println("注意: 程序结束后，内存数据库中的所有数据将丢失")
}
---
b.共享内存数据库
a.功能说明
普通的:memory:数据库每个连接都是独立的实例。使用file::memory:?mode=memory&cache=shared可以创建多个连接共享的内存数据库，适合需要并发访问内存数据的场景。
b.代码示例
---
// 共享内存数据库示例
package main
import (
"database/sql"
"fmt"
"log"
"sync"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 共享内存数据库连接字符串
// cache=shared 允许多个连接共享同一个内存数据库
dsn := "file::memory:?mode=memory&cache=shared"
// 创建第一个连接并初始化表
db1, err := sql.Open("sqlite3", dsn)
if err != nil {
log.Fatal(err)
}
defer db1.Close()
_, err = db1.Exec(`CREATE TABLE shared_data (id INTEGER PRIMARY KEY, msg TEXT)`)
if err != nil {
log.Fatal(err)
}
// 创建第二个连接，共享同一个内存数据库
db2, err := sql.Open("sqlite3", dsn)
if err != nil {
log.Fatal(err)
}
defer db2.Close()
// 并发写入测试
var wg sync.WaitGroup
for i := 0; i < 5; i++ {
wg.Add(1)
go func(n int) {
defer wg.Done()
db := db1
if n%2 == 0 {
db = db2 // 交替使用两个连接
}
_, err := db.Exec(`INSERT INTO shared_data (msg) VALUES (?)`,
fmt.Sprintf("消息来自goroutine-%d", n))
if err != nil {
log.Printf("插入失败: %v", err)
}
}(i)
}
wg.Wait()
// 从任意连接读取数据
rows, _ := db2.Query(`SELECT id, msg FROM shared_data ORDER BY id`)
defer rows.Close()
fmt.Println("共享内存数据库中的数据:")
for rows.Next() {
var id int
var msg string
rows.Scan(&id, &msg)
fmt.Printf(" ID=%d: %s\n", id, msg)
}
}
---
03.DSN参数
a.连接参数详解
a.常用参数
SQLite DSN支持多种参数配置，通过URL查询字符串格式附加在文件路径后。常用参数包括mode（访问模式）、cache（缓存模式）、_journal_mode（日志模式）、_busy_timeout（忙等待超时）等。
b.代码示例
---
// SQLite DSN参数配置示例
package main
import (
"database/sql"
"fmt"
"log"
"net/url"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 构建DSN连接字符串
// 使用url.Values构建参数，确保正确编码
params := url.Values{}
// 访问模式: rwc=读写创建, ro=只读, rw=读写(不创建)
params.Set("mode", "rwc")
// 缓存模式: shared=共享缓存, private=私有缓存
params.Set("cache", "shared")
// 日志模式: WAL性能更好，DELETE是默认模式
params.Set("_journal_mode", "WAL")
// 忙等待超时(毫秒): 遇到锁时等待而非立即返回错误
params.Set("_busy_timeout", "5000")
// 同步模式: NORMAL平衡性能和安全性
params.Set("_synchronous", "NORMAL")
// 外键约束: 默认关闭，需要显式开启
params.Set("_foreign_keys", "ON")
// 组装完整DSN
dsn := fmt.Sprintf("file:./configured.db?%s", params.Encode())
fmt.Printf("DSN: %s\n", dsn)
db, err := sql.Open("sqlite3", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 验证配置是否生效
var journalMode, synchronous string
var foreignKeys int
db.QueryRow("PRAGMA journal_mode").Scan(&journalMode)
db.QueryRow("PRAGMA synchronous").Scan(&synchronous)
db.QueryRow("PRAGMA foreign_keys").Scan(&foreignKeys)
fmt.Printf("日志模式: %s\n", journalMode)
fmt.Printf("同步模式: %s\n", synchronous)
fmt.Printf("外键约束: %v\n", foreignKeys == 1)
}
---
b.只读模式
a.功能说明
只读模式打开数据库时，任何写入操作都会返回错误。这种模式适合数据分析、报表生成等只需读取数据的场景，可以提高并发性能并防止意外修改数据。
b.代码示例
---
// 只读模式数据库访问
package main
import (
"database/sql"
"fmt"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbPath := "./readonly_test.db"
// 首先创建并初始化数据库
initDB, err := sql.Open("sqlite3", dbPath)
if err != nil {
log.Fatal(err)
}
initDB.Exec(`CREATE TABLE IF NOT EXISTS configs (key TEXT, value TEXT)`)
initDB.Exec(`INSERT OR REPLACE INTO configs VALUES ('version', '1.0.0')`)
initDB.Close()
// 以只读模式打开数据库
// mode=ro 表示只读模式
dsn := fmt.Sprintf("file:%s?mode=ro", dbPath)
db, err := sql.Open("sqlite3", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 读取操作正常
var version string
err = db.QueryRow(`SELECT value FROM configs WHERE key = 'version'`).Scan(&version)
if err != nil {
log.Fatal(err)
}
fmt.Printf("读取成功，版本: %s\n", version)
// 写入操作会失败
_, err = db.Exec(`INSERT INTO configs VALUES ('new_key', 'new_value')`)
if err != nil {
fmt.Printf("只读模式写入失败(预期行为): %v\n", err)
}
// 清理测试文件
os.Remove(dbPath)
}
---
1.3 优缺点
01.核心优势
a.零配置部署
a.功能说明
SQLite无需安装、配置或管理数据库服务器。只需在Go项目中导入驱动包，即可直接使用数据库功能。数据库文件可以随应用程序一起分发，用户无需任何额外操作即可运行应用。
b.代码示例
---
// 零配置部署示例：开箱即用的数据库
package main
import (
"database/sql"
"fmt"
"log"
"os"
"path/filepath"
_ "github.com/mattn/go-sqlite3"
)
// AppConfig 应用配置结构
type AppConfig struct {
DataDir string
DBPath string
}
func main() {
// 自动获取应用数据目录
homeDir, _ := os.UserHomeDir()
appDataDir := filepath.Join(homeDir, ".myapp")
// 自动创建数据目录
if err := os.MkdirAll(appDataDir, 0755); err != nil {
log.Fatal(err)
}
// 数据库路径
dbPath := filepath.Join(appDataDir, "data.db")
// 打开数据库（自动创建）
db, err := sql.Open("sqlite3", dbPath)
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 自动初始化表结构
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS app_settings (
key TEXT PRIMARY KEY,
value TEXT,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`)
if err != nil {
log.Fatal(err)
}
fmt.Println("应用数据库已就绪")
fmt.Printf("数据目录: %s\n", appDataDir)
fmt.Printf("数据库文件: %s\n", dbPath)
fmt.Println("无需任何额外配置或服务器安装")
}
---
b.跨平台兼容
a.功能说明
SQLite数据库文件��式是跨平台兼容的，在Windows、Linux、macOS等系统之间可以直接复制使用。Go编译的可执行文件可以在不同平台运行，数据库文件也能无缝迁移。
b.代码示例
---
// 跨平台兼容性检测示例
package main
import (
"database/sql"
"encoding/binary"
"fmt"
"io"
"log"
"os"
"runtime"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbPath := "./cross_platform.db"
// 创建数据库并写入平台信息
db, err := sql.Open("sqlite3", dbPath)
if err != nil {
log.Fatal(err)
}
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS platform_info (
id INTEGER PRIMARY KEY,
os TEXT,
arch TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`)
if err != nil {
log.Fatal(err)
}
// 记录当前平台信息
_, err = db.Exec(`INSERT INTO platform_info (os, arch) VALUES (?, ?)`,
runtime.GOOS, runtime.GOARCH)
if err != nil {
log.Fatal(err)
}
db.Close()
// 读取数据库文件头，验证格式
file, _ := os.Open(dbPath)
defer file.Close()
header := make([]byte, 100)
io.ReadFull(file, header)
// SQLite文件头魔数
magic := string(header[0:16])
pageSize := binary.BigEndian.Uint16(header[16:18])
fmt.Printf("当前平台: %s/%s\n", runtime.GOOS, runtime.GOARCH)
fmt.Printf("SQLite文件头: %s\n", magic)
fmt.Printf("页面大小: %d bytes\n", pageSize)
fmt.Println("此数据库文件可在任何支持SQLite的平台上使用")
os.Remove(dbPath)
}
---
c.单文件存储
a.功能说明
整个数据库（包括多个表、索引、触发器等）都存储在单个文件中，便于备份、迁移和版本控制。可以简单地通过复制文件实现数据库备份，无需复杂的导出导入过程。
b.代码示例
---
// 单文件存储与备份示例
package main
import (
"database/sql"
"fmt"
"io"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
func main() {
srcPath := "./source.db"
backupPath := "./backup.db"
// 创建源数据库
srcDB, err := sql.Open("sqlite3", srcPath)
if err != nil {
log.Fatal(err)
}
// 创建表和数据
srcDB.Exec(`CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)`)
srcDB.Exec(`INSERT INTO products (name, price) VALUES ('笔记本', 5999.00)`)
srcDB.Exec(`INSERT INTO products (name, price) VALUES ('手机', 3999.00)`)
srcDB.Close()
// 简单文件复制实现备份
srcFile, _ := os.Open(srcPath)
dstFile, _ := os.Create(backupPath)
bytesCopied, _ := io.Copy(dstFile, srcFile)
srcFile.Close()
dstFile.Close()
fmt.Printf("备份完成，复制了 %d 字节\n", bytesCopied)
// 验证备份文件
backupDB, err := sql.Open("sqlite3", backupPath)
if err != nil {
log.Fatal(err)
}
defer backupDB.Close()
var count int
backupDB.QueryRow(`SELECT COUNT(*) FROM products`).Scan(&count)
fmt.Printf("备份数据库中有 %d 条产品记录\n", count)
// 清理
os.Remove(srcPath)
os.Remove(backupPath)
}
---
02.主要劣势
a.并发写入限制
a.功能说明
SQLite使用文件级锁，同一时刻只允许一个写入操作。在高并发写入场景下，大量请求会被串行化处理，导致性能瓶颈。WAL模式可以改善读写并发，但写入仍然是串行的。
b.代码示例
---
// 并发写入限制演示
package main
import (
"database/sql"
"fmt"
"log"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 设置忙等待超时，避免立即返回锁错误
db, err := sql.Open("sqlite3", "./concurrent.db?_busy_timeout=5000")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`CREATE TABLE IF NOT EXISTS logs (id INTEGER PRIMARY KEY, msg TEXT, ts DATETIME)`)
// 模拟并发写入
var wg sync.WaitGroup
startTime := time.Now()
concurrency := 10
writesPerGoroutine := 100
for i := 0; i < concurrency; i++ {
wg.Add(1)
go func(workerID int) {
defer wg.Done()
for j := 0; j < writesPerGoroutine; j++ {
_, err := db.Exec(`INSERT INTO logs (msg, ts) VALUES (?, ?)`,
fmt.Sprintf("Worker %d, Write %d", workerID, j),
time.Now())
if err != nil {
log.Printf("写入失败: %v", err)
}
}
}(i)
}
wg.Wait()
elapsed := time.Since(startTime)
var count int
db.QueryRow(`SELECT COUNT(*) FROM logs`).Scan(&count)
fmt.Printf("并发数: %d, 每协程写入: %d\n", concurrency, writesPerGoroutine)
fmt.Printf("总写入: %d 条, 耗时: %v\n", count, elapsed)
fmt.Printf("平均每秒写入: %.2f 条\n", float64(count)/elapsed.Seconds())
fmt.Println("注意: SQLite写入是串行化的，高并发场景需考虑其他方案")
}
---
b.无网络访问能力
a.功能说明
SQLite是嵌入式数据库，只能本地访问，不支持通过网络连接。如果需要多个应用或服务器共享数据，必须借助其他机制如文件共享、API服务或迁移到服务端数据库。
b.代码示例
---
// 本地访问限制说明示例
package main
import (
"database/sql"
"fmt"
"log"
"net"
"os"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// SQLite只能通过文件路径访问
validPaths := []string{
"./local.db", // 相对路径
"/tmp/absolute.db", // 绝对路径
":memory:", // 内存数据库
}
// 无效的网络连接尝试
invalidPaths := []string{
"tcp://localhost:5432/mydb", // TCP连接(无效)
"postgres://user:pass@host/db", // PostgreSQL风格(无效)
"mysql://user:pass@host/db", // MySQL风格(无效)
}
fmt.Println("有效的SQLite连接路径:")
for _, path := range validPaths {
fmt.Printf(" ✓ %s\n", path)
}
fmt.Println("\n无效的连接路径(SQLite不支持网络):")
for _, path := range invalidPaths {
fmt.Printf(" ✗ %s\n", path)
}
// 如果需要网络访问，可以封装HTTP服务
fmt.Println("\n解决方案: 通过HTTP API暴露数据库功能")
// 获取本机IP地址
addrs, _ := net.InterfaceAddrs()
for _, addr := range addrs {
if ipnet, ok := addr.(*net.IPNet); ok && !ipnet.IP.IsLoopback() {
if ipnet.IP.To4() != nil {
fmt.Printf(" 可在 http://%s:8080/api/data 提供访问\n", ipnet.IP)
break
}
}
}
os.Remove("./local.db")
}
---
c.大规模数据局限
a.功能说明
SQLite适合处理GB级别的数据，对于TB级别的大规模数据存储和复杂查询场景，性能和功能可能不足。缺乏分布式能力、高级索引类型和复杂的查询优化器。
b.代码示例
---
// 数据规模评估示例
package main
import (
"database/sql"
"fmt"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", "./scale_test.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 获取SQLite的编译限制
var maxPageCount, pageSize int64
db.QueryRow("PRAGMA max_page_count").Scan(&maxPageCount)
db.QueryRow("PRAGMA page_size").Scan(&pageSize)
// 计算理论最大数据库大小
maxSizeBytes := maxPageCount * pageSize
maxSizeGB := float64(maxSizeBytes) / (1024 * 1024 * 1024)
maxSizeTB := maxSizeGB / 1024
fmt.Println("SQLite数据规模限制:")
fmt.Printf(" 最大页面数: %d\n", maxPageCount)
fmt.Printf(" 页面大小: %d bytes\n", pageSize)
fmt.Printf(" 理论最大大小: %.2f TB\n", maxSizeTB)
fmt.Println("\n实际建议:")
fmt.Println(" ✓ 适合: < 1GB (性能最佳)")
fmt.Println(" ✓ 可用: 1-10GB (需要优化)")
fmt.Println(" △ 谨慎: 10-100GB (可能遇到性能问题)")
fmt.Println(" ✗ 不推荐: > 100GB (应考虑其他数据库)")
os.Remove("./scale_test.db")
}
---
03.适用性评估
a.选择决策
a.功能说明
选择SQLite需要综合考虑应用场景、数据规模、并发需求和部署环境。以下提供一个简单的决策框架帮助判断SQLite是否适合你的项目。
b.代码示例
---
// SQLite适用性决策工具
package main
import (
"fmt"
)
// RequirementScore 需求评分
type RequirementScore struct {
DataSize int // 数据规模 1-5
Concurrency int // 并发需求 1-5
NetworkAccess int // 网络访问需求 1-5
Simplicity int // 简单性需求 1-5
Portability int // 可移植性需求 1-5
}
func evaluateSQLiteFit(req RequirementScore) {
fmt.Println("=== SQLite 适用性评估 ===")
// SQLite优势项
advantages := 0
disadvantages := 0
// 数据规模评估
if req.DataSize <= 2 {
fmt.Println("✓ 数据规模小，SQLite适合")
advantages++
} else if req.DataSize >= 4 {
fmt.Println("✗ 数据规模大，考虑PostgreSQL/MySQL")
disadvantages++
}
// 并发评估
if req.Concurrency <= 2 {
fmt.Println("✓ 并发需求低，SQLite适合")
advantages++
} else if req.Concurrency >= 4 {
fmt.Println("✗ 高并发写入，需要服务端数据库")
disadvantages++
}
// 网络访问评估
if req.NetworkAccess <= 2 {
fmt.Println("✓ 本地访问为主，SQLite适合")
advantages++
} else {
fmt.Println("✗ 需要网络访问，考虑其他方案")
disadvantages++
}
// 简单性评估
if req.Simplicity >= 4 {
fmt.Println("✓ 追求简单，SQLite零配置优势明显")
advantages++
}
// 可移植性评估
if req.Portability >= 4 {
fmt.Println("✓ 需要高可移植性，SQLite单文件优势明显")
advantages++
}
fmt.Printf("\n优势项: %d, 劣势项: %d\n", advantages, disadvantages)
if advantages > disadvantages {
fmt.Println("建议: 使用 SQLite")
} else {
fmt.Println("建议: 考虑其他数据库方案")
}
}
func main() {
// 示例：评估一个桌面应用
desktopApp := RequirementScore{
DataSize: 1, // 数据量小
Concurrency: 1, // 单用户
NetworkAccess: 1, // 本地使用
Simplicity: 5, // 追求简单
Portability: 5, // 需要便携
}
fmt.Println("场景: 桌面应用")
evaluateSQLiteFit(desktopApp)
fmt.Println("\n" + "=".repeat(40) + "\n")
// 示例：评估一个Web服务
webService := RequirementScore{
DataSize: 4, // 数据量大
Concurrency: 5, // 高并发
NetworkAccess: 5, // 网络访问
Simplicity: 3, // 中等
Portability: 2, // 不重要
}
fmt.Println("场景: Web服务后端")
evaluateSQLiteFit(webService)
}
// repeat 重复字符串
func (s string) repeat(n int) string {
result := ""
for i := 0; i < n; i++ {
result += s
}
return result
}
---
1.4 使用场景
01.桌面应用程序
a.配置管理
a.功能说明
桌面应用通常需要持久化存储用户配置、应用设置和状态信息。SQLite提供比JSON或XML更强大的查询能力，支持复杂的配置关系和条件检索，同时保持轻量级特性。
b.代码示例
---
// 桌面应用配置管理示例
package main
import (
"database/sql"
"encoding/json"
"fmt"
"log"
"os"
"path/filepath"
_ "github.com/mattn/go-sqlite3"
)
// AppSettings 应用设置结构
type AppSettings struct {
Theme string `json:"theme"`
Language string `json:"language"`
AutoSave bool `json:"auto_save"`
SaveInterval int `json:"save_interval"`
}
// ConfigManager 配置管理器
type ConfigManager struct {
db *sql.DB
}
func NewConfigManager() (*ConfigManager, error) {
// 获取用户配置目录
homeDir, _ := os.UserHomeDir()
configDir := filepath.Join(homeDir, ".mydesktopapp")
os.MkdirAll(configDir, 0755)
db, err := sql.Open("sqlite3", filepath.Join(configDir, "config.db"))
if err != nil {
return nil, err
}
// 初始化配置表
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS app_config (
key TEXT PRIMARY KEY,
value TEXT,
category TEXT,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`)
if err != nil {
return nil, err
}
return &ConfigManager{db: db}, nil
}
func (cm *ConfigManager) SaveSettings(settings AppSettings) error {
data, err := json.Marshal(settings)
if err != nil {
return err
}
_, err = cm.db.Exec(`
INSERT OR REPLACE INTO app_config (key, value, category)
VALUES ('app_settings', ?, 'general')`,
string(data))
return err
}
func (cm *ConfigManager) LoadSettings() (*AppSettings, error) {
var data string
err := cm.db.QueryRow(`SELECT value FROM app_config WHERE key = 'app_settings'`).Scan(&data)
if err == sql.ErrNoRows {
// 返回默认设置
return &AppSettings{
Theme: "light",
Language: "zh-CN",
AutoSave: true,
SaveInterval: 300,
}, nil
}
if err != nil {
return nil, err
}
var settings AppSettings
err = json.Unmarshal([]byte(data), &settings)
return &settings, err
}
func (cm *ConfigManager) Close() error {
return cm.db.Close()
}
func main() {
cm, err := NewConfigManager()
if err != nil {
log.Fatal(err)
}
defer cm.Close()
// 保存设置
settings := AppSettings{
Theme: "dark",
Language: "en-US",
AutoSave: true,
SaveInterval: 600,
}
cm.SaveSettings(settings)
// 加载设置
loaded, _ := cm.LoadSettings()
fmt.Printf("当前配置: 主题=%s, 语言=%s, 自动保存=%v\n",
loaded.Theme, loaded.Language, loaded.AutoSave)
}
---
b.本地缓存
a.功能说明
桌面应用可以使用SQLite缓存从服务器下载的数据，实现离线访问和快速响应。相比内存缓存，SQLite提供持久化能力，应用重启后数据仍然可用。
b.代码示例
---
// 桌面应用本地缓存示例
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
// CacheItem 缓存项
type CacheItem struct {
Key string
Value string
ExpiresAt time.Time
}
// LocalCache 本地缓存管理器
type LocalCache struct {
db *sql.DB
}
func NewLocalCache(dbPath string) (*LocalCache, error) {
db, err := sql.Open("sqlite3", dbPath)
if err != nil {
return nil, err
}
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS cache (
key TEXT PRIMARY KEY,
value TEXT,
expires_at INTEGER,
created_at INTEGER DEFAULT (strftime('%s', 'now'))
)
`)
if err != nil {
return nil, err
}
// 创建过期时间索引
db.Exec(`CREATE INDEX IF NOT EXISTS idx_expires ON cache(expires_at)`)
return &LocalCache{db: db}, nil
}
func (lc *LocalCache) Set(key, value string, ttl time.Duration) error {
expiresAt := time.Now().Add(ttl).Unix()
_, err := lc.db.Exec(`
INSERT OR REPLACE INTO cache (key, value, expires_at)
VALUES (?, ?, ?)`,
key, value, expiresAt)
return err
}
func (lc *LocalCache) Get(key string) (string, bool, error) {
var value string
var expiresAt int64
err := lc.db.QueryRow(`
SELECT value, expires_at FROM cache WHERE key = ?`, key).Scan(&value, &expiresAt)
if err == sql.ErrNoRows {
return "", false, nil
}
if err != nil {
return "", false, err
}
// 检查是否过期
if time.Now().Unix() > expiresAt {
lc.db.Exec(`DELETE FROM cache WHERE key = ?`, key)
return "", false, nil
}
return value, true, nil
}
func (lc *LocalCache) CleanExpired() (int64, error) {
result, err := lc.db.Exec(`DELETE FROM cache WHERE expires_at < ?`, time.Now().Unix())
if err != nil {
return 0, err
}
return result.RowsAffected()
}
func main() {
cache, err := NewLocalCache(":memory:")
if err != nil {
log.Fatal(err)
}
// 缓存数据（5秒有效期）
cache.Set("user:123", `{"name": "Alice", "age": 30}`, 5*time.Second)
// 立即读取
if value, found, _ := cache.Get("user:123"); found {
fmt.Printf("缓存命中: %s\n", value)
}
// 等待过期
time.Sleep(6 * time.Second)
// 过期后读取
if _, found, _ := cache.Get("user:123"); !found {
fmt.Println("缓存已过期")
}
// 清理过期数据
deleted, _ := cache.CleanExpired()
fmt.Printf("清理了 %d 条过期缓存\n", deleted)
}
---
02.移动应用后端
a.数据同步
a.功能说明
移动应用可以使用SQLite作为本地数据库，存储从服务器同步的数据。支持增量同步、冲突解决和离线编辑，用户在无网络环境下也能正常使用应用。
b.代码示例
---
// 移动应用数据同步管理示例
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
// SyncRecord 同步记录
type SyncRecord struct {
ID int64
ResourceType string
ResourceID string
Action string // create, update, delete
Data string
SyncStatus string // pending, synced, failed
CreatedAt time.Time
}
// SyncManager 同步管理器
type SyncManager struct {
db *sql.DB
}
func NewSyncManager(dbPath string) (*SyncManager, error) {
db, err := sql.Open("sqlite3", dbPath)
if err != nil {
return nil, err
}
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS sync_queue (
id INTEGER PRIMARY KEY AUTOINCREMENT,
resource_type TEXT NOT NULL,
resource_id TEXT NOT NULL,
action TEXT NOT NULL,
data TEXT,
sync_status TEXT DEFAULT 'pending',
created_at INTEGER DEFAULT (strftime('%s', 'now')),
synced_at INTEGER
)
`)
if err != nil {
return nil, err
}
db.Exec(`CREATE INDEX IF NOT EXISTS idx_sync_status ON sync_queue(sync_status)`)
return &SyncManager{db: db}, nil
}
func (sm *SyncManager) AddToQueue(record SyncRecord) error {
_, err := sm.db.Exec(`
INSERT INTO sync_queue (resource_type, resource_id, action, data)
VALUES (?, ?, ?, ?)`,
record.ResourceType, record.ResourceID, record.Action, record.Data)
return err
}
func (sm *SyncManager) GetPendingRecords(limit int) ([]SyncRecord, error) {
rows, err := sm.db.Query(`
SELECT id, resource_type, resource_id, action, data, created_at
FROM sync_queue
WHERE sync_status = 'pending'
ORDER BY created_at
LIMIT ?`, limit)
if err != nil {
return nil, err
}
defer rows.Close()
var records []SyncRecord
for rows.Next() {
var r SyncRecord
var createdAt int64
rows.Scan(&r.ID, &r.ResourceType, &r.ResourceID, &r.Action, &r.Data, &createdAt)
r.CreatedAt = time.Unix(createdAt, 0)
records = append(records, r)
}
return records, nil
}
func (sm *SyncManager) MarkAsSynced(id int64) error {
_, err := sm.db.Exec(`
UPDATE sync_queue
SET sync_status = 'synced', synced_at = strftime('%s', 'now')
WHERE id = ?`, id)
return err
}
func main() {
sm, err := NewSyncManager(":memory:")
if err != nil {
log.Fatal(err)
}
// 添加待同步的变更
sm.AddToQueue(SyncRecord{
ResourceType: "note",
ResourceID: "note-123",
Action: "update",
Data: `{"title": "会议记录", "content": "..."}`,
})
sm.AddToQueue(SyncRecord{
ResourceType: "note",
ResourceID: "note-456",
Action: "create",
Data: `{"title": "待办事项", "content": "..."}`,
})
// 获取待同步记录
pending, _ := sm.GetPendingRecords(10)
fmt.Printf("待同步记录数: %d\n", len(pending))
// 模拟同步完成
for _, record := range pending {
fmt.Printf("正在同步: %s %s\n", record.Action, record.ResourceID)
sm.MarkAsSynced(record.ID)
}
// 验证同步状态
remaining, _ := sm.GetPendingRecords(10)
fmt.Printf("剩余待同步: %d\n", len(remaining))
}
---
03.嵌入式系统
a.物联网设备
a.功能说明
SQLite适合在资源受限的物联网设备上运行，存储传感器数据、设备日志和配置信息。低内存占用和无服务器架构非常适合边缘计算场景。
b.代码示例
---
// 物联网设备数据采集示例
package main
import (
"database/sql"
"fmt"
"log"
"math/rand"
"time"
_ "github.com/mattn/go-sqlite3"
)
// SensorReading 传感器读数
type SensorReading struct {
SensorID string
Temperature float64
Humidity float64
Timestamp time.Time
}
// IoTDataCollector 物联网数据采集器
type IoTDataCollector struct {
db *sql.DB
}
func NewIoTDataCollector(dbPath string) (*IoTDataCollector, error) {
// 使用WAL模式提高写入性能
db, err := sql.Open("sqlite3", fmt.Sprintf("%s?_journal_mode=WAL", dbPath))
if err != nil {
return nil, err
}
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS sensor_data (
id INTEGER PRIMARY KEY AUTOINCREMENT,
sensor_id TEXT NOT NULL,
temperature REAL,
humidity REAL,
timestamp INTEGER NOT NULL
)
`)
if err != nil {
return nil, err
}
// 创建时间索引用于时间范围查询
db.Exec(`CREATE INDEX IF NOT EXISTS idx_timestamp ON sensor_data(timestamp)`)
return &IoTDataCollector{db: db}, nil
}
func (idc *IoTDataCollector) RecordReading(reading SensorReading) error {
_, err := idc.db.Exec(`
INSERT INTO sensor_data (sensor_id, temperature, humidity, timestamp)
VALUES (?, ?, ?, ?)`,
reading.SensorID, reading.Temperature, reading.Humidity, reading.Timestamp.Unix())
return err
}
func (idc *IoTDataCollector) GetAverages(sensorID string, since time.Time) (float64, float64, error) {
var avgTemp, avgHumidity float64
err := idc.db.QueryRow(`
SELECT AVG(temperature), AVG(humidity)
FROM sensor_data
WHERE sensor_id = ? AND timestamp >= ?`,
sensorID, since.Unix()).Scan(&avgTemp, &avgHumidity)
return avgTemp, avgHumidity, err
}
func (idc *IoTDataCollector) PurgeOldData(before time.Time) (int64, error) {
result, err := idc.db.Exec(`DELETE FROM sensor_data WHERE timestamp < ?`, before.Unix())
if err != nil {
return 0, err
}
return result.RowsAffected()
}
func main() {
collector, err := NewIoTDataCollector(":memory:")
if err != nil {
log.Fatal(err)
}
// 模拟传感器数据采集
sensorID := "temp-sensor-001"
for i := 0; i < 100; i++ {
reading := SensorReading{
SensorID: sensorID,
Temperature: 20.0 + rand.Float64()*10.0,
Humidity: 50.0 + rand.Float64()*30.0,
Timestamp: time.Now().Add(-time.Duration(i) * time.Minute),
}
collector.RecordReading(reading)
}
// 计算最近1小时的平均值
avgTemp, avgHumidity, _ := collector.GetAverages(sensorID, time.Now().Add(-1*time.Hour))
fmt.Printf("最近1小时平均: 温度=%.2f°C, 湿度=%.2f\n", utilization)
}
---
b.B-Tree索引
a.功能说明
SQLite使用B-Tree数据结构存储表数据和索引。每个表对应一棵B-Tree,主键为隐式索引。B-Tree的节点存储在页面中,支持范围查询和顺序扫描,平衡树保证O(log N)的查询性能。
b.代码示例
---
// B-Tree索引性能对比
package main
import (
"database/sql"
"fmt"
"log"
"math/rand"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 创建测试表
db.Exec(`CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
email TEXT
)`)
// 插入测试数据
stmt, _ := db.Prepare(`INSERT INTO users (name, age, email) VALUES (?, ?, ?)`)
for i := 0; i < 10000; i++ {
stmt.Exec(
fmt.Sprintf("User%d", i),
rand.Intn(80)+18,
fmt.Sprintf("user%[email protected] ", i),
)
}
stmt.Close()
// 无索引查询
start := time.Now()
var count int
db.QueryRow(`SELECT COUNT(*) FROM users WHERE age > 50`).Scan(&count)
noIndexTime := time.Since(start)
fmt.Printf("无索引查询: %v, 结果: %d条\n", noIndexTime, count)
// 创建索引
db.Exec(`CREATE INDEX idx_age ON users(age)`)
// 有索引查询
start = time.Now()
db.QueryRow(`SELECT COUNT(*) FROM users WHERE age > 50`).Scan(&count)
withIndexTime := time.Since(start)
fmt.Printf("有索引查询: %v, 结果: %d条\n", withIndexTime, count)
// 性能提升
improvement := float64(noIndexTime) / float64(withIndexTime)
fmt.Printf("性能提升: %.2fx\n", improvement)
// 查看索引详情
rows, _ := db.Query(`SELECT name, rootpage FROM sqlite_master WHERE type = 'index'`)
fmt.Println("\n索引列表:")
for rows.Next() {
var name string
var rootpage int
rows.Scan(&name, &rootpage)
fmt.Printf(" - %s (根页面: %d)\n", name, rootpage)
}
}
---
03.事务机制
a.日志模式
a.回滚日志(Rollback Journal)
a.工作原理
回滚日志是SQLite默认的事务实现方式。在修改数据库页面前,先将原始页面内容写入日志文件。如果事务失败,通过回滚日志恢复数据。事务提交后删除日志文件。
b.代码示例
---
// 回滚日志模式演示
package main
import (
"database/sql"
"fmt"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbPath := "./rollback_test.db"
journalPath := dbPath + "-journal"
// 删除旧文件
os.Remove(dbPath)
os.Remove(journalPath)
db, err := sql.Open("sqlite3", dbPath)
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 确认使用DELETE模式(回滚日志)
var journalMode string
db.QueryRow("PRAGMA journal_mode").Scan(&journalMode)
fmt.Printf("日志模式: %s\n", journalMode)
db.Exec(`CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance REAL)`)
db.Exec(`INSERT INTO accounts (balance) VALUES (1000.00)`)
// 开始事务
tx, _ := db.Begin()
// 执行更新
tx.Exec(`UPDATE accounts SET balance = balance - 100 WHERE id = 1`)
// 检查日志文件是否生成
if _, err := os.Stat(journalPath); err == nil {
info, _ := os.Stat(journalPath)
fmt.Printf("日志文件已创建: %s (%d bytes)\n", journalPath, info.Size())
} else {
fmt.Println("日志文件未创建(可能被优化)")
}
// 回滚事务
tx.Rollback()
fmt.Println("事务已回滚")
// 验证数据未改变
var balance float64
db.QueryRow(`SELECT balance FROM accounts WHERE id = 1`).Scan(&balance)
fmt.Printf("余额: %.2f (应为1000.00)\n", balance)
// 检查日志文件是否删除
if _, err := os.Stat(journalPath); os.IsNotExist(err) {
fmt.Println("日志文件已删除")
}
os.Remove(dbPath)
}
---
b.WAL模式(Write-Ahead Logging)
a.工作原理
WAL模式将修改先写入WAL文件,而不直接修改数据库文件。读操作可以读取原始数据库文件,写操作追加到WAL文件,实现读写并发。定期执行checkpoint将WAL合并回数据库。
b.代码示例
---
// WAL模式性能对比
package main
import (
"database/sql"
"fmt"
"log"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
func benchmarkMode(dsn string, modeName string) {
db, err := sql.Open("sqlite3", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)`)
// 并发读写测试
var wg sync.WaitGroup
start := time.Now()
// 1个写协程
wg.Add(1)
go func() {
defer wg.Done()
for i := 0; i < 1000; i++ {
db.Exec(`INSERT INTO test (value) VALUES (?)`, fmt.Sprintf("value%d", i))
}
}()
// 5个读协程
for j := 0; j < 5; j++ {
wg.Add(1)
go func() {
defer wg.Done()
for i := 0; i < 200; i++ {
var count int
db.QueryRow(`SELECT COUNT(*) FROM test`).Scan(&count)
}
}()
}
wg.Wait()
elapsed := time.Since(start)
fmt.Printf("%s 模式: 耗时 %v\n", modeName, elapsed)
}
func main() {
fmt.Println("测试DELETE模式(回滚日志):")
benchmarkMode(":memory:", "DELETE")
fmt.Println("\n测试WAL模式:")
benchmarkMode(":memory:?_journal_mode=WAL", "WAL")
fmt.Println("\nWAL模式优势:")
fmt.Println(" 1. 读写可以并发")
fmt.Println(" 2. 写入性能更好(追加而非修改)")
fmt.Println(" 3. 更好的崩溃恢复")
fmt.Println(" 4. 适合高并发读场景")
}
---
b.ACID特性
a.功能说明
SQLite通过日志机制、锁和页面缓存实现ACID特性。原子性通过事务保证,一致性通过约束检查,隔离性通过锁机制,持久性通过fsync确保数据落盘。
b.代码示例
---
// ACID特性验证
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 创建测试表
db.Exec(`CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
balance REAL CHECK(balance >= 0)
)`)
db.Exec(`INSERT INTO accounts (name, balance) VALUES ('Alice', 1000)`)
db.Exec(`INSERT INTO accounts (name, balance) VALUES ('Bob', 500)`)
fmt.Println("=== ACID特性演示 ===\n")
// 1. 原子性(Atomicity)
fmt.Println("1. 原子性: 转账100元")
tx, _ := db.Begin()
tx.Exec(`UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice'`)
tx.Exec(`UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob'`)
// 模拟异常,回滚事务
tx.Rollback()
fmt.Println(" 事务回滚,所有操作撤销")
var aliceBalance, bobBalance float64
db.QueryRow(`SELECT balance FROM accounts WHERE name = 'Alice'`).Scan(&aliceBalance)
db.QueryRow(`SELECT balance FROM accounts WHERE name = 'Bob'`).Scan(&bobBalance)
fmt.Printf(" Alice: %.2f, Bob: %.2f (未改变)\n\n", aliceBalance, bobBalance)
// 2. 一致性(Consistency)
fmt.Println("2. 一致性: 尝试设置负余额")
_, err = db.Exec(`UPDATE accounts SET balance = -100 WHERE name = 'Alice'`)
if err != nil {
fmt.Printf(" 违反约束,操作被拒绝: %v\n\n", err)
}
// 3. 隔离性(Isolation)
fmt.Println("3. 隔离性: 事务隔离级别")
var isolationLevel string
db.QueryRow(`PRAGMA read_uncommitted`).Scan(&isolationLevel)
fmt.Printf(" 当前隔离级别: %s\n\n", isolationLevel)
// 4. 持久性(Durability)
fmt.Println("4. 持久性: 成功提交的事务")
tx, _ = db.Begin()
tx.Exec(`UPDATE accounts SET balance = balance + 50 WHERE name = 'Alice'`)
tx.Commit()
fmt.Println(" 事务提交,数据持久化保存")
db.QueryRow(`SELECT balance FROM accounts WHERE name = 'Alice'`).Scan(&aliceBalance)
fmt.Printf(" Alice新余额: %.2f\n", aliceBalance)
}
---
04.锁机制
a.锁级别
a.功能说明
SQLite使用5级锁机制:UNLOCKED(无锁)、SHARED(共享锁,多个读)、RESERVED(预留锁,准备写)、PENDING(等待锁,准备排他)、EXCLUSIVE(排他锁,独占写入)。锁的粒度是整个数据库文件。
b.代码示例
---
// 锁机制演示
package main
import (
"database/sql"
"fmt"
"log"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbPath := "./lock_test.db"
dsn := fmt.Sprintf("%s?_busy_timeout=5000", dbPath)
db1, _ := sql.Open("sqlite3", dsn)
defer db1.Close()
db2, _ := sql.Open("sqlite3", dsn)
defer db2.Close()
db1.Exec(`CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, value TEXT)`)
fmt.Println("=== SQLite锁机制演示 ===\n")
// 场景1: 多个读锁可以共存
fmt.Println("场景1: 并发读取(SHARED锁)")
var wg sync.WaitGroup
for i := 0; i < 3; i++ {
wg.Add(1)
go func(n int) {
defer wg.Done()
tx, _ := db1.Begin()
defer tx.Rollback()
var count int
tx.QueryRow(`SELECT COUNT(*) FROM test`).Scan(&count)
fmt.Printf(" 读操作%d: 成功获取SHARED锁\n", n)
time.Sleep(100 * time.Millisecond)
}(i)
}
wg.Wait()
fmt.Println()
// 场景2: 写锁互斥
fmt.Println("场景2: 并发写入(EXCLUSIVE锁)")
tx1, _ := db1.Begin()
fmt.Println(" 连接1: 开始事务,获取RESERVED锁")
tx1.Exec(`INSERT INTO test (value) VALUES ('data1')`)
fmt.Println(" 连接1: 执行写入,升级到EXCLUSIVE锁")
// 另一个连接尝试写入
done := make(chan bool)
go func() {
fmt.Println(" 连接2: 尝试写入...")
_, err := db2.Exec(`INSERT INTO test (value) VALUES ('data2')`)
if err != nil {
fmt.Printf(" 连接2: 等待锁超时或失败: %v\n", err)
} else {
fmt.Println(" 连接2: 写入成功")
}
done <- true
}()
time.Sleep(500 * time.Millisecond)
tx1.Commit()
fmt.Println(" 连接1: 提交事务,释放EXCLUSIVE锁")
<-done
fmt.Println("\n锁机制总结:")
fmt.Println(" - 多个SHARED锁可以共存(并发读)")
fmt.Println(" - EXCLUSIVE锁独占(写操作互斥)")
fmt.Println(" - 锁粒度是整个数据库文件")
}
---
1.6 CGO依赖
01.CGO机制
a.基本概念
a.功能说明
CGO是Go语言调用C代码的机制。go-sqlite3驱动通过CGO直接调用SQLite的C库函数,实现高性能的数据库操作。CGO允许Go代码中嵌入C代码,通过特殊的注释标记和类型转换实现互操作。
b.代码示例
---
// CGO机制说明示例
package main
/*
#cgo CFLAGS: -DSQLITE_ENABLE_FTS5
#include <sqlite3.h>
#include <stdlib.h>
// C函数包装器示例
static int get_sqlite_version_number() {
return sqlite3_libversion_number();
}
*/
import "C"
import (
"database/sql"
"fmt"
"log"
"unsafe"
_ "github.com/mattn/go-sqlite3"
)
func main() {
fmt.Println("=== CGO机制演示 ===\n")
// 通过CGO直接调用SQLite C函数
versionNum := C.get_sqlite_version_number()
fmt.Printf("SQLite版本号(通过CGO): %d\n", versionNum)
// 通过CGO获取版本字符串
cVersion := C.sqlite3_libversion()
goVersion := C.GoString(cVersion)
fmt.Printf("SQLite版本(C字符串): %s\n", goVersion)
// 通过database/sql获取版本(内部也是用CGO)
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
var sqlVersion string
db.QueryRow("SELECT sqlite_version()").Scan(&sqlVersion)
fmt.Printf("SQLite版本(SQL查询): %s\n", sqlVersion)
// 演示CGO的类型转换
fmt.Println("\nCGO类型转换:")
fmt.Printf(" C.int -> Go int: %T\n", int(versionNum))
fmt.Printf(" *C.char -> Go string: %T\n", goVersion)
// 内存管理示例
cString := C.CString("Hello from Go")
defer C.free(unsafe.Pointer(cString))
fmt.Printf(" Go string -> *C.char: %p\n", cString)
}
---
b.编译要求
a.功能说明
使用go-sqlite3需要C编译器环境。Linux和macOS通常自带gcc或clang,Windows需要安装MinGW或TDM-GCC。编译时CGO默认启用,可通过CGO_ENABLED环境变量控制。
b.代码示例
---
// 检测CGO编译环境
package main
import (
"fmt"
"os"
"os/exec"
"runtime"
"strings"
)
func main() {
fmt.Println("=== CGO编译环境检测 ===\n")
// 检查CGO是否启用
cgoEnabled := os.Getenv("CGO_ENABLED")
if cgoEnabled == "" {
cgoEnabled = "1" // 默认启用
}
fmt.Printf("CGO_ENABLED: %s (%s)\n",
cgoEnabled,
map[string]string{"0": "禁用", "1": "启用"}[cgoEnabled])
// 检查C编译器
fmt.Println("\nC编译器检测:")
compilers := []string{"gcc", "clang", "cc"}
var foundCompiler string
for _, compiler := range compilers {
cmd := exec.Command(compiler, "--version")
output, err := cmd.CombinedOutput()
if err == nil {
foundCompiler = compiler
lines := strings.Split(string(output), "\n")
fmt.Printf(" ✓ %s: %s\n", compiler, lines[0])
break
}
}
if foundCompiler == "" {
fmt.Println(" ✗ 未找到C编译器")
fmt.Println("\n安装建议:")
switch runtime.GOOS {
case "linux":
fmt.Println(" sudo apt install gcc # Debian/Ubuntu")
fmt.Println(" sudo yum install gcc # CentOS/RHEL")
case "darwin":
fmt.Println(" xcode-select --install")
case "windows":
fmt.Println(" 下载安装 TDM-GCC: https://jmeubank.github.io/tdm-gcc/")
fmt.Println(" 或使用 MinGW: https://www.mingw-w64.org/")
}
}
// 平台信息
fmt.Printf("\n平台信息:\n")
fmt.Printf(" OS: %s\n", runtime.GOOS)
fmt.Printf(" 架构: %s\n", runtime.GOARCH)
fmt.Printf(" Go版本: %s\n", runtime.Version())
// 编译标签示例
fmt.Println("\n编译go-sqlite3的命令:")
fmt.Println(" go build -tags \"fts5\" # 启用全文搜索")
fmt.Println(" go build -tags \"json1\" # 启用JSON支持")
}
---
02.编译选项
a.构建标签
a.功能说明
go-sqlite3支持多种构建标签(build tags)来启用SQLite的可选功能。常用标签包括fts5(全文搜索)、json1(JSON函数)、icu(国际化)等。通过-tags参数在编译时指定。
b.代码示例
---
// 构建标签功能测试
// 编译命令: go run -tags "fts5 json1" main.go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
fmt.Println("=== SQLite编译选项检测 ===\n")
// 检查FTS5支持(全文搜索)
fmt.Println("1. FTS5全文搜索:")
_, err = db.Exec(`CREATE VIRTUAL TABLE docs USING fts5(content)`)
if err != nil {
fmt.Printf(" ✗ FTS5未启用: %v\n", err)
fmt.Println(" 编译时添加: -tags fts5")
} else {
fmt.Println(" ✓ FTS5已启用")
db.Exec(`INSERT INTO docs VALUES ('Go语言数据库编程')`)
db.Exec(`INSERT INTO docs VALUES ('SQLite全文搜索教程')`)
var result string
db.QueryRow(`SELECT content FROM docs WHERE docs MATCH '数据库'`).Scan(&result)
fmt.Printf(" 搜索结果: %s\n", result)
}
// 检查JSON1支持
fmt.Println("\n2. JSON1函数:")
var jsonResult string
err = db.QueryRow(`SELECT json('{"name": "Alice"}')`).Scan(&jsonResult)
if err != nil {
fmt.Printf(" ✗ JSON1未启用: %v\n", err)
fmt.Println(" 编译时添加: -tags json1")
} else {
fmt.Printf(" ✓ JSON1已启用: %s\n", jsonResult)
// JSON提取示例
var name string
db.QueryRow(`SELECT json_extract('{"name": "Bob", "age": 30}', '$.name')`).Scan(&name)
fmt.Printf(" JSON提取: %s\n", name)
}
// 检查其他编译选项
fmt.Println("\n3. 其他可用选项:")
options := map[string]string{
"fts5": "全文搜索引擎",
"json1": "JSON函数支持",
"icu": "国际化排序",
"foreign_keys": "外键约束(默认启用)",
"secure_delete": "安全删除",
"unlock_notify": "解锁通知",
"vtable": "虚拟表支持",
}
for tag, desc := range options {
fmt.Printf(" -tags %s: %s\n", tag, desc)
}
}
---
b.性能优化标签
a.功能说明
通过编译标签可以优化SQLite性能。例如libsqlite3启用系统SQLite库,omit_load_extension禁用扩展加载提高安全性,sqlite_stat4启用更精确的查询优化器统计。
b.代码示例
---
// 性能优化编译标签示例
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
func benchmark(db *sql.DB, name string) time.Duration {
start := time.Now()
// 创建测试表
db.Exec(`CREATE TABLE IF NOT EXISTS benchmark (id INTEGER PRIMARY KEY, data TEXT)`)
// 插入测试数据
tx, _ := db.Begin()
stmt, _ := tx.Prepare(`INSERT INTO benchmark (data) VALUES (?)`)
for i := 0; i < 10000; i++ {
stmt.Exec(fmt.Sprintf("数据%d", i))
}
stmt.Close()
tx.Commit()
// 查询测试
for i := 0; i < 1000; i++ {
var count int
db.QueryRow(`SELECT COUNT(*) FROM benchmark WHERE id > ?`, i).Scan(&count)
}
return time.Since(start)
}
func main() {
fmt.Println("=== 性能优化编译标签 ===\n")
// 标准编译版本
db1, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db1.Close()
elapsed := benchmark(db1, "标准版本")
fmt.Printf("标准编译版本: %v\n", elapsed)
fmt.Println("\n推荐的性能优化编译选项:")
fmt.Println("1. 使用系统SQLite库:")
fmt.Println(" go build -tags \"libsqlite3\"")
fmt.Println(" 优势: 可能更新,系统优化")
fmt.Println()
fmt.Println("2. 禁用不需要的功能:")
fmt.Println(" go build -tags \"sqlite_omit_load_extension\"")
fmt.Println(" 优势: 减小二进制大小,提高安全性")
fmt.Println()
fmt.Println("3. 启用查询优化:")
fmt.Println(" go build -tags \"sqlite_stat4\"")
fmt.Println(" 优势: 更准确的查询计划")
fmt.Println()
fmt.Println("4. 组合使用:")
fmt.Println(" go build -tags \"fts5 json1 sqlite_stat4\"")
fmt.Println(" 优势: 功能+性能兼顾")
}
---
03.跨平台编译
a.交叉编译挑战
a.功能说明
由于go-sqlite3依赖CGO,交叉编译需要目标平台的C交叉编译器。例如从Linux编译Windows程序需要mingw-w64,这增加了构建复杂度。纯Go的modernc-sqlite可以避免此问题。
b.代码示例
---
// 跨平台编译脚本生成器
package main
import (
"fmt"
"runtime"
)
type BuildTarget struct {
GOOS string
GOARCH string
CC string
Desc string
}
func main() {
fmt.Println("=== go-sqlite3跨平台编译指南 ===\n")
fmt.Printf("当前平台: %s/%s\n\n", runtime.GOOS, runtime.GOARCH)
targets := []BuildTarget{
{"linux", "amd64", "gcc", "Linux 64位"},
{"linux", "arm64", "aarch64-linux-gnu-gcc", "Linux ARM64"},
{"windows", "amd64", "x86_64-w64-mingw32-gcc", "Windows 64位"},
{"darwin", "amd64", "o64-clang", "macOS Intel"},
{"darwin", "arm64", "oa64-clang", "macOS Apple Silicon"},
}
fmt.Println("编译命令:")
for i, target := range targets {
fmt.Printf("\n%d. %s (%s/%s):\n", i+1, target.Desc, target.GOOS, target.GOARCH)
if target.GOOS == "windows" {
fmt.Println(" # 需要安装mingw-w64")
fmt.Println(" # Ubuntu: sudo apt install mingw-w64")
} else if target.GOARCH == "arm64" && target.GOOS == "linux" {
fmt.Println(" # 需要安装ARM交叉编译器")
fmt.Println(" # Ubuntu: sudo apt install gcc-aarch64-linux-gnu")
}
fmt.Printf(" CGO_ENABLED=1 GOOS=%s GOARCH=%s CC=%s go build -o app_%s_%s\n",
target.GOOS, target.GOARCH, target.CC, target.GOOS, target.GOARCH)
}
fmt.Println("\n纯Go方案(无需交叉编译器):")
fmt.Println(" 使用 modernc.org/sqlite 替代 go-sqlite3")
fmt.Println(" GOOS=windows GOARCH=amd64 go build # 直接编译,无需CGO")
fmt.Println()
fmt.Println("Docker构建方案:")
fmt.Println(" 使用包含交叉编译器的Docker镜像")
fmt.Println(" docker run --rm -v \"$PWD\":/usr/src/myapp -w /usr/src/myapp \\")
fmt.Println(" golang:1.21 go build -v")
}
---
b.容器化构建
a.功能说明
使用Docker等容器技术可以简化CGO程序的编译。容器镜像中预装所有必需的编译器和依赖,确保构建环境一致性,避免"在我机器上能运行"的问题。
b.代码示例
---
// Dockerfile生成器
package main
import (
"fmt"
"os"
)
func main() {
dockerfile := `# 多阶段构建Dockerfile示例
# 第一阶段: 编译
FROM golang:1.21-alpine AS builder
# 安装编译依赖
RUN apk add --no-cache gcc musl-dev sqlite-dev
WORKDIR /app
# 复制依赖文件
COPY go.mod go.sum ./
RUN go mod download
# 复制源代码
COPY . .
# 编译应用(静态链接)
RUN CGO_ENABLED=1 GOOS=linux go build -a \
-ldflags '-linkmode external -extldflags "-static"' \
-o /app/myapp .
# 第二阶段: 运行
FROM alpine:latest
# 添加ca证书和时区数据
RUN apk --no-cache add ca-certificates tzdata
WORKDIR /root/
# 从builder阶段复制编译好的程序
COPY --from=builder /app/myapp .
EXPOSE 8080
CMD ["./myapp"]
`
fmt.Println("=== Docker构建配置 ===\n")
fmt.Println("Dockerfile内容:")
fmt.Println(dockerfile)
// 写入Dockerfile
err := os.WriteFile("Dockerfile.example", []byte(dockerfile), 0644)
if err == nil {
fmt.Println("✓ Dockerfile已保存到 Dockerfile.example")
}
fmt.Println("\n构建命令:")
fmt.Println(" docker build -t myapp:latest .")
fmt.Println()
fmt.Println("运行命令:")
fmt.Println(" docker run -d -p 8080:8080 -v ./data:/data myapp:latest")
fmt.Println()
fmt.Println("优势:")
fmt.Println(" ✓ 环境一致性")
fmt.Println(" ✓ 无需本地安装C编译器")
fmt.Println(" ✓ 支持多阶段构建减小镜像")
fmt.Println(" ✓ 便于CI/CD集成")
}
---
04.纯Go替代方案
a.modernc-sqlite
a.功能说明
modernc.org/sqlite是纯Go实现的SQLite驱动,通过c2go工具将SQLite C代码转换为Go代码。无需CGO,支持跨平台编译,但性能略低于CGO版本约10-30%。
b.代码示例
---
// modernc-sqlite使用示例
package main
import (
"database/sql"
"fmt"
"log"
"runtime"
"time"
// 使用纯Go驱动
_ "modernc.org/sqlite"
)
func main() {
fmt.Println("=== modernc-sqlite 纯Go驱动 ===\n")
// 连接数据库(驱动名为sqlite,不是sqlite3)
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 验证驱动信息
var version string
db.QueryRow("SELECT sqlite_version()").Scan(&version)
fmt.Printf("SQLite版本: %s\n", version)
fmt.Printf("驱动类型: 纯Go实现\n")
fmt.Printf("CGO要求: 无\n")
fmt.Printf("当前平台: %s/%s\n\n", runtime.GOOS, runtime.GOARCH)
// 功能测试
db.Exec(`CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)`)
db.Exec(`INSERT INTO test (name) VALUES ('Alice'), ('Bob')`)
rows, _ := db.Query(`SELECT id, name FROM test`)
defer rows.Close()
fmt.Println("数据查询:")
for rows.Next() {
var id int
var name string
rows.Scan(&id, &name)
fmt.Printf(" %d: %s\n", id, name)
}
// 性能基准
fmt.Println("\n性能测试:")
start := time.Now()
tx, _ := db.Begin()
stmt, _ := tx.Prepare(`INSERT INTO test (name) VALUES (?)`)
for i := 0; i < 10000; i++ {
stmt.Exec(fmt.Sprintf("User%d", i))
}
stmt.Close()
tx.Commit()
elapsed := time.Since(start)
fmt.Printf(" 插入10000条记录: %v\n", elapsed)
fmt.Printf(" 每秒操作数: %.0f ops/s\n", 10000/elapsed.Seconds())
fmt.Println("\n优势:")
fmt.Println(" ✓ 无需CGO,编译简单")
fmt.Println(" ✓ 真正的跨平台编译")
fmt.Println(" ✓ 容易调试(纯Go代码)")
fmt.Println(" ✓ 无C编译器依赖")
fmt.Println("\n劣势:")
fmt.Println(" - 性能比CGO版本低10-30%")
fmt.Println(" - 二进制文件略大")
fmt.Println(" - 部分扩展可能不支持")
}
---
1.7 驱动特性
01.连接池管理
a.database/sql连接池
a.功能说明
Go的database/sql包内置连接池管理,通过SetMaxOpenConns、SetMaxIdleConns和SetConnMaxLifetime控制连接行为。对于SQLite,由于文件锁限制,通常设置MaxOpenConns为1以避免写入冲突。
b.代码示例
---
// SQLite连接池配置示例
package main
import (
"database/sql"
"fmt"
"log"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 打开数据库连接
db, err := sql.Open("sqlite3", "./pool_test.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// SQLite推荐配置
// MaxOpenConns=1: 避免SQLITE_BUSY错误
// 写操作是串行的,多个连接无益反而增加锁竞争
db.SetMaxOpenConns(1)
// MaxIdleConns=1: 保持一个空闲连接,减少打开关闭开销
db.SetMaxIdleConns(1)
// ConnMaxLifetime: 连接最大生存时间
// SQLite文件数据库可以设置较长时间
db.SetConnMaxLifetime(time.Hour)
// ConnMaxIdleTime: 空闲连接最大生存时间
db.SetConnMaxIdleTime(10 * time.Minute)
fmt.Println("=== SQLite连接池配置 ===")
fmt.Println("MaxOpenConns: 1 (推荐)")
fmt.Println("MaxIdleConns: 1")
fmt.Println("ConnMaxLifetime: 1小时")
fmt.Println("ConnMaxIdleTime: 10分钟\n")
// 创建测试表
db.Exec(`CREATE TABLE IF NOT EXISTS metrics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
value INTEGER,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)`)
// 并发写入测试
var wg sync.WaitGroup
start := time.Now()
for i := 0; i < 10; i++ {
wg.Add(1)
go func(n int) {
defer wg.Done()
for j := 0; j < 100; j++ {
_, err := db.Exec(`INSERT INTO metrics (value) VALUES (?)`, n*100+j)
if err != nil {
log.Printf("插入失败: %v", err)
}
}
}(i)
}
wg.Wait()
elapsed := time.Since(start)
// 查看连接池统计
stats := db.Stats()
fmt.Println("连接池统计:")
fmt.Printf(" MaxOpenConnections: %d\n", stats.MaxOpenConnections)
fmt.Printf(" OpenConnections: %d\n", stats.OpenConnections)
fmt.Printf(" InUse: %d\n", stats.InUse)
fmt.Printf(" Idle: %d\n", stats.Idle)
fmt.Printf(" WaitCount: %d (等待可用连接的次数)\n", stats.WaitCount)
fmt.Printf(" WaitDuration: %v (总等待时间)\n", stats.WaitDuration)
var count int
db.QueryRow(`SELECT COUNT(*) FROM metrics`).Scan(&count)
fmt.Printf("\n插入总数: %d, 耗时: %v\n", count, elapsed)
}
---
b.WAL模式下的连接
a.功能说明
在WAL模式下,可以适当增加连接数以利用读写并发。读操作可以并发执行,但写操作仍然是串行的。推荐配置MaxOpenConns为CPU核心数,MaxIdleConns为2-4。
b.代码示例
---
// WAL模式连接池优化
package main
import (
"database/sql"
"fmt"
"log"
"runtime"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
func benchmarkConcurrency(maxOpen int, useWAL bool) time.Duration {
mode := "DELETE"
if useWAL {
mode = "WAL"
}
dsn := fmt.Sprintf(":memory:?_journal_mode=%s", mode)
db, err := sql.Open("sqlite3", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 配置连接池
db.SetMaxOpenConns(maxOpen)
db.SetMaxIdleConns(maxOpen / 2)
// 初始化
db.Exec(`CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT)`)
for i := 0; i < 1000; i++ {
db.Exec(`INSERT INTO test (data) VALUES (?)`, fmt.Sprintf("data%d", i))
}
// 并发读写测试
var wg sync.WaitGroup
start := time.Now()
// 1个写协程
wg.Add(1)
go func() {
defer wg.Done()
for i := 0; i < 500; i++ {
db.Exec(`UPDATE test SET data = ? WHERE id = ?`,
fmt.Sprintf("updated%d", i), i%100+1)
}
}()
// 多个读协程
readers := maxOpen - 1
if readers < 1 {
readers = 1
}
for i := 0; i < readers; i++ {
wg.Add(1)
go func() {
defer wg.Done()
for j := 0; j < 200; j++ {
var data string
db.QueryRow(`SELECT data FROM test WHERE id = ?`, j%100+1).Scan(&data)
}
}()
}
wg.Wait()
return time.Since(start)
}
func main() {
fmt.Println("=== WAL模式连接池性能测试 ===\n")
cpuCount := runtime.NumCPU()
fmt.Printf("CPU核心数: %d\n\n", cpuCount)
// DELETE模式,单连接
t1 := benchmarkConcurrency(1, false)
fmt.Printf("DELETE模式 + MaxOpenConns=1: %v\n", t1)
// DELETE模式,多连接(无效果)
t2 := benchmarkConcurrency(cpuCount, false)
fmt.Printf("DELETE模式 + MaxOpenConns=%d: %v\n", cpuCount, t2)
// WAL模式,单连接
t3 := benchmarkConcurrency(1, true)
fmt.Printf("WAL模式 + MaxOpenConns=1: %v\n", t3)
// WAL模式,多连接(有改善)
t4 := benchmarkConcurrency(cpuCount, true)
fmt.Printf("WAL模式 + MaxOpenConns=%d: %v\n", cpuCount, t4)
fmt.Println("\n结论:")
fmt.Println(" - DELETE模式: 使用MaxOpenConns=1")
fmt.Println(" - WAL模式: 使用MaxOpenConns=CPU核心数")
fmt.Println(" - WAL读写并发可提升性能")
}
---
02.参数绑定
a.位置参数
a.功能说明
SQLite支持?占位符表示位置参数,按顺序绑定。位置参数简洁高效,适合参数数量少且顺序固定的场景。database/sql会自动处理类型转换和SQL注入防护。
b.代码示例
---
// 位置参数绑定示例
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 创建测试表
db.Exec(`CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER DEFAULT 0,
created_at DATETIME
)`)
fmt.Println("=== 位置参数绑定 ===\n")
// 单个参数
result, err := db.Exec(`INSERT INTO products (name, price, stock, created_at) VALUES (?, ?, ?, ?)`,
"笔记本电脑",
5999.00,
50,
time.Now(),
)
if err != nil {
log.Fatal(err)
}
id, _ := result.LastInsertId()
fmt.Printf("插入产品ID: %d\n", id)
// 批量插入
tx, _ := db.Begin()
stmt, _ := tx.Prepare(`INSERT INTO products (name, price, stock, created_at) VALUES (?, ?, ?, ?)`)
products := []struct {
Name string
Price float64
Stock int
}{
{"手机", 3999.00, 100},
{"平板", 2999.00, 80},
{"耳机", 299.00, 200},
}
for _, p := range products {
stmt.Exec(p.Name, p.Price, p.Stock, time.Now())
}
stmt.Close()
tx.Commit()
fmt.Println("批量插入完成\n")
// 查询参数绑定
var name string
var price float64
err = db.QueryRow(`SELECT name, price FROM products WHERE id = ?`, 1).Scan(&name, &price)
if err != nil {
log.Fatal(err)
}
fmt.Printf("查询结果: %s, 价格: %.2f\n", name, price)
// 多条件查询
rows, _ := db.Query(`SELECT name, price FROM products WHERE price > ? AND stock > ?`, 1000.00, 50)
defer rows.Close()
fmt.Println("\n高价值库存商品:")
for rows.Next() {
rows.Scan(&name, &price)
fmt.Printf(" - %s: ¥%.2f\n", name, price)
}
// 参数安全性演示
fmt.Println("\nSQL注入防护:")
maliciousInput := "'; DROP TABLE products; --"
rows, _ = db.Query(`SELECT name FROM products WHERE name = ?`, maliciousInput)
defer rows.Close()
count := 0
for rows.Next() {
count++
}
fmt.Printf("恶意输入查询结果: %d条 (参数绑定已防护)\n", count)
// 验证表还在
var tableExists int
db.QueryRow(`SELECT COUNT(*) FROM products`).Scan(&tableExists)
fmt.Printf("表仍然存在,记录数: %d\n", tableExists)
}
---
b.命名参数
a.功能说明
SQLite也支持:name、@name、$name等命名参数语法。命名参数可读性更好,参数顺序无关,适合复杂查询。go-sqlite3通过sql.Named函数支持命名参数。
b.代码示例
---
// 命名参数绑定示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE,
email TEXT,
age INTEGER,
active BOOLEAN
)`)
fmt.Println("=== 命名参数绑定 ===\n")
// 使用:name语法
_, err = db.Exec(`
INSERT INTO users (username, email, age, active)
VALUES (:username, :email, :age, :active)`,
sql.Named("username", "alice"),
sql.Named("email", "[email protected] "),
sql.Named("age", 25),
sql.Named("active", true),
)
if err != nil {
log.Fatal(err)
}
fmt.Println("使用:name语法插入成功")
// 使用@name语法
_, err = db.Exec(`
INSERT INTO users (username, email, age, active)
VALUES (@username, @email, @age, @active)`,
sql.Named("username", "bob"),
sql.Named("email", "[email protected] "),
sql.Named("age", 30),
sql.Named("active", true),
)
if err != nil {
log.Fatal(err)
}
fmt.Println("使用@name语法插入成功")
// 使用$name语法
_, err = db.Exec(`
INSERT INTO users (username, email, age, active)
VALUES ($username, $email, $age, $active)`,
sql.Named("username", "charlie"),
sql.Named("email", "[email protected] "),
sql.Named("age", 28),
sql.Named("active", false),
)
if err != nil {
log.Fatal(err)
}
fmt.Println("使用$name语法插入成功\n")
// 复杂查询使用命名参数
rows, _ := db.Query(`
SELECT username, email, age
FROM users
WHERE age >= :min_age AND age <= :max_age AND active = :active
ORDER BY age`,
sql.Named("min_age", 20),
sql.Named("max_age", 30),
sql.Named("active", true),
)
defer rows.Close()
fmt.Println("活跃用户(20-30岁):")
for rows.Next() {
var username, email string
var age int
rows.Scan(&username, &email, &age)
fmt.Printf(" - %s (%d岁): %s\n", username, age, email)
}
// 更新使用命名参数
result, _ := db.Exec(`
UPDATE users
SET email = :new_email, active = :active
WHERE username = :username`,
sql.Named("new_email", "[email protected] "),
sql.Named("active", false),
sql.Named("username", "alice"),
)
affected, _ := result.RowsAffected()
fmt.Printf("\n更新影响行数: %d\n", affected)
fmt.Println("\n命名参数优势:")
fmt.Println(" ✓ 参数名称自描述")
fmt.Println(" ✓ 顺序无关,易维护")
fmt.Println(" ✓ 适合复杂查询")
}
---
03.自定义函数
a.标量函数
a.功能说明
go-sqlite3允许注册自定义SQL函数,扩展SQLite功能。标量函数接受输入参数返回单个值,可用于复杂计算、字符串处理、加密解密等场景。
b.代码示例
---
// 自定义标量函数示例
package main
import (
"crypto/md5"
"database/sql"
"fmt"
"log"
"strings"
"github.com/mattn/go-sqlite3"
)
func main() {
// 注册自定义函数
sql.Register("sqlite3_custom",
&sqlite3.SQLiteDriver{
ConnectHook: func(conn *sqlite3.SQLiteConn) error {
// MD5哈希函数
if err := conn.RegisterFunc("md5", func(s string) string {
return fmt.Sprintf("%x", md5.Sum([]byte(s)))
}, true); err != nil {
return err
}
// 字符串反转函数
if err := conn.RegisterFunc("reverse", func(s string) string {
runes := []rune(s)
for i, j := 0, len(runes)-1; i < j; i, j = i+1, j-1 {
runes[i], runes[j] = runes[j], runes[i]
}
return string(runes)
}, true); err != nil {
return err
}
// 大��转换函数
return conn.RegisterFunc("upper_cn", func(s string) string {
return strings.ToUpper(s)
}, true)
},
})
// 使用自定义驱动打开数据库
db, err := sql.Open("sqlite3_custom", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
fmt.Println("=== 自定义SQL函数 ===\n")
// 测试MD5函数
var hash string
db.QueryRow(`SELECT md5(?)`, "Hello, SQLite!").Scan(&hash)
fmt.Printf("MD5('Hello, SQLite!'): %s\n", hash)
// 测试字符串反转
var reversed string
db.QueryRow(`SELECT reverse(?)`, "SQLite").Scan(&reversed)
fmt.Printf("REVERSE('SQLite'): %s\n", reversed)
// 测试大写函数
var upper string
db.QueryRow(`SELECT upper_cn(?)`, "你好世界").Scan(&upper)
fmt.Printf("UPPER_CN('你好世界'): %s\n\n", upper)
// 在实际查询中使用
db.Exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT)`)
db.Exec(`INSERT INTO users (username, password) VALUES (?, md5(?))`, "alice", "secret123")
db.QueryRow(`SELECT password FROM users WHERE username = ?`, "alice").Scan(&hash)
fmt.Printf("用户密码哈希: %s\n", hash)
// 验证密码
var match bool
db.QueryRow(`SELECT password = md5(?) FROM users WHERE username = ?`,
"secret123", "alice").Scan(&match)
fmt.Printf("密码验证: %v\n", match)
}
---
b.聚合函数
a.功能说明
自定义聚合函数可以实现复杂的统计计算。聚合函数需要实现Step和Done方法,Step处理每一行数据,Done返回最终结果。适合实现自定义统计指标。
b.代码示例
---
// 自定义聚合函数示例
package main
import (
"database/sql"
"fmt"
"log"
"math"
"github.com/mattn/go-sqlite3"
)
// StdDevAgg 标准差聚合函数
type StdDevAgg struct {
values []float64
}
func (s *StdDevAgg) Step(value float64) {
s.values = append(s.values, value)
}
func (s *StdDevAgg) Done() float64 {
if len(s.values) == 0 {
return 0
}
// 计算平均值
var sum float64
for _, v := range s.values {
sum += v
}
mean := sum / float64(len(s.values))
// 计算方差
var variance float64
for _, v := range s.values {
variance += math.Pow(v-mean, 2)
}
variance /= float64(len(s.values))
// 返回标准差
return math.Sqrt(variance)
}
func main() {
// 注册自定义聚合函数
sql.Register("sqlite3_agg",
&sqlite3.SQLiteDriver{
ConnectHook: func(conn *sqlite3.SQLiteConn) error {
return conn.RegisterAggregator("stddev", func() *StdDevAgg {
return &StdDevAgg{}
}, true)
},
})
db, err := sql.Open("sqlite3_agg", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
fmt.Println("=== 自定义聚合函数 ===\n")
// 创建测试数据
db.Exec(`CREATE TABLE scores (student TEXT, score REAL)`)
scores := []float64{85, 90, 78, 92, 88, 76, 95, 82}
for i, score := range scores {
db.Exec(`INSERT INTO scores VALUES (?, ?)`,
fmt.Sprintf("Student%d", i+1), score)
}
// 使用内置聚合函数
var count int
var avg, min, max float64
db.QueryRow(`SELECT COUNT(*), AVG(score), MIN(score), MAX(score) FROM scores`).
Scan(&count, &avg, &min, &max)
fmt.Printf("学生人数: %d\n", count)
fmt.Printf("平均分: %.2f\n", avg)
fmt.Printf("最低分: %.2f\n", min)
fmt.Printf("最高分: %.2f\n", max)
// 使用自定义标准差函数
var stddev float64
db.QueryRow(`SELECT stddev(score) FROM scores`).Scan(&stddev)
fmt.Printf("标准差: %.2f\n\n", stddev)
fmt.Println("成绩分布分析:")
if stddev < 5 {
fmt.Println(" 成绩分布集中")
} else if stddev < 10 {
fmt.Println(" 成绩分布正常")
} else {
fmt.Println(" 成绩分布离散")
}
}
---
2 数据库操作
2.1 汇总：6个核心操作
01.操作概览
a.CRUD操作分类
Go SQLite数据库操作遵循标准的CRUD模式,包括连接管理(Connection)、创建表(Create Table)、插入数据(Insert)、查询数据(Query)、更新数据(Update)和删除数据(Delete)六大核心操作。每种操作对应database/sql包的特定方法。
b.方法映射关系
---
// 核心操作方法映射表
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
fmt.Println("=== SQLite核心操作方法映射 ===\n")
// 1. 连接管理
fmt.Println("1. 连接管理:")
fmt.Println(" sql.Open() - 打开数据库连接")
fmt.Println(" db.Ping() - 验证连接有效性")
fmt.Println(" db.Close() - 关闭数据库连接")
fmt.Println(" db.SetMaxOpenConns() - 设置最大连接数")
// 2. DDL操作(Data Definition Language)
fmt.Println("\n2. DDL操作(创建表):")
fmt.Println(" db.Exec() - 执行CREATE TABLE语句")
fmt.Println(" db.Exec() - 执行ALTER TABLE语句")
fmt.Println(" db.Exec() - 执行DROP TABLE语句")
// 3. DML操作(Data Manipulation Language)
fmt.Println("\n3. DML操作(数据操作):")
fmt.Println(" db.Exec() - INSERT插入数据")
fmt.Println(" db.Query() - SELECT查询多行")
fmt.Println(" db.QueryRow() - SELECT查询单行")
fmt.Println(" db.Exec() - UPDATE更新数据")
fmt.Println(" db.Exec() - DELETE删除数据")
// 4. 事务操作
fmt.Println("\n4. 事务操作:")
fmt.Println(" db.Begin() - 开始事务")
fmt.Println(" tx.Commit() - 提交事务")
fmt.Println(" tx.Rollback() - 回滚事务")
// 5. 预编译语句
fmt.Println("\n5. 预编译语句:")
fmt.Println(" db.Prepare() - 创建预编译语句")
fmt.Println(" stmt.Exec() - 执行预编译语句")
fmt.Println(" stmt.Query() - 查询预编译语句")
fmt.Println(" stmt.Close() - 关闭预编译语句")
// 演示基本操作流程
fmt.Println("\n=== 基本操作流程演示 ===\n")
// 创建表
_, err = db.Exec(`CREATE TABLE demo (id INTEGER PRIMARY KEY, name TEXT)`)
if err != nil {
log.Fatal(err)
}
fmt.Println("✓ 表创建成功")
// 插入数据
result, err := db.Exec(`INSERT INTO demo (name) VALUES (?)`, "示例数据")
if err != nil {
log.Fatal(err)
}
id, _ := result.LastInsertId()
fmt.Printf("✓ 数据插入成功, ID: %d\n", id)
// 查询数据
var name string
err = db.QueryRow(`SELECT name FROM demo WHERE id = ?`, id).Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Printf("✓ 数据查询成功: %s\n", name)
// 更新数据
_, err = db.Exec(`UPDATE demo SET name = ? WHERE id = ?`, "更新后的数据", id)
if err != nil {
log.Fatal(err)
}
fmt.Println("✓ 数据更新成功")
// 删除数据
_, err = db.Exec(`DELETE FROM demo WHERE id = ?`, id)
if err != nil {
log.Fatal(err)
}
fmt.Println("✓ 数据删除成功")
}
---
02.操作特点
a.同步执行模型
a.功能说明
database/sql的所有操作都是同步阻塞的,调用方法会等待数据库操作完成后才返回。对于耗时操作,建议使用带Context的方法(如ExecContext、QueryContext)实现超时控制和取消功能。
b.代码示例
---
// 同步执行与超时控制示例
package main
import (
"context"
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`CREATE TABLE tasks (id INTEGER PRIMARY KEY, name TEXT)`)
fmt.Println("=== 同步执行与超时控制 ===\n")
// 普通同步执行
start := time.Now()
_, err = db.Exec(`INSERT INTO tasks (name) VALUES (?)`, "任务1")
if err != nil {
log.Fatal(err)
}
fmt.Printf("普通Exec耗时: %v\n", time.Since(start))
// 使用Context的超时控制
ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
defer cancel()
start = time.Now()
_, err = db.ExecContext(ctx, `INSERT INTO tasks (name) VALUES (?)`, "任务2")
if err != nil {
log.Printf("ExecContext失败: %v\n", err)
} else {
fmt.Printf("ExecContext耗时: %v\n", time.Since(start))
}
// 查询超时示例
queryCtx, queryCancel := context.WithTimeout(context.Background(), 1*time.Second)
defer queryCancel()
rows, err := db.QueryContext(queryCtx, `SELECT * FROM tasks`)
if err != nil {
log.Printf("查询失败: %v\n", err)
} else {
defer rows.Close()
count := 0
for rows.Next() {
count++
}
fmt.Printf("查询到 %d 条记录\n", count)
}
// 手动取消示例
cancelCtx, manualCancel := context.WithCancel(context.Background())
go func() {
time.Sleep(100 * time.Millisecond)
manualCancel() // 手动取消
fmt.Println("操作已手动取消")
}()
_, err = db.ExecContext(cancelCtx, `INSERT INTO tasks (name) VALUES (?)`, "任务3")
if err != nil {
fmt.Printf("预期的取消错误: %v\n", err)
}
fmt.Println("\n超时控制建议:")
fmt.Println(" - 用户交互操作: 1-5秒超时")
fmt.Println(" - 后台任务: 30-60秒超时")
fmt.Println(" - 批量处理: 根据数据量设置")
}
---
b.错误处理模式
a.功能说明
所有数据库操作都返回error类型,需要检查错误并妥善处理。常见错误包括连接失败、SQL语法错误、约束违反、记录不存在等。sql.ErrNoRows是查询无结果的特殊错误。
b.代码示例
---
// 错误处理最佳实践
package main
import (
"database/sql"
"errors"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal("打开数据库失败:", err)
}
defer db.Close()
fmt.Println("=== 错误处理模式 ===\n")
// 1. SQL语法错误
_, err = db.Exec(`CREATE TABEL users (id INTEGER)`) // 故意拼错TABLE
if err != nil {
fmt.Printf("SQL语法错误: %v\n", err)
}
// 正确创建表
db.Exec(`CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
email TEXT
)`)
// 2. 约束违反错误
db.Exec(`INSERT INTO users (username, email) VALUES ('alice', '[email protected] ')`)
_, err = db.Exec(`INSERT INTO users (username, email) VALUES ('alice', '[email protected] ')`)
if err != nil {
fmt.Printf("唯一性约束违反: %v\n", err)
}
// 3. NOT NULL约束错误
_, err = db.Exec(`INSERT INTO users (email) VALUES ('[email protected] ')`)
if err != nil {
fmt.Printf("NOT NULL约束违反: %v\n", err)
}
// 4. sql.ErrNoRows处理
var username string
err = db.QueryRow(`SELECT username FROM users WHERE id = 999`).Scan(&username)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
fmt.Println("记录不存在(这是正常情况)")
} else {
fmt.Printf("查询错误: %v\n", err)
}
}
// 5. 正确的查询处理
err = db.QueryRow(`SELECT username FROM users WHERE id = 1`).Scan(&username)
if err == nil {
fmt.Printf("查询成功: %s\n", username)
} else if errors.Is(err, sql.ErrNoRows) {
fmt.Println("用户不存在")
} else {
fmt.Printf("查询失败: %v\n", err)
}
// 6. 事务错误处理
tx, err := db.Begin()
if err != nil {
log.Fatal("开始事务失败:", err)
}
_, err = tx.Exec(`INSERT INTO users (username, email) VALUES ('bob', '[email protected] ')`)
if err != nil {
tx.Rollback()
fmt.Printf("插入失败,已回滚: %v\n", err)
} else {
if err := tx.Commit(); err != nil {
fmt.Printf("提交失败: %v\n", err)
} else {
fmt.Println("事务提交成功")
}
}
fmt.Println("\n错误处理建议:")
fmt.Println(" ✓ 始终检查err != nil")
fmt.Println(" ✓ 区分sql.ErrNoRows和其他错误")
fmt.Println(" ✓ 记录详细错误日志")
fmt.Println(" ✓ 事务失败时及时回滚")
}
---
03.性能考虑
a.批量操作优化
a.功能说明
对于大量数据插入或更新,应使用事务批量处理而非逐条操作。事务可以将多个操作合并为一次磁盘写入,性能提升可达数百倍。预编译语句复用可以进一步提高性能。
b.代码示例
---
// 批量操作性能对比
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
func benchmarkInsert(db *sql.DB, method string, count int) time.Duration {
// 清空表
db.Exec(`DELETE FROM benchmark`)
start := time.Now()
switch method {
case "single":
// 方法1: 逐条插入(最慢)
for i := 0; i < count; i++ {
db.Exec(`INSERT INTO benchmark (value) VALUES (?)`, i)
}
case "transaction":
// 方法2: 事务批量插入
tx, _ := db.Begin()
for i := 0; i < count; i++ {
tx.Exec(`INSERT INTO benchmark (value) VALUES (?)`, i)
}
tx.Commit()
case "prepared":
// 方法3: 预编译语句+事务(最快)
tx, _ := db.Begin()
stmt, _ := tx.Prepare(`INSERT INTO benchmark (value) VALUES (?)`)
for i := 0; i < count; i++ {
stmt.Exec(i)
}
stmt.Close()
tx.Commit()
}
return time.Since(start)
}
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`CREATE TABLE benchmark (id INTEGER PRIMARY KEY, value INTEGER)`)
fmt.Println("=== 批量操作性能对比 ===\n")
count := 1000
// 逐条插入
t1 := benchmarkInsert(db, "single", count)
fmt.Printf("逐条插入 %d 条: %v\n", count, t1)
// 事务批量插入
t2 := benchmarkInsert(db, "transaction", count)
fmt.Printf("事务批量 %d 条: %v (提升 %.1fx)\n", count, t2, float64(t1)/float64(t2))
// 预编译+事务
t3 := benchmarkInsert(db, "prepared", count)
fmt.Printf("预编译+事务 %d 条: %v (提升 %.1fx)\n", count, t3, float64(t1)/float64(t3))
fmt.Println("\n性能优化建议:")
fmt.Println(" 1. 批量操作必须使用事务")
fmt.Println(" 2. 重复操作使用预编译语句")
fmt.Println(" 3. 合理设置事务大小(1000-10000条)")
fmt.Println(" 4. 关闭同步模式提升写入速度")
// 验证数据
var total int
db.QueryRow(`SELECT COUNT(*) FROM benchmark`).Scan(&total)
fmt.Printf("\n最终记录数: %d\n", total)
}
---
b.连接复用
a.功能说明
sql.DB对象内部维护连接池,应该全局创建一次并复用,而不是每次操作都打开关闭。频繁的Open/Close会带来巨大的性能开销,还可能导致文件句柄泄漏。
b.代码示例
---
// 连接复用最佳实践
package main
import (
"database/sql"
"fmt"
"log"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
// 错误示例: 每次操作都打开关闭
func badPractice() time.Duration {
start := time.Now()
for i := 0; i < 100; i++ {
db, _ := sql.Open("sqlite3", ":memory:")
db.Exec(`CREATE TABLE IF NOT EXISTS test (id INTEGER)`)
db.Exec(`INSERT INTO test VALUES (?)`, i)
db.Close()
}
return time.Since(start)
}
// 正确示例: 复用连接
func goodPractice() time.Duration {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE test (id INTEGER)`)
start := time.Now()
for i := 0; i < 100; i++ {
db.Exec(`INSERT INTO test VALUES (?)`, i)
}
return time.Since(start)
}
// 全局数据库实例(推荐模式)
var globalDB *sql.DB
var once sync.Once
func GetDB() *sql.DB {
once.Do(func() {
var err error
globalDB, err = sql.Open("sqlite3", "./app.db")
if err != nil {
log.Fatal(err)
}
globalDB.SetMaxOpenConns(1)
globalDB.SetMaxIdleConns(1)
globalDB.SetConnMaxLifetime(time.Hour)
})
return globalDB
}
func main() {
fmt.Println("=== 连接复用性能对比 ===\n")
// 对比测试
badTime := badPractice()
goodTime := goodPractice()
fmt.Printf("频繁开关连接: %v\n", badTime)
fmt.Printf("复用连接: %v\n", goodTime)
fmt.Printf("性能提升: %.1fx\n", float64(badTime)/float64(goodTime))
fmt.Println("\n推荐模式: 全局单例")
fmt.Println("---")
fmt.Println("var db *sql.DB")
fmt.Println("")
fmt.Println("func init() {")
fmt.Println(" db, _ = sql.Open(\"sqlite3\", \"app.db\")")
fmt.Println(" db.SetMaxOpenConns(1)")
fmt.Println("}")
fmt.Println("")
fmt.Println("func main() {")
fmt.Println(" defer db.Close()")
fmt.Println(" // 使用db进行所有数据库操作")
fmt.Println("}")
fmt.Println("---")
// 演示全局单例使用
db := GetDB()
defer db.Close()
db.Exec(`CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)`)
db.Exec(`INSERT INTO users (name) VALUES ('Alice')`)
var count int
db.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&count)
fmt.Printf("\n使用全局DB实例查询: %d 条记录\n", count)
}
---
2.2 连接管理
01.打开连接
a.sql.Open方法
a.功能说明
sql.Open接受驱动名称和DSN连接字符串,返回*sql.DB对象。注意Open方法只是初始化连接池,并不会立即建立数据库连接,需要调用Ping方法验证连接是否可用。
b.代码示例
---
// sql.Open详解
package main
import (
"database/sql"
"fmt"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
func main() {
fmt.Println("=== sql.Open方法详解 ===\n")
// Open不会立即连接数据库
fmt.Println("1. 调用sql.Open...")
db, err := sql.Open("sqlite3", "./myapp.db")
if err != nil {
log.Fatal("Open失败:", err)
}
defer db.Close()
fmt.Println(" ✓ Open成功(但尚未建立连接)")
// Ping才会真正建立连接
fmt.Println("\n2. 调用db.Ping验证连接...")
err = db.Ping()
if err != nil {
log.Fatal("Ping失败:", err)
}
fmt.Println(" ✓ Ping成功(连接已建立)")
// 检查数据库文件
fileInfo, err := os.Stat("./myapp.db")
if err == nil {
fmt.Printf("\n3. 数据库文件信息:\n")
fmt.Printf(" 路径: %s\n", "./myapp.db")
fmt.Printf(" 大小: %d bytes\n", fileInfo.Size())
fmt.Printf(" 修改时间: %v\n", fileInfo.ModTime())
}
// 获取驱动信息
fmt.Println("\n4. 驱动信息:")
drivers := sql.Drivers()
for _, driver := range drivers {
fmt.Printf(" - %s\n", driver)
}
// 执行简单查询验证
var version string
err = db.QueryRow("SELECT sqlite_version()").Scan(&version)
if err != nil {
log.Fatal("查询失败:", err)
}
fmt.Printf("\n5. SQLite版本: %s\n", version)
// 清理
os.Remove("./myapp.db")
}
---
b.连接字符串选项
a.功能说明
SQLite的DSN支持丰富的选项配置,通过URL查询参数传递。常用选项包括缓存模式、日志模式、同步级别、忙等待超时等,合理配置可以显著影响性能和可靠性。
b.代码示例
---
// DSN连接选项最佳实践
package main
import (
"database/sql"
"fmt"
"log"
"net/url"
_ "github.com/mattn/go-sqlite3"
)
func openWithOptions(name, dsn string) {
db, err := sql.Open("sqlite3", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 验证配置
var journalMode, syncMode string
var cacheSize, busyTimeout int
db.QueryRow("PRAGMA journal_mode").Scan(&journalMode)
db.QueryRow("PRAGMA synchronous").Scan(&syncMode)
db.QueryRow("PRAGMA cache_size").Scan(&cacheSize)
db.QueryRow("PRAGMA busy_timeout").Scan(&busyTimeout)
fmt.Printf("%s配置:\n", name)
fmt.Printf(" 日志模式: %s\n", journalMode)
fmt.Printf(" 同步级别: %s\n", syncMode)
fmt.Printf(" 缓存大小: %d 页\n", cacheSize)
fmt.Printf(" 忙超时: %d ms\n\n", busyTimeout)
}
func main() {
fmt.Println("=== DSN连接选项配置 ===\n")
// 1. 默认配置
openWithOptions("默认配置", ":memory:")
// 2. 性能优化配置
params := url.Values{}
params.Set("_journal_mode", "WAL") // WAL模式
params.Set("_synchronous", "NORMAL") // 平衡模式
params.Set("_cache_size", "-64000") // 64MB缓存
params.Set("_busy_timeout", "5000") // 5秒超时
params.Set("_foreign_keys", "ON") // 启用外键
dsn := fmt.Sprintf("file::memory:?%s", params.Encode())
openWithOptions("性能优化", dsn)
// 3. 安全优先配置
safeParams := url.Values{}
safeParams.Set("_journal_mode", "DELETE")
safeParams.Set("_synchronous", "FULL")
safeParams.Set("_secure_delete", "ON")
safeDSN := fmt.Sprintf("file::memory:?%s", safeParams.Encode())
openWithOptions("安全优先", safeDSN)
// 4. 只读模式
readonlyParams := url.Values{}
readonlyParams.Set("mode", "ro")
readonlyDSN := fmt.Sprintf("file:readonly.db?%s", readonlyParams.Encode())
fmt.Println("只读模式配置:")
fmt.Printf(" DSN: %s\n", readonlyDSN)
fmt.Println(" 说明: 适用于数据分析、报表生成\n")
// 5. 共享缓存模式
sharedParams := url.Values{}
sharedParams.Set("cache", "shared")
sharedDSN := fmt.Sprintf("file::memory:?%s", sharedParams.Encode())
fmt.Println("共享缓存配置:")
fmt.Printf(" DSN: %s\n", sharedDSN)
fmt.Println(" 说明: 多连接共享内存数据库\n")
fmt.Println("推荐配置场景:")
fmt.Println(" 桌面应用: WAL + NORMAL + 适中缓存")
fmt.Println(" 服务端: WAL + NORMAL + 大缓存 + 长超时")
fmt.Println(" 嵌入式: DELETE + NORMAL + 小缓存")
fmt.Println(" 临时数据: :memory: + 默认配置")
}
---
02.连接验证
a.Ping方法
a.功能说明
Ping方法向数据库发送一个简单的查询来验证连接是否有效。建议在应用启动时调用Ping确保数据库可访问,在长时间运行的程序中定期Ping检测连接健康状态。
b.代码示例
---
// 连接健康检查
package main
import (
"context"
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
// HealthChecker 健康检查器
type HealthChecker struct {
db *sql.DB
interval time.Duration
stopCh chan struct{}
}
func NewHealthChecker(db *sql.DB, interval time.Duration) *HealthChecker {
return &HealthChecker{
db: db,
interval: interval,
stopCh: make(chan struct{}),
}
}
func (hc *HealthChecker) Start() {
ticker := time.NewTicker(hc.interval)
go func() {
for {
select {
case <-ticker.C:
hc.check()
case <-hc.stopCh:
ticker.Stop()
return
}
}
}()
}
func (hc *HealthChecker) Stop() {
close(hc.stopCh)
}
func (hc *HealthChecker) check() {
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
start := time.Now()
err := hc.db.PingContext(ctx)
latency := time.Since(start)
if err != nil {
log.Printf("❌ 健康检查失败: %v (延迟: %v)", err, latency)
} else {
fmt.Printf("✓ 健康检查通过 (延迟: %v)\n", latency)
}
}
func main() {
fmt.Println("=== 数据库连接健康检查 ===\n")
db, err := sql.Open("sqlite3", "./health_check.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 初始连接验证
fmt.Println("1. 初始连接验证:")
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
defer cancel()
err = db.PingContext(ctx)
if err != nil {
log.Fatal(" 连接失败:", err)
}
fmt.Println(" ✓ 连接成功\n")
// 获取连接统计
stats := db.Stats()
fmt.Println("2. 连接池状态:")
fmt.Printf(" 打开连接数: %d\n", stats.OpenConnections)
fmt.Printf(" 使用中: %d\n", stats.InUse)
fmt.Printf(" 空闲: %d\n\n", stats.Idle)
// 启动定期健康检查
fmt.Println("3. 启动定期健康检查(每2秒):")
checker := NewHealthChecker(db, 2*time.Second)
checker.Start()
// 运行10秒
time.Sleep(10 * time.Second)
checker.Stop()
fmt.Println("\n4. 最终连接统计:")
finalStats := db.Stats()
fmt.Printf(" 总等待次数: %d\n", finalStats.WaitCount)
fmt.Printf(" 总等待时间: %v\n", finalStats.WaitDuration)
fmt.Printf(" 最大打开连接数: %d\n", finalStats.MaxOpenConnections)
}
---
03.关闭连接
a.Close方法
a.功能说明
Close方法关闭数据库连接池,释放所有资源。关闭后的db对象不能再使用。应该在应用退出时调用Close,通常使用defer确保正确关闭。对于临时数据库操作,使用defer db.Close()是最佳实践。
b.代码示例
---
// 连接关闭最佳实践
package main
import (
"database/sql"
"fmt"
"log"
"os"
"os/signal"
"syscall"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
fmt.Println("=== 连接关闭最佳实践 ===\n")
// 1. 基本defer模式(推荐)
fmt.Println("1. 基本defer模式:")
func() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close() // 函数退出时自动关闭
db.Exec(`CREATE TABLE test (id INTEGER)`)
fmt.Println(" ✓ 使用defer确保关闭\n")
}()
// 2. 优雅关闭模式(长期运行服务)
fmt.Println("2. 优雅关闭模式(捕获信号):")
db, err := sql.Open("sqlite3", "./graceful.db")
if err != nil {
log.Fatal(err)
}
// 捕获终止信号
sigChan := make(chan os.Signal, 1)
signal.Notify(sigChan, os.Interrupt, syscall.SIGTERM)
// 模拟工作
done := make(chan bool)
go func() {
ticker := time.NewTicker(1 * time.Second)
defer ticker.Stop()
for {
select {
case <-ticker.C:
db.Exec(`CREATE TABLE IF NOT EXISTS heartbeat (ts INTEGER)`)
db.Exec(`INSERT INTO heartbeat VALUES (?)`, time.Now().Unix())
case <-done:
return
}
}
}()
// 等待信号或超时
select {
case sig := <-sigChan:
fmt.Printf(" 收到信号: %v\n", sig)
case <-time.After(3 * time.Second):
fmt.Println(" 运行3秒后自动停止")
}
// 优雅关闭
fmt.Println(" 正在优雅关闭...")
done <- true
// 等待正在执行的查询完成
time.Sleep(100 * time.Millisecond)
// 获取最终统计
stats := db.Stats()
fmt.Printf(" 最终打开连接数: %d\n", stats.OpenConnections)
// 关闭数据库
err = db.Close()
if err != nil {
log.Printf(" 关闭失败: %v\n", err)
} else {
fmt.Println(" ✓ 数据库已关闭\n")
}
// 验证关闭后不能使用
fmt.Println("3. 验证关闭后行为:")
err = db.Ping()
if err != nil {
fmt.Printf(" Ping失败(预期): %v\n", err)
}
_, err = db.Exec(`SELECT 1`)
if err != nil {
fmt.Printf(" Exec失败(预期): %v\n\n", err)
}
// 4. 错误处理:重复关闭
fmt.Println("4. 重复关闭测试:")
err = db.Close()
if err != nil {
fmt.Printf(" 重复关闭错误: %v\n", err)
} else {
fmt.Println(" 重复关闭成功(无错误)")
}
fmt.Println("\n关闭建议:")
fmt.Println(" - 短期操作: 使用defer db.Close()")
fmt.Println(" - 长期服务: 捕获信号优雅关闭")
fmt.Println(" - 连接池: 应用生命周期内保持打开")
fmt.Println(" - 测试代码: defer确保资源清理")
os.Remove("./graceful.db")
}
---
04.连接池配置
a.池参数设置
a.功能说明
database/sql通过SetMaxOpenConns、SetMaxIdleConns、SetConnMaxLifetime、SetConnMaxIdleTime四个方法配置连接池。对于SQLite,由于写入串行化,通常设置MaxOpenConns为1,避免锁竞争。
b.代码示例
---
// 连接池配置详解
package main
import (
"database/sql"
"fmt"
"log"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
func testPoolConfig(name string, maxOpen, maxIdle int, maxLifetime, maxIdleTime time.Duration) {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 配置连接池
db.SetMaxOpenConns(maxOpen)
db.SetMaxIdleConns(maxIdle)
db.SetConnMaxLifetime(maxLifetime)
db.SetConnMaxIdleTime(maxIdleTime)
db.Exec(`CREATE TABLE test (id INTEGER PRIMARY KEY, value INTEGER)`)
fmt.Printf("%s:\n", name)
fmt.Printf(" MaxOpenConns: %d\n", maxOpen)
fmt.Printf(" MaxIdleConns: %d\n", maxIdle)
fmt.Printf(" ConnMaxLifetime: %v\n", maxLifetime)
fmt.Printf(" ConnMaxIdleTime: %v\n", maxIdleTime)
// 并发测试
var wg sync.WaitGroup
start := time.Now()
for i := 0; i < 50; i++ {
wg.Add(1)
go func(n int) {
defer wg.Done()
db.Exec(`INSERT INTO test (value) VALUES (?)`, n)
}(i)
}
wg.Wait()
elapsed := time.Since(start)
stats := db.Stats()
fmt.Printf(" 性能: %v\n", elapsed)
fmt.Printf(" 等待次数: %d\n", stats.WaitCount)
fmt.Printf(" 等待时间: %v\n", stats.WaitDuration)
fmt.Printf(" 打开连接: %d\n\n", stats.OpenConnections)
}
func main() {
fmt.Println("=== 连接池配置对比 ===\n")
// 1. SQLite推荐配置
testPoolConfig(
"SQLite推荐(单连接)",
1, // MaxOpenConns
1, // MaxIdleConns
time.Hour, // ConnMaxLifetime
10*time.Minute, // ConnMaxIdleTime
)
// 2. WAL模式配置
testPoolConfig(
"WAL模式(多读一写)",
5, // MaxOpenConns
2, // MaxIdleConns
30*time.Minute, // ConnMaxLifetime
5*time.Minute, // ConnMaxIdleTime
)
// 3. 默认配置(不推荐)
testPoolConfig(
"默认配置(无限制)",
0, // 无限制
2, // 默认2个空闲
0, // 无限制
0, // 无限制
)
// 详细说明
fmt.Println("参数说明:")
fmt.Println(" SetMaxOpenConns(n):")
fmt.Println(" 0: 无限制(不推荐)")
fmt.Println(" 1: SQLite DELETE模式推荐")
fmt.Println(" CPU核心数: WAL模式推荐")
fmt.Println()
fmt.Println(" SetMaxIdleConns(n):")
fmt.Println(" 通常设为MaxOpenConns的1/2")
fmt.Println(" 太大: 浪费资源")
fmt.Println(" 太小: 频繁创建销毁连接")
fmt.Println()
fmt.Println(" SetConnMaxLifetime(d):")
fmt.Println(" 防止连接长期占用")
fmt.Println(" 文件数据库: 30分钟-1小时")
fmt.Println(" 内存数据库: 可设置较长")
fmt.Println()
fmt.Println(" SetConnMaxIdleTime(d):")
fmt.Println(" 空闲连接超时自动关闭")
fmt.Println(" 释放系统资源")
fmt.Println(" 通常5-10分钟")
}
---
2.3 创建表
01.基本语法
a.CREATE TABLE语句
a.功能说明
CREATE TABLE用于创建新表,定义列名、数据类型、约束和索引。SQLite的类型系统灵活,支持INTEGER、TEXT、REAL、BLOB、NUMERIC五种存储类。通过db.Exec执行DDL语句,不返回结果集。
b.代码示例
---
// CREATE TABLE基础示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
fmt.Println("=== CREATE TABLE基础 ===\n")
// 1. 最简单的表
_, err = db.Exec(`
CREATE TABLE simple (
id INTEGER,
name TEXT
)
`)
if err != nil {
log.Fatal(err)
}
fmt.Println("1. 简单表创建成功")
// 2. 完整类型示例
_, err = db.Exec(`
CREATE TABLE types_demo (
id INTEGER,
name TEXT,
price REAL,
data BLOB,
quantity NUMERIC
)
`)
if err != nil {
log.Fatal(err)
}
fmt.Println("2. 类型演示表创建成功")
// 3. 带PRIMARY KEY的表
_, err = db.Exec(`
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
email TEXT,
created_at INTEGER
)
`)
if err != nil {
log.Fatal(err)
}
fmt.Println("3. 用户表创建成功")
// 4. AUTOINCREMENT主键
_, err = db.Exec(`
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
content TEXT,
author_id INTEGER,
published_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`)
if err != nil {
log.Fatal(err)
}
fmt.Println("4. 文章表(自增主键)创建成功")
// 查询已创建的表
fmt.Println("\n已创建的表:")
rows, _ := db.Query(`
SELECT name, sql FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name
`)
defer rows.Close()
for rows.Next() {
var name, sql string
rows.Scan(&name, &sql)
fmt.Printf("\n表名: %s\n", name)
fmt.Printf("定义: %s\n", sql)
}
// 获取表结构
fmt.Println("\n查看users表结构:")
tableInfo, _ := db.Query(`PRAGMA table_info(users)`)
defer tableInfo.Close()
fmt.Println("列信息:")
for tableInfo.Next() {
var cid int
var name, ctype string
var notnull, pk int
var dfltValue sql.NullString
tableInfo.Scan(&cid, &name, &ctype, ¬null, &dfltValue, &pk)
fmt.Printf(" %d: %s %s", cid, name, ctype)
if notnull == 1 {
fmt.Print(" NOT NULL")
}
if pk == 1 {
fmt.Print(" PRIMARY KEY")
}
fmt.Println()
}
}
---
b.数据类型
a.功能说明
SQLite使用动态类型系统,列类型只是建议而非强制。数据实际存储为五种类型:NULL、INTEGER(整数)、REAL(浮点数)、TEXT(文本)、BLOB(二进制)。类型亲和性(Type Affinity)决定如何转换存储值。
b.代码示例
---
// SQLite数据类型详解
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
fmt.Println("=== SQLite数据类型系统 ===\n")
// 创建演示表
db.Exec(`
CREATE TABLE type_test (
int_col INTEGER,
text_col TEXT,
real_col REAL,
blob_col BLOB,
numeric_col NUMERIC,
any_col -- 无类型声明
)
`)
// 插入不同类型数据
db.Exec(`
INSERT INTO type_test VALUES (
42, -- INTEGER
'Hello SQLite', -- TEXT
3.14159, -- REAL
X'48656C6C6F', -- BLOB (hex)
100.5, -- NUMERIC
'anything' -- ANY
)
`)
// 读取数据
var intVal int64
var textVal string
var realVal float64
var blobVal []byte
var numericVal float64
var anyVal interface{}
err = db.QueryRow(`SELECT * FROM type_test`).Scan(
&intVal, &textVal, &realVal, &blobVal, &numericVal, &anyVal)
if err != nil {
log.Fatal(err)
}
fmt.Println("1. 基本类型存储:")
fmt.Printf(" INTEGER: %d (Go类型: %T)\n", intVal, intVal)
fmt.Printf(" TEXT: %s (Go类型: %T)\n", textVal, textVal)
fmt.Printf(" REAL: %.5f (Go类型: %T)\n", realVal, realVal)
fmt.Printf(" BLOB: %v (Go类型: %T)\n", blobVal, blobVal)
fmt.Printf(" NUMERIC: %.1f (Go类型: %T)\n", numericVal, numericVal)
fmt.Printf(" ANY: %v (Go类型: %T)\n\n", anyVal, anyVal)
// 类型亲和性测试
db.Exec(`CREATE TABLE affinity_test (
varchar_col VARCHAR(50),
int_col INT,
double_col DOUBLE,
datetime_col DATETIME
)`)
db.Exec(`INSERT INTO affinity_test VALUES (?, ?, ?, ?)`,
"text value", 123, 45.67, time.Now().Unix())
fmt.Println("2. 类型亲和性规则:")
fmt.Println(" VARCHAR -> TEXT affinity")
fmt.Println(" INT -> INTEGER affinity")
fmt.Println(" DOUBLE -> REAL affinity")
fmt.Println(" DATETIME -> NUMERIC affinity")
// 动态类型演示
db.Exec(`CREATE TABLE dynamic (value)`)
db.Exec(`INSERT INTO dynamic VALUES (42)`) // 整数
db.Exec(`INSERT INTO dynamic VALUES (3.14)`) // 浮点数
db.Exec(`INSERT INTO dynamic VALUES ('text')`) // 文本
db.Exec(`INSERT INTO dynamic VALUES (NULL)`) // NULL
fmt.Println("\n3. 动态类型存储(同一列不同类型):")
rows, _ := db.Query(`SELECT value, typeof(value) FROM dynamic`)
defer rows.Close()
for rows.Next() {
var value interface{}
var valueType string
rows.Scan(&value, &valueType)
fmt.Printf(" 值: %v, SQLite类型: %s, Go类型: %T\n",
value, valueType, value)
}
// 布尔值处理
db.Exec(`CREATE TABLE bool_test (active INTEGER)`)
db.Exec(`INSERT INTO bool_test VALUES (1)`) // true
db.Exec(`INSERT INTO bool_test VALUES (0)`) // false
fmt.Println("\n4. 布尔值处理(SQLite无BOOLEAN类型):")
boolRows, _ := db.Query(`SELECT active FROM bool_test`)
defer boolRows.Close()
for boolRows.Next() {
var active bool
boolRows.Scan(&active)
fmt.Printf(" 布尔值: %v\n", active)
}
// 日期时间处理
fmt.Println("\n5. 日期时间处理:")
db.Exec(`CREATE TABLE events (
id INTEGER PRIMARY KEY,
created_at INTEGER, -- Unix时间戳
updated_at TEXT, -- ISO8601字符串
scheduled_at REAL -- Julian Day Number
)`)
now := time.Now()
db.Exec(`INSERT INTO events (created_at, updated_at, scheduled_at) VALUES (?, ?, ?)`,
now.Unix(),
now.Format(time.RFC3339),
float64(now.Unix())/86400.0 + 2440587.5,
)
var timestamp int64
var isoString string
var julianDay float64
db.QueryRow(`SELECT created_at, updated_at, scheduled_at FROM events WHERE id = 1`).
Scan(×tamp, &isoString, &julianDay)
fmt.Printf(" Unix时间戳: %d\n", timestamp)
fmt.Printf(" ISO8601: %s\n", isoString)
fmt.Printf(" Julian Day: %.5f\n", julianDay)
}
---
02.约束定义
a.PRIMARY KEY约束
a.功能说明
PRIMARY KEY定义主键,确保唯一性且非空。INTEGER PRIMARY KEY是SQLite的特殊情况,会使用内置的ROWID作为主键,性能最优。AUTOINCREMENT确保ID永不重用,但会有轻微性能损失。
b.代码示例
---
// PRIMARY KEY约束详解
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
fmt.Println("=== PRIMARY KEY约束 ===\n")
// 1. INTEGER PRIMARY KEY(使用ROWID)
db.Exec(`
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT
)
`)
// 插入数据(不指定ID,自动分配ROWID)
result, _ := db.Exec(`INSERT INTO users (name) VALUES ('Alice')`)
id1, _ := result.LastInsertId()
fmt.Printf("1. INTEGER PRIMARY KEY (ROWID别名)\n")
fmt.Printf(" 自动分配ID: %d\n", id1)
// 显式指定ID
db.Exec(`INSERT INTO users (id, name) VALUES (100, 'Bob')`)
fmt.Println(" 显式指定ID: 100")
// 再次插入不指定ID,会从101开始
result, _ = db.Exec(`INSERT INTO users (name) VALUES ('Charlie')`)
id2, _ := result.LastInsertId()
fmt.Printf(" 下一个自动ID: %d\n\n", id2)
// 2. AUTOINCREMENT
db.Exec(`
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
)
`)
db.Exec(`INSERT INTO products (name) VALUES ('Product A')`)
db.Exec(`INSERT INTO products (name) VALUES ('Product B')`)
result, _ = db.Exec(`INSERT INTO products (name) VALUES ('Product C')`)
lastID, _ := result.LastInsertId()
fmt.Printf("2. AUTOINCREMENT主键\n")
fmt.Printf(" 最后插入ID: %d\n", lastID)
// 删除中间记录
db.Exec(`DELETE FROM products WHERE id = 2`)
// 再次插入,ID不会重用2
result, _ = db.Exec(`INSERT INTO products (name) VALUES ('Product D')`)
newID, _ := result.LastInsertId()
fmt.Printf(" 删除ID=2后,新ID: %d (不重用)\n\n", newID)
// 3. 复合主键
db.Exec(`
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
)
`)
db.Exec(`INSERT INTO order_items VALUES (1, 101, 2)`)
db.Exec(`INSERT INTO order_items VALUES (1, 102, 1)`)
db.Exec(`INSERT INTO order_items VALUES (2, 101, 3)`)
fmt.Println("3. 复合主键:")
rows, _ := db.Query(`SELECT * FROM order_items`)
defer rows.Close()
for rows.Next() {
var orderID, productID, quantity int
rows.Scan(&orderID, &productID, &quantity)
fmt.Printf(" 订单%d, 产品%d, 数量%d\n", orderID, productID, quantity)
}
// 尝试插入重复主键
_, err = db.Exec(`INSERT INTO order_items VALUES (1, 101, 5)`)
if err != nil {
fmt.Printf("\n 重复主键错误: %v\n", err)
}
// 4. 非INTEGER主键
db.Exec(`
CREATE TABLE sessions (
session_id TEXT PRIMARY KEY,
user_id INTEGER,
created_at INTEGER
)
`)
db.Exec(`INSERT INTO sessions VALUES ('sess_abc123', 1, ?)`, 1234567890)
fmt.Println("\n4. 文本主键:")
var sessionID string
db.QueryRow(`SELECT session_id FROM sessions WHERE user_id = 1`).Scan(&sessionID)
fmt.Printf(" 会话ID: %s\n", sessionID)
fmt.Println("\nPRIMARY KEY对比:")
fmt.Println(" INTEGER PRIMARY KEY:")
fmt.Println(" - 使用内置ROWID,性能最佳")
fmt.Println(" - ID可能重用(删除后)")
fmt.Println(" - 推荐用于大多数场景")
fmt.Println(" INTEGER PRIMARY KEY AUTOINCREMENT:")
fmt.Println(" - ID永不重用")
fmt.Println(" - 额外维护sqlite_sequence表")
fmt.Println(" - 适用于ID敏感场景")
}
---
b.其他约束
a.功能说明
SQLite支持NOT NULL、UNIQUE、CHECK、DEFAULT、FOREIGN KEY等约束。约束在插入和更新时自动检查,违反约束会导致操作失败并返回错误。
b.代码示例
---
// 数据库约束完整示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 启用外键约束
db, err := sql.Open("sqlite3", ":memory:?_foreign_keys=ON")
if err != nil {
log.Fatal(err)
}
defer db.Close()
fmt.Println("=== 数据库约束详解 ===\n")
// 创建带完整约束的表
db.Exec(`
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
age INTEGER CHECK (age >= 18 AND age <= 65),
salary REAL CHECK (salary > 0),
department TEXT DEFAULT 'General',
active INTEGER DEFAULT 1,
hired_date TEXT DEFAULT CURRENT_DATE
)
`)
fmt.Println("1. NOT NULL约束:")
_, err = db.Exec(`INSERT INTO employees (age) VALUES (25)`)
if err != nil {
fmt.Printf(" ✗ 缺少必填字段: %v\n\n", err)
}
fmt.Println("2. UNIQUE约束:")
db.Exec(`INSERT INTO employees (username, email, age) VALUES ('alice', '[email protected] ', 30)`)
_, err = db.Exec(`INSERT INTO employees (username, email, age) VALUES ('alice', '[email protected] ', 28)`)
if err != nil {
fmt.Printf(" ✗ 用户名重复: %v\n\n", err)
}
fmt.Println("3. CHECK约束:")
_, err = db.Exec(`INSERT INTO employees (username, email, age) VALUES ('bob', '[email protected] ', 16)`)
if err != nil {
fmt.Printf(" ✗ 年龄不符合要求: %v\n", err)
}
_, err = db.Exec(`INSERT INTO employees (username, email, age, salary) VALUES ('charlie', '[email protected] ', 25, -1000)`)
if err != nil {
fmt.Printf(" ✗ 薪水必须为正: %v\n\n", err)
}
fmt.Println("4. DEFAULT约束:")
db.Exec(`INSERT INTO employees (username, email, age) VALUES ('david', '[email protected] ', 28)`)
var department string
var active int
var hiredDate string
db.QueryRow(`SELECT department, active, hired_date FROM employees WHERE username = 'david'`).
Scan(&department, &active, &hiredDate)
fmt.Printf(" 默认部门: %s\n", department)
fmt.Printf(" 默认状态: %d\n", active)
fmt.Printf(" 默认入职日期: %s\n\n", hiredDate)
// 5. FOREIGN KEY约束
db.Exec(`
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
)
`)
db.Exec(`
CREATE TABLE employees_fk (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept_id INTEGER,
FOREIGN KEY (dept_id) REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE
)
`)
db.Exec(`INSERT INTO departments VALUES (1, 'Engineering')`)
db.Exec(`INSERT INTO departments VALUES (2, 'Sales')`)
fmt.Println("5. FOREIGN KEY约束:")
db.Exec(`INSERT INTO employees_fk VALUES (1, 'Alice', 1)`)
fmt.Println(" ✓ 引用有效部门成功")
_, err = db.Exec(`INSERT INTO employees_fk VALUES (2, 'Bob', 999)`)
if err != nil {
fmt.Printf(" ✗ 引用无效部门: %v\n", err)
}
// 级联删除测试
db.Exec(`DELETE FROM departments WHERE id = 1`)
var count int
db.QueryRow(`SELECT COUNT(*) FROM employees_fk WHERE dept_id = 1`).Scan(&count)
fmt.Printf(" 级联删除后员工数: %d\n", count)
fmt.Println("\n约束类型总结:")
fmt.Println(" NOT NULL: 列不能为空")
fmt.Println(" UNIQUE: 列值必须唯一")
fmt.Println(" PRIMARY KEY: 唯一标识记录")
fmt.Println(" FOREIGN KEY: 引用完整性")
fmt.Println(" CHECK: 自定义验证规则")
fmt.Println(" DEFAULT: 默认值")
}
---
03.条件创建
a.IF NOT EXISTS
a.功能说明
IF NOT EXISTS子句使CREATE TABLE语句在表已存在时不报错,而是静默跳过。这对于应用初始化脚本和迁移脚本非常有用,可以安全地重复执行而不会失败。
b.代码示例
---
// 条件创建表实践
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
// InitDatabase 数据库初始化函数(幂等性)
func InitDatabase(db *sql.DB) error {
// 用户表
_, err := db.Exec(`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
created_at INTEGER DEFAULT (strftime('%s', 'now'))
)
`)
if err != nil {
return fmt.Errorf("创建users表失败: %w", err)
}
// 配置表
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS settings (
key TEXT PRIMARY KEY,
value TEXT,
updated_at INTEGER DEFAULT (strftime('%s', 'now'))
)
`)
if err != nil {
return fmt.Errorf("创建settings表失败: %w", err)
}
// 日志表
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
level TEXT,
message TEXT,
timestamp INTEGER DEFAULT (strftime('%s', 'now'))
)
`)
if err != nil {
return fmt.Errorf("创建logs表失败: %w", err)
}
return nil
}
func main() {
db, err := sql.Open("sqlite3", "./app.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
fmt.Println("=== IF NOT EXISTS使用 ===\n")
// 第一次初始化
fmt.Println("第一次初始化:")
err = InitDatabase(db)
if err != nil {
log.Fatal(err)
}
// 查询创建的表
var tableCount int
db.QueryRow(`
SELECT COUNT(*) FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
`).Scan(&tableCount)
fmt.Printf(" 创建了 %d 个表\n\n", tableCount)
// 第二次初始化(幂等性)
fmt.Println("第二次初始化(重复执行):")
err = InitDatabase(db)
if err != nil {
log.Fatal(err)
}
fmt.Println(" ✓ 没有报错,幂等性保证\n")
// 检查表是否存在的函数
tableExists := func(tableName string) bool {
var exists int
db.QueryRow(`
SELECT COUNT(*) FROM sqlite_master
WHERE type = 'table' AND name = ?
`, tableName).Scan(&exists)
return exists > 0
}
fmt.Println("验证表存在性:")
tables := []string{"users", "settings", "logs", "nonexistent"}
for _, table := range tables {
if tableExists(table) {
fmt.Printf(" ✓ %s 表存在\n", table)
} else {
fmt.Printf(" ✗ %s 表不存在\n", table)
}
}
// 插入测试数据
db.Exec(`INSERT INTO users (username, password_hash) VALUES ('admin', 'hash123')`)
db.Exec(`INSERT INTO settings VALUES ('app_version', '1.0.0', NULL)`)
db.Exec(`INSERT INTO logs (level, message) VALUES ('INFO', '应用启动')`)
// 第三次初始化,验证数据未丢失
fmt.Println("\n第三次初始化:")
err = InitDatabase(db)
if err != nil {
log.Fatal(err)
}
var userCount, settingCount, logCount int
db.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&userCount)
db.QueryRow(`SELECT COUNT(*) FROM settings`).Scan(&settingCount)
db.QueryRow(`SELECT COUNT(*) FROM logs`).Scan(&logCount)
fmt.Printf(" 用户数: %d\n", userCount)
fmt.Printf(" 配置数: %d\n", settingCount)
fmt.Printf(" 日志数: %d\n", logCount)
fmt.Println(" ✓ 数据未丢失")
fmt.Println("\n最佳实践:")
fmt.Println(" 1. 应用启动时调用初始化函数")
fmt.Println(" 2. 使用IF NOT EXISTS确保幂等性")
fmt.Println(" 3. 集中管理表结构定义")
fmt.Println(" 4. 错误处理返回详细信息")
}
---
2.4 插入数据
01.基本插入
a.INSERT语句
a.功能说明
INSERT INTO语句用于向表中插入新记录。可以插入完整行或指定列,支持VALUES子句提供字面值或使用参数绑定。db.Exec方法执行插入,返回Result对象包含LastInsertId和RowsAffected信息。
b.代码示例
---
// INSERT基础操作
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 创建测试表
db.Exec(`
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER DEFAULT 0,
created_at INTEGER
)
`)
fmt.Println("=== INSERT基础操作 ===\n")
// 1. 插入所有列
result, err := db.Exec(`
INSERT INTO products (name, price, stock, created_at)
VALUES ('笔记本', 5999.00, 50, ?)
`, time.Now().Unix())
if err != nil {
log.Fatal(err)
}
id, _ := result.LastInsertId()
affected, _ := result.RowsAffected()
fmt.Printf("1. 插入所有列:\n")
fmt.Printf(" 新记录ID: %d\n", id)
fmt.Printf(" 影响行数: %d\n\n", affected)
// 2. 插入部分列(使用DEFAULT)
result, _ = db.Exec(`
INSERT INTO products (name, price)
VALUES ('手机', 3999.00)
`)
id, _ = result.LastInsertId()
fmt.Printf("2. 插入部分列(stock使用默认值0):\n")
fmt.Printf(" 新记录ID: %d\n\n", id)
// 3. 使用参数绑定
name := "平板"
price := 2999.00
stock := 30
result, _ = db.Exec(`
INSERT INTO products (name, price, stock, created_at)
VALUES (?, ?, ?, ?)
`, name, price, stock, time.Now().Unix())
id, _ = result.LastInsertId()
fmt.Printf("3. 参数绑定插入:\n")
fmt.Printf(" 产品: %s\n", name)
fmt.Printf(" 价格: %.2f\n", price)
fmt.Printf(" 新ID: %d\n\n", id)
// 4. 插入并返回数据
insertResult, _ := db.Exec(`
INSERT INTO products (name, price, stock)
VALUES ('耳机', 299.00, 100)
`)
lastID, _ := insertResult.LastInsertId()
var insertedName string
var insertedPrice float64
db.QueryRow(`SELECT name, price FROM products WHERE id = ?`, lastID).
Scan(&insertedName, &insertedPrice)
fmt.Printf("4. 插入后查询验证:\n")
fmt.Printf(" ID: %d\n", lastID)
fmt.Printf(" 名称: %s\n", insertedName)
fmt.Printf(" 价格: %.2f\n\n", insertedPrice)
// 5. 查看所有数据
rows, _ := db.Query(`SELECT id, name, price, stock FROM products ORDER BY id`)
defer rows.Close()
fmt.Println("5. 所有产品:")
for rows.Next() {
var id int
var name string
var price float64
var stock int
rows.Scan(&id, &name, &price, &stock)
fmt.Printf(" [%d] %s - ¥%.2f (库存:%d)\n", id, name, price, stock)
}
}
---
b.返回值处理
a.功能说明
Exec返回sql.Result接口,提供LastInsertId获取自增ID和RowsAffected获取影响行数。LastInsertId对于INTEGER PRIMARY KEY返回ROWID,对于非自增主键可能不可用。错误处理检查约束违反等问题。
b.代码示例
---
// Result对象详解
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
email TEXT
)
`)
fmt.Println("=== sql.Result详解 ===\n")
// 1. 正常插入
result, err := db.Exec(`
INSERT INTO users (username, email) VALUES (?, ?)
`, "alice", "[email protected] ")
if err != nil {
log.Fatal(err)
}
lastID, err := result.LastInsertId()
if err != nil {
fmt.Printf("LastInsertId错误: %v\n", err)
} else {
fmt.Printf("1. LastInsertId: %d\n", lastID)
}
rowsAffected, err := result.RowsAffected()
if err != nil {
fmt.Printf("RowsAffected错误: %v\n", err)
} else {
fmt.Printf(" RowsAffected: %d\n\n", rowsAffected)
}
// 2. 插入多条(循环)
fmt.Println("2. 批量插入:")
var totalAffected int64
users := []struct {
Username string
Email string
}{
{"bob", "[email protected] "},
{"charlie", "[email protected] "},
{"david", "[email protected] "},
}
for _, u := range users {
result, err := db.Exec(`INSERT INTO users (username, email) VALUES (?, ?)`,
u.Username, u.Email)
if err != nil {
log.Printf(" 插入%s失败: %v\n", u.Username, err)
continue
}
affected, _ := result.RowsAffected()
totalAffected += affected
}
fmt.Printf(" 总共影响: %d 行\n\n", totalAffected)
// 3. 约束违反处理
fmt.Println("3. 约束违反:")
result, err = db.Exec(`INSERT INTO users (username, email) VALUES (?, ?)`,
"alice", "[email protected] ")
if err != nil {
fmt.Printf(" ✗ 插入失败: %v\n", err)
fmt.Println(" (用户名已存在)")
} else {
affected, _ := result.RowsAffected()
fmt.Printf(" 影响行数: %d\n", affected)
}
// 4. 验证数据
var count int
db.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&count)
fmt.Printf("\n4. 最终用户数: %d\n", count)
fmt.Println("\n最佳实践:")
fmt.Println(" ✓ 始终检查错误")
fmt.Println(" ✓ 使用LastInsertId获取新ID")
fmt.Println(" ✓ 使用RowsAffected确认影响")
fmt.Println(" ✓ 约束违反时妥善处理")
}
---
02.批量插入
a.事务批量
a.功能说明
批量插入大量数据时,必须使用事务将多个INSERT合并为一次提交,否则每次INSERT都会触发磁盘同步,性能极差。事务将所有操作缓存在内存,commit时一次性写入,性能提升可达数百倍。
b.代码示例
---
// 批量插入性能优化
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
func insertWithoutTransaction(db *sql.DB, count int) time.Duration {
db.Exec(`DELETE FROM benchmark`)
start := time.Now()
for i := 0; i < count; i++ {
db.Exec(`INSERT INTO benchmark (value) VALUES (?)`, i)
}
return time.Since(start)
}
func insertWithTransaction(db *sql.DB, count int) time.Duration {
db.Exec(`DELETE FROM benchmark`)
start := time.Now()
tx, _ := db.Begin()
for i := 0; i < count; i++ {
tx.Exec(`INSERT INTO benchmark (value) VALUES (?)`, i)
}
tx.Commit()
return time.Since(start)
}
func insertWithPrepared(db *sql.DB, count int) time.Duration {
db.Exec(`DELETE FROM benchmark`)
start := time.Now()
tx, _ := db.Begin()
stmt, _ := tx.Prepare(`INSERT INTO benchmark (value) VALUES (?)`)
defer stmt.Close()
for i := 0; i < count; i++ {
stmt.Exec(i)
}
tx.Commit()
return time.Since(start)
}
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`CREATE TABLE benchmark (
id INTEGER PRIMARY KEY AUTOINCREMENT,
value INTEGER
)`)
fmt.Println("=== 批量插入性能对比 ===\n")
count := 10000
fmt.Printf("插入 %d 条记录:\n\n", count)
// 方法1: 无事务(很慢,不推荐)
if count <= 1000 { // 仅小数据量测试
t1 := insertWithoutTransaction(db, count)
fmt.Printf("1. 无事务: %v\n", t1)
}
// 方法2: 使用事务
t2 := insertWithTransaction(db, count)
fmt.Printf("2. 使用事务: %v\n", t2)
// 方法3: 预编译+事务
t3 := insertWithPrepared(db, count)
fmt.Printf("3. 预编译+事务: %v\n", t3)
improvement := float64(t2) / float64(t3)
fmt.Printf("\n预编译提升: %.2fx\n", improvement)
// 大批量测试
fmt.Println("\n=== 大批量插入测试 ===\n")
largeCount := 100000
fmt.Printf("插入 %d 条记录:\n", largeCount)
start := time.Now()
tx, _ := db.Begin()
stmt, _ := tx.Prepare(`INSERT INTO benchmark (value) VALUES (?)`)
for i := 0; i < largeCount; i++ {
stmt.Exec(i)
// 每10000条提交一次(避免事务过大)
if (i+1)%10000 == 0 {
stmt.Close()
tx.Commit()
tx, _ = db.Begin()
stmt, _ = tx.Prepare(`INSERT INTO benchmark (value) VALUES (?)`)
}
}
stmt.Close()
tx.Commit()
elapsed := time.Since(start)
var total int
db.QueryRow(`SELECT COUNT(*) FROM benchmark`).Scan(&total)
fmt.Printf("\n总耗时: %v\n", elapsed)
fmt.Printf("每秒插入: %.0f 条\n", float64(largeCount)/elapsed.Seconds())
fmt.Printf("验证总数: %d\n", total)
fmt.Println("\n批量插入建议:")
fmt.Println(" 1. 必须使用事务")
fmt.Println(" 2. 使用预编译语句")
fmt.Println(" 3. 合理分批(每批1000-10000)")
fmt.Println(" 4. 关闭同步模式(PRAGMA synchronous=OFF)")
}
---
b.预编译语句
a.功能说明
预编译语句(Prepared Statement)将SQL语句编译一次,重复执行时只需传入参数,避免重复解析SQL。对于批量操作,预编译可显著提升性能。使用db.Prepare或tx.Prepare创建,用完后必须Close释放资源。
b.代码示例
---
// 预编译语句最佳实践
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`CREATE TABLE logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
level TEXT,
message TEXT,
timestamp INTEGER
)`)
fmt.Println("=== 预编译语句使用 ===\n")
// 1. 基本预编译
stmt, err := db.Prepare(`INSERT INTO logs (level, message, timestamp) VALUES (?, ?, ?)`)
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
fmt.Println("1. 使用预编译语句插入:")
result, _ := stmt.Exec("INFO", "应用启动", time.Now().Unix())
id, _ := result.LastInsertId()
fmt.Printf(" 插入记录ID: %d\n", id)
stmt.Exec("DEBUG", "加载配置", time.Now().Unix())
stmt.Exec("INFO", "服务器监听端口8080", time.Now().Unix())
stmt.Exec("WARN", "缓存未命中", time.Now().Unix())
fmt.Println(" ✓ 批量插入完成\n")
// 2. 在事务中使用预编译
fmt.Println("2. 事务中的预编译语句:")
tx, _ := db.Begin()
txStmt, _ := tx.Prepare(`INSERT INTO logs (level, message, timestamp) VALUES (?, ?, ?)`)
start := time.Now()
for i := 0; i < 1000; i++ {
txStmt.Exec("INFO", fmt.Sprintf("消息#%d", i), time.Now().Unix())
}
txStmt.Close()
tx.Commit()
elapsed := time.Since(start)
fmt.Printf(" 插入1000条耗时: %v\n", elapsed)
fmt.Printf(" 每秒插入: %.0f 条\n\n", 1000/elapsed.Seconds())
// 3. 查询预编译
fmt.Println("3. 查询预编译语句:")
queryStmt, _ := db.Prepare(`SELECT id, level, message FROM logs WHERE level = ? LIMIT ?`)
defer queryStmt.Close()
rows, _ := queryStmt.Query("INFO", 5)
defer rows.Close()
count := 0
for rows.Next() {
var id int
var level, message string
rows.Scan(&id, &level, &message)
count++
}
fmt.Printf(" 查询到 %d 条INFO日志\n\n", count)
// 4. 更新预编译
fmt.Println("4. 更新预编译语句:")
updateStmt, _ := db.Prepare(`UPDATE logs SET level = ? WHERE level = ?`)
defer updateStmt.Close()
result, _ = updateStmt.Exec("WARNING", "WARN")
affected, _ := result.RowsAffected()
fmt.Printf(" 更新了 %d 条记录\n\n", affected)
// 5. 预编译陷阱:忘记Close
fmt.Println("5. 资源管理(重要!):")
fmt.Println(" ✓ 正确: defer stmt.Close()")
fmt.Println(" ✗ 错误: 忘记Close导致资源泄漏")
fmt.Println(" 建议: 始终使用defer确保关闭\n")
// 统计
var totalLogs int
db.QueryRow(`SELECT COUNT(*) FROM logs`).Scan(&totalLogs)
fmt.Printf("最终日志总数: %d\n", totalLogs)
fmt.Println("\n预编译使用场景:")
fmt.Println(" ✓ 批量插入/更新")
fmt.Println(" ✓ 重复执行的查询")
fmt.Println(" ✓ 参数化SQL防注入")
fmt.Println(" ✗ 一次性查询(开销大于收益)")
}
---
03.INSERT扩展语法
a.INSERT OR REPLACE
a.功能说明
INSERT OR REPLACE在主键或唯一约束冲突时删除旧行并插入新行,相当于REPLACE INTO。INSERT OR IGNORE在冲突时忽略插入。INSERT OR ABORT/FAIL/ROLLBACK控制冲突时的事务行为。
b.代码示例
---
// INSERT冲突处理策略
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.Exec(`CREATE TABLE configs (
key TEXT PRIMARY KEY,
value TEXT,
version INTEGER DEFAULT 1
)`)
fmt.Println("=== INSERT冲突处理 ===\n")
// 初始数据
db.Exec(`INSERT INTO configs VALUES ('theme', 'light', 1)`)
db.Exec(`INSERT INTO configs VALUES ('language', 'zh-CN', 1)`)
fmt.Println("1. INSERT OR REPLACE:")
result, _ := db.Exec(`INSERT OR REPLACE INTO configs VALUES ('theme', 'dark', 2)`)
affected, _ := result.RowsAffected()
fmt.Printf(" 影响行数: %d\n", affected)
var theme string
var version int
db.QueryRow(`SELECT value, version FROM configs WHERE key = 'theme'`).Scan(&theme, &version)
fmt.Printf(" 主题: %s (版本: %d)\n\n", theme, version)
// 2. INSERT OR IGNORE
fmt.Println("2. INSERT OR IGNORE:")
db.Exec(`INSERT OR IGNORE INTO configs VALUES ('theme', 'blue', 3)`)
db.QueryRow(`SELECT value, version FROM configs WHERE key = 'theme'`).Scan(&theme, &version)
fmt.Printf(" 主题: %s (版本: %d)\n", theme, version)
fmt.Println(" (保持原值,冲突被忽略)\n")
// 3. ON CONFLICT子句(SQLite 3.24+)
fmt.Println("3. ON CONFLICT DO UPDATE (UPSERT):")
db.Exec(`
INSERT INTO configs (key, value, version) VALUES ('font_size', '14', 1)
ON CONFLICT(key) DO UPDATE SET
value = excluded.value,
version = version + 1
`)
db.Exec(`
INSERT INTO configs (key, value, version) VALUES ('font_size', '16', 1)
ON CONFLICT(key) DO UPDATE SET
value = excluded.value,
version = version + 1
`)
var fontSize string
var fontVersion int
db.QueryRow(`SELECT value, version FROM configs WHERE key = 'font_size'`).
Scan(&fontSize, &fontVersion)
fmt.Printf(" 字体大小: %s (版本: %d)\n", fontSize, fontVersion)
fmt.Println(" (版本号自动递增)\n")
// 4. ON CONFLICT DO NOTHING
fmt.Println("4. ON CONFLICT DO NOTHING:")
result, _ = db.Exec(`
INSERT INTO configs (key, value) VALUES ('language', 'en-US')
ON CONFLICT(key) DO NOTHING
`)
affected, _ = result.RowsAffected()
fmt.Printf(" 影响行数: %d (冲突被忽略)\n\n", affected)
// 5. 查看所有配置
fmt.Println("5. 最终配置:")
rows, _ := db.Query(`SELECT key, value, version FROM configs ORDER BY key`)
defer rows.Close()
for rows.Next() {
var key, value string
var ver int
rows.Scan(&key, &value, &ver)
fmt.Printf(" %s = %s (v%d)\n", key, value, ver)
}
fmt.Println("\n策略选择:")
fmt.Println(" INSERT OR REPLACE: 完全替换旧行")
fmt.Println(" INSERT OR IGNORE: 保留旧行")
fmt.Println(" ON CONFLICT DO UPDATE: 选择性更新字段(推荐)")
fmt.Println(" ON CONFLICT DO NOTHING: 同IGNORE")
}
---
2.5 查询数据
01.基本查询
a.QueryRow方法
a.功能说明
QueryRow用于查询单行数据,返回*sql.Row对象。调用Scan方法将结果映射到Go变量。如果查询无结果返回sql.ErrNoRows错误,需要特殊处理与其他错误区分。
b.代码示例
---
// QueryRow详解
package main
import (
"database/sql"
"errors"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, age INTEGER)`)
db.Exec(`INSERT INTO users VALUES (1, 'alice', 25), (2, 'bob', 30)`)
fmt.Println("=== QueryRow使用 ===\n")
// 查询存在的记录
var username string
var age int
err := db.QueryRow(`SELECT username, age FROM users WHERE id = ?`, 1).Scan(&username, &age)
if err != nil {
log.Fatal(err)
}
fmt.Printf("用户: %s, 年龄: %d\n", username, age)
// 查询不存在的记录
err = db.QueryRow(`SELECT username FROM users WHERE id = 999`).Scan(&username)
if errors.Is(err, sql.ErrNoRows) {
fmt.Println("记录不存在")
} else if err != nil {
log.Fatal(err)
}
}
---
b.Query方法
a.功能说明
Query用于查询多行数据,返回*sql.Rows对象。必须遍历rows.Next()读取每一行,并在完成后调用rows.Close()释放资源。使用defer rows.Close()确保资源释放。
b.代码示例
---
// Query多行查询
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)`)
db.Exec(`INSERT INTO products VALUES (1, '笔记本', 5999), (2, '手机', 3999), (3, '平板', 2999)`)
fmt.Println("=== Query多行查询 ===\n")
rows, err := db.Query(`SELECT id, name, price FROM products WHERE price > ?`, 3000.0)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id int
var name string
var price float64
if err := rows.Scan(&id, &name, &price); err != nil {
log.Fatal(err)
}
fmt.Printf("ID:%d, 产品:%s, 价格:%.2f\n", id, name, price)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
}
---
2.6 更新数据
01.UPDATE语句
a.基本更新
a.功能说明
UPDATE语句修改表中的现有记录,使用SET子句指定新值,WHERE子句限定更新范围。不加WHERE会更新所有行,需谨慎使用。返回RowsAffected表示实际更新的行数。
b.代码示例
---
// UPDATE基础操作
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE inventory (id INTEGER PRIMARY KEY, name TEXT, stock INTEGER)`)
db.Exec(`INSERT INTO inventory VALUES (1, '笔记本', 50), (2, '手机', 100)`)
fmt.Println("=== UPDATE操作 ===\n")
// 单行更新
result, err := db.Exec(`UPDATE inventory SET stock = ? WHERE id = ?`, 45, 1)
if err != nil {
log.Fatal(err)
}
affected, _ := result.RowsAffected()
fmt.Printf("更新行数: %d\n", affected)
// 条件更新
result, _ = db.Exec(`UPDATE inventory SET stock = stock - 10 WHERE stock > 50`)
affected, _ = result.RowsAffected()
fmt.Printf("库存调整: %d 个产品\n", affected)
}
---
2.7 删除数据
01.DELETE语句
a.基本删除
a.功能说明
DELETE FROM语句从表中删除记录,WHERE子句指定删除条件。不加WHERE会删除所有行但保留表结构。SQLite的DELETE不会立即释放磁盘空间,需要VACUUM回收。
b.代码示例
---
// DELETE基础操作
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE logs (id INTEGER PRIMARY KEY, level TEXT, message TEXT, timestamp INTEGER)`)
db.Exec(`INSERT INTO logs (level, message, timestamp) VALUES ('INFO', 'msg1', 1000), ('DEBUG', 'msg2', 2000), ('ERROR', 'msg3', 3000)`)
fmt.Println("=== DELETE操作 ===\n")
// 条件删除
result, err := db.Exec(`DELETE FROM logs WHERE level = ?`, "DEBUG")
if err != nil {
log.Fatal(err)
}
affected, _ := result.RowsAffected()
fmt.Printf("删除DEBUG日志: %d 条\n", affected)
// 时间范围删除
result, _ = db.Exec(`DELETE FROM logs WHERE timestamp < ?`, 2500)
affected, _ = result.RowsAffected()
fmt.Printf("删除旧日志: %d 条\n", affected)
// 验证剩余
var count int
db.QueryRow(`SELECT COUNT(*) FROM logs`).Scan(&count)
fmt.Printf("剩余日志: %d 条\n", count)
}
---
02.TRUNCATE替代
a.DELETE全部数据
a.功能说明
SQLite不支持TRUNCATE语句,使用DELETE FROM table删除所有行。如果需要重置自增ID,需要DELETE后再DELETE FROM sqlite_sequence。或者直接DROP TABLE后重建表。
b.代码示例
---
// TRUNCATE模拟
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, value TEXT)`)
db.Exec(`INSERT INTO test (value) VALUES ('a'), ('b'), ('c')`)
// 清空数据
db.Exec(`DELETE FROM test`)
// 重置自增
db.Exec(`DELETE FROM sqlite_sequence WHERE name='test'`)
// 重新插入,ID从1开始
result, _ := db.Exec(`INSERT INTO test (value) VALUES ('x')`)
id, _ := result.LastInsertId()
fmt.Printf("重置后新ID: %d\n", id)
}
---
3 高级查询
3.1 条件查询
01.WHERE子句
a.比较运算符
a.功能说明
WHERE子句通过条件表达式过滤查询结果。支持=、!=、<、>、<=、>=等比较运算符,以及AND、OR、NOT逻辑运算符。使用参数绑定防止SQL注入,保证查询安全。
b.代码示例
---
// WHERE条件查询示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, salary REAL, dept TEXT)`)
db.Exec(`INSERT INTO employees VALUES (1, 'Alice', 25, 50000, 'IT'), (2, 'Bob', 30, 60000, 'Sales'), (3, 'Charlie', 35, 70000, 'IT')`)
fmt.Println("=== WHERE条件查询 ===\n")
// 等值查询
rows, _ := db.Query(`SELECT name, salary FROM employees WHERE dept = ?`, "IT")
defer rows.Close()
fmt.Println("IT部门员工:")
for rows.Next() {
var name string
var salary float64
rows.Scan(&name, &salary)
fmt.Printf(" %s: %.2f\n", name, salary)
}
// 范围查询
rows2, _ := db.Query(`SELECT name, age FROM employees WHERE age BETWEEN ? AND ?`, 25, 32)
defer rows2.Close()
fmt.Println("\n25-32岁员工:")
for rows2.Next() {
var name string
var age int
rows2.Scan(&name, &age)
fmt.Printf(" %s: %d岁\n", name, age)
}
// 复合条件
rows3, _ := db.Query(`SELECT name FROM employees WHERE dept = ? AND salary > ?`, "IT", 55000)
defer rows3.Close()
fmt.Println("\nIT部门高薪员工:")
for rows3.Next() {
var name string
rows3.Scan(&name)
fmt.Printf(" %s\n", name)
}
}
---
b.模糊查询
a.功能说明
LIKE运算符用于模糊匹配,%匹配任意字符,%name%匹配包含name的字符串。_匹配单个字符。GLOB使用Unix风格通配符,*和?。
b.代码示例
---
// LIKE模糊查询
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, sku TEXT)`)
db.Exec(`INSERT INTO products VALUES (1, 'iPhone 15', 'APL-IP15-BLK'), (2, 'iPad Air', 'APL-IPA-SLV'), (3, 'MacBook Pro', 'APL-MBP-GRY')`)
fmt.Println("=== LIKE模糊查询 ===\n")
// 前缀匹配
rows, _ := db.Query(`SELECT name FROM products WHERE name LIKE ?`, "iP%")
defer rows.Close()
fmt.Println("iP开头的产品:")
for rows.Next() {
var name string
rows.Scan(&name)
fmt.Printf(" %s\n", name)
}
// 包含匹配
rows2, _ := db.Query(`SELECT name FROM products WHERE name LIKE ?`, "%Book%")
defer rows2.Close()
fmt.Println("\n包含Book的产品:")
for rows2.Next() {
var name string
rows2.Scan(&name)
fmt.Printf(" %s\n", name)
}
// GLOB通配符
rows3, _ := db.Query(`SELECT sku FROM products WHERE sku GLOB ?`, "APL-IP*")
defer rows3.Close()
fmt.Println("\nAPL-IP*的SKU:")
for rows3.Next() {
var sku string
rows3.Scan(&sku)
fmt.Printf(" %s\n", sku)
}
}
---
02.IN和EXISTS
a.IN子句
a.功能说明
IN运算符检查值是否在指定列表中。可以使用值列表或子查询。NOT IN检查不在列表中的值。
b.代码示例
---
// IN和NOT IN查询
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, amount REAL)`)
db.Exec(`INSERT INTO orders VALUES (1, 101, 'pending', 100), (2, 102, 'shipped', 200), (3, 103, 'delivered', 150), (4, 101, 'cancelled', 50)`)
fmt.Println("=== IN查询 ===\n")
// IN值列表
rows, _ := db.Query(`SELECT id, status, amount FROM orders WHERE status IN (?, ?, ?)`, "pending", "shipped", "processing")
defer rows.Close()
fmt.Println("待处理订单:")
for rows.Next() {
var id int
var status string
var amount float64
rows.Scan(&id, &status, &amount)
fmt.Printf(" 订单%d: %s, %.2f元\n", id, status, amount)
}
// NOT IN
rows2, _ := db.Query(`SELECT id FROM orders WHERE status NOT IN (?, ?)`, "cancelled", "refunded")
defer rows2.Close()
fmt.Println("\n有效订单ID:")
for rows2.Next() {
var id int
rows2.Scan(&id)
fmt.Printf(" %d\n", id)
}
}
---
3.2 联表查询
01.基本概念
a.功能说明
JOIN操作用于合并多表数据。INNER JOIN返回匹配行,LEFT JOIN包含左表所有行,CROSS JOIN生成笛卡尔积。
b.代码示例
---
// JOIN示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)\`)
db.Exec(\`CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL)\`)
db.Exec(\`INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob')\`)
db.Exec(\`INSERT INTO orders VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)\`)
fmt.Println("=== JOIN查询 ===\\n")
rows, _ := db.Query(\`SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id\`)
defer rows.Close()
for rows.Next() {
var name string
var amount float64
rows.Scan(&name, &amount)
fmt.Printf("%s: %.2f元\\n", name, amount)
}
}
---
3.3 聚合函数
01.基本概念
a.功能说明
聚合函数对多行数据进行计算返回单个值。常用函数包括COUNT、SUM、AVG、MAX、MIN等。
b.代码示例
---
// 聚合函数示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE sales (id INTEGER PRIMARY KEY, product TEXT, quantity INTEGER, price REAL)\`)
db.Exec(\`INSERT INTO sales VALUES (1, 'A', 10, 100), (2, 'B', 5, 200), (3, 'A', 8, 100)\`)
fmt.Println("=== 聚合函数 ===\\n")
var count int
var totalQty int
var avgPrice, maxPrice float64
db.QueryRow(\`SELECT COUNT(*), SUM(quantity), AVG(price), MAX(price) FROM sales\`).Scan(&count, &totalQty, &avgPrice, &maxPrice)
fmt.Printf("记录数: %d\\n总数量: %d\\n平均价格: %.2f\\n最高价格: %.2f\\n", count, totalQty, avgPrice, maxPrice)
}
---
3.4 分组排序
01.基本概念
a.功能说明
GROUP BY对结果分组,HAVING过滤分组,ORDER BY排序结果。支持ASC升序和DESC降序,LIMIT限制返回行数。
b.代码示例
---
// GROUP BY/ORDER BY示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE transactions (id INTEGER PRIMARY KEY, category TEXT, amount REAL)\`)
db.Exec(\`INSERT INTO transactions VALUES (1, 'Food', 50), (2, 'Food', 80), (3, 'Travel', 200)\`)
fmt.Println("=== GROUP BY/ORDER BY ===\\n")
rows, _ := db.Query(\`SELECT category, SUM(amount) as total FROM transactions GROUP BY category ORDER BY total DESC\`)
defer rows.Close()
for rows.Next() {
var category string
var total float64
rows.Scan(&category, &total)
fmt.Printf("%s: %.2f元\\n", category, total)
}
}
---
3.5 子查询
01.基本概念
a.功能说明
子查询是嵌套在其他查询中的SELECT语句。可用于WHERE、FROM、SELECT子句中,支持标量和表子查询。
b.代码示例
---
// 子查询示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)\`)
db.Exec(\`INSERT INTO products VALUES (1, 'A', 100), (2, 'B', 200), (3, 'C', 150)\`)
fmt.Println("=== 子查询 ===\\n")
var avgPrice float64
db.QueryRow(\`SELECT AVG(price) FROM products\`).Scan(&avgPrice)
rows, _ := db.Query(\`SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products)\`)
defer rows.Close()
fmt.Printf("高于平均价(%.2f)的产品:\\n", avgPrice)
for rows.Next() {
var name string
var price float64
rows.Scan(&name, &price)
fmt.Printf(" %s: %.2f\\n", name, price)
}
}
---
3.6 索引优化
01.基本概念
a.功能说明
索引加速查询性能,CREATE INDEX创建索引。索引占用空间但显著提升WHERE、JOIN、ORDER BY性能。
b.代码示例
---
// 索引示例
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE large_table (id INTEGER PRIMARY KEY, code TEXT, value INTEGER)\`)
stmt, _ := db.Prepare(\`INSERT INTO large_table (code, value) VALUES (?, ?)\`)
for i := 0; i < 10000; i++ {
stmt.Exec(fmt.Sprintf("CODE%d", i), i)
}
stmt.Close()
fmt.Println("=== 索引性能 ===\\n")
// 无索引查询
start := time.Now()
db.QueryRow(\`SELECT value FROM large_table WHERE code = ?\`, "CODE5000").Scan(new(int))
fmt.Printf("无索引耗时: %v\\n", time.Since(start))
// 创建索引
db.Exec(\`CREATE INDEX idx_code ON large_table(code)\`)
// 有索引查询
start = time.Now()
db.QueryRow(\`SELECT value FROM large_table WHERE code = ?\`, "CODE5000").Scan(new(int))
fmt.Printf("有索引耗时: %v\\n", time.Since(start))
}
---
4 事务与并发
4.1 事务基础
01.核心概念
a.ACID特性
a.功能说明
事务通过Begin、Commit、Rollback保证ACID。db.Begin()开始事务,tx.Commit()提交,tx.Rollback()回滚。
b.代码示例
---
// 事务操作
package main
import ("database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance REAL)\`)
db.Exec(\`INSERT INTO accounts VALUES (1, 1000)\`)
tx, _ := db.Begin()
tx.Exec(\`UPDATE accounts SET balance = balance - 100 WHERE id = 1\`)
tx.Commit()
var balance float64
db.QueryRow(\`SELECT balance FROM accounts WHERE id = 1\`).Scan(&balance)
fmt.Printf("余额: %.2f\n", balance)
}
---
4.2 事务隔离级别
01.核心概念
a.隔离级别
a.功能说明
SQLite支持READ UNCOMMITTED和SERIALIZABLE隔离级别。通过PRAGMA read_uncommitted设置。
b.代码示例
---
// 隔离级别
package main
import ("database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`PRAGMA read_uncommitted = 1\`)
var ru int
db.QueryRow(\`PRAGMA read_uncommitted\`).Scan(&ru)
fmt.Printf("隔离级别: %d\n", ru)
}
---
4.3 并发控制
01.核心概念
a.并发模式
a.功能说明
WAL模式支持读写并发,DELETE模式读写互斥。多个读可并发,写操作串行化。
b.代码示例
---
// 并发测试
package main
import ("database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE test (id INTEGER)\`)
done := make(chan bool)
go func() { db.Exec(\`INSERT INTO test VALUES (1)\`); done <- true }()
go func() { db.Exec(\`INSERT INTO test VALUES (2)\`); done <- true }()
<-done; <-done
var count int
db.QueryRow(\`SELECT COUNT(*) FROM test\`).Scan(&count)
fmt.Printf("插入: %d条\n", count)
}
---
4.4 锁机制
01.核心概念
a.锁类型
a.功能说明
SQLite使用5级锁:UNLOCKED、SHARED、RESERVED、PENDING、EXCLUSIVE。文件级锁限制并发写。
b.代码示例
---
// 锁演示
package main
import ("database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE locks (id INTEGER)\`)
tx, _ := db.Begin()
tx.Exec(\`INSERT INTO locks VALUES (1)\`)
fmt.Println("事务持有RESERVED锁")
tx.Commit()
fmt.Println("锁已释放")
}
---
4.5 错误处理
01.核心概念
a.错误恢复
a.功能说明
SQLITE_BUSY错误表示数据库锁定,设置busy_timeout等待。事务失败需回滚避免部分提交。
b.代码示例
---
// 错误处理
package main
import ("database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE test (id INTEGER UNIQUE)\`)
tx, _ := db.Begin()
_, err := tx.Exec(\`INSERT INTO test VALUES (1), (1)\`)
if err != nil {
tx.Rollback()
fmt.Printf("错误已回滚: %v\n", err)
} else {
tx.Commit()
}
}
---
5 性能优化
5.1 连接池配置
01.优化技术
a.连接池
a.功能说明
SetMaxOpenConns(1)避免锁竞争,SetMaxIdleConns(1)保持连接,SetConnMaxLifetime控制生命周期。
b.代码示例
---
package main
import ("database/sql"; "fmt"; "time"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.SetMaxOpenConns(1)
db.SetMaxIdleConns(1)
db.SetConnMaxLifetime(time.Hour)
stats := db.Stats()
fmt.Printf("最大连接: %d\n", stats.MaxOpenConnections)
}
---
5.2 预编译语句
01.优化技术
a.预编译
a.功能说明
Prepare编译一次重复使用,批量操作提升性能。事务内使用预编译语句效果最佳。
b.代码示例
---
package main
import ("database/sql"; "fmt"; "time"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE test (value INTEGER)\`)
stmt, _ := db.Prepare(\`INSERT INTO test VALUES (?)\`)
defer stmt.Close()
for i := 0; i < 100; i++ { stmt.Exec(i) }
}
---
5.3 批量操作
01.优化技术
a.批量处理
a.功能说明
批量INSERT使用事务+预编译,每批1000-10000条。关闭synchronous提速。
b.代码示例
---
package main
import ("database/sql"; "fmt"; "time"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE bench (id INTEGER)\`)
tx, _ := db.Begin()
stmt, _ := tx.Prepare(\`INSERT INTO bench VALUES (?)\`)
for i := 0; i < 10000; i++ { stmt.Exec(i) }
stmt.Close()
tx.Commit()
fmt.Println("批量插入完成")
}
---
5.4 内存模式
01.优化技术
a.内存数据库
a.功能说明
:memory:创建内存数据库,速度极快但数据易失。适合临时数据和测试。
b.代码示例
---
package main
import ("database/sql"; "fmt"; "time"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE cache (key TEXT, val TEXT)\`)
db.Exec(\`INSERT INTO cache VALUES ('k1', 'v1')\`)
var val string
db.QueryRow(\`SELECT val FROM cache WHERE key='k1'\`).Scan(&val)
fmt.Printf("缓存值: %s\n", val)
}
---
5.5 WAL模式
01.优化技术
a.WAL日志
a.功能说明
PRAGMA journal_mode=WAL启用,支持读写并发,checkpoint合并数据。
b.代码示例
---
package main
import ("database/sql"; "fmt"; "time"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", "file::memory:?_journal_mode=WAL")
defer db.Close()
var mode string
db.QueryRow(\`PRAGMA journal_mode\`).Scan(&mode)
fmt.Printf("日志模式: %s\n", mode)
db.Exec(\`CREATE TABLE wal_test (id INTEGER)\`)
db.Exec(\`PRAGMA wal_checkpoint(FULL)\`)
fmt.Println("Checkpoint完成")
}
---
5.6 性能监控
01.优化技术
a.监控指标
a.功能说明
db.Stats()获取连接池统计,EXPLAIN QUERY PLAN分析查询,ANALYZE更新统计信息。
b.代码示例
---
package main
import ("database/sql"; "fmt"; "time"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(\`CREATE TABLE perf (id INTEGER PRIMARY KEY, val INTEGER)\`)
for i := 0; i < 1000; i++ { db.Exec(\`INSERT INTO perf VALUES (?, ?)\`, i, i*2) }
rows, _ := db.Query(\`EXPLAIN QUERY PLAN SELECT * FROM perf WHERE id > 500\`)
defer rows.Close()
fmt.Println("查询计划:")
for rows.Next() {
var detail string
rows.Scan(new(int), new(int), new(int), &detail)
fmt.Println(detail)
}
}
---
6 实战应用
6.1 安装配置
01.实践指南
a.依赖安装
a.功能说明
go get github.com/mattn/go-sqlite3安装驱动,需要CGO环境。modernc.org/sqlite纯Go无需CGO。
b.代码示例
---
package main
import ( "database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
db, err := sql.Open("sqlite3", "./app.db")
if err != nil { panic(err) }
defer db.Close()
if err := db.Ping(); err != nil { panic(err) }
fmt.Println("SQLite连接成功")
var version string
db.QueryRow("SELECT sqlite_version()").Scan(&version)
fmt.Printf("SQLite版本: %s\n", version)
}
---
02.最佳实践
a.生产建议
a.要点
使用WAL模式提升并发,设置合理的busy_timeout,启用外键约束,定期VACUUM优化。
6.2 ORM集成
01.实践指南
a.GORM
a.功能说明
GORM是Go流行ORM,支持SQLite。gorm.Open(sqlite.Open())连接,AutoMigrate自动迁移。
b.代码示例
---
package main
import ("gorm.io/driver/sqlite"; "gorm.io/gorm"; "database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
type User struct { ID uint; Name string }
func main() {
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})
if err != nil { panic(err) }
db.AutoMigrate(&User{})
db.Create(&User{Name: "Alice"})
var user User
db.First(&user, 1)
fmt.Printf("用户: %s\n", user.Name)
}
---
02.最佳实践
a.生产建议
a.要点
ORM提升开发效率但有性能开销,复杂查询使用原生SQL,注意N+1查询问题。
6.3 数据迁移
01.实践指南
a.版本管理
a.功能说明
golang-migrate管理数据库版本,支持up/down迁移。编写SQL文件实现schema演进。
b.代码示例
---
package main
import ( "database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", "./migrate.db")
defer db.Close()
_, err := db.Exec(\`CREATE TABLE IF NOT EXISTS schema_version (version INTEGER)\`)
if err == nil {
db.Exec(\`INSERT INTO schema_version VALUES (1)\`)
fmt.Println("迁移v1完成")
}
}
---
02.最佳实践
a.生产建议
a.要点
版本化管理schema变更,编写可逆迁移,测试迁移脚本,备份后再执行生产迁移。
6.4 备份恢复
01.实践指南
a.备份策略
a.功能说明
使用VACUUM INTO导出,文件复制简单备份。在线备份使用SQLite Backup API。
b.代码示例
---
package main
import ( "database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
srcDB, _ := sql.Open("sqlite3", "./source.db")
srcDB.Exec(\`CREATE TABLE data (id INTEGER, val TEXT)\`)
srcDB.Exec(\`INSERT INTO data VALUES (1, 'test')\`)
srcDB.Exec(\`VACUUM INTO 'backup.db'\`)
srcDB.Close()
fmt.Println("备份到backup.db")
}
---
02.最佳实践
a.生产建议
a.要点
定期自动备份,验证备份完整性,使用PRAGMA integrity_check,异地存储备份文件。
6.5 嵌入式应用
01.实践指南
a.嵌入式
a.功能说明
嵌入式设备使用SQLite存储配置和日志,关闭同步模式减少写入,定期清理旧数据。
b.代码示例
---
package main
import ( "database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", "./embedded.db?_synchronous=OFF")
defer db.Close()
db.Exec(\`CREATE TABLE IF NOT EXISTS sensor_data (ts INTEGER, temp REAL)\`)
db.Exec(\`INSERT INTO sensor_data VALUES (?, ?)\`, 1234567890, 25.5)
db.Exec(\`DELETE FROM sensor_data WHERE ts < ?\`, 1234560000)
fmt.Println("嵌入式数据库操作完成")
}
---
02.最佳实践
a.生产建议
a.要点
限制数据库大小,使用内存模式临时数据,批量写入减少磨损,错误处理完善。
6.6 常见问题
01.实践指南
a.FAQ
a.功能说明
database locked错误增加busy_timeout,UNIQUE约束使用INSERT OR IGNORE,类型转换注意动态类型系统。
b.代码示例
---
package main
import ( "database/sql"; "fmt"; _ "github.com/mattn/go-sqlite3")
func main() {
db, _ := sql.Open("sqlite3", "./faq.db?_busy_timeout=5000")
defer db.Close()
db.Exec(\`CREATE TABLE test (id INTEGER UNIQUE)\`)
db.Exec(\`INSERT OR IGNORE INTO test VALUES (1)\`)
db.Exec(\`INSERT OR IGNORE INTO test VALUES (1)\`)
var count int
db.QueryRow(\`SELECT COUNT(*) FROM test\`).Scan(&count)
fmt.Printf("去重后记录数: %d\n", count)
}
---
02.最佳实践
a.生产建议
a.要点
监控数据库大小,记录慢查询,使用索引优化,避免SELECT *,参数化查询防注入。