Day10 数据库驱动 sql与sqlx

本节关键词

• Go语言如何操作MySQL数据库

sql 初始化连接

包database/sql保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动

使用database/sql包时必须注入(至少)一个数据库驱动

• 安装驱动

go get -u github.com/go-sql-driver/mysql

• 具体使用

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func initMySQL() (err error) {
	// DSN:Data Source Name
	dsn := "root:123456@tcp(127.0.0.1:3306)/go_mysql?charset=utf8mb4&parseTime=True"
	// 不会校验账号密码是否正确
	// 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		fmt.Println("open err:", err)
		return err
	}
	// 文件、网络连接必备关闭操作，chan发送方也可以关闭
	// defer db.Close()  // 但是在初始化时，关闭了就不能用，灵活应用

	// 尝试与数据库建立连接（校验dsn是否正确）
	err = db.Ping()
	if err != nil {
		fmt.Println("ping err:", err)
		return err
	}

	// 设置与数据库建立连接的最大数目
	db.SetMaxOpenConns(20)
	// 设置连接池中的最大闲置连接数
	db.SetMaxIdleConns(10)

	return nil
}

func main() {
	err := initMySQL() // 调用输出化数据库的函数
	if err != nil {
		fmt.Printf("init db failed,err: %v\n", err)
		return
	}
}

/*
注意点：
1. 需要导入具体的数据库驱动，本质是注入到一个全局变量
_ "github.com/go-sql-driver/mysql"  driver.go
panic: sql: unknown driver "mysql" (forgotten import?)

func init() {
    sql.Register("mysql", &MySQLDriver{})
}

2. initMySQL 有返回值，注意不要关闭db.Close()
3. 返回的DB对象可以安全地被多个goroutine并发使用，并且维护其自己的空闲连接池。因此，Open函数应该仅被调用一次，很少需要关闭这个DB对象
*/

sql 增删改查

• 建库建表

create database if not exists go_mysql charset utf8mb4 collate utf8mb4_general_ci;
use go_mysql;
create table user(
    id int unsigned not null primary key auto_increment comment '主键',
    name varchar(64) not null comment '姓名',
    age tinyint not null default 0 comment '年龄'
)engine=innodb charset=utf8mb4 comment '用户表';

• 单行查询

// 查询单条数据示例
func queryRowDemo() {
	sqlStr := "select id, name, age from user where id=?"
	var u user
	// 非常重要：确保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 > ?"
	rows, err := db.Query(sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	// 非常重要：关闭rows释放持有的数据库链接
	defer rows.Close()

	// 循环读取结果集中的数据
	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 insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "catherine", 38)
	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)
}

// 同样适合与 update 和 delele

sql 预处理

预处理执行过程：

1. 把SQL语句分成两部分，命令部分与数据部分;
2. 先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理;
3. 然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换;
4. MySQL服务端执行完整的SQL语句并将结果返回给客户端;

// 预处理查询示例
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 stmt.Close()
	rows, err := stmt.Query(0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	// 循环读取结果集中的数据
	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)
	}
}

sql 事务

t, err := db.Begin() // 开启事务
t.Rollback()
t.Commit()

sqlx 基本使用

sqlx是 sql的超集，也需要导入具体的驱动

• 安装依赖

go get github.com/jmoiron/sqlx

• 初始化连接

import (
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)
func initDB() (err error) {
	dsn := "root:123456@tcp(127.0.0.1:3306)/go_mysql?charset=utf8mb4&parseTime=True"
	// 也可以使用MustConnect连接不成功就panic
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

• 具体使用

// 单个
var u user
err := db.Get(&u, sqlStr, 1)
// 多个
var users []user
err := db.Select(&users, sqlStr, 0)

// 增删改
sqlStr := "insert into user(name, age) values (?,?)"
ret, err := db.Exec(sqlStr, "linda", 19)

// 事务
tx, err := db.Beginx() // 开启事务
tx.Rollback()
tx.Exec()

• http://jmoiron.github.io/sqlx/ (opens new window)