golang-6 数据库查询

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

//go get -u github.com/go-sql-driver/mysql

//CREATE TABLE `userinfo` (
//`uid` int(10) NOT NULL AUTO_INCREMENT,
//`username` varchar(64) DEFAULT NULL,
//`departname` varchar(64) DEFAULT NULL,
//`created` date DEFAULT NULL,
//PRIMARY KEY (`uid`)
//) ENGINE=MyISAM DEFAULT CHARSET=utf8;
//
//CREATE TABLE `userdetail` (
//`uid` int(10) NOT NULL DEFAULT '0',
//`intro` text,
//`profile` text,
//PRIMARY KEY (`uid`)
//) ENGINE=MyISAM DEFAULT CHARSET=utf8;
func main() {
	checkErr := func (err error) {
		if err != nil {
			panic(err)
		}
	}

	db, err := sql.Open("mysql", "root:root@/test?charset=utf8mb4")
	checkErr(err)

	//插入数据
	stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
	checkErr(err)

	res, err := stmt.Exec("1234567890", "12", "2012-12-09") //stmt 类型 Result -> LastInsertId int64  RowsAffected int64
	checkErr(err)

	id, err := res.LastInsertId() //int64
	checkErr(err)

	fmt.Println("插入数据ID ", id)

	//更新数据
	stmt, err = db.Prepare("update userinfo set username=? where uid=?")
	checkErr(err)

	res, err = stmt.Exec("25896547899", id)
	checkErr(err)

	affect, err := res.RowsAffected() //int64
	checkErr(err)

	fmt.Println("更新数据行数 ", affect) //0,1,2,3

	//查询数据
	rows, err := db.Query("SELECT * FROM userinfo")
	checkErr(err)

	for rows.Next() {
		var id int
		var username string
		var departname string
		var created string
		err = rows.Scan(&id, &username, &departname, &created)
		checkErr(err)
		fmt.Println(id, username, departname, created)
	}
	//rows.Err()
	//defer rows.Close()

	//删除数据
	stmt, err = db.Prepare("delete from userinfo where uid=?")
	checkErr(err)

	res, err = stmt.Exec(id)
	checkErr(err)
	fmt.Println("删除数据 ", id)

	affect, err = res.RowsAffected()
	checkErr(err)

	fmt.Println("删除数据行数 ", affect)

	fmt.Println("事物")
	tx, err := db.Begin()
	checkErr(err)

	res, err = tx.Exec("INSERT INTO userinfo (username, departname, created) values (?, ?, ?)", "12678900", "2", "2012-12-09")
	if err != nil {
		fmt.Println("exec failed, ", err)
		tx.Rollback()
		return
	}
	id, err = res.LastInsertId()
	if err != nil {
		fmt.Println("exec failed, ", err)
		tx.Rollback()
		return
	}
	fmt.Println("事物插入数据ID ", id)
	res, err = tx.Exec("INSERT INTO userinfo (username, departname, created) values (?, ?, ?)", "12678901", "1", "2012-12-09")
	if err != nil {
		fmt.Println("exec failed, ", err)
		tx.Rollback()
		return
	}
	id, err = res.LastInsertId()
	if err != nil {
		fmt.Println("exec failed, ", err)
		tx.Rollback()
		return
	}
	fmt.Println("事物插入数据ID ", id)
	//tx.Rollback()
	tx.Commit()

	db.Close()
}