Go语言SQLite轻量级数据库应用

Go语言SQLite轻量级数据库应用引言SQLite是一款轻量级的嵌入式数据库无需独立服务进程非常适合单机应用、移动端应用和开发测试环境。Go语言通过database/sql包配合go-sqlite3驱动可以方便地操作SQLite数据库。本文将深入探讨Go语言中SQLite的使用技巧和最佳实践。一、环境配置与连接1.1 安装依赖go get github.com/mattn/go-sqlite31.2 基本连接配置package main import ( database/sql fmt log _ github.com/mattn/go-sqlite3 ) func main() { // 连接SQLite数据库 // 文件不存在时会自动创建 db, err : sql.Open(sqlite3, ./example.db) if err ! nil { log.Fatalf(Failed to open database: %v, err) } defer db.Close() // 验证连接 if err : db.Ping(); err ! nil { log.Fatalf(Failed to ping database: %v, err) } fmt.Println(Successfully connected to SQLite database) }二、数据库初始化2.1 创建表结构func InitializeDatabase(db *sql.DB) error { createUsersTable : CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, age INTEGER DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); _, err : db.Exec(createUsersTable) if err ! nil { return err } createPostsTable : CREATE TABLE IF NOT EXISTS posts ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, title TEXT NOT NULL, content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ); _, err db.Exec(createPostsTable) if err ! nil { return err } // 创建索引 _, err db.Exec(CREATE INDEX IF NOT EXISTS idx_posts_user_id ON posts(user_id);) return err }三、CRUD操作3.1 插入数据type User struct { ID int Name string Email string Age int CreatedAt string } func InsertUser(db *sql.DB, name, email string, age int) (int64, error) { query : INSERT INTO users (name, email, age) VALUES (?, ?, ?) result, err : db.Exec(query, name, email, age) if err ! nil { return 0, err } id, err : result.LastInsertId() if err ! nil { return 0, err } return id, nil }3.2 查询数据func GetUserByID(db *sql.DB, id int) (*User, error) { query : SELECT id, name, email, age, created_at FROM users WHERE id ? var user User err : db.QueryRow(query, id).Scan(user.ID, user.Name, user.Email, user.Age, user.CreatedAt) if err ! nil { if err sql.ErrNoRows { return nil, nil } return nil, err } return user, nil } func GetUsersByAge(db *sql.DB, minAge int) ([]*User, error) { query : SELECT id, name, email, age, created_at FROM users WHERE age ? ORDER BY age DESC rows, err : db.Query(query, minAge) if err ! nil { return nil, err } defer rows.Close() var users []*User for rows.Next() { var user User if err : rows.Scan(user.ID, user.Name, user.Email, user.Age, user.CreatedAt); err ! nil { return nil, err } users append(users, user) } return users, nil }3.3 更新数据func UpdateUser(db *sql.DB, id int, name, email string) (int64, error) { query : UPDATE users SET name ?, email ? WHERE id ? result, err : db.Exec(query, name, email, id) if err ! nil { return 0, err } rowsAffected, err : result.RowsAffected() if err ! nil { return 0, err } return rowsAffected, nil }3.4 删除数据func DeleteUser(db *sql.DB, id int) (int64, error) { query : DELETE FROM users WHERE id ? result, err : db.Exec(query, id) if err ! nil { return 0, err } rowsAffected, err : result.RowsAffected() if err ! nil { return 0, err } return rowsAffected, nil }四、事务处理func TransferPoints(db *sql.DB, fromID, toID int, points int) error { tx, err : db.Begin() if err ! nil { return err } defer tx.Rollback() // 扣除积分 _, err tx.Exec(UPDATE users SET points points - ? WHERE id ?, points, fromID) if err ! nil { return err } // 增加积分 _, err tx.Exec(UPDATE users SET points points ? WHERE id ?, points, toID) if err ! nil { return err } // 记录日志 _, err tx.Exec(INSERT INTO transactions (from_id, to_id, points) VALUES (?, ?, ?), fromID, toID, points) if err ! nil { return err } return tx.Commit() }五、高级查询5.1 连接查询func GetUserPosts(db *sql.DB, userID int) ([]map[string]interface{}, error) { query : SELECT u.name, p.title, p.content, p.created_at FROM users u JOIN posts p ON u.id p.user_id WHERE u.id ? ORDER BY p.created_at DESC rows, err : db.Query(query, userID) if err ! nil { return nil, err } defer rows.Close() var results []map[string]interface{} for rows.Next() { var name, title, content, createdAt string if err : rows.Scan(name, title, content, createdAt); err ! nil { return nil, err } results append(results, map[string]interface{}{ user_name: name, post_title: title, content: content, created_at: createdAt, }) } return results, nil }5.2 聚合查询func GetUserStats(db *sql.DB) ([]map[string]interface{}, error) { query : SELECT COUNT(*) as total_users, AVG(age) as avg_age, MIN(age) as min_age, MAX(age) as max_age FROM users rows, err : db.Query(query) if err ! nil { return nil, err } defer rows.Close() var results []map[string]interface{} for rows.Next() { var totalUsers, minAge, maxAge int var avgAge float64 if err : rows.Scan(totalUsers, avgAge, minAge, maxAge); err ! nil { return nil, err } results append(results, map[string]interface{}{ total_users: totalUsers, avg_age: avgAge, min_age: minAge, max_age: maxAge, }) } return results, nil }六、批量操作func BatchInsertUsers(db *sql.DB, users []*User) error { tx, err : db.Begin() if err ! nil { return err } defer tx.Rollback() stmt, err : tx.Prepare(INSERT INTO users (name, email, age) VALUES (?, ?, ?)) if err ! nil { return err } defer stmt.Close() for _, user : range users { _, err : stmt.Exec(user.Name, user.Email, user.Age) if err ! nil { return err } } return tx.Commit() }七、数据库备份与恢复7.1 备份数据库func BackupDatabase(db *sql.DB, backupPath string) error { query : fmt.Sprintf(BACKUP TO %s, backupPath) _, err : db.Exec(query) return err }7.2 恢复数据库func RestoreDatabase(backupPath string) (*sql.DB, error) { db, err : sql.Open(sqlite3, backupPath) if err ! nil { return nil, err } if err : db.Ping(); err ! nil { db.Close() return nil, err } return db, nil }八、性能优化8.1 连接池配置func ConfigureConnectionPool(db *sql.DB) { db.SetMaxOpenConns(1) // SQLite是文件数据库单连接足够 db.SetMaxIdleConns(1) db.SetConnMaxLifetime(0) }8.2 预编译语句func PrepareStatements(db *sql.DB) (*sql.Stmt, *sql.Stmt, error) { insertStmt, err : db.Prepare(INSERT INTO users (name, email) VALUES (?, ?)) if err ! nil { return nil, nil, err } queryStmt, err : db.Prepare(SELECT * FROM users WHERE id ?) if err ! nil { insertStmt.Close() return nil, nil, err } return insertStmt, queryStmt, nil }结语SQLite作为轻量级数据库与Go语言的结合非常适合开发单机应用、测试环境和嵌入式系统。通过合理使用事务、预编译语句和索引可以构建高效、可靠的SQLite应用。希望本文的实践经验能帮助你更好地使用Go语言与SQLite进行开发。