第十章 Go Mysql
准备好mysql数据库服务端数据。
创建test数据库
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.04 sec)
MariaDB [(none)]> use test;
Database changed
准备好数据表 person
MariaDB [test]> CREATE TABLE `person` (
-> `user_id` int(11) NOT NULL AUTO_INCREMENT,
-> `username` varchar(260) DEFAULT NULL,
-> `sex` varchar(260) DEFAULT NULL,
-> `email` varchar(260) DEFAULT NULL,
-> PRIMARY KEY (`user_id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)
准备好place表
CREATE TABLE place (
country varchar(200),
city varchar(200),
telcode int
)ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
查看表结构
MariaDB [test]> desc person;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(260) | YES | | NULL | |
| sex | varchar(260) | YES | | NULL | |
| email | varchar(260) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)
MariaDB [test]> desc place;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| country | varchar(200) | YES | | NULL | |
| city | varchar(200) | YES | | NULL | |
| telcode | int(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1.1. mysql驱动
命令行输入 :
go get github.com/go-sql-driver/mysql
go get github.com/jmoiron/sqlx
1.2. go-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"`
}
var Db *sqlx.DB
func init() {
database, err := sqlx.Open("mysql", "root:redhat@tcp(123.206.16.61:3306)/test")
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)
}
查询数据
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"`
}
var Db *sqlx.DB
func init() {
database, err := sqlx.Open("mysql", "root:redhat@tcp(123.206.16.61:3306)/test")
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)
}
更新数据
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"`
}
var Db *sqlx.DB
func init() {
database, err := sqlx.Open("mysql", "root:redhat@tcp(123.206.16.61:3306)/test")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
func main() {
res, err := Db.Exec("update person set username=? where user_id=?", "stu0003", 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)
}
删除数据
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"`
}
var Db *sqlx.DB
func init() {
database, err := sqlx.Open("mysql", "root:redhat@tcp(123.206.16.61:3306)/test")
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
}
*/
res, err := Db.Exec("delete from person where user_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)
}
1.3. mysql事务
mysql事务特性:
1) 原子性
2) 一致性
3) 隔离性
4) 持久性
golang MySQL事务应用:
1) import (“github.com/jmoiron/sqlx")
2) Db.Begin() 开始事务
3) Db.Submit() 提交事务
4) Db.Rollback() 回滚事务