gorose
v2.1.8
PHP Laravel ORM 的go 實現, 與laravel 官方文件保持一致https://laravel.com/docs/10.x/queries .
分為go 風格(struct 結構綁定用法) 和php 風格(map 結構用法).
php 風格用法, 完全可以使用laravel query builder 的文檔做參考, 盡量做到1:1 還原.
目前還處於beta階段, 請謹慎使用. 由於沒有打tag, 只能使用go mod 的方式引入
# go.mod
require github.com/gohouse/gorose/v3 mastergo風格用法
package main
import (
gorose "github.com/gohouse/gorose/v3"
// 引入mysql驱动
_ "github.com/go-sql-driver/mysql"
)
type User struct {
Id int64 `db:"id,pk"` // 这里的 pk 是指主键
Name string `db:"name"`
Email string `db:"email"`
// 定义表名字,等同于 func (User) TableName() string {return "users"}, 二选一即可
// TableName string `db:"users" json:"-"`
}
func ( User ) TableName () string {
return "users"
}
var rose = gorose . Open ( "mysql" , "root:123456@tcp(localhost:3306)/test?charset=utf8mb4&parseTime=true" )
func db () * gorose. Database {
return rose . NewDatabase ()
}
func main () {
// select id,name,email from users limit 1;
var user User
db (). To ( & user )
// insert into users (name,email) values ("test","[email protected]");
var user = User { Name : "test" , Email : "[email protected]" }}
db (). Insert ( & user )
// update users set name="test2" where id=1;
var user = User { Id : 1 , Name : "test2" }
db (). Update ( & user )
// delete from users where id=1;
var user = User { Id : 1 }
db (). Delete ( & user )
}php風格用法和go風格用法查詢如下sql對比:
select id,name,email from users where id = 1 and name = " test " // php 风格写法
user , err := db (). Table ( "users" ).
Select ( "id" , "name" , "email" ).
Where ( "id" , "=" , 1 ). Where ( "name" , "test" ).
First ()
// go 风格写法
var user = User { Id : 1 , Name : "test" }
err := db (). To ( & user )上邊的兩種用法結果相同,由此可以看出,除了對錶模型的綁定區別, 其他方法通用
單一資料庫連線, 可以直接同官方介面一樣用法
var rose = gorose . Open ( "mysql" , "root:123456@tcp(localhost:3306)/test?charset=utf8mb4&parseTime=true" )也可以用
var conf1 = gorose. Config {
Driver : "mysql" ,
DSN : "root:123456@tcp(localhost:3306)/test?charset=utf8mb4&parseTime=true" ,
Prefix : "tb_" ,
Weight : 0 ,
MaxIdleConns : 0 ,
MaxOpenConns : 0 ,
ConnMaxLifetime : 0 ,
ConnMaxIdleTime : 0 ,
}
var rose = gorose . Open ( conf )或使用讀寫分離集群,事務內,自動強制從寫庫讀取數據
var rose = gorose . Open (
gorose. ConfigCluster {
WriteConf : []gorose. Config {
conf1 ,
conf2 ,
},
ReadConf : []gorose. Config {
conf3 ,
conf4 ,
}
},
)目前實現了上述5中資料庫的支援, 通用資料庫,只需要添加更多的gorose/driver/dialect.IDialect介面即可非通用資料庫,只要實現了gorose/driver.IDriver 介面,理論上可以支援任意資料庫,包括redis,mongo 等都可以透過這個介面來實現所有的orm 鍊式操作都在gorose/builder.Context中, 直接可以拿到最原始的數據
// 全自动事务, 有错误会自动回滚, 无错误会自动提交
// Transaction 方法可以接收多个操作, 同用一个 tx, 方便在不同方法内处理同一个事务
db (). Transaction ( func ( tx gorose. TxHandler ) error {
tx (). Insert ( & user )
tx (). Update ( & user )
tx (). To ( & user )
})
// 手动事务
tx = db (). Begin ()
tx (). Insert ( & user )
tx (). Update ( & user )
tx (). To ( & user )
tx (). Rollback ()
tx (). Commit ()
// 全自动嵌套事务
db (). Transaction ( func ( tx gorose. TxHandler ) error {
tx (). Insert ( & user )
...
// 自动子事务
tx (). Transaction ( func ( tx2 gorose. TxHandler ) error {
tx2 (). Update ( & user )
...
}
}
// 手动嵌套事务
var tx = db (). Begin ()
// 自动子事务
tx (). Begin () // 自动 savepoint 子事务
...
tx (). Rollback () // 自动回滚到上一个 savepoint
...
// 手动子事务
tx (). SavePoint ( "savepoint1" ) // 手动 savepoint 到 savepoint1(自定义名字)
...
tx (). RollbackTo ( "savepoint1" ) // 手动回滚到自定义的 savepoint
tx (). Commit ()在插入和更新資料時,如果使用struct 模型作為資料物件的時候, 預設忽略類型零值,如果想強制寫入,則可以從第二個參數開始傳入需要強制寫入的欄位即可,如:
var user = User { Id : 1 , Name : "test" }
// 这里不会对 sex 做任何操作,
//update user set name="test" where id=1
db (). Update ( & user )
// 这里会强制将sex更改为0
//update user set name="test", sex=0 where id=1
db (). Update ( & user , "sex" )
// 等同于
db (). Table ( & user ). Where ( "id" , 1 ). Update ( map [ string ] any { "name" : "test" , "sex" : 0 }))如果沒有where條件,則會自動加入tag中指定了pk的欄位作為條件,如: db:"id,pk" , 因為指定了pk,如果id 的值不為0值, 則id 會作為主鍵條件更新
參考update
var user = User { Id : 1 }
db (). Delete ( & user )
// 等同于
db (). Table ( & user ). Where ( "id" , 1 ). Delete ()
// 要加上字段0值条件,只需要传入第二个字段,如:
// delete from users where id=1 and sex=0 and name=""
db (). Delete ( & user , "sex" , "name" ) db (). Table ( User {})
db (). Table ( "users" ) db (). Table ( User {}, "u" )
db (). Table ( "users" , "u" ) sub := db (). Table ( "users" ). Select ( "id" , "name" )
db (). Table ( sub ). Where ( "id" , ">" , 1 ). Get () type UserInfo struct {
UserId int64 `db:"user_id"`
TableName string `db:"user_info"`
} db (). Table ( "users" ). Join ( UserInfo {}, "user.id" , "=" , "user_info.user_id" ). Get () // select * from users a inner join user_info b on a.id=b.user_id
db (). Table ( "users" , "u" ). Join ( gorose . As ( UserInfo {}, "b" ), "u.id" , "=" , "b.user_id" ). Get ()
// 等同于
db (). Table ( User {}, "u" ). Join ( gorose . As ( "user_info" , "b" ), "u.id" , "=" , "b.user_id" ). Get () gorose.As(UserInfo{}, "b")中, user_info取別名b
db (). Table ( "users" ). Join ( UserInfo {}, func ( wh builder. IJoinOn ) {
wh . On ( "a.id" , "b.user_id" ). OrOn ( "a.sex" , "b.sex" )
}). Get ()
// 等同于
db (). Table ( "users" ). JoinOn ( UserInfo {}, func ( wh builder. IJoinOn ) {
wh . On ( "a.id" , "b.user_id" ). OrOn ( "a.sex" , "b.sex" )
}). Get ()當Join的第二個參數為builder.IJoinOn時,等同於JoinOn用法(第二個參數有強型別提醒,方便ide快捷提示)
// where id in (select user_id from user_info)
sub := db (). Table ( "user_info" ). Select ( "user_id" )
db (). Table ( User {}). Where ( "id" , "in" , sub ). Get () // where id in (select user_id from user_info)
db (). Table ( User {}). WhereSub ( "id" , "in" , func ( tx * builder. Context ) {
tx . Table ( "user_info" ). Select ( "user_id" )
}). Get () // where id in (select user_id from user_info)
sub := db (). Table ( "user_info" ). Select ( "user_id" )
db (). Table ( User {}). WhereBuilder ( "id" , "in" , sub ). Get ()以上3種用法等同
// where id>1 and (sex=1 or sex=2)
db (). Table ( User {}). Where ( "id" , ">" , 1 ). Where ( func ( wh builder. IWhere ) {
wh . Where ( "sex" , 1 ). OrWhere ( "sex" , 2 )
})這裡的Where 等同於WhereNested
// where id>1 and (sex=1 or sex=2)
db (). Table ( User {}). Where ( "id" , ">" , 1 ). WhereNested ( func ( wh builder. IWhere ) {
wh . Where ( "sex" , 1 ). OrWhere ( "sex" , 2 )
})以上兩種用法等同
Table,Where,Join內部都可以用子查詢
sub := db (). Table ( "user" ). Where (). OrWhere ()
sub2 := db (). Table ( "address" ). Select ( "user_id" ). Where (). OrWhere ()
sub3 := db (). Table ( "user_info" ). Select ( "user_id" ). Where (). OrWhere ()用在Table,Where,Join 內
db ().
Table ( sub , "a" ).
Join ( gorose . As ( sub2 , "b" ), "a.id" , "=" , "b.user_id" )).
WhereIn ( "a.id" , sub3 ).
Get ()用在Union,UnionAll 內
db ().
Table ( "users" ).
Union ( sub ).
Get () var sub2222 = db (). Table ( "user" ). Where (). OrWhere ()
var to [] User
db ().
Table ( "users" ).
UnionAll ( sub , sub2222 ).
To ( & to )傳回兩列資料到一個map中,第一列為value,第二列為key
// select id,name from users
db (). Table ( "users" ). Pluck ( "name" , "id" )
// 返回 map[<id>]<name>, 实际得到 map[int64]string{1: "张三", 2: "李四"} 傳回一列資料到一個數組中
// select id,name from users
db (). Table ( "users" ). List ( "id" )
// 返回 []<id>, 实际得到 []int64{1,2,3} 使用結構體欄位作為select 欄位使用結構體欄位值作為where 條件查詢結果綁定到結構體,支援一或多條
// 查询一条数据
var user User
db (). To ( & user )
// 查询条件,一条数据
// select id,name,email from users where id=1
var user = User { Id : 1 }
db (). To ( & user )
// 查询多条数据
var users [] User
db (). To ( & users )
// 查询条件,多条数据
var users [] User
db (). Where ( "id" , ">" , 1 ). To ( & users )僅用作查詢結果的綁定結構體字段,不作為查詢字段和條件常用作join或者手動指定字段查詢綁定
type Result struct {
Id int64 `db:"id"`
Aname string `db:"aname"`
Bname string `db:"bname"`
}
var res Result
// select a.id, a.name aname, b.name bname from a inner join b on a.id=b.aid where a.id>1
db (). Table ( "a" ). Join ( "b" , "a.id" , "b.aid" ). Select ( "a.id" , "a.name aname" , "b.name bname" ). Where ( "a.id" , ">" , 1 ). Bind ( & res )查詢字段的顯示名字一定要跟結構體的字段tag(db) 名字相同, 否則不會被賦值字段數量可以不一樣
var list [] int
db (). Table ( "users" ). ListTo ( "age" , & list )
var pluck map [ int64 ] string
db (). Table ( "users" ). PluckTo ( "name" , "id" , & pluck )
var value int
db (). Table ( "users" ). ValueTo ( "age" , & value ) var sum int
db (). Table ( "users" ). SumTo ( "age" , & sum )
var max int
db (). Table ( "users" ). MaxTo ( "age" , & max )
var min int
db (). Table ( "users" ). MinTo ( "age" , & min )預設採用官方函式庫的slog debug level, 如果不想顯示sql日誌, 只需要設定slog的level到debug以上即可, 如: Info, Warn, Error
type User struct {
Id int64 `db:"id,pk"`
Sex * int8 `db:"sex"` // 使用指针可以绑定 null 值
Age sql. NullInt64 `db:"age"` // 使用sql.NullInt64 可以绑定 null 值
}指針賦值
var sex = int8 ( 1 )
var user = User { Id : 1 , Sex : & sex }
// 或者,快捷用法
var user = User { Id : 1 , Sex : gorose . Ptr ( int8 ( 1 ))}sql.Null* 賦值
var age = sql. NullInt64 { Int64 : 18 , Valid : true }sql.Null* 使用
if age . Valid {
fmt . Println ( age . Int64 )
}由此可見,null處理起來還是有點麻煩,所以,建議在設計表的時候,不要允許null即可,給定預設值,而大部分預設值剛好可以與go的類型零值對應
Table
Select
SelectRaw
AddSelect
Join
GroupBy
Having
HavingRaw
OrHaving
OrHavingRaw
OrderBy
Limit
Offset
Where
WhereRaw
OrWhere
OrWhereRaw
WhereBetween
OrWhereBetween
WhereNotBetween
OrWhereNotBetween
WhereIn
OrWhereIn
WhereNotIn
OrWhereNotIn
WhereNull
OrWhereNull
WhereNotNull
OrWhereNotNull
WhereLike
OrWhereLike
WhereNotLike
OrWhereNotLike
WhereExists
WhereNotExists
WhereNot
Get
First
Find
Insert
Update
Delete
Max
Min
Sum
Avg
Count
InsertGetId
Upsert
InsertOrIgnore
Exists
DoesntExist
Pluck
List
Value
Paginate
Increment
Decrement
IncrementEach
DecrementEach
Truncate
Union
UnionAll
SharedLock
LockForUpdate
Replace
Page
LastSql
WhereBuilder
OrWhereBuilder
WhereSub
OrWhereSub
WhereNested
OrWhereNested
To
Bind
ListTo
PluckTo
ValueTo
SumTo
MaxTo
UnionTo
UnionAllTo
