Posted on 13 Dec 2017

Go-MySQL-Driver

A MySQL-Driver for Go’s database/sql package

官网

https://github.com/go-sql-driver/mysql

下载

go get github.com/Go-SQL-Driver/MySQL

导入

import (
	"database/sql"
	_"github.com/Go-SQL-Driver/MySQL"
)

链接数据库

db, err := sql.Open("mysql", "用户名:密码@tcp(IP:端口)/数据库?charset=utf8")
如: db, err = sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?charset=utf8")

增删改查

数据库样例

CREATE TABLE `tbl_user` (
  `id` int(11) NOT NULL,
  `username` varchar(16) NOT NULL COMMENT '用户名',
  `password` char(32) NOT NULL COMMENT '密码'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='用户表';
  1. insert

     // 1. 直接使用Exec函数添加
     res, err := db.Exec("INSERT INTO tb_user (username, password) VALUES (?, ?)","test","123456")
     if err != nil {
         log.Fatal(err)
     }
     // 2. 首先使用Prepare获得stmt,然后调用Exec添加
     stmt, err := db.Prepare("INSERT tb_user SET username=?,password=?")
     if err != nil {
         log.Fatal(err)
     }
     res, err := stmt.Exec("test", "123456")
     if err != nil {
         log.Fatal(err)
     }
    
     // 获取刚刚插入的记录的id
     id, err := res.LastInsertId()
     if err != nil {
         log.Fatal(err)
     }
    
  2. update

     stmt, err := db.Prepare("UPDATE `tb_user` SET `password`=? WHERE `username`=?")
     if err != nil {
         log.Fatal(err)
     }
     res, err := stmt.Exec("654321", "test")
     if err != nil {
         log.Fatal(err)
     }
     num, err := res.RowsAffected()
     if err != nil {
         log.Fatal(err)
     }
    
  3. select

     // 1. 查询单条数据
     var id int
     var username, password string
    
     err := db.QueryRow("SELECT `id`, `username`, `password` FROM `tb_user` WHERE `username`=?", "test").Scan(&id, &username, &password)
     if err != nil {
         log.Fatal(err)
     }
    
     // 2. 查询多条数据,并遍历
     rows, err := db.Query("SELECT `username`, `password` FROM `tbl_user` WHERE `username`=?", "test")
     if err != nil {
         log.Fatal(err)
     }
     for rows.Next() {
         var id int
         var username, password string
         if err := rows.Scan(&id, &username, &password); err == nil {
             log.Fatal(err)
         }
         fmt.Println(id)
         fmt.Println(username)
         fmt.Println(password)
     }
    
  4. delete

     stmt, err := db.Prepare("DELETE FROM `tb_user` WHERE `username`=?")
     if err != nil {
         log.Fatal(err)
     }
     res, err := stmt.Exec("test")
     if err != nil {
         log.Fatal(err)
     }
     num, err := res.RowsAffected()
     if err != nil {
         log.Fatal(err)
     }
    

事务

t, err := db.Begin()
if err != nil {
	log.Fatal(err)
}
stmt, err1 := t.Prepare("INSERT INTO `tb_user` (`username`, `password`) VALUES (?, ?)");
if err1 != nil {
	log.Fatal(err1)
}
_, err2 := stmt.Exec("test", "123456")

if err2 != nil {
	log.Fatal(err2)
}

err3 := t.Commit()
if err3 != nil {
	err4 := t.Rollback()
	if err4 != nil {
		log.Fatal(err4)
	}
}