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 mainimport ("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 errordb60, err = sql.Open("mysql", constr) //建立与数据库的链接if err != nil {fmt.Printf("err: %v\n", err)}db60.SetConnMaxLifetime(time.Second * 60) //超时时间60sdb60.SetMaxOpenConns(0)                   //最大链接数，0为无限制db60.SetMaxIdleConns(10)                  //设置空闲链接数// return db60
}type Student struct {id        intname      stringage       intbirthdate 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 mainimport ("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 errordb60, err = sql.Open("mysql", constr) //建立与数据库的链接if err != nil {fmt.Printf("err: %v\n", err)}db60.SetConnMaxLifetime(time.Second * 60) //超时时间60sdb60.SetMaxOpenConns(0)                   //最大链接数，0为无限制db60.SetMaxIdleConns(10)                  //设置空闲链接数// return db60
}type Student struct {id        intname      stringage       intbirthdate 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