- 联表查询
var Orm *xorm.Engine
func InitMysql() error {
conn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8",
"root", "123456", "192.168.56.3", 3306, "netacl")
orm, err := xorm.NewEngine("mysql", conn)
if err != nil {
return err
}
f, err := os.Create("db.log")
if err != nil {
println(err.Error())
return err
}
orm.SetMaxIdleConns(10)
orm.SetMaxOpenConns(200)
orm.ShowSQL(false)
orm.Logger().SetLevel(core.LOG_DEBUG)
orm.SetLogger(xorm.NewSimpleLogger(f))
err = orm.Ping()
if err != nil {
fmt.Println("连接数据库失败...")
return err
}
fmt.Println("[Connecting MySQL Success!]")
Orm = orm
return nil
}
type AclOrderTag struct {
Acl `xorm:"extends"`
Tag string
}
func test_4() {
res := make([]*AclOrderTag, 0)
_ = Orm.Table("acl_order").Join("INNER", "acl", "acl.order_id = acl_order.id").Where("acl_order.tree_id is NULL").Find(&res)
fmt.Println(res[0])
}
参考:https://xorm.io/zh/docs/chapter-05/5.join/
2. 软删除
Deleted
软删除实例
package main
import (
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
"github.com/go-xorm/xorm"
)
var engine *xorm.Engine
type User struct {
Name string `xorm:"varchar(25) 'name'"`
Id int `xorm:"pk 'id' autoincr"`
DeletedAt time.Time `xorm:"deleted"`
}
func init() {
var err error
engine, err = xorm.NewEngine("mysql", "root:123456@tcp(192.168.56.3:3306)/test?charset=utf8")
if err != nil {
log.Fatal(err)
return
}
//创建表和插入数据
err = engine.CreateTables(User{})
if err != nil {
log.Fatal(err)
return
}
u := make([]User, 3)
u[0].Name = "u0"
u[1].Name = "u1"
u[2].Name = "u2"
_, err = engine.Insert(u)
if err != nil {
log.Fatal(err)
return
}
}
func main() {
uu := new(User)
// "SELECT `name`, `id`, `deleted_at` FROM `user` WHERE (`deleted_at` IS NULL OR `deleted_at`=?) AND `id`=? LIMIT 1"
has, err := engine.Id(2).Get(uu)
if err != nil {
log.Fatal(err)
return
}
if has {
fmt.Println("软删除Deleted前,查询用户名:", uu.Name, "deleteAt:", uu.DeletedAt)
} else {
fmt.Println("该数据现在已经不存在")
}
// "UPDATE `user` SET `deleted_at` = ? WHERE `name`=? AND `id`=? AND (`deleted_at` IS NULL OR `deleted_at`=?) AND `id`=?"
affected, err := engine.Id(2).Delete(uu)
if err != nil {
log.Fatal(err)
return
}
fmt.Println("软删除Delete,受影响的行数:", affected)
fmt.Println()
uu = new(User)
has, err = engine.Id(2).Get(uu)
if err != nil {
log.Fatal(err)
return
}
if has {
fmt.Println("软删除Deleted后,查询用户名:", uu.Name, "deleteAt:", uu.DeletedAt)
} else {
fmt.Println("该数据现在已经不存在")
}
affected, err = engine.Id(2).Delete(uu)
if err != nil {
log.Fatal(err)
return
}
fmt.Println("再次软删除Delete,受影响的行数:", affected)
fmt.Println()
affected, err = engine.Table(new(User)).Id(2).Update(map[string]interface{}{"deleted_at": nil})
if err != nil {
log.Fatal(err)
return
}
fmt.Println("恢复软删除,受影响的行数:", affected)
uu = new(User)
has, err = engine.Id(2).Get(uu)
if err != nil {
log.Fatal(err)
return
}
if has {
fmt.Println("恢复软删除,查询用户名:", uu.Name, "deleteAt:", uu.DeletedAt)
} else {
fmt.Println("该数据现在已经不存在")
}
affected, err = engine.Id(2).Delete(uu)
if err != nil {
log.Fatal(err)
return
}
fmt.Println("恢复后再次软删除Delete,受影响的行数:", affected)
fmt.Println()
//那么如果记录已经被标记为删除后,要真正的获得该条记录或者真正的删除该条记录,需要启用Unscoped,如下所示:
// 此时将可以获得记录,此时将可以真正的删除记录
uu = new(User)
// "SELECT `name`, `id`, `deleted_at` FROM `user` WHERE `id`=? LIMIT 1"
has, err = engine.Id(2).Unscoped().Get(uu)
if err != nil {
log.Fatal(err)
return
}
if has {
fmt.Println("启用Unscoped,查询软删除后 用户名:", uu.Name, "deleteAt:", uu.DeletedAt)
} else {
fmt.Println("该数据现在已经不存在")
}
// "DELETE FROM `user` WHERE `name`=? AND `id`=? AND (`deleted_at` IS NULL OR `deleted_at`=?) AND `id`=?"
affected, err = engine.Id(2).Unscoped().Delete(uu)
if err != nil {
log.Fatal(err)
return
}
fmt.Println("启用Unscoped 物理删除,受影响的行数:", affected)
/*
软删除Deleted前,查询用户名: u1 deleteAt: 0001-01-01 00:00:00 +0000 UTC
软删除Delete,受影响的行数: 1
该数据现在已经不存在
再次软删除Delete,受影响的行数: 0
恢复软删除,受影响的行数: 1
恢复软删除,查询用户名: u1 deleteAt: 0001-01-01 00:00:00 +0000 UTC
恢复后再次软删除Delete,受影响的行数: 1
启用Unscoped,查询软删除后 用户名: u1 deleteAt: 2024-04-26 14:53:35 +0800 CST
启用Unscoped 物理删除,受影响的行数: 1
*/
}
- 软删除实际执行的sql语句是
"UPDATE `user` SET `deleted_at` = ? WHERE `name`=? AND `id`=? AND (`deleted_at` IS NULL OR `deleted_at`=?) AND `id`=?"
- 物理删除、获取已经软删除的数据用Unscoped()
- 恢复软删除的数据:把deleted_at 设置为nil
3、Get
Get获取一个对象。
Find获取一个列表。
实例
package main
import (
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
"github.com/go-xorm/xorm"
)
var engine *xorm.Engine
type User struct {
Name string `xorm:"varchar(25) 'name'"`
Id int `xorm:"pk 'id' autoincr"`
DeletedAt time.Time `xorm:"deleted"`
}
func init() {
var err error
engine, err = xorm.NewEngine("mysql", "root:123456@tcp(192.168.56.3:3306)/test?charset=utf8")
if err != nil {
log.Fatal(err)
return
}
}
func main() {
query := fmt.Sprintf("name = '%s'", "u1")
ret := new(User)
_, err := engine.Where(query).Get(ret)
// u1 在数据库中不存在,查询 返回的用户id为 0,数据不在数据库时,并不是一种错误,不需要返回错误。
fmt.Printf("user don't existed, id %d\n", ret.Id)
if err != nil {
fmt.Println(err)
}
query = fmt.Sprintf("name = '%s'", "u0")
ret = new(User)
_, err = engine.Where(query).Get(ret)
// u0 在数据库中存在,查询 返回的用户id为 1
fmt.Printf("user exist, id %d\n", ret.Id)
if err != nil {
fmt.Println(err)
}
}
/*
打印结果:
user don't existed, id 0
user exist, id 1
*/