go操作mysql的驱动包很多,这里讲解当下比较流行的Go-SQL-Driver/MySQL
1.下载安装
执行下面两个命令:
下载:go get github.com/Go-SQL-Driver/MySQL
安装:go install github.com/Go-SQL-Driver/MySQL
安装完成以后的文件截图
root/go_s是go工作目录,环境变量:$GOPATH中设置的值
2.导入包
import (
"database/sql"
_"github.com/Go-SQL-Driver/MySQL"
)
3.链接数据库
Open函数:
db, err := sql.Open("mysql", "用户名:密码@tcp(IP:端口)/数据库?charset=utf8")
例如:db, err := sql.Open("mysql", "root:111111@tcp(127.0.0.1:3306)/test?charset=utf8")
4.增删改查
下面例子中的表结构:
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=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
截图:
增加
有两种方法:
1.直接使用Exec函数添加
result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)","lily","销售","2016-06-21")
2.首先使用Prepare获得stmt,然后调用Exec添加
stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
res, err := stmt.Exec("zhja", "研发", "2016-06-17")
另一个经常用到的功能,获得刚刚添加数据的自增ID
id, err := res.LastInsertId()
相关代码截图:
删除
删除和上面的增加语法一样,只是把其中的INSERT语句改为DELETE语句
修改
修改和上面的增加语法一样,只是把其中的INSERT语句改为UPDATE语句
查询
查询单条数据,QueryEow 函数
var username, departname, created string
err := db.QueryRow("SELECT username,departname,created FROM userinfo WHERE uid=?", 3).Scan(&username, &departname, &created)
代码截图:
查询多条数据,并遍历
Query 获取数据,for xxx.Next() 遍历数据
5.事务
在操作数据库之前执行,db.Begin()
例:tx, err := db.Begin()
保存到数据库:err := tx.Commit()
回滚:err := tx.Rollback()
注意设置事务以后操作数据库就不是db了,而是tx
下面是写文章时测试文件的源代码,//注释的内容请自行删除测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
package main
import (
//"fmt"
"database/sql"
_"github.com/Go-SQL-Driver/MySQL"
)
type userinfostruct {
username string
departname string
created string
}
func main(){
db, err := sql.Open("mysql","root:111111@tcp(127.0.0.1:3306)/test?charset=utf8")
checkErr(err)
//insert
//stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
//checkErr(err)
//res, err := stmt.Exec("zhja", "研发", "2016-06-17")
//checkErr(err)
//id, err := res.LastInsertId()
//checkErr(err)
//fmt.Println(id)
//result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)","lily","销售","2016-06-21")
//checkErr(err)
//ids, err := result.LastInsertId()
//fmt.Println(ids)
//db.Exec("DELETE FROM userinfo WHERE uid=?", 1)
//checkErr(err)
//stmt, err := db.Prepare("DELETE FROM userinfo WHERE uid=?")
//stmt.Exec(2)
//var username, departname, created string
//err = db.QueryRow("SELECT username,departname,created FROM userinfo WHERE uid=?", 3).Scan(&username, &departname, &created)
//fmt.Println(username)
//fmt.Println(departname)
//fmt.Println(created)
rows, err := db.Query("SELECT username,departname,created FROM userinfo WHERE username=?","zhja")
checkErr(err)
for rows.Next() {
var username, departname, created string
if err := rows.Scan(&username, &departname, &created); err == nil {
fmt.Println(err)
}
fmt.Println(username)
fmt.Println(departname)
fmt.Println(created)
}
tx, err := db.Begin()
checkErr(err)
stmt, err1 := tx.Prepare("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)")
checkErr(err1)
_, err2 := stmt.Exec("test","测试","2016-06-20")
checkErr(err2)
//err3 := tx.Commit()
err3 := tx.Rollback()
checkErr(err3)
}
func checkErr(err error){
if err != nil {
panic(err)
}
}