README
¶
简介 
一款超好用Golang版SQL构造器，支持链式操作拼接SQL语句，单元测试覆盖率100%，详细用法请查看测试用例。
获取包
go get -v github.com/kwinh/go-sql-builder
SQL构造器
Select
查询字段 默认查询所有，即
*支持多个参数，单个参数，切片方式传值
// SELECT `id`,`name` as `n` FROM `user` []
sql, params = NewBuilder("user").Select("id", "name as n").ToSql()
sql, params = NewBuilder("user").Select("id,name n").ToSql()
sql, params = NewBuilder("user").Select([]string{"id", "name n"}).ToSql()
聚合查询
// SELECT max( `id` ) as `id_max` FROM `user` []
sql, params = NewBuilder("user").Select("max(`id`) as id_max").ToSql()
// SELECT min( `id` ) as `id_min` FROM `user` []
sql, params = NewBuilder("user").Select("min(`id`) as id_min").ToSql()
// SELECT count( * ) as `c` FROM `user` []
sql, params = NewBuilder("user").Select("count(*) c").ToSql()
原生表达式
有时候你可能需要在查询中使用原生表达式。你可以使用
sqlBuilder.Raw创建一个原生表达式：
原生字段
// SELECT DISTINCT mobile FROM `user` []
sql, params = user.Select(Raw("DISTINCT mobile")).ToSql()
原生条件
// SELECT * FROM `user` WHERE price > IF(state = 'TX', 200, 100) []
sql, params = user.Where(Raw("price > IF(state = 'TX', 200, 100)")).ToSql()
Table
指定查询表名
//SELECT * FROM `users`
user.Table("users").ToSql()
子查询
// SELECT * FROM (SELECT * FROM (SELECT `sex`,count( * ) as `c` FROM m_users GROUP BY `sex`) as `tmp2`) as `tmp1` []
sql, params = user.Table(func (m *Builder) {
m.Table(func (m *Builder) {
m.Table("m_users").Select("sex", "count(*) as c").Group("sex")
})
Where
简单where语句
在构造 where 查询实例中，你可以使用 where 方法。调用 where 最基本的方式是需要传递三个参数：第一个参数是列名，第二个参数是任意一个数据库系统支持的运算符，第三个是该列要比较的值。
// SELECT `id`,`name` FROM `users` WHERE `id` = ? [1]
sql, params = user.
Where("id", "=", 1).
ToSql()
为了方便，如果你只是简单比较列值和给定数值是否相等，可以将数值直接作为 where 方法的第二个参数：
// SELECT `id`,`name` FROM `users` WHERE `id` = ? [1]
sql, params = user.
Where("id", 1).
ToSql()
OrWhere语句
orWhere 方法和 where 方法接收的参数一样：
// SELECT * FROM `user` WHERE `id` = ? OR `name` like ? [1 %q%]
sql, params = user.
Where("id", 1).
OrWhere("name", "like", "%q%").
ToSql()
WhereBetween / WhereNotIn / WhereNotBetween / OrWhereNotBetween
WhereBetween方法验证字段值是否在给定的两个值之间：
可以传一个数组，也可以传2个值
// SELECT * FROM `user` WHERE `sex` = ? AND `attribute` BETWEEN ? AND ? [1 2 3]
sql, params = NewBuilder("user").Where("sex", 1).
WhereBetween("attribute", 2, 3).
ToSql()
// SELECT * FROM `user` WHERE `sex` = ? OR `attribute` BETWEEN ? AND ? [1 2 3]
sql, params = NewBuilder("user").Where("sex", 1).
OrWhereBetween("attribute", []int{2, 3}).
ToSql()
WhereNotBetween方法用于验证字段值是否在给定的两个值之外：
可以传一个数组，也可以传2个值
// SELECT * FROM `user` WHERE `sex` = ? AND `attribute` NOT BETWEEN ? AND ? [1 2 3]
sql, params = NewBuilder("user").Where("sex", 1).
WhereNotBetween("attribute", 2, 3).
ToSql()
// SELECT * FROM `user` WHERE `sex` = ? OR `attribute` NOT BETWEEN ? AND ? [1 2 3]
sql, params = NewBuilder("user").Where("sex", 1).
OrWhereNotBetween("attribute", []int{2, 3}).
ToSql()
WhereIn / WhereNotIn / OrWhereIn / OrWhereNotIn
WhereIn方法验证给定列的值是否包含在给定数组中：
可以传一个数组，也可以传多个值
// SELECT * FROM `user` WHERE `sex` = ? AND `id` IN (?,?) [1 100 200]
sql, params = user.Where("sex", 1).
WhereIn("id", 100, 200).ToSql()
// SELECT * FROM `user` WHERE `sex` = ? OR `id` IN (?,?) [1 100 200]
sql, params = user.Where("sex", 1).
OrWhereIn("id", []int{100, 200}).ToSql()
WhereNotIn方法验证给定列的值是否不存在给定的数组中：
// SELECT * FROM `user` WHERE `sex` = ? AND `id` NOT IN (?,?) [1 100 200]
sql, params = user.Where("sex", 1).
WhereNotIn("id", []int{100, 200}).ToSql()
// SELECT * FROM `user` WHERE `sex` = ? OR `id` NOT IN (?,?) [1 100 200]
sql, params = user.Where("sex", 1).
OrWhereNotIn("id", []int{100, 200}).ToSql()
ＷhereNull / ＷhereNotNull / ＯrWhereNull / ＯrWhereNotNull
＞ＷhereNull 方法验证指定的字段必须是 NULL:
// SELECT * FROM `user` WHERE `sex` = ? AND `deleted_at` IS NULL [1]
sql, params = user.Where("sex", 1).
WhereNull("deleted_at").ToSql()
// SELECT * FROM `user` WHERE `sex` = ? OR `deleted_at` IS NULL [1]
sql, params = user.Where("sex", 1).
OrWhereNull("deleted_at").ToSql()
WhereNotNull方法验证指定的字段肯定不是 NULL:
// SELECT * FROM `user` WHERE `sex` = ? AND `deleted_at` IS NOT NULL [1]
sql, params = user.Where("sex", 1).
WhereNotNull("deleted_at").ToSql()
// SELECT * FROM `user` WHERE `sex` = ? OR `deleted_at` IS NOT NULL [1]
sql, params = user.Where("sex", 1).
OrWhereNotNull("deleted_at").ToSql()
分组查询
如果需要在括号内对 or 条件进行分组，将闭包作为 orWhere 方法的第一个参数也是可以的：
// SELECT `id` FROM `user` WHERE `id` <> ? OR ( `age` > ? AND `name` like ?) [1 18 %q%]
sql, params := user.Where("id", "<>", 1).
OrWhere(func (m *Builder) {
m.Where("age", ">", 18).
Where("name", "like", "%q%")
}).ToSql()
子查询 Where 语句
// SELECT * FROM `user` WHERE `id` <> ? AND `id` in (SELECT `id` FROM `user_old` WHERE `age` > ? AND `name` like ?) [1 18 %q%]
sql, params = user.Where("id", "<>", 1).
WhereIn("id", func (m *Builder) {
m.Select("id").
Table("user_old").
Where("age", ">", 18).
Where("name", "like", "%q%")
}).ToSql()
Order
Order方法允许你通过给定字段对结果集进行排序。order的第一个参数应该是你希望排序的字段，第二个参数控制排序的方向，可以是asc或desc,也可以省略，默认是desc
// SELECT `id`,`name` FROM `user` ORDER BY `id` DESC []
sql, params = user.Select("id", "name").
Order("id", "desc").
ToSql()
如果你需要使用多个字段进行排序，你可以多次调用
Order
// SELECT `id`,`name` FROM `user` ORDER BY `id` DESC,`age` ASC []
sql, params = user.Select("id", "name").
Order("id").
Order("age", "asc").
ToSql()
groupBy / Having
groupBy 和 having 方法用于将结果分组。 having 方法的使用与 where 方法十分相似：
// SELECT `age`,count( * ) as `c` FROM `user` GROUP BY `age` HAVING `c` > ? [10]
sql, params = user.Select("age", "count(*) as c").Group("age").Having("c", ">", 10).ToSql()
// SELECT `age`,`sex`,count( * ) as `c` FROM `user` GROUP BY `age`,`sex` HAVING `c` > ? [10]
sql, params = user.Select("age", 'sex', "count(*) as c").Group("age", "sex").Having("c", ">", 10).ToSql()
Limit
// SELECT `id`,`name` FROM `user` LIMIT 10 []
sql, params = user.Select("id", "name").Limit(10).ToSql()
// SELECT `id`,`name` FROM `user` LIMIT 1,10 []
sql, params = user.Select("id", "name").Limit(1, 10).ToSql()
Page
//SELECT `id`,`name` FROM `user` LIMIT 0,10 []
sql, params = user.Select("id", "name").Page(1, 10).ToSql()
Joins
Inner Join 语句
查询构造器也可以编写 join 方法。若要执行基本的「内链接」，你可以在查询构造器实例上使用 Join 方法。传递给 Join 方法的第一个参数是你需要连接的表的名称，第二个参数是指定连接的字段约束，而其他的则是绑定参数。你还可以在单个查询中连接多个数据表：
// SELECT `id`,`name` FROM `user` INNER JOIN `order` as `o` o.user_id=u.user_id and o.type=? INNER JOIN `contacts` as `c` c.user_id=u.user_id [1]
sql, params = user.Select("id", "name").
Join("order o", "o.user_id=u.user_id and o.type=?", 1).
Join("contacts c", "c.user_id=u.user_id").
ToSql()
Left Join / Right Join 语句
如果你想使用 「左连接」或者 「右连接」代替「内连接」 ，可以使用 ＬeftJoin 或者 ＲightJoin 方法。这两个方法与 Join 方法用法相同：
// SELECT `id`,`name` FROM `user` RIGHT JOIN `contacts` as `c` c.user_id=u.user_id []
sql, params = user.Select("id", "name").
LeftJoin("contacts c", "c.user_id=u.user_id").
ToSql()
// SELECT `id`,`name` FROM `user` LEFT JOIN `contacts` as `c` c.user_id=u.user_id []
sql, params = user.Select("id", "name").
RightJoin("contacts c", "c.user_id=u.user_id").
ToSql()
关联子查询
// SELECT `id`,`name` FROM `user` as `u` INNER JOIN (SELECT * FROM `contacts` WHERE `id` > ?) as `tmp1` tmp1.user_id=u.user_id [100]
sql, params = user.Table("user u").Select("id", "name").
Join(func(b *Builder) {
b.Table("contacts").Where("id", ">", 100)
}, "tmp1.user_id=u.user_id").
ToSql()
插入
查询构造器还提供了
insert方法用于插入记录到数据库中。insert方法接收数组形式的字段名和字段值进行插入操作：
// INSERT INTO `user` (`name`,`age`) VALUES(?,?) [张三 18]
sql, params, err = user.Insert(map[string]interface{}{
"name": "张三",
"age": 18,
})
你甚至可以传递多个map给 insert 方法，依次将多个记录插入到表中：
注意：多个map参数要一致，以第一个为准，否则会省略后面不一致的map
// INSERT INTO `user` (`name`,`age`) VALUES(?,?),(?,?) [张三 18 李四 30]
sql, params, err = user.Insert(map[string]interface{}{
"name": "张三",
"age": 18,
}, map[string]interface{}{
"name": "李四",
"age": 30,
})
更新
// UPDATE `user` SET `name`=?,`age`=? WHERE `id` = ? [test 18 1]
sql, params = user.Table("user").Where("id", 1).Update(map[string]interface{}{
"name": "test",
"age": 18,
})
删除
// delete from `user` WHERE `id` = ? [1]
sql, params = user.Select("id", "name").Where("id", 1).Delete()
Documentation
¶
Index ¶
- type Builder
- func (b *Builder) Clone() *Builder
- func (b *Builder) Delete() (string, []interface{})
- func (b *Builder) DuplicateKey(duplicateKey map[string]interface{}) *Builder
- func (b *Builder) GetField() []interface{}
- func (b *Builder) GetGroup() []string
- func (b *Builder) GetHaving() []string
- func (b *Builder) GetJoin() []string
- func (b *Builder) GetLimit() string
- func (b *Builder) GetOrder() []string
- func (b *Builder) GetTable() string
- func (b *Builder) GetWhere() []string
- func (b *Builder) Group(group ...string) *Builder
- func (b *Builder) Having(args ...interface{}) *Builder
- func (b *Builder) Insert(args ...interface{}) (string, []interface{})
- func (b *Builder) Join(table interface{}, condition string, params ...interface{}) *Builder
- func (b *Builder) Joins(table interface{}, condition string, joinType string, params ...interface{}) *Builder
- func (b *Builder) LefJoin(table interface{}, condition string, params ...interface{}) *Builder
- func (b *Builder) Limit(args ...int64) *Builder
- func (b *Builder) OrHaving(args ...interface{}) *Builder
- func (b *Builder) OrWhere(args ...interface{}) *Builder
- func (b *Builder) OrWhereBetween(field string, value ...interface{}) *Builder
- func (b *Builder) OrWhereExists(where func(*Builder)) *Builder
- func (b *Builder) OrWhereIn(field string, value ...interface{}) *Builder
- func (b *Builder) OrWhereNotBetween(field string, value ...interface{}) *Builder
- func (b *Builder) OrWhereNotExists(where func(*Builder)) *Builder
- func (b *Builder) OrWhereNotIn(field string, value ...interface{}) *Builder
- func (b *Builder) OrWhereNotNull(field string) *Builder
- func (b *Builder) OrWhereNull(field string) *Builder
- func (b *Builder) Order(args ...interface{}) *Builder
- func (b *Builder) Page(page int64, listRows int64) *Builder
- func (b *Builder) Replace(args ...interface{}) (string, []interface{})
- func (b *Builder) RightJoin(table interface{}, condition string, params ...interface{}) *Builder
- func (b *Builder) Select(args ...interface{}) *Builder
- func (b *Builder) Table(table interface{}) *Builder
- func (b *Builder) TmpTable() string
- func (b *Builder) ToSql() (string, []interface{})
- func (b *Builder) Update(data map[string]interface{}) (string, []interface{})
- func (b *Builder) Where(args ...interface{}) *Builder
- func (b *Builder) WhereBetween(field string, value ...interface{}) *Builder
- func (b *Builder) WhereExists(where func(*Builder)) *Builder
- func (b *Builder) WhereIn(field string, value ...interface{}) *Builder
- func (b *Builder) WhereNotBetween(field string, value ...interface{}) *Builder
- func (b *Builder) WhereNotExists(where func(*Builder)) *Builder
- func (b *Builder) WhereNotIn(field string, value ...interface{}) *Builder
- func (b *Builder) WhereNotNull(field string) *Builder
- func (b *Builder) WhereNull(field string) *Builder
- type Raw
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type Builder ¶
type Builder struct {
TableName string
TableAlias string
// contains filtered or unexported fields
}
func NewBuilder ¶
func (*Builder) DuplicateKey ¶
func (*Builder) Limit ¶
Limit @Description: 指定查询数量 @receiver b @param int64 offset 起始位置 @param int64 length 查询数量 @return *Builder
func (*Builder) OrWhereBetween ¶
func (*Builder) OrWhereExists ¶
func (*Builder) OrWhereNotBetween ¶
func (*Builder) OrWhereNotExists ¶
func (*Builder) OrWhereNotIn ¶
func (*Builder) OrWhereNotNull ¶
func (*Builder) OrWhereNull ¶
func (*Builder) WhereBetween ¶
func (*Builder) WhereExists ¶
func (*Builder) WhereNotBetween ¶
func (*Builder) WhereNotExists ¶
func (*Builder) WhereNotIn ¶
func (*Builder) WhereNotNull ¶
Source Files
Click to show internal directories.
Click to hide internal directories.