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

0. 安装PostgreSQL 数据库
可以参考PostgreSQL官网 https://www.postgresql.org/download/linux/redhat/ 安装该数据库
特别需要说明的是，安装完成后，自动建立了一个名为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： https://github.com/dongguangming/golang-learn/tree/master/go-postgresql
注：由于我没有用可视化编程工具，是用vi编写的go代码，请你们自行排版其结构。

参考： Postgresql 密码设置 http://www.mamicode.com/info-detail-1977540.html golang连接postgresql数据库 https://msd.misuland.com/pd/3181438578597038522 cannot find module providing package github.com/xxx: working directory is not part of a module https://www.sunzhongwei.com/cannot-find-module-providing-package-githubcomxxx-working-directory-is-not-part-of-a-module SSL is not enabled on the server https://stackoverflow.com/questions/21959148/ssl-is-not-enabled-on-the-server Resolve "FATAL:no pg_hba.conf entry for host" Error when you Connect from PGAdmin4 https://www.cisco.com/c/en/us/support/docs/cloud-systems-management/cloudcenter/212585-resolve-fatal-no-pg-hba-conf-entry-for.html Connect to PostgreSQL and Run a Query https://golangcode.com/postgresql-connect-and-query/ golang postgresql CRUD https://www.cnblogs.com/ibgo/p/6010245.html