henryspace

连接Mysql操作

package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

type Person struct {
	UserId   int    `db:"user_id"`
	Username string `db:"username"`
	Sex      string `db:"sex"`
	Email    string `db:"email"`
}

type Place struct {
	Country string `db:"country"`
	City    string `db:"city"`
	TelCode int    `db:"telcode"`
}

// 新增
func Insert(db *sqlx.DB) {
	r, err := db.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu002", "man", "stu01@qq.com")
	if err != nil {
		fmt.Println("exec failed, ", err)
		return
	}
	id, err := r.LastInsertId()
	if err != nil {
		fmt.Println("exec failed, ", err)
		return
	}
	fmt.Println("insert succ:", id)
}

// 查询
func Select(db *sqlx.DB) {
	var people []Person
	err := db.Select(&people, "select * from person where user_id > ?", 1)
	if err != nil {
		fmt.Println("select failed:", err)
		return
	}
	fmt.Println(people)
}

// 更新
func Update(db *sqlx.DB) {
	r, err := db.Exec("update person set username = ? where user_id = ?", "stu0022", 2)
	if err != nil {
		fmt.Println("update mysql failed,", err)
		return
	}
	num, err := r.RowsAffected()
	if err != nil {
		fmt.Println("update mysql failed,", err)
		return
	}
	fmt.Println("update success,", num)
}

// 删除
func Delete(db *sqlx.DB) {
	r, err := db.Exec("delete from person where user_id = ?", 2)
	if err != nil {
		fmt.Println("delete mysql failed,", err)
		return
	}
	num, err := r.RowsAffected()
	if err != nil {
		fmt.Println("delete mysql failed,", err)
		return
	}
	fmt.Println("delete success,", num)
}

func main() {
	Db, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return
	}
	fmt.Println(Db.Ping())
	//Insert(Db)
	//Select(Db)
	//Update(Db)
	Delete(Db)
	Select(Db)
}