spellsql 高效能sql拼接器和輕量級orm

1. 介紹

• 透過 sync.Pool, strings.Builder 等實現的高效能 sql 拼接工具
• 具有: 可控列印 sql 最終的 log, 非法字元自動轉義, 支援格式化 sql等
• 支援輕量級 orm, 效能方面接近原生(即: database/sql)
• 安裝:

go get -u gitee.com/xuesongtao/spellsql

2. 佔位符

• 目前支援佔位符 ?, ?d, ?v, 說明如下:

2.1 佔位符 ?

• 直接根據 args 中型別來自動推動 arg 的型別, 使用如下:

1. 第一種用法: 根據 args 中型別來自動推動 arg 的型別

如: NewCacheSql("SELECT username, password FROM sys_user WHERE username = ? AND password = ?", "test", 123).GetSqlStr()
=> SELECT username, password FROM sys_user WHERE username = "test" AND password = 123

2. 第二種用法: 當 arg 為 []int, 暫時支援 []int, []int32, []int64

如: NewCacheSql("SELECT username, password FROM sys_user WHERE id IN (?)", []int{1, 2, 3}).GetSqlStr()
=> SELECT username, password FROM sys_user WHERE id IN (1,2,3)

2.2 佔位符 ?d

• 只會把數字型的字串轉為數字型, 如果是字母的話會被轉義為 0, 如: "123" => 123; []string{"1", "2", "3"} => 1,2,3, 如下:
  第一種用法: 當 arg 為字串時, 又想不加雙引號就用這個

如: NewCacheSql("SELECT username, password FROM sys_user WHERE id = ?d", "123").GetSqlStr()
=> SELECT username, password FROM sys_user WHERE id = 123

第二種用法: 當 arg 為 []string, 又想把解析後的單個元素不加引號

如: NewCacheSql("SELECT username, password FROM sys_user WHERE id IN (?d)", []string{"1", "2", "3"}).GetSqlStr()
=> SELECT username, password FROM sys_user WHERE id IN (1,2,3)

2.3 佔位符為: ?v

• 這樣會讓字串型別不加引號, 原樣輸出, 如: “test” => test;
  第一種用法: 當 arg 為字串時, 又想不加雙引號就用這個, 注: 只支援 arg 為字串型別

如: NewCacheSql("SELECT username, password FROM ?v WHERE id = ?d", "sys_user", "123").GetSqlStr()
=> SELECT username, password FROM sys_user WHERE id = 123

第二種用法: 子查詢

如: NewCacheSql("SELECT u.username, u.password FROM sys_user su LEFT JOIN user u ON su.id = u.id WHERE u.id IN (?v)", FmtSqlStr("SELECT id FROM user WHERE name=?", "test").GetSqlStr()
=> SELECT u.username, u.password FROM sys_user su LEFT JOIN user u ON su.id = u.id WHERE u.id IN (SELECT id FROM user WHERE name="test");

• 注: 由於這種不會進行轉義處理, 所有這種不推薦用於請求輸入(外部非法輸入)的內容, 會出現 SQL 注入風險; 當我們明確知道引數是幹什麼的可以使用會簡化我們程式碼, 這裡就不進行演示.

3. spellsql 使用

• 可以參考 getsqlstr_test.go 裡的測試方法

  3.1 新增

s := NewCacheSql("INSERT INTO sys_user (username, password, name)")
s.SetInsertValues("xuesongtao", "123456", "阿桃")
s.SetInsertValues("xuesongtao", "123456", "阿桃")
s.GetSqlStr()

// Output:
// INSERT INTO sys_user (username, password, name) VALUES ("test", 123456, "阿濤"), ("xuesongtao", "123456", "阿桃"), ("xuesongtao", "123456", "阿桃");

3.2 刪除

s := NewCacheSql("DELETE FROM sys_user WHERE id = ?", 123)
if true {
    s.SetWhere("name", "test")
}
s.GetSqlStr()
// Output:
// DELETE FROM sys_user WHERE id = 123 AND name = "test";

3.3 查詢

s := NewCacheSql("SELECT * FROM user u LEFT JOIN role r ON u.id = r.user_id")
s.SetOrWhere("u.name", "xue")
s.SetOrWhereArgs("(r.id IN (?d))", []string{"1", "2"})
s.SetWhere("u.age", ">", 20)
s.SetWhereArgs("u.addr = ?", "南部")
s.GetTotalSqlStr()
s.SetLimit(1, 10)
s.GetSqlStr()

// Output:
// sqlTotalStr: SELECT COUNT(*) FROM user u LEFT JOIN role r ON u.id = r.user_id WHERE u.name = "xue" OR (r.id IN (1,2)) AND u.age > 20 AND u.addr = "南部";
// sqlStr: SELECT * FROM user u LEFT JOIN role r ON u.id = r.user_id WHERE u.name = "xue" OR (r.id IN (1,2)) AND u.age > 20 AND u.addr = "南部" LIMIT 0, 10;

3.4 修改

s := NewCacheSql("UPDATE sys_user SET")
idsStr := []string{"1", "2", "3", "4", "5"}
s.SetUpdateValue("name", "xue")
s.SetUpdateValueArgs("age = ?, score = ?", 18, 90.5)
s.SetWhereArgs("id IN (?d) AND name = ?", idsStr, "tao")
s.GetSqlStr()

// Output:
// UPDATE sys_user SET name = "xue", age = 18, score = 90.50 WHERE id IN (1,2,3,4,5) AND name = "tao";

3.5 追加

s := NewCacheSql("INSERT INTO sys_user (username, password, age)")
s.SetInsertValuesArgs("?, ?, ?d", "xuesongtao", "123", "20")
s.Append("ON DUPLICATE KEY UPDATE username=VALUES(username)")
s.GetSqlStr()

// Output:
// INSERT INTO sys_user (username, password, age) VALUES ("xuesongtao", "123", 20) ON DUPLICATE KEY UPDATE username=VALUES(username);

3.6 複用

• 1. NewCacheSql() 獲取的物件在呼叫 GetSqlStr() 後會重置並放入記憶體池, 是不能對結果進行再進行 GetSqlStr(), 當然你是可以對結果作為 NewCacheSql() 的入參進行使用以此達到複用, 這樣程式碼看起來不是多優雅, 分頁處理案例如下:

sqlObj := NewCacheSql("SELECT * FROM user_info WHERE status = 1")
handleFn := func(obj *SqlStrObj, page, size int32) {
    // 業務程式碼
    fmt.Println(obj.SetLimit(page, size).SetPrintLog(false).GetSqlStr())
}

// 每次同步大小
var (
    totalNum int32 = 30
    page int32 = 1
    size int32 = 10
    totalPage int32 = int32(math.Ceil(float64(totalNum / size)))
)

sqlStr := sqlObj.SetPrintLog(false).GetSqlStr("", "")
for page <= totalPage {
    handleFn(NewCacheSql(sqlStr), page, size)
    page++
}

// Output:
// SELECT * FROM user_info WHERE u_status = 1 LIMIT 0, 10;
// SELECT * FROM user_info WHERE u_status = 1 LIMIT 10, 10;
// SELECT * FROM user_info WHERE u_status = 1 LIMIT 20, 10;

• NewSql() 的產生的物件不會放入記憶體池, 可以進行多次呼叫 GetSqlStr(), 對應上面的示例可以使用 NewSql() 再呼叫 Clone() 進行處理, 如下:

sqlObj := NewSql("SELECT u_name, phone, account_id FROM user_info WHERE u_status = 1")
handleFn := func(obj *SqlStrObj, page, size int32) {
    // 業務程式碼
    fmt.Println(obj.SetLimit(page, size).SetPrintLog(false).GetSqlStr())
}

// 每次同步大小
var (
    totalNum int32 = 30
    page int32 = 1
    size int32 = 10
    totalPage int32 = int32(math.Ceil(float64(totalNum / size)))
)

for page <= totalPage {
    handleFn(sqlObj.Clone(), page, size)
    page++
}

// Output:
// SELECT * FROM user_info WHERE u_status = 1 LIMIT 0, 10;
// SELECT * FROM user_info WHERE u_status = 1 LIMIT 10, 10;
// SELECT * FROM user_info WHERE u_status = 1 LIMIT 20, 10;

4 orm 功能介紹

• spellsql_orm 能夠高效的處理單表 CURD. 在查詢方面的效能接近原生, 其中做幾個效能對比: 原生 >= spellsql_orm > gorm (orm_test.go 裡有測試資料), 可以在 dev 分支上測試
• 支援自定義 tag, 預設 json

type Man struct {
    Id int32 `json:"id,omitempty"`
    Name string `json:"name,omitempty"`
    Age int32 `json:"age,omitempty"`
    Addr string `json:"addr,omitempty"`
}

4.1 新增

m := Man{
    Name: "xue1234",
    Age: 18,
    Addr: "成都市",
}

// 1
rows, _ = InsertForObj(db, "man", m)
t.Log(rows.LastInsertId())

// 3
sqlObj := NewCacheSql("INSERT INTO man (name,age,addr) VALUES (?, ?, ?)", m.Name, m.Age, m.Addr)
rows, _ = ExecForSql(db, sqlObj)
t.Log(rows.LastInsertId())

4.2 刪除

m := Man{
    Id: 9,
}

// 1
rows, _ := NewTable(db).Delete(m).Exec()
t.Log(rows.LastInsertId())

// 2
rows, _ = DeleteWhere(db, "man", "id=?", 9)
t.Log(rows.LastInsertId())

// 3
sqlObj := NewCacheSql("DELETE FROM man WHERE id=?", 9)
rows, _ = ExecForSql(db, sqlObj)
t.Log(rows.LastInsertId())

4.3 修改

m := Man{
    Name: "xue12",
    Age: 20,
    Addr: "測試",
}

// 1
rows, _ := NewTable(db).Update(m, "id=?", 7).Exec()
t.Log(rows.LastInsertId())

// 2
sqlObj := NewCacheSql("UPDATE man SET name=?,age=?,addr=? WHERE id=?", m.Name, m.Age, m.Addr, 7)
rows, _ = ExecForSql(db, sqlObj)
t.Log(rows.LastInsertId())

4.4 查詢

4.4.1 單查詢

var m Man
// 1
_ = NewTable(db, "man").Select("name,age").Where("id=?", 1).FindOne(&m)
t.Log(m)

// 2
_ = NewTable(db).SelectAuto("name,age", "man").Where("id=?", 1).FindOne(&m)
t.Log(m)

// 3
_ = FindOne(db, NewCacheSql("SELECT name,age FROM man WHERE id=?", 1), &m)
t.Log(m)

// 4, 對查詢結果進行內容修改
_ = FindOneFn(db, NewCacheSql("SELECT name,age FROM man WHERE id=?", 1), &m, func(_row interface{}) error {
    v := _row.(*Man)
    v.Name = "被修改了哦"
    v.Age = 100000
    return nil
})
t.Log(m)

// 5
_ = FindWhere(db, "man", &m, "id=?", 1)
t.Log(m)

// 6
var b map[string]string
_ = FindWhere(db, "man", &b, "id=?", 1)
t.Log(b)

• 查詢結果支援: struct, map, 單欄位
• 資料庫返回的 NULL 型別, 不需要處理, orm 會自行處理, 如果傳入空型別值會報錯(如: sql.NullString)

4.4.2 多條記錄查詢

var m []*Man
err := NewTable(db, "man").Select("id,name,age,addr").Where("id>?", 1).FindAll(&m, func(_row interface{}) error {
    v := _row.(*Man)
    if v.Id == 5 {
        v.Name = "test"
    }
    fmt.Println(v.Id, v.Name, v.Age)
    return nil
})
if err != nil {
    t.Fatal(err)
}
t.Logf("%+v", m)

• 查詢結果支援的切片型別: struct, map, 單欄位
• 資料庫返回的 NULL 型別, 不需要處理, orm 會自行處理, 如果傳入空型別值會報錯(如: sql.NullString)

4.4.3 別名查詢

type Tmp struct {
    Name1 string `json:"name_1,omitempty"`
    Age1 int32 `json:"age_1,omitempty"`
}

var m Tmp
err := NewTable(db).
TagAlias(map[string]string{"name_1": "name", "age_1": "age"}).
Select("name,age").
From("man").
FindWhere(&m, "id=?", 1)
if err != nil {
    t.Fatal(err)
}

4.4.3 其他

• 使用可以參考 orm_test.go 和 example_orm_test.go
• 在連表查詢時, 如果兩個表的列名相同查詢結果會出現錯誤, 我們可以透過根據別名來區分, 或者直接呼叫 Query 來自行對結果進行處理(注: 呼叫 Query 時需要處理 Null 型別)

其他

• 歡迎大佬們指正, 希望大佬給 star，to gitee

本作品採用《CC 協議》，轉載必須註明作者和本文連結