49. 访问PostgreSQL数据库增删改查 | 厚土Go学习笔记

李海彬

发布于 2018-03-19 10:20:38

1.3K0

发布于 2018-03-19 10:20:38

文章被收录于专栏：Golang语言社区Golang语言社区

PostgreSQL是常见的免费的大型关系型数据库，具有丰富的数据类型，也是软件项目常用的数据库之一。因其可靠的稳定性，通常我们可以拿它来做Oracle的替代品。

使用 Go 语言访问 PostgreSQL 数据库，与其他数据库是略有不同的。

为了能够对数据库进行访问，我们先建立一个数据库 cofoxdb，并且建立一个数据表 user

建表 SQL 脚本如下

---------------------------
---postgresql SQL by Junbo Jian
---------------------------
drop table if exists "user";

CREATE TABLE "user"(
    id serial PRIMARY KEY,
    "userName" varchar(45) UNIQUE,
    "password" varchar(255) NOT NULL,
    "nickName" varchar(45) UNIQUE,
    "registTime" time with time zone NOT NULL DEFAULT now(),
    "lastTimeLogin" time with time zone,
    "newLoginTime" time with time zone,
    bak varchar(1000) DEFAULT NULL,
    online char(1) DEFAULT 'N',
    "createTime" time with time zone NOT NULL,
    creator varchar(45) DEFAULT NULL,
    "updateTime" time with time zone DEFAULT NULL,
    updator varchar(45) DEFAULT NULL
    
);

COMMENT ON COLUMN "user".id is '流水号';
COMMENT ON COLUMN "user"."userName" is '用户名【不可更改】';
COMMENT ON COLUMN "user"."password" is '密码';
COMMENT ON COLUMN "user"."nickName" is '昵称';
COMMENT ON COLUMN "user"."registTime" is '用户注册时间';
COMMENT ON COLUMN "user"."lastTimeLogin" is '上次登录时间';
COMMENT ON COLUMN "user"."newLoginTime" is '最新登录时间（当前登录时间）';
COMMENT ON COLUMN "user".bak is '备注';
COMMENT ON COLUMN "user".online is '当前在线，Y/N Y：在线 N：不在线';
COMMENT ON COLUMN "user"."createTime" is '记录创建时间';
COMMENT ON COLUMN "user".creator is '记录创建人';
COMMENT ON COLUMN "user"."updateTime" is '记录修改时间';
COMMENT ON COLUMN "user".updator is '记录修改人';

在用 Go 语言编写 PostgreSQL 的访问代码前，我们需要先了解一下 PostgreSQL 的一些特性。

PostgreSQL 无论是表名还是字段名，如果你需要使用大写字母或者一些关键字，那么这个表名或字段在使用的时候，需要加 双引号。

在代码中，也需要先添加数据库驱动包。

我们推荐两个包，任意加一个就可以了。

_ "github.com/bmizerany/pq" _ "github.com/lib/pq"

你会看到我的代码中 import 块是这样的

import (
    "database/sql"
    "fmt"
    //_ "github.com/bmizerany/pq"
    _ "github.com/lib/pq"
    "time"
    "log"
)

其中一个驱动包是被注释掉的。无论保留哪个，都不会影响代码的运行。只是不同的驱动包，出现访问错误时，返回的信息内容与格式不同。看你喜欢用哪个了。

依然是先声明全局变量 db 和 err ，再加载数据库连接，并设定好连接池。

var db *sql.DB
var err error

func init()  {
    db, err = sql.Open("postgres", "user=cofox password=Q1w2e3r4 dbname=cofoxdb sslmode=disable")
    check(err)

    db.SetMaxOpenConns(2000)
    db.SetMaxIdleConns(1000)
    check(db.Ping())
}

增删改查四种操作，我们用四个函数来分别写出来。在主函数 main 中就直接是这四个函数的调用了。

func main() {
    //query2()
    insert()
    //update()
    //remove()
}

依然是你用哪个就写哪个，不用的就先注释掉。

首先是插入数据。前面已经说过了，要加双引号的事情，这里就看一下 Go 语言里使用转义符号在 SQL 里的用法吧。

stmt, err := db.Prepare("INSERT INTO \"user\"(\"userName\", password, \"nickName\", bak, creator)   VALUES ($1, $2, $3, $4, $5)")

\" 就是双引号的转义格式。双引号成对出现，需要两个这样的符号。\"user\"

这里还需要注意 VALUES 后面的参数，是用 $ 符号跟着一个顺序的数字的格式。这表明是第一个参数、第二个参数、第三个...

这几个参数对应的就是下面的这行代码，顺序对应

res, err := stmt.Exec("cofox","123456","冷静的狐狸","","gopher")

完整的 insert 函数代码如下

//插入数据
func insert()  {
    stmt, err := db.Prepare("INSERT INTO \"user\"(\"userName\", password, \"nickName\", bak, creator)   VALUES ($1, $2, $3, $4, $5)")
    check(err)

    res, err := stmt.Exec("cofox","123456","冷静的狐狸","","gopher")
    check(err)

    id, err := res.RowsAffected() //.LastInsertId()
    check(err)

    fmt.Println(id)
    stmt.Close()

}

提交到数据库的方法，使用的是 RowsAffected 而不是我们常见的 LastInsertId 。这是 PostgreSQL 的特性决定的，我们无法获取刚增加进去的数据记录的 id 号。（尽管在数据库上，我们使用 serial 和 Primary Key 能够让记录自增）

如果看过 “访问MySQL数据库增删改查” 的那两篇文章，下面的修改函数update就不难理解了。

//修改数据
func update() {
    stmt, err := db.Prepare("UPDATE \"user\" SET password=$1, \"nickName\"=$2, \"lastTimeLogin\"=$3, \"newLoginTime\"=$4, bak=$5, online=$6, \"updateTime\"=$7, updator=$8  WHERE id=$9")
    check(err)

    res, err := stmt.Exec("7654321","厚土火焰山",time.Now().Format("2006-01-02 15:04:05"),time.Now().Format("2006-01-02 15:04:05"),"修改了一次","Y",time.Now().Format("2006-01-02 15:04:05"),"gopher", 1)
    check(err)

    num, err := res.RowsAffected()
    check(err)

    fmt.Println(num)
    stmt.Close()
}

只是同样要注意 \"的使用。

删除函数也没什么特殊之处，依然是 Exec(1)里面放的是参数值。

看删除函数 remove 的代码

//删除数据
func remove() {
    stmt, err := db.Prepare("DELETE FROM \"user\" WHERE id=$1")
    check(err)

    res, err := stmt.Exec(1)
    check(err)

    num, err := res.RowsAffected()
    check(err)

    fmt.Println(num)
    stmt.Close()

}

查询数据 query2() 函数。因为数据库中很多字段是允许为 NULL 的，所以 query2 中很多变量的类型都是 sql.NullString。

取得了返回数据记录后，用 for rows.Next()遍历所有的记录，在循环中展示数据。

func query2()  {
    rows, err := db.Query("SELECT \"id\", \"userName\", \"password\", \"nickName\", \"registTime\", \"lastTimeLogin\", \"newLoginTime\", bak, online, \"createTime\", creator, \"updateTime\", updator FROM \"user\"")
    check(err)

    for rows.Next(){
        var id int
        var userName string
        var password string
        var nickName string
        var registTime string
        var lastTimeLogin sql.NullString
        var newLoginTime sql.NullString
        var bak sql.NullString
        var online sql.NullString
        var createTime sql.NullString
        var creator sql.NullString
        var updateTime sql.NullString
        var updator sql.NullString

        //注意这里的Scan括号中的参数顺序，和 SELECT 的字段顺序要保持一致。
        if err := rows.Scan(&id, &userName, &password, &nickName, &registTime, &lastTimeLogin, &newLoginTime, &bak, &online, &createTime, &creator, &updateTime, &updator); err != nil {
            log.Fatal(err)
        }

        fmt.Printf("id = \"%d\", userName = \"%s\", password = \"%s\", nickName = \"%s\", registTime = \"%s\", lastTimeLogin = \"%s\", newLoginTime = \"%s\", bak = \"%s\", online = \"%s\", createTime = \"%s\", creator = \"%s\", updateTime = \"%s\", updator = \"%s\"\n",id, userName, password, nickName, registTime, lastTimeLogin.String, newLoginTime.String, bak.String, online.String, createTime.String, creator.String, updateTime.String, updator.String)

    }

    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    rows.Close()
}

看一遍完整的 go 访问 PostgreSQL 的代码

package main

import (
    "database/sql"
    "fmt"
    //_ "github.com/bmizerany/pq"
    _ "github.com/lib/pq"
    "time"
    "log"
)

var db *sql.DB
var err error

func init()  {
    db, err = sql.Open("postgres", "user=cofox password=Q1w2e3r4 dbname=cofoxdb sslmode=disable")
    check(err)

    db.SetMaxOpenConns(2000)
    db.SetMaxIdleConns(1000)
    check(db.Ping())
}

func main() {
    query2()
    //insert()
    //update()
    //remove()
}

//查询数据
func query2()  {
    rows, err := db.Query("SELECT \"id\", \"userName\", \"password\", \"nickName\", \"registTime\", \"lastTimeLogin\", \"newLoginTime\", bak, online, \"createTime\", creator, \"updateTime\", updator FROM \"user\"")
    check(err)

    for rows.Next(){
        var id int
        var userName string
        var password string
        var nickName string
        var registTime string
        var lastTimeLogin sql.NullString
        var newLoginTime sql.NullString
        var bak sql.NullString
        var online sql.NullString
        var createTime sql.NullString
        var creator sql.NullString
        var updateTime sql.NullString
        var updator sql.NullString

        //注意这里的Scan括号中的参数顺序，和 SELECT 的字段顺序要保持一致。
        if err := rows.Scan(&id, &userName, &password, &nickName, &registTime, &lastTimeLogin, &newLoginTime, &bak, &online, &createTime, &creator, &updateTime, &updator); err != nil {
            log.Fatal(err)
        }

        fmt.Printf("id = \"%d\", userName = \"%s\", password = \"%s\", nickName = \"%s\", registTime = \"%s\", lastTimeLogin = \"%s\", newLoginTime = \"%s\", bak = \"%s\", online = \"%s\", createTime = \"%s\", creator = \"%s\", updateTime = \"%s\", updator = \"%s\"\n",id, userName, password, nickName, registTime, lastTimeLogin.String, newLoginTime.String, bak.String, online.String, createTime.String, creator.String, updateTime.String, updator.String)

    }

    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    rows.Close()
}

//插入数据
func insert()  {
    stmt, err := db.Prepare("INSERT INTO \"user\"(\"userName\", password, \"nickName\", bak, creator)   VALUES ($1, $2, $3, $4, $5)")
    check(err)

    res, err := stmt.Exec("cofox","123456","冷静的狐狸","","gopher")
    check(err)

    id, err := res.RowsAffected()//.LastInsertId()//
    check(err)

    fmt.Println(id)
    stmt.Close()

}

//修改数据
func update() {
    stmt, err := db.Prepare("UPDATE \"user\" SET password=$1, \"nickName\"=$2, \"lastTimeLogin\"=$3, \"newLoginTime\"=$4, bak=$5, online=$6, \"updateTime\"=$7, updator=$8  WHERE id=$9")
    check(err)

    res, err := stmt.Exec("7654321","厚土火焰山",time.Now().Format("2006-01-02 15:04:05"),time.Now().Format("2006-01-02 15:04:05"),"修改了一次","Y",time.Now().Format("2006-01-02 15:04:05"),"gopher", 1)
    check(err)

    num, err := res.RowsAffected()
    check(err)

    fmt.Println(num)
    stmt.Close()
}

//删除数据
func remove() {
    stmt, err := db.Prepare("DELETE FROM \"user\" WHERE id=$1")
    check(err)

    res, err := stmt.Exec(1)
    check(err)

    num, err := res.RowsAffected()
    check(err)

    fmt.Println(num)
    stmt.Close()

}
func check(err error) {
    if err != nil{
        fmt.Println(err)
        panic(err)
    }
}

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2018-01-25，如有侵权请联系 cloudcommunity@tencent.com 删除

postgresql