github.com/jtzjtz/kit@v1.0.2/sql/sqlbinding.go (about)

     1  package sql
     2  
     3  import (
     4  	"fmt"
     5  	"strings"
     6  	"time"
     7  )
     8  
     9  var replace = map[string]string{"\\": "\\\\", "'": `\'`, "\\0": "\\\\0", "\n": "\\n", "\r": "\\r", `"`: `\"`, "\x1a": "\\Z"}
    10  
    11  func mysqlRealEscapeString(v interface{}) interface{} {
    12  	vstr, ok := v.(string)
    13  	if !ok {
    14  		t, istime := v.(time.Time)
    15  		if !istime {
    16  			return v
    17  		}
    18  
    19  		vstr = t.Format("2006-01-02 15:04:05")
    20  	}
    21  
    22  	for key, escape := range replace {
    23  		vstr = strings.Replace(vstr, key, escape, -1)
    24  	}
    25  
    26  	return fmt.Sprintf("'%s'", vstr)
    27  }
    28  
    29  func mysqlRealEscapeStringLike(v interface{}) interface{} {
    30  	vstr, ok := v.(string)
    31  	if !ok {
    32  		return v
    33  	}
    34  
    35  	for key, escape := range replace {
    36  		vstr = strings.Replace(vstr, key, escape, -1)
    37  	}
    38  
    39  	return fmt.Sprintf("%s", vstr)
    40  }
    41  
    42  // OR .
    43  func OR(sqls ...Fielder) Fielder {
    44  	strSQL := make([]string, len(sqls))
    45  	for i, sql := range sqls {
    46  		strSQL[i] = fmt.Sprint(sql)
    47  	}
    48  
    49  	return Field(fmt.Sprintf("(%s)", strings.Join(strSQL, " OR ")))
    50  }
    51  
    52  // Relation 表示字段与值的关系
    53  type Relation interface {
    54  	In(...interface{}) Fielder
    55  	Like(interface{}) Fielder
    56  	LLike(interface{}) Fielder
    57  	RLike(interface{}) Fielder
    58  	GT(interface{}) Fielder
    59  	GTE(interface{}) Fielder
    60  	LT(interface{}) Fielder
    61  	LTE(interface{}) Fielder
    62  	EQ(interface{}) Fielder
    63  	NEQ(interface{}) Fielder
    64  	String() string
    65  }
    66  
    67  // Fielder 字段接口
    68  type Fielder interface {
    69  	And(f string) Relation
    70  }
    71  
    72  var _ Relation = Field("")
    73  var _ Fielder = Field("")
    74  
    75  // Field Fielder 接口的实现
    76  type Field string
    77  
    78  // And 。
    79  func (ff Field) And(f string) Relation {
    80  	return Field(fmt.Sprintf("%s AND %s", ff, f))
    81  }
    82  
    83  // In .
    84  func (ff Field) In(vs ...interface{}) Fielder {
    85  	v := make([]string, len(vs))
    86  
    87  	for index, item := range vs {
    88  		v[index] = fmt.Sprintf("%v", mysqlRealEscapeString(item))
    89  	}
    90  	sql := fmt.Sprintf("%s IN (%s)", ff, strings.Join(v, ", "))
    91  	sql = strings.TrimPrefix(sql, "1=1 AND")
    92  
    93  	return Field(sql)
    94  }
    95  
    96  // Like .
    97  func (ff Field) Like(v interface{}) Fielder {
    98  	sql := fmt.Sprintf("%s LIKE '%%%s%%'", ff, mysqlRealEscapeStringLike(v))
    99  	sql = strings.TrimPrefix(sql, "1=1 AND")
   100  	return Field(sql)
   101  }
   102  
   103  // LLike .
   104  func (ff Field) LLike(v interface{}) Fielder {
   105  	sql := fmt.Sprintf("%s LIKE '%%%s'", ff, mysqlRealEscapeStringLike(v))
   106  	sql = strings.TrimPrefix(sql, "1=1 AND")
   107  	return Field(sql)
   108  }
   109  
   110  // RLike .
   111  func (ff Field) RLike(v interface{}) Fielder {
   112  	sql := fmt.Sprintf("%s LIKE '%s%%'", ff, mysqlRealEscapeStringLike(v))
   113  	sql = strings.TrimPrefix(sql, "1=1 AND")
   114  	return Field(sql)
   115  }
   116  
   117  // GT .
   118  func (ff Field) GT(v interface{}) Fielder {
   119  	sql := fmt.Sprintf("%s > %v", ff, mysqlRealEscapeString(v))
   120  	sql = strings.TrimPrefix(sql, "1=1 AND")
   121  	return Field(sql)
   122  }
   123  
   124  // GTE .
   125  func (ff Field) GTE(v interface{}) Fielder {
   126  	sql := fmt.Sprintf("%s >= %v", ff, mysqlRealEscapeString(v))
   127  	sql = strings.TrimPrefix(sql, "1=1 AND")
   128  	return Field(sql)
   129  }
   130  
   131  // LT .
   132  func (ff Field) LT(v interface{}) Fielder {
   133  	sql := fmt.Sprintf("%s < %v", ff, mysqlRealEscapeString(v))
   134  	sql = strings.TrimPrefix(sql, "1=1 AND")
   135  	return Field(sql)
   136  }
   137  
   138  // LTE .
   139  func (ff Field) LTE(v interface{}) Fielder {
   140  	sql := fmt.Sprintf("%s <= %v", ff, mysqlRealEscapeString(v))
   141  	sql = strings.TrimPrefix(sql, "1=1 AND")
   142  	return Field(sql)
   143  }
   144  
   145  // EQ .
   146  func (ff Field) EQ(v interface{}) Fielder {
   147  	sql := fmt.Sprintf("%s = %v", ff, mysqlRealEscapeString(v))
   148  	sql = strings.TrimPrefix(sql, "1=1 AND")
   149  	return Field(sql)
   150  }
   151  
   152  // NEQ .
   153  func (ff Field) NEQ(v interface{}) Fielder {
   154  	sql := fmt.Sprintf("%s != %v", ff, mysqlRealEscapeString(v))
   155  	sql = strings.TrimPrefix(sql, "1=1 AND")
   156  	return Field(sql)
   157  }
   158  
   159  func (ff Field) String() string {
   160  	return string(ff)
   161  }
   162  
   163  var (
   164  	// DefaultField .
   165  	DefaultField Fielder = Field("1=1")
   166  )