Golang连接PostgreSQL基本操作

前言：本篇文章对如何使用golang连接并操作postgre数据库进行了简要说明。文中使用到的主要工具：DBeaver21、VSCode，Golang1.17。

以用户，文章，评论三个表作为例子，下面是数据库建表sql：

CREATE TABLE public.user_info (
    u_id serial4 NOT NULL,
    user_name varchar NULL,
    create_time date NULL,
    CONSTRAINT user_info_pk PRIMARY KEY (u_id)
);
CREATE TABLE public.user_info (
    u_id serial4 NOT NULL,
    user_name varchar NULL,
    create_time date NULL,
    CONSTRAINT user_info_pk PRIMARY KEY (u_id)
);
CREATE TABLE public."comment" (
    c_id serial4 NOT NULL,
    "content" varchar NULL,
    CONSTRAINT comment_pk PRIMARY KEY (c_id)
);

连接数据库

github.com/lib/pqpq_sqlpubliccurrentSchema=myschemamyschema.TABLE

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/lib/pq"
)

var db *sql.DB

func DbOpen() {
    var err error
    //参数根据自己的数据库进行修改
    db, err = sql.Open("postgres", "host=localhost port=5432 user=angelhand password=2222 dbname=ahdb sslmode=disable")
    checkError(err)
    err = db.Ping()
    checkError(err)
}

sql.DB

sql.DB

type DB struct {
    // Atomic access only. At top of struct to prevent mis-alignment
    // on 32-bit platforms. Of type time.Duration.
    waitDuration int64 // Total time waited for new connections.

    connector driver.Connector
    // numClosed is an atomic counter which represents a total number of
    // closed connections. Stmt.openStmt checks it before cleaning closed
    // connections in Stmt.css.
    numClosed uint64

    mu           sync.Mutex // protects following fields
    freeConn     []*driverConn
    connRequests map[uint64]chan connRequest
    nextRequest  uint64 // Next key to use in connRequests.
    numOpen      int    // number of opened and pending open connections
    // Used to signal the need for new connections
    // a goroutine running connectionOpener() reads on this chan and
    // maybeOpenNewConnections sends on the chan (one send per needed connection)
    // It is closed during db.Close(). The close tells the connectionOpener
    // goroutine to exit.
    openerCh          chan struct{}
    closed            bool
    dep               map[finalCloser]depSet
    lastPut           map[*driverConn]string // stacktrace of last conn's put; debug only
    maxIdleCount      int                    // zero means defaultMaxIdleConns; negative means 0
    maxOpen           int                    // <= 0 means unlimited
    maxLifetime       time.Duration          // maximum amount of time a connection may be reused
    maxIdleTime       time.Duration          // maximum amount of time a connection may be idle before being closed
    cleanerCh         chan struct{}
    waitCount         int64 // Total number of connections waited for.
    maxIdleClosed     int64 // Total number of connections closed due to idle count.
    maxIdleTimeClosed int64 // Total number of connections closed due to idle time.
    maxLifetimeClosed int64 // Total number of connections closed due to max connection lifetime limit.

    stop func() // stop cancels the connection opener.
}

sql.DBsql.DBsql.DBOpen()Close()

增删改查

下面代码实现对数据简单的增删改查操作。

插入数据

func insert() {
    stmt, err := db.Prepare("INSERT INTO user_info(user_name,create_time) VALUES($1,$2)")
    if err != nil {
        panic(err)
    }

    res, err := stmt.Exec("ah", time.Now())
    if err != nil {
        panic(err)
    }

    fmt.Printf("res = %d", res)
}

Exec()sql.ResultresLastInserId() (int64, error)RowsAffected() (int64, error)

更新数据

func update() {
    stmt, err := db.Prepare("update user_info set user_name=$1 WHERE u_id=$2")
    if err != nil {
        panic(err)
    }
    res, err := stmt.Exec("angelhand", 1)
    if err != nil {
        panic(err)
    }

    fmt.Printf("res = %d", res)
}

查询数据

结构体如下：

type u struct {
    id          int
    user_name   string
    create_time time.Time
}

接下来是查询的代码

func query() {
    rows, err := db.Query("select u_id, user_name, create_time from user_info where user_name=$1", "ah")
    if err != nil {
        panic(err)

    }
    //延迟关闭rows
    defer rows.Close()

    for rows.Next() {
        user := u{}
        err := rows.Scan(&user.id, &user.user_name, &user.create_time)
        if err != nil {
            panic(err)
        }
        fmt.Printf("id = %v, name = %v, time = %v\n", user.id, user.user_name, user.create_time)
    }
}

rows.Close()rows.Next()rows.Scan()rows.Next()rows.Scan()

rows.Close()rowsdeferfinallyrowsrows.Close()

db.Query()db.Prepare()

还可以这样来查询单条记录：

err := db.Query("select u_id, user_name, create_time from user_info where user_name=$1", "ah").Scan(&user.user_name)

删除数据

func delete() {
    stmt, err := db.Prepare("delete from user_info where user_name=$1")
    if err != nil {
        panic(err)
    }
    res, err := stmt.Exec("angelhand")
    if err != nil {
        panic(err)
    }

    fmt.Printf("res = %d", res)
}