go操作Mysql

一. Mysql使用

        使用第三方开源mysql库：

• github.com/go-sql-driver/mysql (mysql驱动)
• github . com / jmoiron / sqlx (基于mysql驱动的封装)

go get  github . com / go - sql - driver / mysql

go get github . com / jmoiron / sqlx

go get 时的问题：

• 需要保证go环境变量GO111MODULE=on
• 需要生成go mod文件 在src文件夹下创建一个文件(如:sample-app)，使用go mod init sample-app

        链接mysql：

database, err := sqlx.Open("mysql", "root:xxx@tcp(127.0.0.1:3306)/test")
 
//database, err := sqlx.Open("数据库类型", "用户名:密码@tcp(ip:端口)/数据库名")

        1.1 Insert操作

package main
 
import (
	"fmt"
 
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)
 
var Db *sqlx.DB
 
func init() {
 
	database, err := sqlx.Open("mysql", "root:Root_123@tcp(127.0.0.1:3306)/")
	if err != nil {
		fmt.Println("open mysql fail", err)
		return
	}
 
	Db = database
}
 
func main() {
	//创建数据库
	_, err := Db.Exec("create database if not exists test")
	if err != nil {
		fmt.Println("exec fail", err)
		return
	}
	//进入数据库
	_, err = Db.Exec("use test")
	if err != nil {
		fmt.Println("exec fail", err)
		return
	}
	//创建表
	_, err = Db.Exec("create table if not exists person(username varchar(64) default \"\", sex varchar(8) default \"man\", email varchar(258))")
	if err != nil {
		fmt.Println("exec fail", err)
		return
	}
	//往数据库中插入数据
	r, err := Db.Exec("insert into person(username, sex, email)value(?, ?, ?)", "stu001", "man", "stu001@qq.com")
	if err != nil {
		fmt.Println("exec fail", err)
		return
	}
 
	id, err := r.LastInsertId()
	if err != nil {
		fmt.Println("exec fail", err)
		return
	}
	fmt.Println("insert succ: ", id)
}

        1.2 select操作

package main
 
import (
	"fmt"
 
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)
 
type Person struct {
	Id    uint32 `db:"userid"`
	Name  string `db:"username"`
	Sex   string `db:"sex"`
	Email string `db:"email"`
}
 
var Db *sqlx.DB
 
func init() {
 
	database, err := sqlx.Open("mysql", "root:Root_123@tcp(127.0.0.1:3306)/")
	if err != nil {
		fmt.Println("open mysql fail", err)
		return
	}
 
	Db = database
}
 
func main() {
	//创建数据库
	_, err := Db.Exec("create database if not exists test")
	if err != nil {
		fmt.Println("create database exec fail", err)
		return
	}
	//进入数据库
	_, err = Db.Exec("use test")
	if err != nil {
		fmt.Println("use exec fail", err)
		return
	}
	//创建表
	_, err = Db.Exec("create table if not exists person(userid int unsigned primary key, username varchar(64) default \"\", sex varchar(8) default \"man\", email varchar(258))")
	if err != nil {
		fmt.Println("create table exec fail", err)
		return
	}
	//往数据库中插入数据
	//var r sql.Result
	for i := 0; i < 5; i++ {
		name := fmt.Sprintf("stu%v", i)
		email := fmt.Sprintf("stu%v@qq.com", i)
		_, err := Db.Exec("insert into person(userid, username, sex, email)value(?, ?, ?, ?)", i, name, "man", email)
		if err != nil {
			fmt.Println("exec fail", err)
			return
		}
	}
 
	//select 语句
	var person []Person
	err = Db.Select(&person, "select userid, username, sex, email from person where userid=?", 1)
	if err != nil {
		fmt.Println("select fail", err)
		return
	}
 
	fmt.Println("insert succ: ", person)
}

        1.3 Update操作

test库的person表：

package main
 
import (
	"fmt"
 
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)
 
var Db *sqlx.DB
 
func init() {
 
	database, err := sqlx.Open("mysql", "root:Root_123@tcp(127.0.0.1:3306)/test")
	if err != nil {
		fmt.Println("open mysql fail", err)
		return
	}
 
	Db = database
}
 
func main() {
	//update 语句
	res, err := Db.Exec("update person set username=? where userid=?", "stu111", 1)
	if err != nil {
		fmt.Println("exec update fail", err)
		return
	}
 
	row, err := res.RowsAffected()
	if err != nil {
		fmt.Println("rows fail, ", err)
	}
	fmt.Println("update suc: ", row)
}

        运行之后输出结果和表： 

        1.4 Delete操作 

删除前的表：

package main
 
import (
	"fmt"
 
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)
 
var Db *sqlx.DB
 
func init() {
 
	database, err := sqlx.Open("mysql", "root:Root_123@tcp(127.0.0.1:3306)/test")
	if err != nil {
		fmt.Println("open mysql fail", err)
		return
	}
 
	Db = database
}
 
func main() {
	//delete 语句
	res, err := Db.Exec("delete from person where userid=?", 1)
	if err != nil {
		fmt.Println("exec delete fail", err)
		return
	}
 
	row, err := res.RowsAffected()
	if err != nil {
		fmt.Println("rows fail, ", err)
	}
	fmt.Println("update suc: ", row)
}

      运行之后输出结果和表：  

 二. Mysql事务

        Mysql事务特性：

• 原子性
• 一致性
• 隔离性
• 持久性

        golang Mysql事务应用：

import ("github.com/jmoiron/sqlx")
 
Db.Begin()     //开始事务
Db.Commit()    //提交事务
Db.Rollback()  //回滚事务

        执行代码前表：

package main
 
import (
	"fmt"
 
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)
 
var Db *sqlx.DB
 
func init() {
 
	database, err := sqlx.Open("mysql", "root:Root_123@tcp(127.0.0.1:3306)/test")
	if err != nil {
		fmt.Println("open mysql fail", err)
		return
	}
 
	Db = database
}
 
func main() {
	conn, err := Db.Begin()
	if err != nil {
		fmt.Println("begin fail")
		return
	}
 
	r, err := conn.Exec("insert into person(userid, username, sex, email) value(?, ?, ?, ?)", 10, "stu10", "man", "stu10@qq.com")
	if err != nil {
		fmt.Println("exec insert fail ", err)
		conn.Rollback()
		return
	}
	id, err := r.LastInsertId()
	if err != nil {
		fmt.Println("exec fail ", err)
		conn.Rollback()
		return
	}
	fmt.Println("insert succ ", id)
 
	r, err = conn.Exec("insert into person(userid, username, sex, email) value(?, ?, ?, ?)", 20, "stu20", "man", "stu20@qq.com")
	if err != nil {
		fmt.Println("exec insert fail ", err)
		conn.Rollback()
		return
	}
	id, err = r.LastInsertId()
	if err != nil {
		fmt.Println("exec fail ", err)
		conn.Rollback()
		return
	}
	fmt.Println("insert succ ", id)
 
	conn.Commit()
}

         执行代码后输出和表：