291 lines
7.9 KiB
Go
291 lines
7.9 KiB
Go
/*
|
||
=================================================================================
|
||
* License: GPL-2.0 license
|
||
* Author: 众产® https://ciy.cn/code
|
||
* Version: 0.1.0
|
||
=================================================================================
|
||
SQL连接器 CiySQL / NewCiySQL
|
||
=================================================================================
|
||
csql := c.NewCiySQL("table") //支持 table a 别名
|
||
csql.Where(field, any...) //查询条件，支持多个?。默认and，可拼接or条件
|
||
csql.Where_daterange(field, data) //绝对日期区间查询。2022-1-1~2022-2-1 (只日期) 2022-1-1 0:0:0~2022-1-1 8:0:0(带时间)
|
||
csql.Where_dayrange(field, data) //相对日期区间查询。-30~0 (最近1个月) 0(今天) -1(昨天)
|
||
csql.Where_monthrange(field, data) //月份区间查询。 2024-1 (本月) 2024-1~2024-2 (月份区间)
|
||
csql.Where_numrange(field,data1,data2,bet) //数字区间查询。 支持普通数字(bet1)、钱(bet=100)、公斤(bet=1000)等
|
||
csql.Order(order) //排序
|
||
csql.Limit(pageno, pagecount) //分页，第n页，每页n条
|
||
csql.Column(col) //查询字段，默认* 支持!xxx,xxx 排除字段
|
||
csql.Group(order) //分组
|
||
csql.Having(order) //分组条件
|
||
csql.Join(table, onsql,op) //Join关联 op默认left，可填right/inner/outer
|
||
csql.RawSQL(sql, any...) //原始SQL语句
|
||
|
||
=================================================================================
|
||
示例:
|
||
csql := c.NewCiySQL("user")
|
||
csql.Where("name like", "王")
|
||
csql.Order("id desc")
|
||
csql.Limit(1, 10)
|
||
=================================================================================
|
||
*/
|
||
package zciyon
|
||
|
||
import (
|
||
"fmt"
|
||
"regexp"
|
||
"strings"
|
||
"time"
|
||
)
|
||
|
||
type CiySQL struct {
|
||
tsmt []any //预编译数据
|
||
rawsql string //原始SQL
|
||
table string //表名
|
||
sqlcolumn string //字段名
|
||
sqllimit string //限制条数
|
||
sqlwhere string //条件
|
||
sqlorder string //排序
|
||
sqlgroup string //分组
|
||
sqlhaving string //分组条件
|
||
sqljoin string //连接
|
||
Err string //错误信息
|
||
LastSQL string //最后一条执行的SQL语句
|
||
}
|
||
|
||
func NewCiySQL(table string) *CiySQL {
|
||
db := &CiySQL{}
|
||
db.tsmt = make([]any, 0)
|
||
db.table = table
|
||
return db
|
||
}
|
||
func (thos *CiySQL) RawSQL(sqlstr string, argdata ...any) *CiySQL {
|
||
cnt := strings.Count(sqlstr, "?")
|
||
if cnt > 0 {
|
||
if cnt != len(argdata) {
|
||
thos.Err = "_sql data length mismatch"
|
||
return thos
|
||
} else {
|
||
thos.tsmt = argdata
|
||
}
|
||
}
|
||
thos.rawsql = sqlstr
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) Column(col string) *CiySQL {
|
||
thos.sqlcolumn = col
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) Limit(pageno int, pagecount int) *CiySQL {
|
||
if pageno < 1 {
|
||
pageno = 1
|
||
}
|
||
if pagecount < 1 {
|
||
pagecount = 10
|
||
}
|
||
thos.sqllimit = fmt.Sprintf(" limit %d,%d", (pageno-1)*pagecount, pagecount)
|
||
return thos
|
||
}
|
||
|
||
// eg. csql.Join('tab2','left','tab1.id=tab2.id');
|
||
func (thos *CiySQL) Join(table string, on string, argop ...string) *CiySQL { //op:left right inner outer
|
||
op := "left"
|
||
if len(argop) > 0 {
|
||
op = argop[0]
|
||
}
|
||
thos.sqljoin = fmt.Sprintf(" %s join %s on %s", op, table, on)
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) Order(order string) *CiySQL {
|
||
re := regexp.MustCompile(`^[0-9a-zA-Z_\-\.]+$`)
|
||
chks := strings.Split(order, ",")
|
||
for _, chk := range chks {
|
||
chk = strings.Replace(chk, "desc", "", -1)
|
||
chk = strings.TrimSpace(chk)
|
||
if !re.MatchString(chk) {
|
||
return thos
|
||
}
|
||
}
|
||
thos.sqlorder = order
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) Group(group string) *CiySQL {
|
||
thos.sqlgroup = group
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) Having(having string, argdata ...any) *CiySQL {
|
||
thos.sqlhaving = thos.query(having, argdata...)
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) Where(query string, argdata ...any) *CiySQL {
|
||
thos.sqlwhere = thos.sqlwhere + thos.query(query, argdata...)
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) query(query string, argdata ...any) string {
|
||
if query == "" {
|
||
thos.Err = "_query query empty"
|
||
return ""
|
||
}
|
||
if len(argdata) == 0 {
|
||
if query == "id" {
|
||
return "id=0" // 防止误操作
|
||
}
|
||
return " and " + query
|
||
}
|
||
|
||
cnt := strings.Count(query, "?")
|
||
if cnt > 0 {
|
||
if cnt != len(argdata) {
|
||
thos.Err = "_query data length mismatch"
|
||
return ""
|
||
}
|
||
if query[0] != ' ' {
|
||
query = " and " + query
|
||
}
|
||
thos.tsmt = append(thos.tsmt, argdata...)
|
||
return query
|
||
}
|
||
|
||
query = strings.TrimSpace(query)
|
||
data := Tostr(argdata[0])
|
||
if data == "" {
|
||
return ""
|
||
}
|
||
if strings.HasSuffix(query, " like") {
|
||
if data == ",," {
|
||
return ""
|
||
}
|
||
if !strings.HasPrefix(data, "%") && !strings.HasSuffix(data, "%") {
|
||
data = "%" + data + "%"
|
||
}
|
||
query = fmt.Sprintf(" and %s ?", query)
|
||
} else if strings.HasSuffix(query, " in") {
|
||
return fmt.Sprintf(" and %s (%s)", query, data)
|
||
} else {
|
||
lastChar := rune(query[len(query)-1])
|
||
if lastChar != '=' && lastChar != '>' && lastChar != '<' {
|
||
query += "="
|
||
}
|
||
query = fmt.Sprintf(" and %s?", query)
|
||
}
|
||
thos.tsmt = append(thos.tsmt, data)
|
||
return query
|
||
}
|
||
func (thos *CiySQL) Buildwhere() string {
|
||
if thos.sqlwhere == "" {
|
||
return ""
|
||
}
|
||
thos.sqlwhere = strings.TrimPrefix(thos.sqlwhere, " and ")
|
||
return " where " + thos.sqlwhere
|
||
}
|
||
|
||
func (thos *CiySQL) Buildsql(column string) (string, string) {
|
||
if thos.table == "" {
|
||
return "", ""
|
||
}
|
||
sqlstr := fmt.Sprintf("select %s from %s", column, thos.table)
|
||
if thos.sqljoin != "" {
|
||
sqlstr += thos.sqljoin
|
||
}
|
||
sqlstr += thos.Buildwhere()
|
||
if thos.sqlgroup != "" {
|
||
sqlstr += " group by " + thos.sqlgroup
|
||
}
|
||
if thos.sqlhaving != "" {
|
||
sqlstr += " having " + strings.TrimPrefix(thos.sqlhaving, " and ")
|
||
}
|
||
if thos.sqlorder != "" {
|
||
sqlstr += " order by " + thos.sqlorder
|
||
}
|
||
return sqlstr, column
|
||
}
|
||
func (thos *CiySQL) Where_daterange(query string, data string) *CiySQL { //2022-1-1~2022-2-1 2022-1-1 0:0:0~2022-1-1 8:0:0
|
||
ds := strings.Split(data, "~")
|
||
if len(ds) != 2 {
|
||
return thos
|
||
}
|
||
dstr := strings.TrimSpace(ds[0])
|
||
if dstr != "" {
|
||
if !strings.Contains(dstr, ":") {
|
||
dstr = dstr + " 00:00:00"
|
||
}
|
||
t, err := time.ParseInLocation("2006-01-02 15:04:05", dstr, time.Local)
|
||
if err == nil {
|
||
thos.Where(query+">=", t.Unix())
|
||
}
|
||
}
|
||
dstr = strings.TrimSpace(ds[1])
|
||
if dstr != "" {
|
||
if !strings.Contains(dstr, ":") {
|
||
dstr = dstr + " 23:59:59"
|
||
}
|
||
t, err := time.ParseInLocation("2006-01-02 15:04:05", dstr, time.Local)
|
||
if err == nil {
|
||
thos.Where(query+"<=", t.Unix())
|
||
}
|
||
}
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) Where_dayrange(query string, data string) *CiySQL { // -30~0最近1个月。 0今天。 -1昨天。
|
||
ds := strings.Split(data, "~")
|
||
now := time.Now()
|
||
date := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
|
||
dstr := strings.TrimSpace(ds[0])
|
||
if dstr != "" {
|
||
dint := Toint(dstr)
|
||
t := date.AddDate(0, 0, dint)
|
||
thos.Where(query+">=", t.Unix())
|
||
}
|
||
if len(ds) == 1 {
|
||
dstr = strings.TrimSpace(ds[0])
|
||
} else {
|
||
dstr = strings.TrimSpace(ds[1])
|
||
}
|
||
if dstr != "" {
|
||
dint := Toint(dstr)
|
||
t := date.AddDate(0, 0, dint+1)
|
||
thos.Where(query+"<=", t.Unix())
|
||
}
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) Where_monthrange(query string, data string) *CiySQL { //2024-1本月 2024-1~2024-2月份区间
|
||
ds := strings.Split(data, "~")
|
||
dstr := strings.TrimSpace(ds[0])
|
||
if dstr != "" {
|
||
if !strings.Contains(dstr, ":") {
|
||
dstr = dstr + "-01 00:00:00"
|
||
}
|
||
t, err := time.ParseInLocation("2006-01-02 15:04:05", dstr, time.Local)
|
||
if err == nil {
|
||
thos.Where(query+">=", t.Unix())
|
||
}
|
||
}
|
||
if len(ds) == 1 {
|
||
dstr = strings.TrimSpace(ds[0])
|
||
} else {
|
||
dstr = strings.TrimSpace(ds[1])
|
||
}
|
||
if dstr != "" {
|
||
if !strings.Contains(dstr, ":") {
|
||
dstr = dstr + "-01 23:59:59"
|
||
}
|
||
t, err := time.ParseInLocation("2006-01-02 15:04:05", dstr, time.Local)
|
||
if err == nil {
|
||
thos.Where(query+"<=", t.Unix())
|
||
}
|
||
}
|
||
return thos
|
||
}
|
||
func (thos *CiySQL) Where_numrange(query string, data1 any, data2 any, bet int) *CiySQL {
|
||
dstr := strings.TrimSpace(Tostr(data1))
|
||
if dstr != "" {
|
||
dnum := Tofloat(dstr) * float64(bet)
|
||
thos.Where(query+">=", dnum)
|
||
}
|
||
dstr = strings.TrimSpace(Tostr(data2))
|
||
if dstr != "" {
|
||
dnum := Tofloat(dstr) * float64(bet)
|
||
thos.Where(query+"<=", dnum)
|
||
}
|
||
return thos
|
||
}
|