Golang语言学习从入门到实战----Go操作Mysql实现增删改查

Hello，我是普通Gopher，00后男孩，极致的共享主义者，想要成为一个终身学习者。专注于做最通俗易懂的计算机基础知识类公众号。每天推送Golang技术干货，内容起于K8S而不止于K8S，涉及Docker、微服务、DevOps、数据库、虚拟化等云计算内容及SRE经验总结
=======================
初次见面，我为你准备了100G学习大礼包：
1、《百余本最新计算机电子图书》
2、《30G Golang学习视频》
3、《20G Java学习视频》
4、《90G Liunx高级学习视频》
5、《10G 算法（含蓝桥杯真题）学习视频》
6、《英语四级，周杰伦歌曲免费送！》
路过麻烦动动小手，点个关注，持续更新技术文章与资料！

建库建表

新建person表

create table person(
user_id int primary key auto_increment,
username varchar(260),
sex varchar(260),
email varchar(260)
);

Golang操作Mysql数据库

安装sqlx

go get github.com/jmoiron/sqlx,

sqlx是一个go语言包，在内置database／sql包之上增加了很多扩展，简化数据库操作代码的书写

代码

定义初始化mysql函数:

func init() {
	database, err := sqlx.Open("mysql", "root:123456@tcp(192.168.0.131:3306)/goTest")
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return
	}
	Db = database
}

注意: MySQL ERROR 1130 (HY000): Host 'XXXX' is not allowed to connect to this MySQL server要Mysql开启远程连接

use mysql;
update user set host = '%' where user = 'root';
flush privileges;

重启mysql

Insert

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"`
}


var Db *sqlx.DB

func init() {
	database, err := sqlx.Open("mysql", "root:123456@tcp(192.168.0.131:3306)/goTest")
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return
	}
	Db = database
}

func main() {
	r, err := Db.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "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)

}

mysql> select * from person;
+---------+----------+------+--------------+
| user_id | username | sex  | email        |
+---------+----------+------+--------------+
|       1 | stu001   | man  | stu01@qq.com |
+---------+----------+------+--------------+
1 row in set (0.00 sec)

mysql> 

插入数据成功!

Update

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"`
}


var Db *sqlx.DB

func init() {
	database, err := sqlx.Open("mysql", "root:123456@tcp(192.168.0.131:3306)/goTest")
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return
	}
	Db = database
}

func main() {

	_, err := Db.Exec("update person set username=? where user_id=?", "stu003", 1)
	if err != nil {
		fmt.Println("exec failed, ", err)
		return
	}

	fmt.Println("修改成功")
}

mysql> select * from person;
+---------+----------+------+--------------+
| user_id | username | sex  | email        |
+---------+----------+------+--------------+
|       1 | stu003   | man  | stu01@qq.com |
+---------+----------+------+--------------+
1 row in set (0.00 sec)

mysql> 

Select

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"`
}


var Db *sqlx.DB

func init() {
	database, err := sqlx.Open("mysql", "root:123456@tcp(192.168.0.131:3306)/goTest")
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return
	}
	Db = database
}

func main() {

	var person []Person
	err := Db.Select(&person, "select user_id, username, sex, email from person where user_id=?", 1)
	if err != nil {
		fmt.Println("exec failed, ", err)
		return
	}

	fmt.Println("select succ:", person)
}

输出:

select succ: [{1 stu003 man stu01@qq.com}]

Delete

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"`
}


var Db *sqlx.DB

func init() {
	database, err := sqlx.Open("mysql", "root:123456@tcp(192.168.0.131:3306)/goTest")
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return
	}
	Db = database
}

func main() {

	_, err := Db.Exec("delete from person where user_id=?", 1)
	if err != nil {
		fmt.Println("exec failed, ", err)
		return
	}

	fmt.Println("delete succ")
}

mysql> select * from person;
Empty set (0.00 sec)

mysql> 

删除成功!