[转帖]golang操作mysql使用总结

前言

database/sqlSQL数据库sql.DB

sql.DB 通过数据库驱动为我们提供管理底层数据库连接的打开和关闭操作.
sql.DB 为我们管理数据库连接池

需要注意的是，sql.DB表示操作数据库的抽象访问接口,而非一个数据库连接对象;它可以根据driver打开关闭数据库连接，管理连接池。正在使用的连接被标记为繁忙，用完后回到连接池等待下次使用。所以，如果你没有把连接释放回连接池，会导致过多连接使系统资源耗尽。

操作mysql

1.导入mysql数据库驱动

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

2.连接数据库

123456789101112131415161718

type DbWorker struct {    //mysql data source name    Dsn string }

func main() {    dbw := DbWorker{        Dsn: "user:password@tcp(127.0.0.1:3306)/test",    }	    db, err := sql.Open("mysql",        dbw.Dsn)    if err != nil {        panic(err)        return    }    defer db.Close()}

sql.DB指针

func Open(driverName, dataSourceName string) (*DB, error)

driverNamedataSourceName

3.数据库基本操作

数据库查询的一般步骤如下:

调用 db.Query 执行 SQL 语句, 此方法会返回一个 Rows 作为查询的结果
通过 rows.Next() 迭代查询数据.
通过 rows.Scan() 读取每一行的值
调用 db.Close() 关闭查询

user

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=3 DEFAULT CHARSET=utf8mb4

查询数据

12345678910111213141516171819202122232425262728

func (dbw *DbWorker) QueryData() {	dbw.QueryDataPre()	rows, err := dbw.Db.Query(`SELECT * From user where age >= 20 AND age < 30`)	defer rows.Close()	if err != nil {		fmt.Printf("insert data error: %v\n", err)		return	}	for rows.Next() {		rows.Scan(&dbw.UserInfo.Id, &dbw.UserInfo.Name, &dbw.UserInfo.Age)		if err != nil {			fmt.Printf(err.Error())			continue		}		if !dbw.UserInfo.Name.Valid {			dbw.UserInfo.Name.String = ""		}		if !dbw.UserInfo.Age.Valid {			dbw.UserInfo.Age.Int64 = 0		}		fmt.Println("get data, id: ", dbw.UserInfo.Id, " name: ", dbw.UserInfo.Name.String, " age: ", int(dbw.UserInfo.Age.Int64))	}

	err = rows.Err()	if err != nil {		fmt.Printf(err.Error())	}}

单行查询

var name stringerr = db.QueryRow("select name from user where id = ?", 1).Scan(&name)if err != nil {    log.Fatal(err)}fmt.Println(name)

插入数据

12345678910111213

func (dbw *DbWorker) insertData() {	ret, err := dbw.Db.Exec(`INSERT INTO user (name, age) VALUES ("xys", 23)`)	if err != nil {		fmt.Printf("insert data error: %v\n", err)		return	}	if LastInsertId, err := ret.LastInsertId(); nil == err {		fmt.Println("LastInsertId:", LastInsertId)	}	if RowsAffected, err := ret.RowsAffected(); nil == err {		fmt.Println("RowsAffected:", RowsAffected)	}}

db.Exec()errretRowsAffected

4.预编译语句(Prepared Statement)
预编译语句(PreparedStatement)提供了诸多好处, 因此我们在开发中尽量使用它. 下面列出了使用预编译语句所提供的功能:

PreparedStatement 可以实现自定义参数的查询
PreparedStatement 通常来说, 比手动拼接字符串 SQL 语句高效.
PreparedStatement 可以防止SQL注入攻击

Prepared StatementsExec()INSERTUPDATEDELETE

下面是将上述案例用Prepared Statement 修改之后的完整代码

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687

package main

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

type DbWorker struct {	Dsn      string	Db       *sql.DB	UserInfo userTB}type userTB struct {	Id   int	Name sql.NullString	Age  sql.NullInt64}

func main() {	var err error	dbw := DbWorker{		Dsn: "root:123456@tcp(localhost:3306)/sqlx_db?charset=utf8mb4",	}	dbw.Db, err = sql.Open("mysql", dbw.Dsn)	if err != nil {		panic(err)		return	}	defer dbw.Db.Close()

	dbw.insertData()	dbw.queryData()}

func (dbw *DbWorker) insertData() {	stmt, _ := dbw.Db.Prepare(`INSERT INTO user (name, age) VALUES (?, ?)`)	defer stmt.Close()

	ret, err := stmt.Exec("xys", 23)	if err != nil {		fmt.Printf("insert data error: %v\n", err)		return	}	if LastInsertId, err := ret.LastInsertId(); nil == err {		fmt.Println("LastInsertId:", LastInsertId)	}	if RowsAffected, err := ret.RowsAffected(); nil == err {		fmt.Println("RowsAffected:", RowsAffected)	}}

func (dbw *DbWorker) QueryDataPre() {	dbw.UserInfo = userTB{}}func (dbw *DbWorker) queryData() {	stmt, _ := dbw.Db.Prepare(`SELECT * From user where age >= ? AND age < ?`)	defer stmt.Close()

	dbw.QueryDataPre()

	rows, err := stmt.Query(20, 30)	defer rows.Close()	if err != nil {		fmt.Printf("insert data error: %v\n", err)		return	}	for rows.Next() {		rows.Scan(&dbw.UserInfo.Id, &dbw.UserInfo.Name, &dbw.UserInfo.Age)		if err != nil {			fmt.Printf(err.Error())			continue		}		if !dbw.UserInfo.Name.Valid {			dbw.UserInfo.Name.String = ""		}		if !dbw.UserInfo.Age.Valid {			dbw.UserInfo.Age.Int64 = 0		}		fmt.Println("get data, id: ", dbw.UserInfo.Id, " name: ", dbw.UserInfo.Name.String, " age: ", int(dbw.UserInfo.Age.Int64))	}

	err = rows.Err()	if err != nil {		fmt.Printf(err.Error())	}}

[转帖]golang操作mysql使用总结 | SQL

前言

操作mysql