package mainimport ("database/sql""fmt"_ "github.com/go-sql-driver/mysql"
)var db *sql.DBtype user struct {id   intname stringage  int
}// 建立連接
func initDB() (err error) {dsn := "root:123456@tcp(127.0.0.1:3306)/mysqltest"db, err = sql.Open("mysql", dsn)if err != nil {return}fmt.Println("連接數據庫成功")db.SetMaxIdleConns(10)return
}// 增加
func insert() {sqlStr := `insert into usertest(name,age) values("星期三",30)`ret, err := db.Exec(sqlStr)if err != nil {fmt.Printf("insert failed,err:%v\n", err)return}id, err := ret.LastInsertId() //獲取IDif err != nil {fmt.Printf("get id failed ,err:%v\n", err)return}fmt.Println("id", id)
}// 刪除
func deleteRow(id int) {sqlStr := `delete from  usertest where id=?`ret, err := db.Exec(sqlStr, id)if err != nil {fmt.Printf("delete failed,err:%v\n", err)return}n, _ := ret.RowsAffected()fmt.Printf("刪除了%d行的數據\n", n)
}// 改update
func updateRow(newAge int, id int) {sqlStr := `update usertest set age=? where id=?`ret, err := db.Exec(sqlStr, newAge, id)if err != nil {fmt.Printf("update failed,err:%v\n", err)return}n, _ := ret.RowsAffected()fmt.Printf("更新了%d行數據\n", n)
}// 查詢-單行
func query(id int) {sqlStr := "select id,name,age from usertest where id=?;"rowObj := db.QueryRow(sqlStr, id) //用于執行單行查詢的方法var u1 userrowObj.Scan(&u1.id, &u1.name, &u1.age) //將查詢結果掃描到變量中fmt.Printf("u1:%#v\n", u1)
}// 查詢-多行
func queryMore(n int) {sqlStr := "select id,name,age from user where id >?;"rows, err := db.Query(sqlStr, n)if err != nil {fmt.Printf("%s query failed,err:%v\n", sqlStr, err)return}defer rows.Close()for rows.Next() {var u1 userrows.Scan(&u1.id, &u1.name, &u1.age)fmt.Printf("u1:%#v\n", u1)}
}// 模糊查詢
func queryNameLike(pattern string) {sqlStr := "SELECT id,name,age FROM usertest WHERE name LIKE ?"rows, err := db.Query(sqlStr, "%"+pattern+"%")if err != nil {fmt.Printf("query failed, err:%v\n", err)return}defer rows.Close()fmt.Println("模糊查詢結果：")for rows.Next() {var u usererr := rows.Scan(&u.id, &u.name, &u.age)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, u.age)}// 檢查遍歷是否出錯if err = rows.Err(); err != nil {fmt.Printf("rows iteration error:%v\n", err)}
}// 年齡范圍查詢
func queryByAgeRange(minAge, maxAge int) {sqlStr := `SELECT id, name, age FROM usertest WHERE age BETWEEN ? AND ? ORDER BY age DESC`rows, err := db.Query(sqlStr, minAge, maxAge)if err != nil {fmt.Printf("query failed, err:%v\n", err)return}defer rows.Close()fmt.Printf("年齡%d~%d查詢結果：\n", minAge, maxAge)for rows.Next() {var u userif err := rows.Scan(&u.id, &u.name, &u.age); err != nil {fmt.Printf("scan failed, err:%v\n", err)return}fmt.Printf("ID:%d Name:%s Age:%d\n", u.id, u.name, u.age)}if err = rows.Err(); err != nil {fmt.Printf("rows error:%v\n", err)}
}// 3. 預處理更新
func preparedUpdate(id int, newName string, newAge int) {// 準備預處理語句stmt, err := db.Prepare("UPDATE usertest SET name=?, age=? WHERE id=?")if err != nil {fmt.Printf("prepare failed, err:%v\n", err)return}defer stmt.Close()// 執行預處理語句result, err := stmt.Exec(newName, newAge, id)if err != nil {fmt.Printf("exec failed, err:%v\n", err)return}// 檢查影響行數rowsAffected, err := result.RowsAffected()if err != nil {fmt.Printf("get rows affected failed: %v\n", err)return}fmt.Printf("更新成功，影響%d行\n", rowsAffected)
}func main() {err := initDB()if err != nil {fmt.Printf("init db failed,err:%v\n", err)}//insert() //增加//deleteRow(3) //刪除//updateRow(89, 1) //更新//query(1) // 單行查詢//queryMore(1) //多行查詢preparedUpdate(1, "大姐", 66)
}

效果