0. 引用
sqlx地址:https://github.com/jmoiron/sqlx
PostgreSQL官网:https://www.postgresql.org/
1. PostgreSQL安装、运行
可以到官网下载二进制包 https://www.postgresql.org/download/
也可以用apt、源代码或者WAPP、LAPP等方式安装
由于docker更方便，直接用docker安装运行
创建数据存放的目录
mkdir /data
mkdir /data/postgres
运行PostgreSQL
docker run -it --name postgres --restart always -e POSTGRES_PASSWORD='admin123' -e ALLOW_IP_RANGE=0.0.0.0/0 -v /data/postgres:/var/lib/postgresql -p 54321:5432 -d postgres
-e POSTGRES_PASSWORD='abc123'设置密码为admin123-e ALLOW_IP_RANGE=0.0.0.0/0所有ip可以连接-v /data/postgres:/var/lib/postgresql数据存在本地/data/postgres-p 54321:5432指定端口为54321
PgAdmin4连接测试
2. Go连接数据库
最主要是设置dsn, host设置为数据库ip或域名，port为端口默认5432，我这里用54321，user是postgres ，password是刚才设置的admin123 ，dbname根据需求设置
package main
import (
"fmt"
"github.com/jmoiron/sqlx"
_ "github.com/lib/pq"
)
var db *sqlx.DB
func initDB() (err error) {
dsn := "host=X.X.X.X port=54321 user=postgres password=admin123 dbname=postgres sslmode=disable"
db, err := sqlx.Connect("postgres", dsn)
if err != nil {
fmt.Printf("connect DB failed, err:%v\n", err)
return
}
db.SetMaxOpenConns(50)
db.SetMaxIdleConns(5)
return
}
func main() {
err := initDB()
if err != nil {
return
}
fmt.Println("DB Connected...")
}
3. 建立测试用的库和表
新建一个数据库test用于测试
CREATE DATABASE "test"
WITH
ENCODING = 'UTF8'
;
再建一个表user_info
CREATE TABLE "public"."user_info" (
"id" serial4,
"uid" int8 NOT NULL,
"name" varchar(255) NOT NULL,
"group" varchar(255) NOT NULL,
"balance" decimal(8,2) NOT NULL,
"proportion" float8 NOT NULL,
"create_time" timestamp NOT NULL,
"comments" varchar(255),
PRIMARY KEY ("id")
);
CREATE INDEX "indexs" ON "public"."user_info" USING btree (
"name",
"group"
);
CREATE UNIQUE INDEX "uniques" ON "public"."user_info" USING btree (
"uid"
);
4. 增删改
增删改用sqlx的exec
增 Insert
插入数据 要注意MySQL占位符用的? ,? ,? 而PG用的$1 ,$2 ,$3
func testInsert() {
sqli := `INSERT INTO "public"."user_info"
("uid", "name", "group", "balance", "proportion", "create_time", "comments")
VALUES ($1, $2, $3, $4, $5, $6, $7);`
_, err := db.Exec(sqli, 100, "名字", "组", 2.33, 27.148, "2022-05-08 18:11:22", nil)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
fmt.Printf("insert success\n")
}
删 Delete
删除id为6的行
func testDelete() {
sqld := `DELETE FROM "public"."user_info" WHERE "id" = $1`
ret, err := db.Exec(sqld, 6)
if err != nil {
fmt.Printf("delete failed, err:%v\n", err)
return
}
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
fmt.Printf("get RowsAffected failed, err:%v\n", err)
return
}
fmt.Printf("delete success, affected rows:%d\n", n)
}
改 Update
把uid为100的name改为张三
func testUpdate() {
sqlu := `UPDATE "public"."user_info" SET "name" = $1 WHERE "uid" = $2`
ret, err := db.Exec(sqlu, "张三", 100)
if err != nil {
fmt.Printf("update failed, err:%v\n", err)
return
}
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
fmt.Printf("get RowsAffected failed, err:%v\n", err)
return
}
fmt.Printf("update success, affected rows:%d\n", n)
}
5. 查
查询前先写一个结构体，查到后直接绑定
type UserInfoType struct {
Id int `db:"id"`
Uid int64 `db:"uid"`
Name string `db:"name"`
Group string `db:"group"`
Balance float64 `db:"balance"`
Proportion float64 `db:"proportion"`
CreateTime time.Time `db:"create_time"`
Comments sql.NullString `db:"comments"`
}
5.1 查询单行数据
func testSelectOne() {
sqls := `SELECT * FROM "public"."user_info" WHERE "uid" = $1`
var user UserInfoType
err := db.Get(&user, sqls, 100)
if err != nil {
fmt.Printf("get failed, err:%v\n", err)
return
}
fmt.Printf("one user: %#v\n", user)
}
5.2 查询多行数据
func testSelectAll() {
sqls := `SELECT * FROM "public"."user_info" WHERE "uid" > $1`
var users []UserInfoType
err := db.Select(&users, sqls, 0)
if err != nil {
fmt.Printf("query failed, err:%v\n", err)
return
}
fmt.Printf("all users: %#v\n", users)
}
5.3 In查询
查询uid为 集合(2,3)的行
func testSelectIn() {
uids := []int{2, 3}
sqlin := `SELECT * FROM "public"."user_info" WHERE "uid" in(?)`
query, args, err := sqlx.In(sqlin, uids)
if err != nil {
fmt.Printf("sqlin failed, err:%v\n", err)
return
}
query = db.Rebind(query)
fmt.Println(query)
var users []UserInfoType
err = db.Select(&users, query, args...)
if err != nil {
fmt.Printf("query failed, err:%v\n", err)
return
}
fmt.Printf("all user: %#v\n", users)
}
