05 go操作mysql(原生)

01go操作mysql.go

package main
​
// 01go操作mysql
​
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
​
/*说明
(1) 下载mysql驱动并导入包
go get -u github.com/go-sql-driver/mysql
_ "github.com/go-sql-driver/mysql"
(2) 使用mysql驱动
func Open(driverName, dataSourceName string) (*DB, error)
driverName指定的数据库，dataSourceName指定数据源。
(3) 返回的DB对象可以安全地被多个goroutine并发使用，并且维护其自己的空闲连接池。因此，Open函数应该仅被调用一次，很少需要关闭这个DB对象。
(4) bindvars(绑定变量)
1) 查询占位符 ? 在内部称为 bindvars，它非常重要，应该始终使用它们向数据库发送值，因为它们可以防止SQL注入攻击。
2) 我们任何时候都不应该自己拼接SQL语句。
3) ? 其实仅用于参数化，不允许更改SQL语句的结构，例如使用 bindvars 尝试参数化列或表名将不起作用。
*/
​
/*1.1 建库建表*/
/*
CREATE DATABASE gorm01 DEFAULT CHARSET=utf8;
​
use gorm01;
​
CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
​
insert into user(name,age) values("zhangSan",13),("liSi",14),("wangEr",12);
*/
​
/*1.2 连接mysql数据库*/
// 定义一个全局对象db
// sql.DB是表示连接的数据库对象(结构体实例)，它保存了连接数据库相关的所有信息。它内部维护着一个具有零到
// 多个底层连接的连接池，它可以安全地被多个goroutine同时使用。
var db *sql.DB
​
// 定义一个初始化数据库的函数
func initDB() (err error) {
// DSN:Data Source Name
dsn := "root:LiuChang@=123456@tcp(121.199.57.215:3306)/gorm01?charset=utf8&parseTime=True"
// 不会校验账号密码是否正确
// 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
db, err = sql.Open("mysql", dsn)
if err != nil {
    return err
}
// 尝试与数据库建立连接（校验dsn是否正确）
err = db.Ping()
if err != nil {
    return err
}
return nil
}
​
/*1.3 查询数据*/
// 定义一个结构体来存储user表的数据
type user struct {
id   int
age  int
name string
}
​
// 单行查询
// 查询单条数据示例
func queryRowDemo() {
sqlStr := "select id, name, age from user where id=?"
var u user
// 单行查询db.QueryRow()执行一次查询，并期望返回最多一行结果（即Row）。参数args表示query中的占位参数。
// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
// 表字段类型数据依次赋值到对应结构体字段类型上
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
if err != nil {
    fmt.Printf("scan failed, err:%v\n", err)
    return
}
fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}
​
// 多行查询
// 查询多条数据示例
func queryMultiRowDemo() {
sqlStr := "select id, name, age from user where id > ?"
// 多行查询db.Query()执行一次查询，返回多行结果（即Rows），一般用于执行select命令。参数args表示query中的占位参数。
rows, err := db.Query(sqlStr, 1)
if err != nil {
    fmt.Printf("query failed, err:%v\n", err)
    return
}
// 非常重要：关闭rows释放持有的数据库链接
defer func(rows *sql.Rows) {
    err = rows.Close()
    if err != nil {
        fmt.Printf("rows close failed,err:%v\n", err)
        return
    }
}(rows)
​
// 循环读取结果集中的数据
for rows.Next() {
    var u user
    // 表字段类型数据依次赋值到对应结构体字段类型上
    err = rows.Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}
}
​
/*1.4 插入数据*/
// 插入数据
func insertRowDemo() {
sqlStr := "insert into user(name, age) values (?,?)"
ret, err := db.Exec(sqlStr, "wangWu", 15)
if err != nil {
    fmt.Printf("insert failed, err:%v\n", err)
    return
}
theID, err := ret.LastInsertId() // 新插入数据的id
if err != nil {
    fmt.Printf("get lastinsert ID failed, err:%v\n", err)
    return
}
fmt.Printf("insert success, the id is %d.\n", theID)
}
​
/*1.5 更新数据*/
// 更新数据
func updateRowDemo() {
sqlStr := "update user set age=? where id = ?"
ret, err := db.Exec(sqlStr, 28, 5)
if err != nil {
    fmt.Printf("update failed, err:%v\n", err)
    return
}
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
    fmt.Printf("get RowsAffected failed, err:%v\n", err)
    return
}
fmt.Printf("update success, affected rows:%d\n", n)
}
​
/*1.6 删除数据*/
// 删除数据
func deleteRowDemo() {
sqlStr := "delete from user where id = ?"
ret, err := db.Exec(sqlStr, 5)
if err != nil {
    fmt.Printf("delete failed, err:%v\n", err)
    return
}
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
    fmt.Printf("get RowsAffected failed, err:%v\n", err)
    return
}
fmt.Printf("delete success, affected rows:%d\n", n)
}
​
/*1.7 MySQL预处理*/
// 预处理查询多行示例
func prepareQueryDemo() {
sqlStr := "select id, name, age from user where id > ?"
stmt, err := db.Prepare(sqlStr)
if err != nil {
    fmt.Printf("prepare failed, err:%v\n", err)
    return
}
defer func(stmt *sql.Stmt) {
    err = stmt.Close()
    if err != nil {
        fmt.Printf("prepare close failed,err:%v\n", err)
        return
    }
}(stmt)
​
rows, err := stmt.Query(0)
if err != nil {
    fmt.Printf("query failed, err:%v\n", err)
    return
}
defer func(rows *sql.Rows) {
    err = rows.Close()
    if err != nil {
        fmt.Printf("rows close failed,err:%v\n", err)
        return
    }
}(rows)
​
// 循环读取结果集中的数据
for rows.Next() {
    var u user
    // 表字段类型数据依次赋值到对应结构体字段类型上
    err = rows.Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}
}
​
// 插入操作的预处理
func prepareInsertDemo() {
sqlStr := "insert into user(name,age) values(?,?)"
stmt, err := db.Prepare(sqlStr)
if err != nil {
    fmt.Printf("prepare failed, err:%v\n", err)
    return
}
defer func(stmt *sql.Stmt) {
    err = stmt.Close()
    if err != nil {
        fmt.Printf("prepare close failed, err:%v\n", err)
        return
    }
}(stmt)
​
ret, err := stmt.Exec("paoPao", 22)
if err != nil {
    fmt.Printf("insert failed err:%v\n", err)
    return
}
​
thId, err := ret.LastInsertId() //新插入数据的id
if err != nil {
    fmt.Printf("get lastinsert ID failed err:%v\n", err)
    return
}
​
fmt.Printf("insert success,the id is %d.\n", thId)
}
​
func main() {
/*1.2 连接mysql数据库*/
// 调用输出化数据库的函数
err := initDB()
​
// 设置与数据库建立连接的最大数目。
// 如果n大于0且小于最大闲置连接数，会将最大闲置连接数减小到匹配最大开启连接数的限制。如果n<=0，不会限制最大开启连接数，默认为0(无限制)。
db.SetMaxOpenConns(10)
// 设置连接池中的最大闲置连接数。
// 如果n大于最大开启连接数，则新的最大闲置连接数会减小到匹配最大开启连接数的限制。如果n<=0，不会保留闲置连接。
db.SetMaxIdleConns(10)
​
if err != nil {
    fmt.Printf("init db failed,err:%v\n", err)
    return
}
​
fmt.Println("mysql数据库连接成功!")
​
/*1.3 查询数据*/
​
// 单行查询
//queryRowDemo() // id:1 name:zhangSan age:13
​
// 多行查询
//queryMultiRowDemo()
/*输出结果:
id:2 name:liSi age:14
id:3 name:wangEr age:12
*/
​
/*1.4 插入数据*/
// 插入、更新和删除操作都使用Exec方法。
​
//insertRowDemo() //insert success, the id is 5.
​
/*1.5 更新数据*/
//updateRowDemo() // update success, affected rows:1
​
/*1.6 删除数据*/
//deleteRowDemo() // delete success, affected rows:1
​
/*1.7 MySQL预处理*/
/*说明:
(1) 什么是预处理
1) 普通SQL语句执行过程
客户端对SQL语句进行占位符替换得到完整的SQL语句。
客户端发送完整SQL语句到MySQL服务端
MySQL服务端执行完整的SQL语句并将结果返回给客户端。
2) 预处理执行过程
把SQL语句分成两部分，命令部分与数据部分。
先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。
然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。
MySQL服务端执行完整的SQL语句并将结果返回给客户端。
​
(2) 为什么要预处理
优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。
避免SQL注入问题。
​
(3) Go实现MySQL预处理
database/sql中使用下面的Prepare方法来实现预处理操作。
func (db *DB) Prepare(query string) (*Stmt, error)
Prepare方法会先将sql语句发送给MySQL服务端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。
*/
​
// 预处理查询多行示例
//prepareQueryDemo()
/*输出结果
id:1 name:zhangSan age:13
id:2 name:liSi age:14
id:3 name:wangEr age:12
*/
​
// 插入操作的预处理
//prepareInsertDemo() // insert success,the id is 6.
​
}
​

02go使用sqlx操作mysql.go

package main
​
// 02go使用sqlx操作mysql
​
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
// 该库代替了"database/sql"库
"github.com/jmoiron/sqlx"
)
​
/*说明:
(1) sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。
(2) 安装sqlx并导入包
go get github.com/jmoiron/sqlx
go get -u github.com/go-sql-driver/mysql
_ "github.com/go-sql-driver/mysql"
(3) 建库建表
CREATE DATABASE gorm01 DEFAULT CHARSET=utf8;
​
use gorm01;
​
CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
​
insert into user(name,age) values("zhangSan",13),("liSi",14),("wangEr",12);
*/
​
/*2.1 连接数据库*/
var db02 *sqlx.DB
​
func initDB02() (err error) {
dsn := "root:LiuChang@=123456@tcp(121.199.57.215:3306)/gorm01?charset=utf8&parseTime=True"
// 也可以使用MustConnect连接不成功就panic
db02, err = sqlx.Connect("mysql", dsn)
if err != nil {
    fmt.Printf("connect DB failed, err:%v\n", err)
    return
}
db02.SetMaxOpenConns(20)
db02.SetMaxIdleConns(10)
return
}
​
/*2.2 查询*/
// 定义一个结构体来存储user表的数据
// 注意: 结构体字段名必须首字母大写，否则会报"scannable dest type struct with ＞1 columns (3) in result"的错误。
// 定义一个user02结构体，字段通过tag与数据库中user表的列一致
type user02 struct {
ID   int    `db:"id"`
Name string `db:"name"`
Age  int    `db:"age"`
}
​
// 查询单条数据示例
func queryRowDemo02() {
sqlStr := "select id, name, age from user where id=?"
var u user02
err := db02.Get(&u, sqlStr, 1)
if err != nil {
    fmt.Printf("get failed, err:%v\n", err)
    return
}
fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}
​
// 查询多条数据示例
func queryMultiRowDemo02() {
sqlStr := "select id, name, age from user where id > ?"
var users []user02
err := db02.Select(&users, sqlStr, 0)
if err != nil {
    fmt.Printf("query failed, err:%v\n", err)
    return
}
fmt.Printf("users:%#v\n", users)
}
​
/*2.3 插入、更新和删除*/
// sqlx中的exec方法与原生sql中的exec使用基本一致
​
// 插入数据
func insertRowDemo02() {
sqlStr := "insert into user(name, age) values (?,?)"
ret, err := db02.Exec(sqlStr, "Tom", 25)
if err != nil {
    fmt.Printf("insert failed, err:%v\n", err)
    return
}
theID, err := ret.LastInsertId() // 新插入数据的id
if err != nil {
    fmt.Printf("get lastinsert ID failed, err:%v\n", err)
    return
}
fmt.Printf("insert success, the id is %d.\n", theID)
}
​
// 更新数据
func updateRowDemo02() {
sqlStr := "update user set age=? where id = ?"
ret, err := db02.Exec(sqlStr, 27, 7)
if err != nil {
    fmt.Printf("update failed, err:%v\n", err)
    return
}
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
    fmt.Printf("get RowsAffected failed, err:%v\n", err)
    return
}
fmt.Printf("update success, affected rows:%d\n", n)
}
​
// 删除数据
func deleteRowDemo02() {
sqlStr := "delete from user where id = ?"
ret, err := db02.Exec(sqlStr, 7)
if err != nil {
    fmt.Printf("delete failed, err:%v\n", err)
    return
}
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
    fmt.Printf("get RowsAffected failed, err:%v\n", err)
    return
}
fmt.Printf("delete success, affected rows:%d\n", n)
}
​
/*2.4 NamedExec*/
// DB.NamedExec方法用来绑定SQL语句与结构体或map中的同名字段。
// 支持数据的增删改，这里以增加数据为例
func insertUserDemo02() {
sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
ret, err := db02.NamedExec(sqlStr,
    map[string]interface{}{
        "name": "Jerry",
        "age":  26,
    })
​
if err != nil {
    fmt.Printf("insert failed, err:%v\n", err)
    return
}
theID, err := ret.LastInsertId() // 新插入数据的id
if err != nil {
    fmt.Printf("get lastinsert ID failed, err:%v\n", err)
    return
}
fmt.Printf("insert success, the id is %d.\n", theID)
}
​
/*2.5 NamedQuery*/
// DB.NamedQuery方法用来绑定SQL语句与结构体或map中的同名字段。
// 支持数据的查询
​
// 使用map做命名查询
func namedQuery02() {
sqlStr := "SELECT * FROM user WHERE name=:name"
// 使用map做命名查询
rows, err := db02.NamedQuery(sqlStr, map[string]interface{}{"name": "wangEr"})
​
if err != nil {
    fmt.Printf("db.NamedQuery failed, err:%v\n", err)
    return
}
​
defer func(rows *sqlx.Rows) {
    err = rows.Close()
    if err != nil {
        fmt.Printf("db.NamedQuery close failed, err:%v\n", err)
        return
    }
}(rows)
​
for rows.Next() {
    var u user02
    err = rows.StructScan(&u)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("user:%#v\n", u)
}
}
​
// 使用结构体命名查询，根据结构体字段的db tag进行映射
func namedQuery021() {
u := user02{
    Name: "zhangSan",
}
​
sqlStr := "SELECT * FROM user WHERE name=:name"
rows, err := db02.NamedQuery(sqlStr, &u)
if err != nil {
    fmt.Printf("db.NamedQuery failed, err:%v\n", err)
    return
}
​
defer func(rows *sqlx.Rows) {
    err = rows.Close()
    if err != nil {
        fmt.Printf("db.NamedQuery close failed, err:%v\n", err)
        return
    }
}(rows)
​
for rows.Next() {
    var u user02
    err = rows.StructScan(&u)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("user:%#v\n", u)
}
}
​
/*2.6 使用NamedExec实现批量插入*/
func batchInsertUsers(users []*user02) {
sqlStr := "INSERT INTO user (name, age) VALUES (:name, :age)"
ret, err := db02.NamedExec(sqlStr, users)
​
if err != nil {
    fmt.Printf("insert failed, err:%v\n", err)
    return
}
​
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
    fmt.Printf("get RowsAffected failed, err:%v\n", err)
    return
}
fmt.Printf("insert success, affected rows:%d\n", n)
}
​
func main() {
/*2.1 连接数据库*/
err := initDB02()
if err != nil {
    fmt.Println(err)
    return
}
fmt.Println("连接mysql数据库成功!")
​
/*2.2 查询*/
// 查询单条数据示例
//queryRowDemo02() // id:1 name:zhangSan age:13
​
// 查询多条数据示例
//queryMultiRowDemo02()
/*输出结果:
users:[]main.user02{main.user02{ID:1, Name:"zhangSan", Age:13}, main.user02{ID:2, Name:"liSi", Age:14}, main.user02{ID:3, Name:"wangEr", Age:12}, main.user02{ID:6, Name:"paoPao", Age:22}}
*/
​
/*2.3 插入、更新和删除*/
// 插入数据
//insertRowDemo02() // insert success, the id is 7.
// 更新数据
//updateRowDemo02() // update success, affected rows:1
// 删除数据
//deleteRowDemo02() // delete success, affected rows:1
​
/*2.4 NamedExec*/
//insertUserDemo02() // insert success, the id is 9.
​
/*2.5 NamedQuery*/
// 使用map做命名查询
//namedQuery02()
/*输出结果:
user:main.user02{ID:3, Name:"wangEr", Age:12}
user:main.user02{ID:10, Name:"wangEr", Age:15}
*/
​
// 使用结构体命名查询，根据结构体字段的db tag进行映射
//namedQuery021()
/*输出结果:
user:main.user02{ID:1, Name:"zhangSan", Age:13}
*/
​
/*2.6 使用NamedExec实现批量插入*/
u1 := user02{Name: "jinGang", Age: 16}
u2 := user02{Name: "neZha", Age: 17}
u3 := user02{Name: "huLuWa", Age: 18}
​
users := []*user02{&u1, &u2, &u3}
batchInsertUsers(users) // insert success, affected rows:3
}
​