go语言中操作mysql(Postgresql、oracle)的方法

最新推荐文章于 2023-10-20 18:04:56 发布

weixin_34148456

最新推荐文章于 2023-10-20 18:04:56 发布

阅读量320

点赞数

文章标签： golang 数据库 git

原文链接：https://my.oschina.net/u/2245781/blog/1610655

版权

2019独角兽企业重金招聘Python工程师标准>>>

一、需要下载指定的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

转载于:https://my.oschina.net/u/2245781/blog/1610655

weixin_34148456

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
go语言中操作mysql(Postgresql、oracle)的方法

2019独角兽企业重金招聘Python工程师标准>>> ...
复制链接

扫一扫

go语言中操作mysql(Postgresql、oracle)的方法

“相关推荐”对你有帮助么？