Just Do IT !

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

Golang
字数统计: 665阅读时长: 3 min
2020/04/04 Share

建库建表

新建person表

1
2
3
4
5
6
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函数:

1
2
3
4
5
6
7
8
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开启远程连接

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

重启mysql

Insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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)

}
1
2
3
4
5
6
7
8
9
mysql> select * from person;
+---------+----------+------+--------------+
| user_id | username | sex  | email        |
+---------+----------+------+--------------+
|       1 | stu001   | man  | stu01@qq.com |
+---------+----------+------+--------------+
1 row in set (0.00 sec)

mysql>

插入数据成功!

Update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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("修改成功")
}
1
2
3
4
5
6
7
8
9
mysql> select * from person;
+---------+----------+------+--------------+
| user_id | username | sex  | email        |
+---------+----------+------+--------------+
|       1 | stu003   | man  | stu01@qq.com |
+---------+----------+------+--------------+
1 row in set (0.00 sec)

mysql>

Select

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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)
}

输出:

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

Delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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")
}
1
2
3
4
mysql> select * from person;
Empty set (0.00 sec)

mysql>

删除成功!

原文作者：浩翰

原文链接：https://plutoacharon.github.io/2020/04/04/Golang语言学习从入门到实战-Go操作Mysql实现增删改查/

发表日期：April 4th 2020, 7:29:49 am

更新日期：April 4th 2020, 7:30:34 am

版权声明：本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

CATALOG
  1. 1. 建库建表
  2. 2. Golang操作Mysql数据库
    1. 2.1. 安装sqlx
    2. 2.2. 代码
      1. 2.2.1. Insert
      2. 2.2.2. Update
      3. 2.2.3. Select
      4. 2.2.4. Delete
Total : 190
2020
2019
CTF Liunx 高可用负载均衡 Java Hadoop Docker Esxi Dokcer Flume GitHub Kafka Golang Golang日志收集系统 Hbase Hive Git Kali Kubernetes Shell Python算法 Spark Sqoop Vmware 机器学习 Zookeeper PHP Hexo 安全 Python PySpark Maven 网络 数据采集 CDN AWD ZooKeeper
CTF Liunx Hadoop Docker GitHub Golang Golang日志收集系统 高可用负载均衡 Hive Java Kubernetes 其他 Python算法 Spark Vmware 机器学习 博客 Python 网络 CDN