一、需要下载指定的golang的mysql驱动包
$ cd $GOPATH/src
$ go get github.com/go-sql-driver/mysql
二、建库
--
-- 表的结构 `test`
--
CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) NOT NULL,
`price` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
三、例子testsql.go:
package main;
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
)
func main() {
//打开数据库
//DSN数据源字符串:用户名:密码@协议(地址:端口)/数据库?参数=参数值
db, err := sql.Open("mysql", "mymotif:wxwpxh@tcp(127.0.0.1:3306)/test?charset=utf8");
if err != nil {
fmt.Println(err);
}
//关闭数据库,db会被多个goroutine共享,可以不调用
defer db.Close();
//查询数据,指定字段名,返回sql.Rows结果集
rows, _ := db.Query("select id,name from test");
id := 0;
name := "";
for rows.Next() {
rows.Scan(&id, &name);
fmt.Println(id, name);
}
//查询数据,取所有字段
rows2, _ := db.Query("select * from test");
//返回所有列
cols, _ := rows2.Columns();
//这里表示一行所有列的值,用[]byte表示
vals := make([][]byte, len(cols));
//这里表示一行填充数据
scans := make([]interface{}, len(cols));
//这里scans引用vals,把数据填充到[]byte里
for k, _ := range vals {
scans[k] = &vals[k];
}
i := 0;
result := make(map[int]map[string]string);
for rows2.Next() {
//填充数据
rows2.Scan(scans...);
//每行数据
row := make(map[string]string);
//把vals中的数据复制到row中
for k, v := range vals {
key := cols[k];
//这里把[]byte数据转成string
row[key] = string(v);
}
//放入结果集
result[i] = row;
i++;
}
fmt.Println(result);
//查询一行数据
rows3 := db.QueryRow("select id,name from test where id = ?", 1);
rows3.Scan(&id, &name);
fmt.Println(id, name);
//插入一行数据
ret, _ := db.Exec("insert into test(id,name) values(null, '444')");
//获取插入ID
ins_id, _ := ret.LastInsertId();
fmt.Println(ins_id);
//更新数据
ret2, _ := db.Exec("update test set name = '000' where id > ?", 2);
//获取影响行数
aff_nums, _ := ret2.RowsAffected();
fmt.Println(aff_nums);
//删除数据
ret3, _ := db.Exec("delete from test where id = ?", 3);
//获取影响行数
del_nums, _ := ret3.RowsAffected();
fmt.Println(del_nums);
//预处理语句
stmt, _ := db.Prepare("select id,name from test where id = ?");
rows4, _ := stmt.Query(3);
//注意这里需要Next()下,不然下面取不到值
rows4.Next();
rows4.Scan(&id, &name);
fmt.Println(id, name);
stmt2, _ := db.Prepare("insert into test values(null, ?, ?)");
rows5, _ := stmt2.Exec("666", 66);
fmt.Println(rows5.RowsAffected());
//事务处理
tx, _ := db.Begin();
ret4, _ := tx.Exec("update test set price = price + 100 where id = ?", 1);
ret5, _ := tx.Exec("update test set price = price - 100 where id = ?", 2);
upd_nums1, _ := ret4.RowsAffected();
upd_nums2, _ := ret5.RowsAffected();
if upd_nums1 > 0 && upd_nums2 > 0 {
//只有两条更新同时成功,那么才提交
tx.Commit();
} else {
//否则回滚
tx.Rollback();
}
}
四、运行
go run testsql.go
注意在ubuntu下的go还是1.6.2版本,会出现以下错误:
src/github.com/go-sql-driver/mysql/utils.go:81: undefined: cloneTLSConfig
需要用go-7(1.8.1版本)才能正常运行:
go-7 run testsql.go
五、postgresql
表:
CREATE TABLE userinfo
(
uid serial NOT NULL,
username character varying(100) NOT NULL,
departname character varying(500) NOT NULL,
Created date,
CONSTRAINT userinfo_pkey PRIMARY KEY (uid)
);
驱动包安装:
cd $GOPATH/src
go get github.com/bmizerany/pq/
go代码:
package main
import (
"database/sql"
"fmt"
_ "github.com/bmizerany/pq"
)
func main() {
db, err := sql.Open("postgres", "user=mymotif password=wxwpxh dbname=testdb sslmode=disable")
checkErr(err)
//查询数据
rows, err := db.Query("SELECT * FROM userinfo")
checkErr(err)
for rows.Next() {
var uid int
var username string
var department string
var created string
err = rows.Scan(&uid, &username, &department, &created)
checkErr(err)
fmt.Println(uid)
fmt.Println(username)
fmt.Println(department)
fmt.Println(created)
}
db.Close()
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
运行:
$ go run testpg.go
1
astaxie
研发部门
2012-12-09T00:00:00Z
2
myreas
研发部门
2015-12-09T00:00:00Z
六、ORACLE
oracle环境见:oracle pro*c中调用PL/SQL存储过程
不翻墙安装golang.org/x/net
cd $GOPATH/src/golang.org/x/
git clone https://github.com/golang/net.git
增加/opt/ora11g/instantclient_11_2/lib/pkgconfig/oci8.pc并把对应目录追加到PKG_CONFIG_PATH路径中:
sudo mkdir /opt/ora11g/instantclient_11_2/lib/pkgconfig
sudo vi /opt/ora11g/instantclient_11_2/lib/pkgconfig/oci8.pc
cat /opt/ora11g/instantclient_11_2/lib/pkgconfig/oci8.pc
prefix=/opt/ora11g/instantclient_11_2
exec_prefix=/opt/ora11g/instantclient_11_2/bin
libdir=${prefix}/lib
includedir=${prefix}/sdk/include
Name: OCI
Description: Oracle database engine
Version: 11.2
Libs: -L${libdir} -lclntsh
Libs.private:
Cflags: -I${includedir}
.bashrc中加上:
export PKG_CONFIG_PATH=$ORACLE_HOME/lib/pkgconfig:$PKG_CONFIG_PATH
即oracle环境变量的设置为:
export ORACLE_HOME=/opt/ora11g/instantclient_11_2
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG='simplified chinese_china'.UTF8
export PATH=$ORACLE_HOME/bin:$PATH
export PKG_CONFIG_PATH=$ORACLE_HOME/lib/pkgconfig:$PKG_CONFIG_PATH
alias sqlplus='/usr/bin/rlwrap sqlplus'
安装go的oracle驱动:
go get github.com/mattn/go-oci8
代码:
$ cat testora2.go
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-oci8"
)
func main() {
//修改成自己数据账号及密码
db, err := sql.Open("oci8", "scott/tiger@xe")
checkErr(err)
rows, err := db.Query("select EMPNO,ENAME, JOB,SAL from emp")
checkErr(err)
for rows.Next() {
var EMPNO int
var ENAME string
var JOB string
var SAL float32
err = rows.Scan(&EMPNO, &ENAME, &JOB, &SAL)
checkErr(err)
fmt.Println(EMPNO)
fmt.Println(ENAME)
fmt.Println(JOB)
fmt.Println(SAL)
fmt.Println("--------------")
}
db.Close()
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
运行:
$ go run testora2.go
7369
SMITH
CLERK
800
--------------
7499
ALLEN
SALESMAN
1600
--------------
7521
WARD
SALESMAN
1250
--------------
7566
JONES
MANAGER
2975
--------------
7654
MARTIN
SALESMAN
1250
--------------
7698
BLAKE
MANAGER
2850
--------------
7782
CLARK
MANAGER
2450
--------------
7788
SCOTT
ANALYST
3000
--------------
7839
KING
PRESIDENT
5000
--------------
7844
TURNER
SALESMAN
1500
--------------
7876
ADAMS
CLERK
1100
--------------
7900
JAMES
CLERK
950
--------------
7902
FORD
ANALYST
3000
--------------
7934
MILLER
CLERK
1300
--------------
七、DB2
驱动包安装:
$ cd $GOPATH/src
$ go get bitbucket.org/phiggins/db2cli
# bitbucket.org/phiggins/db2cli/api
bitbucket.org/phiggins/db2cli/api/api_unix.go:12:22: fatal error: sqlcli1.h: No such file or directory
// #include <sqlcli1.h>
^
compilation terminated.
编译出错:
修改源代码:
vi bitbucket.org/phiggins/db2cli/api/api_unix.go
把:
package api
// #cgo darwin LDFLAGS: -ldb2
// #cgo linux CFLAGS: -Wall
// #cgo linux LDFLAGS: -ldb2
// #include <sqlcli1.h>
改为:
package api
// #cgo darwin LDFLAGS: -ldb2
// #cgo linux CFLAGS: -Wall -I/home/db2inst1/sqllib/include
// #cgo linux LDFLAGS: -L/home/db2inst1/sqllib/lib -ldb2
// #include <sqlcli1.h>
再安装:
go install bitbucket.org/phiggins/db2cli