sql

Documentation

Index

• func GetBatchUpdateSQL(tableName, pkName string, pks []int64, values map[string]map[int64]interface{}) string
• func GetUnionSQL(subSQLs []string) string
• type SqlBuilder
  • func NewSqlBuilder() *SqlBuilder
  • func (b *SqlBuilder) Build() (string, []any, error)
  • func (b *SqlBuilder) Count(args ...string) *SqlBuilder
  • func (b *SqlBuilder) From(table string, alias ...string) *SqlBuilder
  • func (b *SqlBuilder) GroupBy(groupBy string) *SqlBuilder
  • func (b *SqlBuilder) InnerJoin(table, alias, on string, args ...any) *SqlBuilder
  • func (b *SqlBuilder) LeftJoin(table, alias, on string, args ...any) *SqlBuilder
  • func (b *SqlBuilder) Limit(listParam any) *SqlBuilder
  • func (b *SqlBuilder) OrderBy(orderBy string) *SqlBuilder
  • func (b *SqlBuilder) RightJoin(table, alias, on string, args ...any) *SqlBuilder
  • func (b *SqlBuilder) Select(items ...string) *SqlBuilder
• type SqlTemplate
  • func NewJoinSubQuery(template, alias, on string) SqlTemplate
  • func NewSqlTemplate() SqlTemplate

Functions

func GetBatchUpdateSQL

func GetBatchUpdateSQL(tableName, pkName string, pks []int64, values map[string]map[int64]interface{}) string

GetBatchUpdateSQL 根据给定的主键列表对相应的值进行批量更新 UPDATE <tableName> SET

<fieldname1> = CASE pkName
    WHEN <pk1> THEN <interface{}>
    WHEN <pk2> THEN <interface{}>
    WHEN <pk3> THEN <interface{}>
END,
<fieldname1> = CASE pkName
    WHEN <pk1> THEN <interface{}>
    WHEN <pk1> THEN <interface{}>
    WHEN <pk1> THEN <interface{}>
END

WHERE pkName IN (<pks>)

values: <fieldName1> => {
     pk1 => interface{}
     pk2 => interface{}
     pk3 => interface{}
}

func GetUnionSQL

func GetUnionSQL(subSQLs []string) string

GetUnionSQL 通过Union关键字实现多SQL批量查询

		(
		SELECT user_id, amount FROM fission_reward
		WHERE user_id=123 AND created_at BETWEEN 1575376982 and (1575376982+24*3600*7)
     )
	    UNION ALL
		(
	     	SELECT user_id, amount FROM fission_reward
			WHERE user_id=456 AND created_at BETWEEN 1575376982 and (1575376982+24*3600*7)
		)

Types

type SqlBuilder

type SqlBuilder struct {
	// contains filtered or unexported fields
}

SqlBuilder 查询SQL创建支持所有语法

func NewSqlBuilder

func NewSqlBuilder() *SqlBuilder

func (*SqlBuilder) Build

func (b *SqlBuilder) Build() (string, []any, error)

func (*SqlBuilder) Count

func (b *SqlBuilder) Count(args ...string) *SqlBuilder

func (*SqlBuilder) From

func (b *SqlBuilder) From(table string, alias ...string) *SqlBuilder

func (*SqlBuilder) GroupBy

func (b *SqlBuilder) GroupBy(groupBy string) *SqlBuilder

func (*SqlBuilder) InnerJoin

func (b *SqlBuilder) InnerJoin(table, alias, on string, args ...any) *SqlBuilder

func (*SqlBuilder) LeftJoin

func (b *SqlBuilder) LeftJoin(table, alias, on string, args ...any) *SqlBuilder

func (*SqlBuilder) Limit

func (b *SqlBuilder) Limit(listParam any) *SqlBuilder

func (*SqlBuilder) OrderBy

func (b *SqlBuilder) OrderBy(orderBy string) *SqlBuilder

func (*SqlBuilder) RightJoin

func (b *SqlBuilder) RightJoin(table, alias, on string, args ...any) *SqlBuilder

func (*SqlBuilder) Select

func (b *SqlBuilder) Select(items ...string) *SqlBuilder

type SqlTemplate

type SqlTemplate interface {
	With(template string) SqlTemplate
	Limit(listParam *protobuf.ListParam) SqlTemplate
	Next(pkName string, nextParam *protobuf.NextParam) SqlTemplate
	OrderBy(orderBy string) SqlTemplate
	GroupBy(groupBy string) SqlTemplate
	InsertArgs(extraArgs ...any) SqlTemplate
	AppendArgs(extraArgs ...any) SqlTemplate
	JoinSubQuery(tpl SqlTemplate) SqlTemplate

	Where(condition string, args ...any)
	Generate() (string, []any, error)
	Get(v any) error
	Select(v any) error
	Count() (int64, error)
	ExportConditions() ([]string, []any) // 导出where条件
	ImportConditions([]string, []any)    // 导入where条件
}

SqlTemplate 基于模板创建, 只支持where和limit

func NewJoinSubQuery

func NewJoinSubQuery(template, alias, on string) SqlTemplate

func NewSqlTemplate

func NewSqlTemplate() SqlTemplate