【Golang | Database】通过database/sql包的query和exec方法操作mysql

环境：

• Golang：go1.18.2 windows/amd64
• github.com/go-sql-driver/mysql：1.6.0

1. 简介

当我们想执行一些mysql语句同时需要获得返回值时（例如SELECT），可以使用database/sql包里的Query方法；如果不需要获取返回值（例如INSERT,DELETE,UPDATE），可以使用Exec方法

2. 准备

2.1 如果您当前环境没有mysql，建议使用容器方式部署，可参考我之前的文章【Golang | Database】利用database/sql访问容器化的MySQL
2.2 使用go get github.com/go-sql-driver/mysql下载mysql驱动，用作插件注册
2.3 新建一个数据库gosql

mysql> create database gosql;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| gosql              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

3. 示例

3.1 连接数据库gosql

导入database/sql和github.com/go-sql-driver/mysql，并使用sql.Open建立与数据库gosql的连接

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

// 此处192.168.67.92是容器所在主机的ip，1234是容器在主机上的映射端口
// docker run --name mysql -d -p 1234:3306 -e MYSQL_ROOT_PASSWORD=root docker.io/mysql:latest
db, err := sql.Open("mysql", "root:root@tcp(192.168.67.92:1234)/gosql")
if err != nil {
	log.Panic(err)
}
// 使用Ping判断连接是否正常
if err := db.Ping(); err != nil {
	log.Panic(err)
}

3.2 使用Exec新建一个表users，并插入两条记录

如果直接用SQL语句新建一个table，形如：

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username TEXT NOT NULL,
    password TEXT NOT NULL,
    created_at DATETIME,
    PRIMARY KEY (id)
);

如果用go语句来新建table，可以使用Exec方法

	query := `
	CREATE TABLE users (
	   id INT AUTO_INCREMENT,
	   username TEXT NOT NULL,
	   password TEXT NOT NULL,
	   created_at DATETIME,
	   PRIMARY KEY (id)
	);`
	_, err = db.Exec(query)
	if err != nil {
		log.Panic(err)
	}

插入两个user记录

import "time"

_, err = db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, "foo", "foo", time.Now())
result, err := db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, "bar", "bar", time.Now())

注：返回值result是一个接口，有两个方法LastInsertId() (int64, error)（一般等于"auto increment"属性列的值）RowsAffected() (int64, error)（执行update, insert, or delete等操作后影响的行数）

3.3 使用Query获取多行记录

使用Query方法获取多行记录，并使用Scan方法将需要的值保存到变量中

	rows, _ := db.Query("select id, username from users")
	for rows.Next() {
		var name string
		var id int
		rows.Scan(&id, &name)
		fmt.Printf("id=%d的用户名是：%s\n", id, name)
	}

3.4 使用QueryRow获取单行记录

	// 单行指定查询，获取id=1的username
	var nameSpecified string
	db.QueryRow("select username from users where id= ?", 1).Scan(&nameSpecified)
	fmt.Printf("指定id=1获取的用户名是：%s\n", nameSpecified)

3.5 完整代码

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"time"
)

func main() {
	// 此处192.168.67.92是容器所在主机的ip，1234是容器在主机上的映射端口
	// docker run --name mysql -d -p 1234:3306 -e MYSQL_ROOT_PASSWORD=root docker.io/mysql:latest
	db, err := sql.Open("mysql", "root:root@tcp(192.168.67.92:1234)/gosql")
	if err != nil {
		log.Panic(err)
	}
	// 使用Ping判断连接是否正常
	if err := db.Ping(); err != nil {
		log.Panic(err)
	}
	defer db.Close()
	
	// 新建table
	query := `
	CREATE TABLE users (
	   id INT AUTO_INCREMENT,
	   username TEXT NOT NULL,
	   password TEXT NOT NULL,
	   created_at DATETIME,
	   PRIMARY KEY (id)
	);`
	_, err = db.Exec(query)
	if err != nil {
		log.Panic(err)
	}
	
	// 插入两个user记录
	_, err = db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, "foo", "foo", time.Now())
	result, err := db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, "bar", "bar", time.Now())
	lastInsertId, _ := result.LastInsertId()
	rowsAffected, _ := result.RowsAffected()
	fmt.Printf("最后插入的id是：%d\n", lastInsertId)
	fmt.Printf("insert后影响的行数：%d\n", rowsAffected)
	
	// 多行查询
	rows, _ := db.Query("select id, username from users")
	for rows.Next() {
		var name string
		var id int
		rows.Scan(&id, &name)
		fmt.Printf("id=%d的用户名是：%s\n", id, name)
	}
	
	// 单行指定查询，获取id=1的username
	var nameSpecified string
	db.QueryRow("select username from users where id= ?", 1).Scan(&nameSpecified)
	fmt.Printf("指定id=1获取的用户名是：%s\n", nameSpecified)
}

执行后，数据库中内容如下

mysql> select * from users;
+----+----------+----------+---------------------+
| id | username | password | created_at          |
+----+----------+----------+---------------------+
|  1 | foo      | foo      | 2022-06-08 15:59:35 |
|  2 | bar      | bar      | 2022-06-08 15:59:35 |
+----+----------+----------+---------------------+
2 rows in set (0.00 sec)

mysql>

代码的返回值如下

最后插入的id是：2
insert后影响的行数：1      
id=1的用户名是：foo        
id=2的用户名是：bar        
指定id=1获取的用户名是：foo

4. 总结

• 使用Exec执行不需要返回值的SQL语句，使用Query执行需要返回值的SQL语句
• 使用Scan将Query获取的返回值保存到变量中