package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "time"
    "xorm.io/xorm"
)

func MysqlDbContent() {
    //数据库基本信息
    var (
       userName  string = "root"
       password  string = "12345678"
       ipAddress string = "127.0.0.1"
       port      int    = 3306
       dbName    string = "dt"
       charset   string = "utf8mb4"
    )
    //构建数据库连接信息
    dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)

    engine, err := xorm.NewEngine("mysql", dataSourceName)
    if err != nil {
       fmt.Println("数据库连接失败:")
    }
    fmt.Println("数据库连接成功")

    type User struct {
       Id      int64
       Name    string
       Avatar  string `xorm:"varchar(200)"`
       Age     int
       Passwd  string    `xorm:"varchar(200)"`
       Created time.Time `xorm:"created"`
       Updated time.Time `xorm:"updated"`
    }

    err = engine.Sync(new(User))
    if err != nil {
       fmt.Println("表结构同步失败")
    }
    fmt.Println("表结构同步成功")

}

数据库增删改查

package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
	"xorm.io/xorm"
)

func MysqlDbContent() {
	//数据库基本信息
	var (
		userName  string = "root"
		password  string = "12345678"
		ipAddress string = "127.0.0.1"
		port      int    = 3306
		dbName    string = "dt"
		charset   string = "utf8mb4"
	)
	//构建数据库连接信息
	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)

	engine, err := xorm.NewEngine("mysql", dataSourceName)
	if err != nil {
		fmt.Println("数据库连接失败:")
	}
	fmt.Println("数据库连接成功")

	type User struct {
		Id      int64
		Name    string
		Avatar  string `xorm:"varchar(200)"`
		Age     int
		Passwd  string    `xorm:"varchar(200)"`
		Created time.Time `xorm:"created"`
		Updated time.Time `xorm:"updated"`
	}

	err = engine.Sync(new(User))
	if err != nil {
		fmt.Println("表结构同步失败")
	}
	fmt.Println("表结构同步成功")

	//插入数据 插入 对象,返回值：受影响行数
	user1 := User{Id: 1001, Name: "张三1", Avatar: "http://www.baidu.com", Age: 20, Passwd: "12345678", Created: time.Now(), Updated: time.Now()}
	user2 := User{Id: 1002, Name: "张三2", Avatar: "http://www.baidu.com", Age: 20, Passwd: "12345678", Created: time.Now(), Updated: time.Now()}
	user3 := User{Id: 1003, Name: "张三3", Avatar: "http://www.baidu.com", Age: 20, Passwd: "12345678", Created: time.Now(), Updated: time.Now()}
	insert, _ := engine.Insert(&user1, &user2, &user3)
	if insert > 1 {
		fmt.Println("数据插入成功")
	}

	var users []User
	users = append(users, User{Id: 1004, Name: "张1", Avatar: "http://www.baidu.com", Age: 20, Passwd: "12345678", Created: time.Now(), Updated: time.Now()})
	users = append(users, User{Id: 1005, Name: "张2", Avatar: "http://www.baidu.com", Age: 20, Passwd: "12345678", Created: time.Now(), Updated: time.Now()})
	users = append(users, User{Id: 1006, Name: "张3", Avatar: "http://www.baidu.com", Age: 20, Passwd: "12345678", Created: time.Now(), Updated: time.Now()})
	//批量插入数据  Insert
	i, err := engine.Insert(&users)
	if i > 1 {
		fmt.Println("批量数据插入成功")
	}
	//Update
	//查询id=1000的数据并修改name字段
	user := User{Name: "laozhang"}
	update, err := engine.ID(1000).Update(&user)
	fmt.Println(update)

	//Delete
	user1 = User{Name: "张1"}
	i2, _ := engine.ID(1004).Delete(&user1)
	fmt.Println(i2)

	engine.Exec("update user set age = ? where id = ?", 10, 1003)

	//查询
	results, _ := engine.Query("select * from user")
	fmt.Println(results)
	results2, _ := engine.QueryString("select * from user")
	fmt.Println(results2)
	results3, _ := engine.QueryInterface("select * from user")
	fmt.Println(results3)

	//Get  select * from user limit 1
	user11 := User{}
	engine.Get(&user11)
	fmt.Println(user11)

	//指定条件查询用户
	user12 := User{Name: "张1"}
	engine.Where("name = ?", user1.Name).Desc("id").Get(&user12)
	fmt.Println(user12)

	//获取指定字段的值
	var name string
	engine.Table(&user).Where("id = ?", 1002).Cols("name").Get(&name)
	fmt.Println(name)

	//Find 查询多条记录
	var users112 []User
	engine.Where("password=12345678").And("age=20").Limit(10, 0).Find(&users112)
	fmt.Println(users112)

	//Count 统计记录数
	user113 := User{Passwd: "12345678"}
	count, _ := engine.Count(&user113)
	fmt.Println(count)

	//Iterate 和 Rows 根据条件遍历数据库，可以有两种方式: Iterate and Rows
	engine.Iterate(&User{Passwd: "12345678"}, func(idx int, bean interface{}) error {
		user := bean.(*User)
		fmt.Println(user)
		return nil
	})
	rows, _ := engine.Rows(&User{Passwd: "12345678"})
	defer rows.Close()
	userBean := new(User)
	for rows.Next() {
		rows.Scan(userBean)
		fmt.Println(userBean)

	}
}