用 Go 访问 MySql 数据库

• 所有代码样例

package mainimport ("database/sql""fmt"_ "github.com/go-sql-driver/mysql"
)var db *sql.DB// 初始化连接
func initDB() (err error) {db, err = sql.Open("mysql", "root:mm..1213@tcp(127.0.0.1:3306)/chapter4")if err != nil {return nil}err = db.Ping()if err != nil {return err}return nil
}type User struct {Uid   intName  stringPhone string
}// 单行查询
func queryRow() {u := User{}err := db.QueryRow("select uid,name,phone from `user` where uid=?", 1).Scan(&u.Uid, &u.Name, &u.Phone)if err != nil {fmt.Printf("scan failed,err:%v\n", err)return}fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
}// 多行查询
func queryMultiRow() {u := User{}rows, err := db.Query("select uid,name,phone from `user` where uid > ?", 0)if err != nil {fmt.Printf("query failed, err:%v\n", err)return}defer rows.Close()for rows.Next() {err := rows.Scan(&u.Uid, &u.Name, &u.Phone)if err != nil {fmt.Printf("scan failed, err:%v\n", err)return}fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)}
}// 插入操作
func insertRow() {ret, err := db.Exec("insert into user(name,phone) values (?,?)", "王五", 13988557744)if err != nil {fmt.Printf("insert failed,err:%v\n", err)return}uid, 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", uid)
}// 更新数据
func updateRow() {ret, err := db.Exec("update user set name=? where uid =?", "张三", 3)if err != nil {fmt.Printf("update failed,err:%v\n", err)return}n, err := ret.RowsAffected()if err != nil {fmt.Printf("get RowsAffected failed,err:%v\n", err)}fmt.Printf("update success, affected rows:%d\n", n)
}// 删除数据
func deleteRow() {ret, err := db.Exec("delete from user where uid = ?", 2)if err != nil {fmt.Printf("delete failed,err:%v\n", err)return}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)
}// 预处理查询
func prepareQuery() {u := User{}stmt, err := db.Prepare("select uid,name,phone from `user` where uid>?")if err != nil {fmt.Printf("prepare failed,err:%v\n", err)return}defer stmt.Close()rows, err := stmt.Query(0)if err != nil {fmt.Printf("prepare failed:%v\n", err)return}defer rows.Close()for rows.Next() {err := rows.Scan(&u.Uid, &u.Name, &u.Phone)if err != nil {fmt.Printf("scan failed %v\n", err)return}fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)}
}// 预处理插入
func prepareInsert() {stmt, err := db.Prepare("insert into user(name,phone) values (?,?)")if err != nil {fmt.Printf("prepare failed,err:%v\n", err)return}defer stmt.Close()_, err = stmt.Exec("barry", 18799887766)if err != nil {fmt.Printf("insert failed,err:%v\n", err)return}_, err = stmt.Exec("jim", 18999999999)if err != nil {fmt.Printf("insert failed,err:%v\n", err)return}fmt.Printf("insert success")
}// 事务操作
func transaction() {tx, err := db.Begin()if err != nil {if tx != nil {tx.Rollback()}fmt.Printf("begin trans failed,err:%v\n", err)return}_, err = tx.Exec("update user set name='james' where uid=?", 1)if err != nil {tx.Rollback()fmt.Printf("exec sql1 failed,err:%v\n", err)return}_, err = tx.Exec("update user set name='james' where uid=?", 3)if err != nil {tx.Rollback()fmt.Printf("exec sql2 failed,err:%v\n", err)return}tx.Commit()fmt.Printf("exec transaction success!")
}// SQL 自行拼接语句，会好不要这样写，会被注入，引发安全问题
func sqlInject(name string) {sqlStr := fmt.Sprintf("select uid, name, phone from user where name='%s'", name)fmt.Printf("SQL:%s\n", sqlStr)rows, err := db.Query(sqlStr) // 使用 db.Query 执行查询语句if err != nil {fmt.Printf("query failed,err:%v\n", err)return}defer rows.Close()for rows.Next() {u := User{}err := rows.Scan(&u.Uid, &u.Name, &u.Phone)if err != nil {fmt.Printf("scan failed %v\n", err)return}fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)}
}func main() {err := initDB()if err != nil {fmt.Printf("init db failed,err:%v\n", err)}sqlInject("james")
}