gorm|xorm

mysql|sqlite3原生

_ "github.com/go-sql-driver/mysql"
//_ "github.com/mattn/go-sqlite3"
"github.com/jmoiron/sqlx"
var db *sqlx.DB
type User struct{
    Id int64 `db:"id"`
    Title string `db:"title"`
    Createat string `db:"createat"`
}
func init(){
    db,_= sqlx.Open("mysql", "root:mysql@tcp(127.0.0.1:3306)/test")
    //db,_= sqlx.Open("sqlite3", "db.db")
}
//非增删改查语句
sql:=`create table if not exists  user(
   id int unsigned not null   auto_increment  comment "描述",
   title varchar(100) default "0"  not null ,
   createat date,
  primary key ( id )
)engine=InnoDB default  charset=utf8;`
/*sql:=`create table user(
   id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
   title varchar(100) default "0"  not null ,
   createat char
)` */ //sqlite3语句
db.Exec(sql)
//插入数据
r,_:=db.Exec(`insert into user(title,createat) values(?,?)`,"李白","2020-12-20")
    id, _ := r.LastInsertId()
//修改
r, _ := db.Exec("update user set title=? where id=?", "stu0003", 1)
    id,_:=r.RowsAffected()//影响行数,0表示没修改
//删除
r,_:=db.Exec(`delete from user where id=?`,2)
    id,_:=r.RowsAffected()//影响行数,0表示没删除
//单条
    rs:=User{}
    db.Get(&rs,"select * from user where id=?",1)
    fmt.Println(rs.Id,rs.Title,rs.Createat)
//多条
    rows:=[]User{}
    db.Select(&rows,"select * from user where 1=1")
    fmt.Println(rows)
//事务
    conn, _ := db.Begin()
    _,err:=conn.Exec("update user set title=? where id=?", "stu0003", 1)
    if err != nil {
        fmt.Println("exec failed, ", err)
        conn.Rollback()
        return
    }
    conn.Commit()

• xorm导入库

"github.com/go-xorm/xorm"
_ "github.com/mattn/go-sqlite3"
_ "github.com/go-sql-driver/mysql"
type User struct {
    Id           int64 "xorm:pk autoincr notnull comment('描述')"
    Title         string `xorm:"varchar(30) default('测试')"`
    CreateAt time.Time `xorm:"createat created"`//自动时间
}
var db, _ = xorm.NewEngine("sqlite3", "db.db")
db, _ := xorm.NewEngine("mysql", "root:mysql@tcp(127.0.0.1:3306)/test?charset=utf8")
    db.Sync2(&User{})
    db.ShowSQL(true)
db.SetTableMapper(core.NewPrefixMapper(core.SnakeMapper{}, "prefix_")) //设置前缀
db.IsTableEmpty("table") 表是否为空
db.IsTableExist("table") 是否存在
db.DropTables("table")删除表
err := db.Sync2(new(User), new(Group)) 同步结构体到表
db.TZLocation, _ = time.LoadLocation("Asia/Shanghai") 设置时区
//插入
rs:=User{}
rs.Title="李白2"
rs.CreateAt=time.Now()
db.Insert(&rs)
fmt.Println(rs.Id)//插入id
//修改
up:=User{}
up.Title="中国"
db.Where("id=?",1).Update(up)
//删除
dl:=User{}
db.Where("id=?",2).Delete(dl)
//查询一条
c:=User{}
isok,_:=db.Where("id=?",1).Get(&c)
fmt.Println(c,isok)//isok为true表示记录存在,false不存在
//查询多条
rows:=[]User{}
db.Where("id>?",0).Find(&rows)//len(rows)==0 表示没查到记录
//判断是否查到
isok,_=db.Where("id=?",11).Exist(&User{})
fmt.Println(isok) //true表示查询到,false无记录,比上边两个快
//其他查询
rs:=[]User{}
db.Where("1=1").In("id",[]int{1,2,3}).Select("id,title").Limit(10,0).OrderBy("id desc").GroupBy("title").Find(&rs)
fmt.Println(rs)
//统计
total,_:=db.Where("1=1").Count(&User{})
fmt.Println(total)
//原生查询
db.SQL("select * from user where id=3").Get(&c)
fmt.Println(c)
rows1:=[]User{}
db.SQL("select * from user where id in(1,3)").Find(&rows1)
fmt.Println(rows1)
//单字段切片
sl:=[]string{}
db.SQL("select title from user where id in(1,3)").Find(&sl)
fmt.Println(sl)
//原生插入
sql:="insert into user(title) values (?)"
res, _ := db.Exec(sql, "OSCHINA")
id,_:=res.LastInsertId()
fmt.Println(id)

• gorm

"gorm.io/driver/mysql"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
type User struct{
    ID int64 `gorm:"PRIMARY_KEY;AUTO_INCREMENT"`
    Title string `gorm:"type:varchar(200);NOT NULL;comment:注释"`
    Createat string `gorm:"size:20"`
}
func (User) TableName() string {//返回不是复数表名称
    return "user"
}
var db  *gorm.DB
func init(){
    db, _ = gorm.Open(sqlite.Open("db.db"), &gorm.Config{})
dsn := "root:mysql@tcp(127.0.0.1:3306)/test?charset=utf8&parseTime=True&loc=Local"
db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{})
db.LogMode(true)//显示sql
}
    db.AutoMigrate(&User{})//同步数据表创建支持多个
//插入
    rs:=User{}
    rs.Title="李白2"
    rs.Createat="2020-03-08"
    db.Create(&rs)
    fmt.Println(rs.ID)//插入id

    rs:=User{Title:"中国",Createat: "2010-20-22"}
    db.Create(&rs)
//修改
    db.Model(&User{}).Where("id=?",1).Update("title", "hello")
    db.Model(&User{}).Where("id=?",2).Updates(map[string]interface{}{"title":"hellox","createat":"2020-02-31"}) //多字段
//删除
    db.Delete(&User{}, 1)
    db.Delete(&User{}, []int{1,2,3})
    db.Where("id = ?", 3).Delete(&User{})
    Unscoped()永久删除,不加默认会有软删除
//查询一条
    rs:=User{}
     db.Where(`id in(?)`,[]int{1,2}).First(&rs)
    fmt.Println(rs)
//多条
    rows:=[]User{}
    db.Where(`id in(?)`,[]int{1,2}).Find(&rows)
    fmt.Println(rows)

    rss:=[]User{}//排序支持多次调用
    db.Select("id,title,createat as sj").Where("1=1").Order("id desc").Limit(10).Offset(0).Group("title").Find(&rss)
    fmt.Println(rss)
//统计
    var num int64
    db.Find(&rows).Count(&num)
    fmt.Println(num)
//原生sql查询
    rs1:=User{};rows1:=[]User{}
    err:=db.Raw("select * from user where id=11").First(&rs1)
    fmt.Println(err.RowsAffected)//没找为0
    err1:=db.Raw("select * from user where id>?",10).Find(&rows1)
    fmt.Println(err1.RowsAffected)//没找到返回0
//原生sql非查询
    db.Exec("update user set title=? where id=? ","中",1)
//事务
tx := db.Begin()
tx.Rollback()
tx.Commit()

• 纯go语言sqlite3,可以直接驱动到goframe

    "github.com/jmoiron/sqlx"
    _ "github.com/logoove/sqlite"
    var db *sqlx.DB
    type User struct{
        Id int64 `db:"id"`
        Name string `db:"name"`
    }
    db, _ = sqlx.Open("sqlite3","./db.db")
db.Exec(`CREATE TABLE users (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "name" char(15) NOT NULL
);`)
    db.Exec(`insert into users(name) values(?)`,"李白")
    db.Exec(`insert into users(name) values(?)`,"白居易")
    rows:=[]User{}
    db.Select(&rows,"SELECT id,name FROM users ORDER BY id")
    fmt.Println(rows)

• 纯go驱动sqlite
  ~
  _ “github.com/glebarez/go-sqlite”

~