准备mysql数据库
# Host: localhost (Version: 5.7.26)
# Date: 2022-03-07 09:24:26
# Generator: MySQL-Front 5.3 (Build 4.234)
/*!40101 SET NAMES utf8 */;
#
# Structure for table "user_tb1"
#
DROP TABLE IF EXISTS `user_tb1`;
CREATE TABLE `user_tb1` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
#
# Data for table "user_tb1"
#
INSERT INTO `user_tb1` VALUES (1,'王帆','123',1),(2,'我是王帆','456',2);
安装配置mysql驱动
安装驱动
go get -u github.com/go-sql-driver/mysql
初始化模块
go mod init m
执行go mod tidy
go mod tidy
导入驱动
package main
import (
"database/sql"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
}
获得数据库连接
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB
func initDB() (err error) {
// dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4&parseTime=True"
dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4"
// open函数只是验证格式是否正确,并不是创建数据库连接
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 与数据库建立连接
err2 := db.Ping()
if err2 != nil {
return err2
}
return nil
}
func main() {
err := initDB()
if err != nil {
fmt.Printf("err: %v\n", err)
} else {
fmt.Println("连接成功")
}
fmt.Printf("db: %v\n", db)
}
插入数据
插入一条数据
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB
func initDB() (err error) {
// dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4&parseTime=True"
dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4"
// open函数只是验证格式是否正确,并不是创建数据库连接
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 与数据库建立连接
err2 := db.Ping()
if err2 != nil {
return err2
}
return nil
}
func insertData() {
sqlStr := "insert into user_tb1(username,password,status) values (?,?,?)"
r, err := db.Exec(sqlStr, "我叫王帆", "789", 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
i2, err2 := r.LastInsertId()
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("i2: %v\n", i2)
}
func main() {
err := initDB()
if err != nil {
fmt.Printf("err: %v\n", err)
} else {
fmt.Println("连接成功")
}
fmt.Printf("db: %v\n", db)
insertData()
}
插入多条数据
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB
func initDB() (err error) {
// dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4&parseTime=True"
dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4"
// open函数只是验证格式是否正确,并不是创建数据库连接
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 与数据库建立连接
err2 := db.Ping()
if err2 != nil {
return err2
}
return nil
}
func insertData() {
sqlStr := "insert into user_tb1(username,password,status) values (?,?,?)"
r, err := db.Exec(sqlStr, "我叫王帆", "789", 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
i2, err2 := r.LastInsertId()
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("i2: %v\n", i2)
}
func insertManyData() {
data := []map[string]string{
{"username": "1", "password": "1", "status": "1"},
{"username": "2", "password": "2", "status": "2"},
{"username": "3", "password": "3", "status": "1"},
}
sqlStr := "insert into user_tb1(username,password,status) values "
for k, v := range data {
if k == 0 {
sqlStr += fmt.Sprintf("(%v,%v,%v)", v["username"], v["password"], v["status"])
} else {
sqlStr += fmt.Sprintf(",(%v,%v,%v)", v["username"], v["password"], v["status"])
}
}
fmt.Printf("sqlStr: %v\n", sqlStr)
r, err := db.Exec(sqlStr)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
fmt.Println(r.RowsAffected())
}
func main() {
err := initDB()
if err != nil {
fmt.Printf("err: %v\n", err)
} else {
fmt.Println("连接成功")
}
fmt.Printf("db: %v\n", db)
insertManyData()
}
查询数据
查询一条数据
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB
func initDB() (err error) {
// dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4&parseTime=True"
dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4"
// open函数只是验证格式是否正确,并不是创建数据库连接
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 与数据库建立连接
err2 := db.Ping()
if err2 != nil {
return err2
}
return nil
}
func insertData() {
sqlStr := "insert into user_tb1(username,password,status) values (?,?,?)"
r, err := db.Exec(sqlStr, "我叫王帆", "789", 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
i2, err2 := r.LastInsertId()
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("i2: %v\n", i2)
}
func insertManyData() {
data := []map[string]string{
{"username": "1", "password": "1", "status": "1"},
{"username": "2", "password": "2", "status": "2"},
{"username": "3", "password": "3", "status": "1"},
}
sqlStr := "insert into user_tb1(username,password,status) values "
for k, v := range data {
if k == 0 {
sqlStr += fmt.Sprintf("(%v,%v,%v)", v["username"], v["password"], v["status"])
} else {
sqlStr += fmt.Sprintf(",(%v,%v,%v)", v["username"], v["password"], v["status"])
}
}
fmt.Printf("sqlStr: %v\n", sqlStr)
r, err := db.Exec(sqlStr)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
fmt.Println(r.RowsAffected())
}
type user struct {
id int
username string
password string
status int
}
func queryRowData() {
sqlStr := "select Id,username,password,status from user_tb1 where id=?"
var u user
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.username, &u.password, &u.status)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
fmt.Printf("Id:%d username:%v password:%v status:%d", u.id, u.username, u.password, u.status)
}
func main() {
err := initDB()
if err != nil {
fmt.Printf("err: %v\n", err)
} else {
fmt.Println("连接成功")
}
fmt.Printf("db: %v\n", db)
queryRowData()
}
查询多条数据
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB
func initDB() (err error) {
// dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4&parseTime=True"
dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4"
// open函数只是验证格式是否正确,并不是创建数据库连接
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 与数据库建立连接
err2 := db.Ping()
if err2 != nil {
return err2
}
return nil
}
func insertData() {
sqlStr := "insert into user_tb1(username,password,status) values (?,?,?)"
r, err := db.Exec(sqlStr, "我叫王帆", "789", 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
i2, err2 := r.LastInsertId()
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("i2: %v\n", i2)
}
func insertManyData() {
data := []map[string]string{
{"username": "1", "password": "1", "status": "1"},
{"username": "2", "password": "2", "status": "2"},
{"username": "3", "password": "3", "status": "1"},
}
sqlStr := "insert into user_tb1(username,password,status) values "
for k, v := range data {
if k == 0 {
sqlStr += fmt.Sprintf("(%v,%v,%v)", v["username"], v["password"], v["status"])
} else {
sqlStr += fmt.Sprintf(",(%v,%v,%v)", v["username"], v["password"], v["status"])
}
}
fmt.Printf("sqlStr: %v\n", sqlStr)
r, err := db.Exec(sqlStr)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
fmt.Println(r.RowsAffected())
}
type user struct {
id int
username string
password string
status int
}
func queryRowData() {
sqlStr := "select Id,username,password,status from user_tb1 where id=?"
var u user
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.username, &u.password, &u.status)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
fmt.Printf("Id:%d username:%v password:%v status:%d", u.id, u.username, u.password, u.status)
}
func queryManyData() {
sqlStr := "select Id,username,password,status from user_tb1 where status = ?"
r, err := db.Query(sqlStr, 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
defer r.Close()
// 循环读取结果集中的数据
for r.Next() {
var u user
err2 := r.Scan(&u.id, &u.username, &u.password, &u.status)
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("Id:%d username:%v password:%v status:%d\n", u.id, u.username, u.password, u.status)
}
}
func main() {
err := initDB()
if err != nil {
fmt.Printf("err: %v\n", err)
} else {
fmt.Println("连接成功")
}
fmt.Printf("db: %v\n", db)
queryManyData()
}
稍加修改
func queryManyData1() {
sqlStr := "select Id,username,password,status from user_tb1 where status = ?"
r, err := db.Query(sqlStr, 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
defer r.Close()
users := make([]user, 0)
// 循环读取结果集中的数据
for r.Next() {
var u user
err2 := r.Scan(&u.id, &u.username, &u.password, &u.status)
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
users = append(users, u)
}
fmt.Printf("users: %+v\n", users)
}
更新数据
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB
func initDB() (err error) {
// dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4&parseTime=True"
dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4"
// open函数只是验证格式是否正确,并不是创建数据库连接
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 与数据库建立连接
err2 := db.Ping()
if err2 != nil {
return err2
}
return nil
}
func insertData() {
sqlStr := "insert into user_tb1(username,password,status) values (?,?,?)"
r, err := db.Exec(sqlStr, "我叫王帆", "789", 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
i2, err2 := r.LastInsertId()
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("i2: %v\n", i2)
}
func insertManyData() {
data := []map[string]string{
{"username": "1", "password": "1", "status": "1"},
{"username": "2", "password": "2", "status": "2"},
{"username": "3", "password": "3", "status": "1"},
}
sqlStr := "insert into user_tb1(username,password,status) values "
for k, v := range data {
if k == 0 {
sqlStr += fmt.Sprintf("(%v,%v,%v)", v["username"], v["password"], v["status"])
} else {
sqlStr += fmt.Sprintf(",(%v,%v,%v)", v["username"], v["password"], v["status"])
}
}
fmt.Printf("sqlStr: %v\n", sqlStr)
r, err := db.Exec(sqlStr)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
fmt.Println(r.RowsAffected())
}
type user struct {
id int
username string
password string
status int
}
func queryRowData() {
sqlStr := "select Id,username,password,status from user_tb1 where id=?"
var u user
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.username, &u.password, &u.status)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
fmt.Printf("Id:%d username:%v password:%v status:%d", u.id, u.username, u.password, u.status)
}
func queryManyData() {
sqlStr := "select Id,username,password,status from user_tb1 where status = ?"
r, err := db.Query(sqlStr, 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
defer r.Close()
// 循环读取结果集中的数据
for r.Next() {
var u user
err2 := r.Scan(&u.id, &u.username, &u.password, &u.status)
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("Id:%d username:%v password:%v status:%d\n", u.id, u.username, u.password, u.status)
}
}
func queryManyData1() {
sqlStr := "select Id,username,password,status from user_tb1 where status = ?"
r, err := db.Query(sqlStr, 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
defer r.Close()
users := make([]user, 0)
// 循环读取结果集中的数据
for r.Next() {
var u user
err2 := r.Scan(&u.id, &u.username, &u.password, &u.status)
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
users = append(users, u)
}
fmt.Printf("users: %+v\n", users)
}
func updateData() {
strSql := "update user_tb1 set username=?,password=? where id=?"
r, err := db.Exec(strSql, "name_update", "pwd_update", 1)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
i2, err2 := r.RowsAffected()
if err != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("i2: %v\n", i2)
}
func main() {
err := initDB()
if err != nil {
fmt.Printf("err: %v\n", err)
} else {
fmt.Println("连接成功")
}
fmt.Printf("db: %v\n", db)
updateData()
}
删除数据
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB
func initDB() (err error) {
// dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4&parseTime=True"
dsn := "root:root@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4"
// open函数只是验证格式是否正确,并不是创建数据库连接
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 与数据库建立连接
err2 := db.Ping()
if err2 != nil {
return err2
}
return nil
}
func insertData() {
sqlStr := "insert into user_tb1(username,password,status) values (?,?,?)"
r, err := db.Exec(sqlStr, "我叫王帆", "789", 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
i2, err2 := r.LastInsertId()
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("i2: %v\n", i2)
}
func insertManyData() {
data := []map[string]string{
{"username": "1", "password": "1", "status": "1"},
{"username": "2", "password": "2", "status": "2"},
{"username": "3", "password": "3", "status": "1"},
}
sqlStr := "insert into user_tb1(username,password,status) values "
for k, v := range data {
if k == 0 {
sqlStr += fmt.Sprintf("(%v,%v,%v)", v["username"], v["password"], v["status"])
} else {
sqlStr += fmt.Sprintf(",(%v,%v,%v)", v["username"], v["password"], v["status"])
}
}
fmt.Printf("sqlStr: %v\n", sqlStr)
r, err := db.Exec(sqlStr)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
fmt.Println(r.RowsAffected())
}
type user struct {
id int
username string
password string
status int
}
func queryRowData() {
sqlStr := "select Id,username,password,status from user_tb1 where id=?"
var u user
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.username, &u.password, &u.status)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
fmt.Printf("Id:%d username:%v password:%v status:%d", u.id, u.username, u.password, u.status)
}
func queryManyData() {
sqlStr := "select Id,username,password,status from user_tb1 where status = ?"
r, err := db.Query(sqlStr, 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
defer r.Close()
// 循环读取结果集中的数据
for r.Next() {
var u user
err2 := r.Scan(&u.id, &u.username, &u.password, &u.status)
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("Id:%d username:%v password:%v status:%d\n", u.id, u.username, u.password, u.status)
}
}
func queryManyData1() {
sqlStr := "select Id,username,password,status from user_tb1 where status = ?"
r, err := db.Query(sqlStr, 2)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
defer r.Close()
users := make([]user, 0)
// 循环读取结果集中的数据
for r.Next() {
var u user
err2 := r.Scan(&u.id, &u.username, &u.password, &u.status)
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return
}
users = append(users, u)
}
fmt.Printf("users: %+v\n", users)
}
func updateData() {
strSql := "update user_tb1 set username=?,password=? where id=?"
r, err := db.Exec(strSql, "name_update", "pwd_update", 1)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
i2, err2 := r.RowsAffected()
if err != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("i2: %v\n", i2)
}
func delData() {
strSql := "delete from user_tb1 where id = ?"
r, err := db.Exec(strSql, 1)
if err != nil {
fmt.Printf("err: %v\n", err)
return
}
i2, err2 := r.RowsAffected()
if err != nil {
fmt.Printf("err2: %v\n", err2)
return
}
fmt.Printf("i2: %v\n", i2)
}
func main() {
err := initDB()
if err != nil {
fmt.Printf("err: %v\n", err)
} else {
fmt.Println("连接成功")
}
fmt.Printf("db: %v\n", db)
delData()
}
事务处理结合预处理
// 事务处理结合预处理
func transaction() {
tx, _ := db.Begin()
// 新增用户
s, _ := db.Prepare("insert into user_tb1(username,password,status) values (?,?,?)")
r, _ := s.Exec("t_username", "t_password", 2)
i2, _ := r.LastInsertId()
fmt.Printf("insert-i2: %v\n", i2)
// 更新
r2, _ := tx.Exec("update user_tb1 set username = 'w1' where id=?", 4)
i3, _ := r2.RowsAffected()
fmt.Printf("update-i3: %v\n", i3)
r3, _ := tx.Exec("update user_tb1 set username = 'w1' where id=?", 5)
i4, _ := r3.RowsAffected()
fmt.Printf("update-i4: %v\n", i4)
if i2 > 0 && i3 > 0 && i4 > 0 {
tx.Commit()
} else {
tx.Rollback()
}
}
i2那块改成获取受影响的行数也行 RowsAffected
执行前后数据库数据对比