mysql驱动使用

初始化

import (

	"database/sql"

	_ "github.com/go-sql-driver/mysql"

)

DB, err = sql.Open("mysql", "root:root@tcp(localhost:3306)/golang_db")

DB.SetMaxOpenConns(100)

DB.SetMaxIdleConns(16)

一次查多行

type User struct {

	Id   int64          `db:"id"`

	Name sql.NullString `db:"string"`

	Age  int            `db:"age"`

}

rows, err := DB.Query("select id, name, age from user where id > ?", 0)

//重点关注， rows对象一定要close掉

defer func() {

	if rows != nil {

		rows.Close()

	}

}()

if err != nil {

	fmt.Printf("query failed, err:%v\n", err)

	return

}

for rows.Next() {

	var user User

	err := rows.Scan(&user.Id, &user.Name, &user.Age)

	if err != nil {

		fmt.Printf("scan failed, err:%v\n", err)

		return

	}

	fmt.Printf("user:%#v\n", user)

}

测试查询

row := DB.QueryRow("select id, name, age from user where id=?", 2)

for rows.Next() {

    var user User

    err := row.Scan(&user.Id, &user.Name, &user.Age)

    id, err := result.LastInsertId()

    fmt.Printf("id is %d\n", id)

}

插入

result, err := DB.Exec("insert into user(name, age) values(?, ?)", "tom", 18)

id, err := result.LastInsertId()

fmt.Printf("id is %d\n", id)

更新

result, err := DB.Exec("update user set name=? where id=?", "jim", 3)

affected, err := result.RowsAffected()

fmt.Printf("update db succ, affected rows:%d\n", affected)

删除

sqlstr := "delete from user where id=?"

result, err := DB.Exec(sqlstr, 3)

affected, err := result.RowsAffected()

fmt.Printf("delete db succ, affected rows:%d\n", affected)

prepare data

stmt, err := DB.Prepare("select id, name, age from user where id > ?")

defer func() {

	if stmt != nil {

		stmt.Close()

	}

}()

rows, err := stmt.Query(0)

defer func() {

	if rows != nil {

		rows.Close()

	}

}()

for rows.Next() {

	var user User

	err := rows.Scan(&user.Id, &user.Name, &user.Age)

	if err != nil {

		fmt.Printf("scan failed, err:%v\n", err)

		return

	}

	fmt.Printf("user:%#v\n", user)

}

prepare处理流程

好处：

事务

conn, err := DB.Begin()

_, err = conn.Exec("update user set age = 1 where id = ?", 1)

if err != nil {

	conn.Rollback()

	fmt.Printf("exec sql:%s failed, err:%v\n", sqlstr, err)

	return

}

_, err = conn.Exec("update user set age = 2 where id = ?", 2)

if err != nil {

	conn.Rollback()

	fmt.Printf("exec second sql:%s failed, err:%v\n", sqlstr, err)

	return

}

err = conn.Commit()

if err != nil {

	fmt.Printf("commit failed, err:%v\n", err)

	conn.Rollback()

	return

}

sqlx的使用

import (

	"database/sql"

	_ "github.com/go-sql-driver/mysql"

	"github.com/jmoiron/sqlx"

)

//更新

db, _ := sqlx.Open("mysql", "root:123456@tcp(localhost:3306)/mydb")

result, e := db.Exec("update person set name = ? where id = ?","张一蛋",1)

rowsAffected, _ := result.RowsAffected() //受影响的行数

lastInsertId, _ := result.LastInsertId() //最后一行的ID

// 查询

/*该结构体对应着数据库里的person表*/

type Person struct {

	//对应name表字段

	Name string `db:"name"`

	//对应age表字段

	Age int `db:"age"`

	//对应rmb表字段

	Money float64 `db:"rmb"`

}

var ps []Person

e := database.Select(&ps, "select name,age,rmb from person where name like ?", "%蛋")

汇总demo

mysql

package main

import (

	"database/sql"

	_ "github.com/go-sql-driver/mysql"

	"fmt"

)

var DB *sql.DB

func initDb() error {

	var err error

	dsn := "root:root@tcp(localhost:3306)/golang_db"

	DB, err = sql.Open("mysql", dsn)

	if err != nil {

		return err

	}

	DB.SetMaxOpenConns(100)

	DB.SetMaxIdleConns(16)

	return nil

}

type User struct {

	Id   int64          `db:"id"`

	Name sql.NullString `db:"string"`

	Age  int            `db:"age"`

}

func testQueryMultilRow() {

	sqlstr := "select id, name, age from user where id > ?"

	rows, err := DB.Query(sqlstr, 0)

	//重点关注， rows对象一定要close掉

	defer func() {

		if rows != nil {

			rows.Close()

		}

	}()

	if err != nil {

		fmt.Printf("query failed, err:%v\n", err)

		return

	}

	for rows.Next() {

		var user User

		err := rows.Scan(&user.Id, &user.Name, &user.Age)

		if err != nil {

			fmt.Printf("scan failed, err:%v\n", err)

			return

		}

		fmt.Printf("user:%#v\n", user)

	}

}

func testQueryData() {

	for i := 0; i < 101; i++ {

		fmt.Printf("query %d times\n", i)

		sqlstr := "select id, name, age from user where id=?"

		row := DB.QueryRow(sqlstr, 2)

		/*if row != nil {

			continue

		}*/

		var user User

		err := row.Scan(&user.Id, &user.Name, &user.Age)

		if err != nil {

			fmt.Printf("scan failed, err:%v\n", err)

			return

		}

		fmt.Printf("id:%d name:%v age:%d\n", user.Id, user.Name, user.Age)

	}

}

func testInsertData() {

	sqlstr := "insert into user(name, age) values(?, ?)"

	result, err := DB.Exec(sqlstr, "tom", 18)

	if err != nil {

		fmt.Printf("insert failed, err:%v\n", err)

		return

	}

	id, err := result.LastInsertId()

	if err != nil {

		fmt.Printf("get last insert id failed, err:%v\n", err)

		return

	}

	fmt.Printf("id is %d\n", id)

}

func testUpdateData() {

	sqlstr := "update user set name=? where id=?"

	result, err := DB.Exec(sqlstr, "jim", 3)

	if err != nil {

		fmt.Printf("insert failed, err:%v\n", err)

		return

	}

	affected, err := result.RowsAffected()

	if err != nil {

		fmt.Printf("get affected rows failed, err:%v\n", err)

	}

	fmt.Printf("update db succ, affected rows:%d\n", affected)

}

func testDeleteData() {

	sqlstr := "delete from user where id=?"

	result, err := DB.Exec(sqlstr, 3)

	if err != nil {

		fmt.Printf("insert failed, err:%v\n", err)

		return

	}

	affected, err := result.RowsAffected()

	if err != nil {

		fmt.Printf("get affected rows failed, err:%v\n", err)

	}

	fmt.Printf("delete db succ, affected rows:%d\n", affected)

}

func testPrepareData() {

	sqlstr := "select id, name, age from user where id > ?"

	stmt, err := DB.Prepare(sqlstr)

	if err != nil {

		fmt.Printf("prepare failed, err:%v\n", err)

		return

	}

	defer func() {

		if stmt != nil {

			stmt.Close()

		}

	}()

	rows, err := stmt.Query(0)

	//重点关注， rows对象一定要close掉

	defer func() {

		if rows != nil {

			rows.Close()

		}

	}()

	if err != nil {

		fmt.Printf("query failed, err:%v\n", err)

		return

	}

	for rows.Next() {

		var user User

		err := rows.Scan(&user.Id, &user.Name, &user.Age)

		if err != nil {

			fmt.Printf("scan failed, err:%v\n", err)

			return

		}

		fmt.Printf("user:%#v\n", user)

	}

}

func testPrepareInsertData() {

	sqlstr := "insert into user(name, age) values(?, ?)"

	stmt, err := DB.Prepare(sqlstr)

	if err != nil {

		fmt.Printf("insert failed, err:%v\n", err)

		return

	}

	defer func() {

		if stmt != nil {

			stmt.Close()

		}

	}()

	result, err := stmt.Exec("jim", 100)

	id, err := result.LastInsertId()

	if err != nil {

		fmt.Printf("get last insert id failed, err:%v\n", err)

		return

	}

	fmt.Printf("id is %d\n", id)

}

func testTrans() {

	conn, err := DB.Begin()

	if err != nil {

		if conn != nil {

			conn.Rollback()

		}

		fmt.Printf("begin failed, err:%v\n", err)

		return

	}

	sqlstr := "update user set age = 1 where id = ?"

	_, err = conn.Exec(sqlstr, 1)

	if err != nil {

		conn.Rollback()

		fmt.Printf("exec sql:%s failed, err:%v\n", sqlstr, err)

		return

	}

	sqlstr = "update user set age = 2 where id = ?"

	_, err = conn.Exec(sqlstr, 2)

	if err != nil {

		conn.Rollback()

		fmt.Printf("exec second sql:%s failed, err:%v\n", sqlstr, err)

		return

	}

	err = conn.Commit()

	if err != nil {

		fmt.Printf("commit failed, err:%v\n", err)

		conn.Rollback()

		return

	}

}

func main() {

	err := initDb()

	if err != nil {

		fmt.Printf("init db failed, err:%v\n", err)

		return

	}

	//testQueryData()

	//testQueryMultilRow()

	//testInsertData()

	//testUpdateData()

	//testDeleteData()

	//testPrepareData()

	//testPrepareInsertData()

	testTrans()

}

sqlx

package main

import (

	"database/sql"

	"fmt"

	_ "github.com/go-sql-driver/mysql"

	"github.com/jmoiron/sqlx"

)

var DB *sqlx.DB

func initDb() error {

	var err error

	dsn := "root:root@tcp(localhost:3306)/golang_db"

	DB, err = sqlx.Open("mysql", dsn)

	if err != nil {

		return err

	}

	DB.SetMaxOpenConns(100)

	DB.SetMaxIdleConns(16)

	return nil

}

type User struct {

	Id   int64          `db:"id"`

	Name sql.NullString `db:"name"`

	Age  int            `db:"age"`

}

func testQuery() {

	sqlstr := "select id, name, age from user where id=?"

	var user User

	err := DB.Get(&user, sqlstr, 2)

	if err != nil {

		fmt.Printf("get failed, err:%v\n", err)

		return

	}

	fmt.Printf("user:%#v\n", user)

}

func testQueryMulti() {

	sqlstr := "select id, name, age from user where id>?"

	var user []User

	err := DB.Select(&user, sqlstr, 1)

	if err != nil {

		fmt.Printf("get failed, err:%v\n", err)

		return

	}

	fmt.Printf("user:%#v\n", user)

}

func testUpdate() {

	sqlstr := "update user set name=? where id=?"

	result, err := DB.Exec(sqlstr, "abc", 1)

	if err != nil {

		fmt.Printf("update failed, err:%v\n", err)

		return

	}

	count, err := result.RowsAffected()

	if err != nil {

		fmt.Printf("affected rows failed, err:%v\n", err)

		return

	}

	fmt.Printf("affect rows:%d\n", count)

}

func queryDB(name string) {

	sqlstr := fmt.Sprintf("select id, name, age from user where name='%s'", name)

	fmt.Printf("sql:%s\n", sqlstr)

	var user []User

	err := DB.Select(&user, sqlstr)

	if err != nil {

		fmt.Printf("select failed, err:%v\n", err)

		return

	}

	for _, v := range user {

		fmt.Printf("user:%#v\n", v)

	}

}

func queryDBBySqlx(name string) {

	sqlstr := "select id, name, age from user where name=?"

	//fmt.Printf("sql:%s\n", sqlstr)

	var user []User

	err := DB.Select(&user, sqlstr, name)

	if err != nil {

		fmt.Printf("select failed, err:%v\n", err)

		return

	}

	for _, v := range user {

		fmt.Printf("user:%#v\n", v)

	}

}

func testSqlInject() {

	//queryDB("abc' or 1 = 1 #")

	//queryDB("name=abc' and (select count(*) from user ) < 10#")

	//queryDB("name=123' union select *from user #")

	queryDBBySqlx("name=123' union select *from user #")

}

func main() {

	err := initDb()

	if err != nil {

		fmt.Printf("init db failed, err:%v\n", err)

		return

	}

	//testQuery()

	//testQueryMulti()

	//testUpdate()

	testSqlInject()

}

最新文章

1. 【深入浅出jQuery】源码浅析2--奇技淫巧
2. file_put_contents 错误:failed to open stream: Invalid argument 一种原因
3. 基于SoCkit的opencl实验1-基础例程
4. 【WP 8.1开发】电子罗盘
5. Mssql中一些常用数据类型的说明和区别
6. Linux shell misc
7. Java Map遍历方式的选择
8. 【rails3教材】博客构建过程
9. seajs构建方法
10. NOIP2015 心得
11. 关于 __proto__和prototype的一些理解
12. Kotlin初探
13. 浏览器事件window.onload、o…
14. GPIO复位功能说明
15. TOJ 4120 Zombies VS Plants
16. PLSQL学习教程(全)
17. eclipse配置tomcat添加外部项目
18. DB2 Version 10.5 补丁下载
19. 文末有福利 | IT从业者应关注哪些技术热点？
20. HTTP Status 500 PWC6188 jsp/jstl/core cannot be resolved in either web.xml or the jar files deployed with this application

热门文章

1. 原创js脚本实现百度网盘任意文件强制下载
2. 远程操控批量复制应用(scp/pssh/pscp.pssh/rsync/pslurp)
3. linux基础2-cd、mkdir、touch、umask、chattr、lsattr、SUID/SGID/Sticky Bit
4. 实用： 将程序的内容写出到excel中
5. python : import详解。
6. java.lang.NoClassDefFoundError: javax/servlet/ServletOutputStream
7. 前端知识体系：JavaScript基础-原型和原型链-理解JavaScript的执行上下文栈，可以应用堆栈信息快速定位问题
8. RecursiveTask和RecursiveAction的使用 以及java 8 并行流和顺序流（转）
9. 为什么说Redis是单线程的以及Redis为什么这么快！（转）
10. Vmware虚拟机 centos7设置固定IP地址