用 Go 访问 MySql 数据库

• 所有代码样例

package main

import (
	"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   int
	Name  string
	Phone 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")
}