目录
一、安装依赖
使用第三方开源的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
二、数据库
创建表
CREATE TABLE `demo1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT '用户名称',
`phone` bigint(11) NOT NULL DEFAULT '0' COMMENT '用户手机号',
`create_time` int(11) DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='Mysql-增删改查测试表';
三.实现代码
1.连接数据库
代码如下(示例):
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var Db *sqlx.DB
func main() {
// db, err := sqlx.Open("数据库类型", "用户名:密码@tcp(地址:端口)/数据库名")
db, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/demo")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = db
}
2.Select
查询
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type Demo1 struct {
Id int `db:"id"`
Name string `db:"name"`
Phone string `db:"phone"`
Create_time int `db:"create_time"`
}
var Db *sqlx.DB
// 初始化mysql
func init() {
// db, err := sqlx.Open("数据库类型", "用户名:密码@tcp(地址:端口)/数据库名")
database, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/demo")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
func main() {
var demo1 []Demo1
err := Db.Select(&demo1, "select id, name, phone, create_time from demo1 where id=?", 1)
if err != nil {
fmt.Println("exec failed, ", err)
return
}
fmt.Println("select succ:", demo1)
}
输出结果:
select succ: [{1 张三 15965873315 1642132195}]
2.Insert
添加
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var Db *sqlx.DB
// 初始化mysql
func init() {
// db, err := sqlx.Open("数据库类型", "用户名:密码@tcp(地址:端口)/数据库名")
database, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/demo")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
func main() {
r, err := Db.Exec("insert into demo1(name, phone, create_time)values(?, ?, ?)", "张三", "15965873315", "1642132195")
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)
}
3.Update
修改
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type Demo1 struct {
Id int `db:"id"`
Name string `db:"name"`
Phone string `db:"phone"`
Create_time int `db:"create_time"`
}
var Db *sqlx.DB
// 初始化mysql
func init() {
// db, err := sqlx.Open("数据库类型", "用户名:密码@tcp(地址:端口)/数据库名")
database, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/demo")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
func main() {
res, err := Db.Exec("update demo1 set name=? where id=?", "张哈哈", 1)
if err != nil {
fmt.Println("exec failed, ", err)
return
}
row, err := res.RowsAffected()
if err != nil {
fmt.Println("rows failed, ", err)
}
fmt.Println("update succ:", row)
}
输出结果:
第一次运行
update succ: 1
第二次运行
update succ: 0
4.Delete
删除
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type Demo1 struct {
Id int `db:"id"`
Name string `db:"name"`
Phone string `db:"phone"`
Create_time int `db:"create_time"`
}
var Db *sqlx.DB
// 初始化mysql
func init() {
// db, err := sqlx.Open("数据库类型", "用户名:密码@tcp(地址:端口)/数据库名")
database, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/demo")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
func main() {
res, err := Db.Exec("delete from demo1 where id=?", 1)
if err != nil {
fmt.Println("exec failed, ", err)
return
}
row, err := res.RowsAffected()
if err != nil {
fmt.Println("rows failed, ", err)
}
fmt.Println("delete succ: ", row)
}
输出结果:
第一次运行
delete succ: 1
第二次运行
delete succ: 0
四.MySQL事务
1.mysql事务特性
1) 原子性
2) 一致性
3) 隔离性
4) 持久性
2.golang MySQL事务应用:
1) import (“github.com/jmoiron/sqlx")
2) Db.Begin() 开始事务
3) Db.Commit() 提交事务
4) Db.Rollback() 回滚事务