535 lines
10 KiB
Markdown
535 lines
10 KiB
Markdown
# StarDB
|
||
|
||
一个轻量级的 Go 数据库封装库，个人学习用，提供简洁的 API 和 ORM 功能。
|
||
|
||
[](https://golang.org/)
|
||
[](LICENSE)
|
||
|
||
## ✨ 特性
|
||
|
||
- ✅ **零第三方依赖** - 仅使用 Go 标准库
|
||
- ✅ **类型安全** - 提供类型安全的结果转换方法
|
||
- ✅ **简单 ORM** - 支持结构体与数据库的自动映射
|
||
- ✅ **Context 支持** - 所有操作都支持 context
|
||
- ✅ **事务支持** - 完整的事务操作支持
|
||
- ✅ **预编译语句** - 支持预编译语句以提升性能
|
||
- ✅ **批量操作** - 高效的批量插入功能
|
||
- ✅ **连接池管理** - 便捷的连接池配置
|
||
- ✅ **查询构建器** - 链式调用构建 SQL 查询
|
||
|
||
## 📦 安装
|
||
|
||
```bash
|
||
go get b612.me/stardb
|
||
```
|
||
|
||
## 🚀 快速开始
|
||
|
||
### 基本使用
|
||
|
||
```go
|
||
package main
|
||
|
||
import (
|
||
"b612.me/stardb"
|
||
_ "github.com/mattn/go-sqlite3"
|
||
)
|
||
|
||
func main() {
|
||
// 创建数据库实例
|
||
db := stardb.NewStarDB()
|
||
err := db.Open("sqlite3", "test.db")
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
defer db.Close()
|
||
|
||
// 执行查询
|
||
rows, err := db.Query("SELECT * FROM users WHERE age > ?", 18)
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
// 遍历结果
|
||
for i := 0; i < rows.Length(); i++ {
|
||
row := rows.Row(i)
|
||
name := row.MustString("name")
|
||
age := row.MustInt("age")
|
||
println(name, age)
|
||
}
|
||
}
|
||
```
|
||
|
||
### ORM 使用
|
||
|
||
```go
|
||
package main
|
||
|
||
import (
|
||
"b612.me/stardb"
|
||
"time"
|
||
_ "github.com/mattn/go-sqlite3"
|
||
)
|
||
|
||
// 定义结构体
|
||
type User struct {
|
||
ID int64 `db:"id"`
|
||
Name string `db:"name"`
|
||
Email string `db:"email"`
|
||
Age int `db:"age"`
|
||
Active bool `db:"active"`
|
||
CreatedAt time.Time `db:"created_at"`
|
||
}
|
||
|
||
func main() {
|
||
db := stardb.NewStarDB()
|
||
db.Open("sqlite3", "test.db")
|
||
defer db.Close()
|
||
|
||
// 查询单个对象
|
||
rows, _ := db.Query("SELECT * FROM users WHERE id = ?", 1)
|
||
defer rows.Close()
|
||
|
||
var user User
|
||
rows.Orm(&user)
|
||
println(user.Name)
|
||
|
||
// 查询多个对象
|
||
rows2, _ := db.Query("SELECT * FROM users WHERE age > ?", 18)
|
||
defer rows2.Close()
|
||
|
||
var users []User
|
||
rows2.Orm(&users)
|
||
|
||
for _, u := range users {
|
||
println(u.Name, u.Age)
|
||
}
|
||
}
|
||
```
|
||
|
||
### 插入和更新
|
||
|
||
```go
|
||
// 插入数据
|
||
user := User{
|
||
Name: "Alice",
|
||
Email: "alice@example.com",
|
||
Age: 25,
|
||
Active: true,
|
||
CreatedAt: time.Now(),
|
||
}
|
||
|
||
result, err := db.Insert(&user, "users", "id") // "id" 是自增字段
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
|
||
lastID, _ := result.LastInsertId()
|
||
println("插入的 ID:", lastID)
|
||
|
||
// 更新数据
|
||
user.Age = 26
|
||
result, err = db.Update(&user, "users", "id") // "id" 是主键
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
|
||
affected, _ := result.RowsAffected()
|
||
println("更新的行数:", affected)
|
||
```
|
||
|
||
### 批量插入
|
||
|
||
```go
|
||
// 方式 1：使用原始数据
|
||
columns := []string{"name", "email", "age"}
|
||
values := [][]interface{}{
|
||
{"Alice", "alice@example.com", 25},
|
||
{"Bob", "bob@example.com", 30},
|
||
{"Charlie", "charlie@example.com", 35},
|
||
}
|
||
|
||
result, err := db.BatchInsert("users", columns, values)
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
|
||
// 方式 2：使用结构体
|
||
users := []User{
|
||
{Name: "Alice", Email: "alice@example.com", Age: 25, CreatedAt: time.Now()},
|
||
{Name: "Bob", Email: "bob@example.com", Age: 30, CreatedAt: time.Now()},
|
||
{Name: "Charlie", Email: "charlie@example.com", Age: 35, CreatedAt: time.Now()},
|
||
}
|
||
|
||
result, err = db.BatchInsertStructs("users", users, "id")
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
|
||
affected, _ := result.RowsAffected()
|
||
println("批量插入行数:", affected)
|
||
```
|
||
|
||
### 事务操作
|
||
|
||
```go
|
||
// 开始事务
|
||
tx, err := db.Begin()
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
|
||
// 执行操作
|
||
_, err = tx.Exec("INSERT INTO users (name, email) VALUES (?, ?)", "Alice", "alice@example.com")
|
||
if err != nil {
|
||
tx.Rollback()
|
||
panic(err)
|
||
}
|
||
|
||
_, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE user_id = ?", 100, 1)
|
||
if err != nil {
|
||
tx.Rollback()
|
||
panic(err)
|
||
}
|
||
|
||
// 提交事务
|
||
err = tx.Commit()
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
```
|
||
|
||
### 预编译语句
|
||
|
||
```go
|
||
// 创建预编译语句
|
||
stmt, err := db.Prepare("SELECT * FROM users WHERE age > ?")
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
defer stmt.Close()
|
||
|
||
// 多次执行
|
||
rows1, _ := stmt.Query(18)
|
||
defer rows1.Close()
|
||
|
||
rows2, _ := stmt.Query(25)
|
||
defer rows2.Close()
|
||
|
||
rows3, _ := stmt.Query(30)
|
||
defer rows3.Close()
|
||
```
|
||
|
||
### 查询构建器
|
||
|
||
```go
|
||
// 使用查询构建器
|
||
rows, err := stardb.NewQueryBuilder("users").
|
||
Select("id", "name", "email").
|
||
Where("age > ?", 18).
|
||
Where("active = ?", true).
|
||
OrderBy("name ASC").
|
||
Limit(10).
|
||
Offset(0).
|
||
Query(db)
|
||
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
var users []User
|
||
rows.Orm(&users)
|
||
```
|
||
|
||
### 连接池配置
|
||
|
||
```go
|
||
// 方式 1：使用默认配置
|
||
db, err := stardb.OpenWithPool("sqlite3", "test.db", nil)
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
defer db.Close()
|
||
|
||
// 方式 2：自定义配置
|
||
config := &stardb.PoolConfig{
|
||
MaxOpenConns: 50, // 最大打开连接数
|
||
MaxIdleConns: 10, // 最大空闲连接数
|
||
ConnMaxLifetime: 1 * time.Hour, // 连接最大生命周期
|
||
ConnMaxIdleTime: 10 * time.Minute, // 连接最大空闲时间
|
||
}
|
||
|
||
db, err = stardb.OpenWithPool("mysql", "user:pass@tcp(localhost:3306)/dbname", config)
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
defer db.Close()
|
||
|
||
// 方式 3：手动设置
|
||
db := stardb.NewStarDB()
|
||
db.Open("postgres", "postgres://user:pass@localhost/dbname")
|
||
db.SetPoolConfig(config)
|
||
```
|
||
|
||
### 命名参数绑定
|
||
|
||
```go
|
||
user := User{
|
||
Name: "Alice",
|
||
Age: 25,
|
||
}
|
||
|
||
// 使用 :fieldname 语法绑定结构体字段
|
||
rows, err := db.QueryX(&user,
|
||
"SELECT * FROM users WHERE name = ? AND age > ?",
|
||
":name", ":age")
|
||
if err != nil {
|
||
panic(err)
|
||
}
|
||
defer rows.Close()
|
||
```
|
||
|
||
## 📖 详细文档
|
||
|
||
### 结果转换方法
|
||
|
||
StarDB 提供了丰富的类型转换方法：
|
||
|
||
```go
|
||
row := rows.Row(0)
|
||
|
||
// 字符串
|
||
name := row.MustString("name")
|
||
|
||
// 整数
|
||
age := row.MustInt("age")
|
||
id := row.MustInt64("id")
|
||
count := row.MustInt32("count")
|
||
uid := row.MustUint64("uid")
|
||
|
||
// 浮点数
|
||
price := row.MustFloat64("price")
|
||
rate := row.MustFloat32("rate")
|
||
|
||
// 布尔值
|
||
active := row.MustBool("active")
|
||
|
||
// 字节数组
|
||
data := row.MustBytes("data")
|
||
|
||
// 时间
|
||
createdAt := row.MustDate("created_at", "2006-01-02 15:04:05")
|
||
|
||
// 检查 NULL
|
||
isNull := row.IsNil("optional_field")
|
||
```
|
||
|
||
### 列操作
|
||
|
||
```go
|
||
// 获取某一列的所有值
|
||
col := rows.Col("name")
|
||
|
||
names := col.MustString() // []string
|
||
ages := col.MustInt() // []int
|
||
prices := col.MustFloat64() // []float64
|
||
actives := col.MustBool() // []bool
|
||
```
|
||
|
||
### Context 支持
|
||
|
||
所有操作都有对应的 Context 版本：
|
||
|
||
```go
|
||
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
|
||
defer cancel()
|
||
|
||
// 查询
|
||
rows, err := db.QueryContext(ctx, "SELECT * FROM users")
|
||
|
||
// 执行
|
||
result, err := db.ExecContext(ctx, "UPDATE users SET age = ?", 26)
|
||
|
||
// 事务
|
||
tx, err := db.BeginTx(ctx, nil)
|
||
|
||
// 批量插入
|
||
result, err := db.BatchInsertContext(ctx, "users", columns, values)
|
||
```
|
||
|
||
### 错误处理
|
||
|
||
```go
|
||
// 使用 Must* 方法（忽略错误，返回零值）
|
||
name := row.MustString("name")
|
||
|
||
// 使用 Get* 方法（返回错误）
|
||
name, err := row.GetString("name")
|
||
if err != nil {
|
||
// 处理错误
|
||
}
|
||
```
|
||
|
||
## 🔧 高级用法
|
||
|
||
### 嵌套结构体
|
||
|
||
```go
|
||
type Profile struct {
|
||
Bio string `db:"bio"`
|
||
Avatar string `db:"avatar"`
|
||
}
|
||
|
||
type User struct {
|
||
ID int64 `db:"id"`
|
||
Name string `db:"name"`
|
||
Profile Profile `db:"---"` // 使用 "---" 标记嵌套结构体
|
||
}
|
||
|
||
rows, _ := db.Query("SELECT id, name, bio, avatar FROM users")
|
||
defer rows.Close()
|
||
|
||
var user User
|
||
rows.Orm(&user)
|
||
|
||
println(user.Name)
|
||
println(user.Profile.Bio)
|
||
```
|
||
|
||
### 手动扫描模式
|
||
|
||
```go
|
||
db := stardb.NewStarDB()
|
||
db.ManualScan = true // 启用手动扫描模式
|
||
db.Open("sqlite3", "test.db")
|
||
|
||
rows, _ := db.Query("SELECT * FROM users")
|
||
defer rows.Close()
|
||
|
||
// 手动触发解析
|
||
rows.Rescan()
|
||
|
||
// 现在可以访问数据
|
||
println(rows.Length())
|
||
```
|
||
|
||
### 直接访问底层 *sql.DB
|
||
|
||
```go
|
||
db := stardb.NewStarDB()
|
||
db.Open("sqlite3", "test.db")
|
||
|
||
// 获取底层 *sql.DB
|
||
rawDB := db.DB()
|
||
rawDB.SetMaxOpenConns(100)
|
||
|
||
// 或者使用已有的 *sql.DB
|
||
sqlDB, _ := sql.Open("sqlite3", "test.db")
|
||
db := stardb.NewStarDBWithDB(sqlDB)
|
||
```
|
||
|
||
## 🎯 性能优化建议
|
||
|
||
### 1. 使用预编译语句
|
||
|
||
对于重复执行的查询，使用预编译语句可以提升 20-50% 的性能：
|
||
|
||
```go
|
||
stmt, _ := db.Prepare("SELECT * FROM users WHERE id = ?")
|
||
defer stmt.Close()
|
||
|
||
for _, id := range userIDs {
|
||
rows, _ := stmt.Query(id)
|
||
// 处理结果
|
||
rows.Close()
|
||
}
|
||
```
|
||
|
||
### 2. 批量操作
|
||
|
||
批量插入比单条插入快 2-3 倍：
|
||
|
||
```go
|
||
// ❌ 慢
|
||
for _, user := range users {
|
||
db.Exec("INSERT INTO users (name) VALUES (?)", user.Name)
|
||
}
|
||
|
||
// ✅ 快
|
||
db.BatchInsertStructs("users", users, "id")
|
||
```
|
||
|
||
### 3. 合理配置连接池
|
||
|
||
```go
|
||
config := &stardb.PoolConfig{
|
||
MaxOpenConns: 25, // 根据数据库服务器调整
|
||
MaxIdleConns: 5, // 保持少量空闲连接
|
||
ConnMaxLifetime: 1 * time.Hour,
|
||
ConnMaxIdleTime: 10 * time.Minute,
|
||
}
|
||
db.SetPoolConfig(config)
|
||
```
|
||
|
||
### 4. 使用事务
|
||
|
||
将多个操作放在一个事务中可以显著提升性能：
|
||
|
||
```go
|
||
tx, _ := db.Begin()
|
||
for _, user := range users {
|
||
tx.Exec("INSERT INTO users (name) VALUES (?)", user.Name)
|
||
}
|
||
tx.Commit()
|
||
```
|
||
|
||
## 🧪 测试
|
||
|
||
项目包含完整的单元测试：
|
||
|
||
```bash
|
||
# 运行所有测试
|
||
cd testing
|
||
go test -v
|
||
|
||
# 运行特定测试
|
||
go test -v -run TestStarDB_Query
|
||
|
||
# 查看覆盖率
|
||
go test -cover
|
||
|
||
# 生成覆盖率报告
|
||
go test -coverprofile=coverage.out
|
||
go tool cover -html=coverage.out
|
||
|
||
# 运行基准测试
|
||
go test -bench=. -benchmem
|
||
```
|
||
|
||
## 📊 支持的数据库
|
||
|
||
StarDB 支持所有实现了 `database/sql` 接口的数据库驱动：
|
||
|
||
| 数据库 | 驱动 | 导入 |
|
||
|--------|------|------|
|
||
| SQLite | modernc.org/sqlite | `_ "modernc.org/sqlite"` |
|
||
| MySQL | github.com/go-sql-driver/mysql | `_ "github.com/go-sql-driver/mysql"` |
|
||
| PostgreSQL | github.com/lib/pq | `_ "github.com/lib/pq"` |
|
||
| SQL Server | github.com/denisenkom/go-mssqldb | `_ "github.com/denisenkom/go-mssqldb"` |
|
||
| Oracle | github.com/sijms/go-ora/v2 | `_ "github.com/sijms/go-ora/v2"` |
|
||
|
||
|
||
## 📄 许可证
|
||
|
||
本项目采用 Apache 2.0 许可证 - 详见 [LICENSE](LICENSE) 文件
|
||
|
||
## 🙏 致谢
|
||
|
||
- 感谢 Go 标准库提供的 `database/sql` 包
|
||
- 灵感来源于 xorm、gorm 等优秀的 ORM 框架
|
||
|
||
## 📮 联系方式
|
||
|
||
- 项目主页: https://git.b612.me/b612/stardb.git |