golang mysql

驱动

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

使用到的方法

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

func (*sql.DB).Prepare(query string) (*sql.Stmt, error)//使用DB.Prepare预编译并使用参数化查询，对预编译的SQL语句进行缓存，省去了每次解析优化该SQL语句的过程
func (*sql.Stmt).Query(args ...any) (*sql.Rows, error)

func (*sql.DB).Query(query string, args ...any) (*sql.Rows, error)

func (*sql.Rows).Next() bool
func (*sql.Rows).Scan(dest ...any) error

单行查询

package main

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

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

var db60 *sql.DB //数据库连接和交互的类型，可以理解为句柄

func init() { 
	constr := "otoyix:otoyix@tcp(192.168.0.60:3306)/test1"
	var err error
	db60, err = sql.Open("mysql", constr) //建立与数据库的链接
	if err != nil {
		fmt.Printf("err: %v\n", err)
	}
	db60.SetConnMaxLifetime(time.Second * 60) //超时时间60s
	db60.SetMaxOpenConns(0)                   //最大链接数，0为无限制
	db60.SetMaxIdleConns(10)                  //设置空闲链接数
	// return db60
}

type Student struct {
	id        int
	name      string
	age       int
	birthdate string
}

func main() {
	row := db60.QueryRow("select * from student where id > ? limit ?", 0, 1) //返回结果行

	student := Student{}
	err := row.Scan(&student.id, &student.name, &student.age, &student.birthdate)
	if err != nil {
		fmt.Printf("err: %v\n", err)
	}
	fmt.Printf("student: %v\n", student)

}

多行查询 - 预编译-参数化

package main

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

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

var db60 *sql.DB //数据库连接和交互的类型，可以理解为句柄

func init() { 
	constr := "otoyix:otoyix@tcp(192.168.0.60:3306)/test1"
	var err error
	db60, err = sql.Open("mysql", constr) //建立与数据库的链接
	if err != nil {
		fmt.Printf("err: %v\n", err)
	}
	db60.SetConnMaxLifetime(time.Second * 60) //超时时间60s
	db60.SetMaxOpenConns(0)                   //最大链接数，0为无限制
	db60.SetMaxIdleConns(10)                  //设置空闲链接数
	// return db60
}

type Student struct {
	id        int
	name      string
	age       int
	birthdate string
}

func main() {
	stmt, err := db60.Prepare("select * from student where id > ? limit ?") 
	if err != nil {
		fmt.Printf("err: %v\n", err)
	}
	rows, err := stmt.Query(0, 2)
	if err != nil {
		fmt.Printf("err: %v\n", err)
	}

	student := Student{}
	for rows.Next() {
		err = rows.Scan(&student.id, &student.name, &student.age, &student.birthdate)
		if err != nil {
			panic(err)
		}
		fmt.Printf("student: %v\n", student)
	}

}

非查询

源代码

func (db *DB) Exec(query string, args ...any) (Result, error)

type Result interface {
	// LastInsertId returns the integer generated by the database
	// in response to a command. Typically this will be from an
	// "auto increment" column when inserting a new row. Not all
	// databases support this feature, and the syntax of such
	// statements varies.
	LastInsertId() (int64, error)

	// RowsAffected returns the number of rows affected by an
	// update, insert, or delete. Not every database or database
	// driver may support this.
	RowsAffected() (int64, error)
}

实现

	Result, err := db60.Exec("delete from student where id >1")
	fmt.Println(Result.RowsAffected())
	//返回受影响的行 与err

--------------end