Go操作MySQL数据库
主要的结构体DB和基于该结构体的方法
type DBfunc (db DB) Begin() (Tx, error)func (db *DB) Close() errorfunc (db *DB) Driver() driver.Driverfunc (db *DB) Exec(query string, args ...interface{}) (Result, error)func (db *DB) Prepare(query string) (*Stmt, error)func (db *DB) Query(query string, args ...interface{}) (*Rows, error)func (db *DB) QueryRow(query string, args ...interface{}) *Row Query && QueryRow
如下代码示例，
package mainimport ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "log")func main() { //完整的数据库连接字符串 //username:password@protocol(address)/dbname?param=value db, err := sql.Open("mysql", "root:034039@tcp(127.0.0.1:3306)/account") defer db.Close() if err != nil { log.Println(err) } rows, err := db.Query("SELECT vin,no FROM vehicle_gps where type= ?", 2) if err != nil { log.Println(err) } var vin, no string for rows.Next() { err := rows.Scan(&vin, &no) //获取字段,select几个字段就传入几个变量 if err != nil { log.Println(err) } fmt.Println(vin, no) } // 查询单条记录 singledRow := db.QueryRow("SELECT id,type FROM vehicle_gps where id = ?", 3901) var id, kind int singledRow.Scan(&id, &kind) fmt.Println(id, kind)} Exec
如下代码示例，
package mainimport ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "log" "math/rand" "time")func main() { //完整的数据库连接字符串 //username:password@protocol(address)/dbname?param=value db, err := sql.Open("mysql", "root:034039@tcp(127.0.0.1:3306)/account") if err != nil { log.Println(err) } //在这里进行一些数据库操作 defer db.Close() //db.Exec 执行查询 //rand.Intn(100) 返回100 以内随机数 rand.Seed(time.Now().Unix()) //设置随机数的种子 random := rand.Intn(100) result, err := db.Exec("update vehicle_gps set type = ? where id = ?", random, 8033) if err != nil { fmt.Println("db.Exec error") log.Println(err) } n, _ := result.RowsAffected() fmt.Println(n)} Prepare
如下代码示例
package mainimport ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "log" "math/rand" "time")func main() { db, err := sql.Open("mysql", "root:034039@tcp(127.0.0.1:3306)/account") defer db.Close() if err != nil { log.Println(err) } stmt, err := db.Prepare("update vehicle_gps set type = ? where vin = ?") defer stmt.Close() if err != nil { log.Println(err) } //rand.Intn(10) 返回10 以内随机数 rand.Seed(time.Now().Unix()) //设置随机数的种子 random := rand.Intn(100) //Exec方法返回的是sql.Result对象 result, err := stmt.Exec(random, "JM7CW09FXC0106372") if err != nil { log.Println(err) } rows, err := db.Query("select type from vehicle_gps where vin = ?", "JM7CW09FXC0106372") if err != nil { log.Println(err) } var t int for rows.Next() { rows.Scan(&t) fmt.Println(t) if t != random { panic("update type error") } } n, _ := result.RowsAffected() fmt.Println(n)} 事务的支持
如下代码示例
package mainimport ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "log" "math/rand" "time")func main() { db, err := sql.Open("mysql", "root:034039@tcp(127.0.0.1:3306)/account") defer db.Close() if err != nil { log.Println(err) } tx, err := db.Begin() //开启一个事物 defer tx.Commit() //一定要commit if err != nil { log.Println(err) } stmt, err := tx.Prepare("update vehicle_gps set type = ? where vin = ?") defer stmt.Close() if err != nil { log.Println(err) } //rand.Intn(10) 返回10 以内随机数 rand.Seed(time.Now().Unix()) //设置随机数的种子 random := rand.Intn(100) result, err := stmt.Exec(random, "JM7CW09FXC0106372") if err != nil { log.Println(err) } fmt.Println(random) if n, _ := result.RowsAffected(); n == 2 { fmt.Println("rollback") tx.Rollback() //回滚事务后同时也要提交 }} =========END=========