go 通过sql操作mysql

GoLang学习更方便，一文在手，面试我有
GoLang面试 | GoLang面试 | GoLang面试

go 通过sql操作mysql

亲测、2022/08/15 20:53 北京朝阳

@

• 前言
• go代码
• sql
• 汇总-增删改查

前言

表格

结果

go代码

package main

import (
	"database/sql"
	"fmt"
	"time"

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

var db *sql.DB   //全局变量client

func initMySQL() (err error) {
	dsn := "root:123456@tcp(127.0.0.1:3306)/test"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	err = db.Ping() //检测是否连接成功
	if err != nil {
		return
	}
	db.SetMaxOpenConns(200)                 //最大连接数
	db.SetMaxIdleConns(10)                  //连接池里最大空闲连接数。必须要比maxOpenConns小
	db.SetConnMaxLifetime(time.Second * 10) //最大存活保持时间
	db.SetConnMaxIdleTime(time.Second * 10) //最大空闲保持时间
	return
}

func main() {
	if err := initMySQL(); err != nil {
		fmt.Printf("connect to db failed,err:%v\n", err)
	} else {
		fmt.Println("connect to db success")
	}

	sqlStr := "SELECT id, name FROM sys_user WHERE id=?"
	var u user
	//非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name)
	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)

	defer db.Close()

}

//user结构体
type user struct {
	id int
	name string
}

sql

database: test

CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

汇总-增删改查

结果：

package main

import (
	"database/sql"
	"fmt"
	"time"

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

var db *sql.DB

func initMySQL() (err error) {
	dsn := "root:1234qwer@tcp(bj-cdb-mw08tjgs.sql.tencentcdb.com:60042)/test"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	err = db.Ping()
	if err != nil {
		return
	}
	db.SetMaxOpenConns(200)                 //最大连接数
	db.SetMaxIdleConns(10)                  //连接池里最大空闲连接数。必须要比maxOpenConns小
	db.SetConnMaxLifetime(time.Second * 10) //最大存活保持时间
	db.SetConnMaxIdleTime(time.Second * 10) //最大空闲保持时间
	return
}

func main() {
	if err := initMySQL(); err != nil {
		fmt.Printf("connect to db failed,err:%v\n", err)
	} else {
		fmt.Println("connect to db success")
	}
	select_one()
	queryMultiRowDemo()
	insertRowDemo()
	updateRowDemo()
	deleteRowDemo()
	defer db.Close()

}

type user struct {
	id int
	// age  int
	name string
}

//查询
func select_one() {
	sqlStr := "SELECT id, name FROM sys_user WHERE id=?"
	var u user
	//非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name)
	if err != nil {
		fmt.Printf("scan failed， err: %v\n", err)
		return
	}
	fmt.Printf("id:%d,name:%s\n", u.id, u.name)
}

// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "SELECT id,name FROM sys_user WHERE id>?"
	var u user
	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() {
		err := rows.Scan(&u.id, &u.name)
		if err != nil {
			fmt.Printf("scan failed, err: %v\n", err)
			return
		}
		fmt.Printf("id:%d,name:%s\n", u.id, u.name)
	}
}

//插入数据
func insertRowDemo() {
	sqlStr := "INSERT INTO sys_user(id,name) VALUES(?,?)"
	ret, err := db.Exec(sqlStr, 23, "javapub")
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	var theID int64
	theID, err = ret.LastInsertId()
	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 updateRowDemo() {
	sqlStr := "UPDATE sys_user SET name=? WHERE id=?"
	ret, err := db.Exec(sqlStr, "i love javapub", 23)
	if err != nil {
		fmt.Printf("updated failed,err:%v\n", err)
		return
	}
	var n int64
	n, err = ret.RowsAffected()
	if err != nil {
		fmt.Printf("get rowsAffected failed,err:%v\b", err)
		return
	}
	fmt.Printf("updated success, the rows affected is %d\n", n)
}

//删除数据
func deleteRowDemo() {
	sqlStr := "DELETE FROM sys_user WHERE id=?"
	ret, err := db.Exec(sqlStr, 1)
	if err != nil {
		fmt.Printf("delete failed,err:%v\n", err)
		return
	}
	var n int64
	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)
}