继Golang学习系列第三天：学习数组、切片、Map、结构体、指针、函数、接口类型、channel通道，今天学习golang操作数据库，以PostgreSQL为例。

0. 安装PostgreSQL 数据库

可以参考PostgreSQL官网www.postgresql.org/download/linux/...安装该数据库

特别需要说明的是，安装完成后，自动建立了一个名为postgres的用户，默认密码为空；同时也自动创建了一个名字叫postgres的数据库。

0.1、修改默认生成的数据库用户postgres的密码。

​

把密码设置为12345678.

0.2 创建示例数据库

测试数据库名可以自取，

​

然后建一张测试表让golang使用

CREATE TABLE users (
    id serial PRIMARY KEY,
    email VARCHAR (355) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL
);

insert into users(id,email,password) values(1,'1056764180@qq,com','12345678');
insert into users(id,email,password) values(2,'10567@qq,com','1234567890');
insert into users(id,email,password) values(3,'10567567@qq,com','12345678908');

0.3 开启远程访问

由于数据库和应用程序不在同一机器上，故数据库要开启远程访问功能

修改配置文件，即

vim /var/lib/pgsql/12/data/postgresql.conf

找到listen_adderess配置项设为*

​

继续修改另一配置文件，即

vim /var/lib/pgsql/12/data/pg_hba.conf

在# IPv4 local connections:处追加客户端的连接信息

​

重启postgresql服务

systemctl restart postgresql-12

最后客户端测试连接

​

1. golang操作数据库

连接数据库会使用第三方驱动包，由于墙的缘故，可以先设置一下代理

go env -w GO111MODULE=on
go env -w GOPROXY=https://mirrors.aliyun.com/goproxy/,direct

就以基本的增删改查数据，记录如何使用go操作数据库

1. 1 Select查询数据

新建postgres.go项目，键入以下测试连接数据库的代码

package main

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

)

const (
    // TODO fill this in directly or through environment variable
    // Build a DSN e.g. postgres://username:password@url.com:5432/dbName
    DB_DSN = "postgres://postgres:12345678@192.168.8.200:5432/douyin?sslmode=disable"
)

type User struct {
    ID       int
    Email    string
    Password string
}

func main() {

    // Create DB pool
    //db, err := sql.Open("postgres", "host=192.168.8.200 port=5432 user=postgres password=12345678 dbname=douyin sslmode=disable")
    db, err := sql.Open("postgres",DB_DSN)
        if err != nil {
        log.Fatal("Failed to open a DB connection: ", err)
    }
    defer db.Close()

    // Create an empty user and make the sql query (using $1 for the parameter)
    var myUser User
    userSql := "SELECT id, email, password FROM users WHERE id = $1"

    err = db.QueryRow(userSql, 1).Scan(&myUser.ID, &myUser.Email, &myUser.Password)
    if err != nil {
        log.Fatal("Failed to execute query: ", err)
    }

    fmt.Printf("你好 邮箱：%s, 密码：%s,  欢迎回来!\n", myUser.Email, myUser.Password)
}

然后创建一个模块依赖文件

go mod init  pluginModel

安装具体的依赖包

go get github.com/lib/pq

最后运行测试代码

[root@master goworkspace]# go run postgres.go

从数据库查询id等于1的记录，如图

​

和数据库里的数据是对应的

​

1.2 增加数据

接上1.1示例代码，稍作更改即可，文件命名为postgres-create.go

package main

import (
    "database/sql"
    "fmt"
    "log"

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

)

const (
    // TODO fill this in directly or through environment variable
    // Build a DSN e.g. postgres://username:password@url.com:5432/dbName
    DB_DSN = "postgres://postgres:12345678@192.168.8.200:5432/douyin?sslmode=disable"
)

type User struct {
    ID       int
    Email    string
    Password string
}

func main() {
    // Create DB pool
    //db, err := sql.Open("postgres", "host=192.168.8.200 port=5432 user=postgres 
    password=12345678 dbname=douyin sslmode=disable")
    db, err := sql.Open("postgres",DB_DSN)
        if err != nil {
        log.Fatal("Failed to open a DB connection: ", err)
    }
    defer db.Close()

    //创建一个用户，预要插入到数据库里
    var user User = User{ID:4,Email:"110@qq.com",Password:"1234567890"}
    //执行插入操作
    _, err = db.Exec("INSERT INTO users (id,email,password) VALUES($1,$2,$3)", 
    user.ID,user.Email,user.Password)
    if err != nil {
        log.Fatal(err)
    }
    //打印日志
    log.Printf("create ok!!!")

    //测试数据是否插入成功，执行具体的查询语句
    var myUser User
    userSql := "SELECT id, email, password FROM users WHERE id = $1"

    //设置查询参数为4，即创建数据时的ID值
    err = db.QueryRow(userSql, 4).Scan(&myUser.ID, &myUser.Email, 
    &myUser.Password)
    if err != nil {
        log.Fatal("Failed to execute query: ", err)
    }

    //输出查询结果
    fmt.Printf("hello email: %s, password: %s, welcome back!\n", 
    myUser.Email,myUser.Password)

}

执行程序代码，输出结果

​

1.3 update修改数据

接上1.2示例代码，稍作更改即可，文件命名为postgres-update.go

package main

import (
    "database/sql"
    "fmt"
    "log"

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

)

const (
    // TODO fill this in directly or through environment variable
    // Build a DSN e.g. postgres://username:password@url.com:5432/dbName
    DB_DSN = "postgres://postgres:12345678@192.168.8.200:5432/douyin?sslmode=disable"
)

type User struct {
    ID       int
    Email    string
    Password string
}

func main() {
    // Create DB pool
    //db, err := sql.Open("postgres", "host=192.168.8.200 port=5432 user=postgres 
    password=12345678 dbname=douyin sslmode=disable")
    db, err := sql.Open("postgres",DB_DSN)
        if err != nil {
        log.Fatal("Failed to open a DB connection: ", err)
    }
    defer db.Close()

    //创建一个用户，预要通过主键更改到数据库里
    var user User = User{ID:4,Email:"dong@qq.com",Password:"abcdedf120"}
    //执行更改操作
    _, err = db.Exec("UPDATE  users SET email=$1, password=$2 where id=$3", user.Email,user.Password,user.ID)
    if err != nil {
        log.Fatal(err)
    }
    //打印日志
    log.Printf("update ok!!!")

    //测试数据是否更改成功，执行具体的查询语句
    var myUser User
    userSql := "SELECT id, email, password FROM users WHERE id = $1"

    //设置查询参数为4，即要更改数据的ID值
    err = db.QueryRow(userSql, 4).Scan(&myUser.ID, &myUser.Email, 
    &myUser.Password)
    if err != nil {
        log.Fatal("Failed to execute query: ", err)
    }

    //输出查询结果
    fmt.Printf("hello email: %s, password: %s, welcome back!\n", 
    myUser.Email,myUser.Password)

}

执行程序代码，输出结果

​

1.4 delete删除数据记录

接上1.3示例代码，稍作更改即可，文件命名为postgres-delete.go

package main

import (
    "database/sql"
    "fmt"
    "log"

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

)

const (
    // TODO fill this in directly or through environment variable
    // Build a DSN e.g. postgres://username:password@url.com:5432/dbName
    DB_DSN = "postgres://postgres:12345678@192.168.8.200:5432/douyin?sslmode=disable"
)

type User struct {
    ID       int
    Email    string
    Password string
}

func main() {
    // Create DB pool
    //db, err := sql.Open("postgres", "host=192.168.8.200 port=5432 user=postgres 
    password=12345678 dbname=douyin sslmode=disable")
    db, err := sql.Open("postgres",DB_DSN)
        if err != nil {
        log.Fatal("Failed to open a DB connection: ", err)
    }
    defer db.Close()

    //执行更改操作
    _, err = db.Exec("DELETE FROM  users  where id=$1", 4)
    if err != nil {
        log.Fatal(err)
    }
    //打印日志
    log.Printf("delete ok!!!")

    //测试数据是否更改成功，执行具体的查询语句
    var myUser User
    userSql := "SELECT id, email, password FROM users WHERE id = $1"

    //设置查询参数为4，即要更改数据的ID值
    err = db.QueryRow(userSql, 4).Scan(&myUser.ID, &myUser.Email, 
    &myUser.Password)
    if err != nil {
        log.Fatal("Failed to execute query: ", err)
    }

    //输出查询结果
    fmt.Printf("hello email: %s, password: %s, welcome back!\n", 
    myUser.Email,myUser.Password)
}

执行以上程序代码，执行输出结果

​

至此到这里关于golang操作数据库postgresql就告一段落了，收工。

代码已上传到github：github.com/dongguangming/golang-le...

注：由于我没有用可视化编程工具，是用vi编写的go代码，请你们自行排版其结构。

参考：

1. Postgresql 密码设置 www.mamicode.com/info-detail-197754...

2. golang连接postgresql数据库 msd.misuland.com/pd/31814385785970...

3. cannot find module providing package github.com/xxx: working directory is not part of a module www.sunzhongwei.com/cannot-find-mo...

4. SSL is not enabled on the server stackoverflow.com/questions/219591...

5. Resolve “FATAL:no pg_hba.conf entry for host” Error when you Connect from PGAdmin4 www.cisco.com/c/en/us/support/docs...

6. Connect to PostgreSQL and Run a Query golangcode.com/postgresql-connect-...

7. golang postgresql CRUD www.cnblogs.com/ibgo/p/6010245.htm...

本作品采用《CC 协议》，转载必须注明作者和本文链接