PostgreSQL

一：PostgreSQL介绍

PostgreSQL是一个功能强大的开源的关系型数据库。底层基于C实现。

PostgreSQL的开源协议和Linux内核版本的开源协议是一样的。BSD协议，这个协议基本和MIT开源协议一样，说人话，就是你可以对PostgreSQL进行一些封装，然后商业化并进行收费。

PostgreSQL的名字咋来的。之前叫Ingres,后面为了解决一些ingres中的一些问题，作为后面的ingres，就起名叫postgre。

PostgreSQL版本迭代的速度比较快，现在最新的正式的发布版本，已经到了15.RELEASE。

PGSQL的版本选择一般有两种：

如果为了稳定的运行，推荐使用12.x版本。
如果想体验新特性，推荐使用14.x版本。

PGSQL允许跨版本升级，而且没有什么大问题。

PGSQL社区特别活跃，基本是三个月一发版。意味着很多常见的BUG都可以得到及时的修复。

PGSQL其实在国外使用的比较多，国内暂时还是以MySQL为主。

但是国内很多国产数据库都是基于PGSQL做的二次封装：比如华为GaussDB,还有腾讯的Tbase等等。甚至很多公司原来的Oracle，直接平转到PGSQL。同时国内的很多云产品都支持PGSQL了。

PGSQL因为开源，有很多做数据迁移的工具，可以让你快速的MySQL,SQLServer,Oracle直接平转到PGSQL内部，比如pgloader这样的数据迁移工具。

PGSQL的官方地址：PostgreSQL: The world’s most advanced open source database

PGSQL的中文社区：文档目录/Document Index: 世界上功能最强大的开源数据库…

二：PostgreSQL和MySQL的区别

技术没有好坏之分，只是看一下是否符合你的业务，能否解决你的业务需求。其次也要查看社区的活跃度以及更新的频次。比如：MQ,Rabbit,Rocket,Kafka,Pular…

MySQL不支持的几点内容：

MySQL不支持序列概念，Squence。
MySQL的数据类型不够丰富。
使用MySQL时，网上比较好用的插件比较少。
MySQL的性能优化监控工具不是很多，定位问题的成本比较高。
MySQL的主从复制没有一个官方的同步策略，同步问题难以解决。
MySQL虽然开源，但是不够彻底，GPL开源策略（如果你使用mysql做点东西你也得开源）

PostgreSQL相对MySQL上述问题的特点：

PostgreSQL的数据类型嘎嘎丰富。
PostgreSQL是有序列的概念的。
PostgreSQL的插件特别丰富。
PostgreSQL支持主从复制的同步操作，可以实现数据的0丢失。
PostgreSQL的MVCC实现和MySQL不太一样。PostgreSQL一行数据会存储多个版本。最多可以存储40亿个事务版本。

三：PostgreSQL的安装

咱们只在Linux中安装，不推荐在Windows下安装。

Linux的版本尽量使用7.xb版本，最好是7.6或者7.8版本。

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

如果出错，可能是那些问题：

安装Linux的时候，一定要选择最小安装
你的Linux不能连接外网
Linux中5432端口，可能被占用了

1	systemctl status postgresql-14

[root@iZ2zeedzgfcteisfxy4e6vZ ~]# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2025-08-18 20:26:00 CST; 3min 18s ago
     Docs: https://www.postgresql.org/docs/14/static/
 Main PID: 1470 (postmaster)
   CGroup: /system.slice/postgresql-14.service
           ├─1470 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─1473 postgres: logger
           ├─1475 postgres: checkpointer
           ├─1476 postgres: background writer
           ├─1477 postgres: walwriter
           ├─1478 postgres: autovacuum launcher
           ├─1479 postgres: stats collector
           └─1480 postgres: logical replication launcher

Aug 18 20:26:00 iZ2zeedzgfcteisfxy4e6vZ systemd[1]: Starting PostgreSQL 14 database server...
Aug 18 20:26:00 iZ2zeedzgfcteisfxy4e6vZ postmaster[1470]: 2025-08-18 20:26:00.485 CST [1470] LOG:  redirecting log output...ocess
Aug 18 20:26:00 iZ2zeedzgfcteisfxy4e6vZ postmaster[1470]: 2025-08-18 20:26:00.485 CST [1470] HINT:  Future log output wil...log".
Aug 18 20:26:00 iZ2zeedzgfcteisfxy4e6vZ systemd[1]: Started PostgreSQL 14 database server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@iZ2zeedzgfcteisfxy4e6vZ ~]#

PostgreSQL 不推荐使用root管理，在安装成功postgreSQL后，他默认会给你创建一个用户：postgres

玩PGSQL前，先切换到postgres

1	su postgres

切换到postgres用户后，直接输入psql即可进入到postgreSQL提供的的客户端

查看有那些库，如果是新安装的，有三个库

\l

[root@iZ2zeedzgfcteisfxy4e6vZ ~]# su postgres
bash-4.2$ who am i
root     pts/0        2025-08-18 20:17 (120.235.181.139)
bash-4.2$ psql
could not change directory to "/root": Permission denied
psql (14.19)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=#

四：PostgreSQL的配置

要搞两个配置信息，一个关于postgreSQL的远程连接配置以及postgreSQL的日志配置。

PostgreSQL的主要配置放在数据目录下的，postgresql.conf以及pg_hba.conf配置文件

\q 退出

这些配置文件都放在了

1	/var/lib/pgsql/14/data

bash-4.2$ ls -l
total 136
drwx------ 5 postgres postgres  4096 Aug 18 20:25 base
-rw------- 1 postgres postgres    30 Aug 18 20:26 current_logfiles
drwx------ 2 postgres postgres  4096 Aug 18 20:37 global
drwx------ 2 postgres postgres  4096 Aug 18 20:26 log
drwx------ 2 postgres postgres  4096 Aug 18 20:25 pg_commit_ts
drwx------ 2 postgres postgres  4096 Aug 18 20:25 pg_dynshmem
-rw------- 1 postgres postgres  4577 Aug 18 20:25 pg_hba.conf
-rw------- 1 postgres postgres  1636 Aug 18 20:25 pg_ident.conf
drwx------ 4 postgres postgres  4096 Aug 18 20:31 pg_logical
drwx------ 4 postgres postgres  4096 Aug 18 20:25 pg_multixact
drwx------ 2 postgres postgres  4096 Aug 18 20:25 pg_notify

上图可以看到，postgreSQL的核心文件，都属于postgres用户，操作的时候，尽可能的别用root用户，容易玩出坑，尽可能先切换到postgres用户去玩。

PostgreSQL默认情况下不支持远程连接的，这个跟MySQL几乎一样

MySQL给mysql.user追加用户，一般是采用grant的命令去玩。
PostgreSQL要基于配置文件修改，才能指定用户是否可以远程连接。

直接去修改pg_hba.conf 配置文件

用户以及对应数据库和连接方式的编写模板


# databases they can access.  Records take one of these forms:
#
# local         DATABASE  USER  METHOD  [OPTIONS]
# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostgssenc    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnogssenc  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# 第一块
local代表本地连接，host代表可以指定连接的ADDRESS
# 第二块
database 编写数据库名，如果写all,代表所有库都可以连接
# 第三块
user编写连接的用户，可以写all，代表所有用户
# 第四块
address 代表那些IP地址可以连接
# 第五块
method加密方式，这块不用过多关注，直接md5
# 修改：允许任意地址的所有用户连接数据库

host    all             all             0.0.0.0/0               md5
# 我配置的是set nu 86行

为了实现远程连接，除了用户级别的这种配置，还要针对服务级别修改一个配置

服务级别的配置在postgresql.conf

：set nu 看见需要大概在60行左右

发现默认情况下PGSQL只允许localhost连接，直接配置*即可解决问题。

60 listen_addresses = '*'         # what IP address(es) to listen on;
     61                                         # comma-separated list of addresses;
     62                                         # defaults to 'localhost'; use '*' for all
     63                                         # (change requires restart)
     64 #port = 5432                            # (change requires restart)
     65 max_connections = 100                   # (change requires restart)
     66 #superuser_reserved_connections = 3     # (change requires restart)
     67 #unix_socket_directories = '/run/postgresql, /tmp'      # comma-separated list of directories
     68                                         # (change requires restart)
     69 #unix_socket_group = ''                 # (change requires restart)
     70 #unix_socket_permissions = 0777         # begin with 0 to use octal notat

记得为了生效，一定要重启

1 2	# postgres 密码不管直接切换roor sudo systemctl restart postgresql-14

配置数据库的日志

修改配置大概428行这样样子

postgreSQL默认情况下

# 代表日志是开启的
logging_collector = on
# 日志存放的路径，默认放到当前目录下的log里
log_directory = 'log'
# 日志的文件名，默认是postgresql为前缀，星期作为后缀
log_filename = 'postgresql-%a.log'
# 默认一周过后，日志文件会被覆盖
log_truncate_on_rotation = on
# 一天一个日志文件
log_rotation_age = 1d
# 一个日志文件 没有大小限制
log_rotation_size = 0

五：PostgreSQL的基操

只在psql命令行（客户端）下，执行了一次\l ，查看了所有的库信息

可以直接基于psql查看一些信息，也可以基于psql进入到命令行后再做操作

psql --help

1	可以直接进入命令行的原因，是psql默认情况下，就是以postgres用户去连接本地的pgsql，所以可以直接进入下面的图是默认的连接方式

后面都基于psql的命令行（客户端）去进行操作

5.1 构建用户

命令绝对不要去背，需要使用去找帮助文档\help

1
2
3

# 区别就是create user默认有连接权限，create role没有，不过可以基于选项去设置
CREARE USER 名称 [ [WITH] 选项 [...] ]
CREARE role 名称 [ [WITH] 选项 [...] ]

构建一个超级管理员用户

1	create user root with SUPERUSER PASSWORD 'root';

成功：

1
2
3


postgres=# create user root with SUPERUSER PASSWORD 'root';
CREATE ROLE

\help 即可查看到数据库级别的一些命令

\? 可以查看到服务级别的一些命令

退出psql命令行 \q

编写psql命令尝试去用root用户登录

1	psql -h 182.92.135.91 -p 5432 -U root -W -d postgres

但是没有对应的数据库所以报错

光有用户不给登录比如要有数据库

1	create database root;

可以再不退出psql的情况下切换用户 \c root

也可以退出psql，重新基于psql命令去切换用户以及数据库

查看当前的全部用户信息

\du

5.2 权限操作

权限操作前，要先掌握以下PGSQL的逻辑结构

PostgreSQL 的逻辑架构可以按“从大到小、层层嵌套”来理解。下面用一句话概括后再拆开讲：

“一个 PostgreSQL Server 里只有一个 Cluster；Cluster 里可以有很多 Database；每个 Database 里可以有很多 Schema；Schema 里再存放真正的对象（表、视图、索引、函数、触发器、序列 …）。”

Server（服务器实例）
指正在运行的 postmaster 进程 + 共享内存 + 后台进程。
一个操作系统上可以同时跑多个 Server，只要它们监听不同的端口、数据目录分开即可。
Cluster（实例 / 数据目录）
• 每个 Server 对应一个 Cluster（由 initdb 创建）。
• 物理上就是 $PGDATA 目录；逻辑上是“一组共享系统目录 (pg_catalog) 的数据库集合”。
• Cluster 内的所有数据库共享：
– 同一个配置文件 (postgresql.conf)
– 同一个 WAL 流
– 同一个用户/角色体系 (pg_authid)
– 同一个表空间列表 (pg_tablespace)
Database（数据库）
• Cluster 下面可以创建多个独立的数据库 (CREATE DATABASE)。
• 每个数据库彼此隔离：
– 独立的系统表副本 (pg_class, pg_attribute, …)
– 独立的 search_path、public schema 等。
• 注意：跨数据库不能直接访问表，要用 dblink / FDW 或逻辑复制。
Schema（模式）
• 每个数据库里可以建多个 schema（CREATE SCHEMA）。
• 用于“逻辑分组”和“权限隔离”。
• 默认有 public；search_path 决定对象查找顺序。
对象（Objects）
位于 schema 之下，包括：
• 表、视图、物化视图、外部表
• 索引（B-tree、GIN、GIST …）
• 序列、复合类型、域
• 函数、存储过程、触发器、事件触发器
• 行级安全策略、规则、约束、注释 …
Tablespace（表空间）
• 与上面“垂直层级”不同，它是 横向的存储映射。
• 逻辑上属于 Cluster；物理上是任意的目录路径。
• 创建对象时可通过 TABLESPACE xxx 把表/索引放到指定目录，实现“冷热数据分离”。

一张脑图式速记：

┌────────────────────────────────────────────────────────┐
│                   Server (postmaster)                  │
│  ----------------------------------------------------  │
│  Cluster ($PGDATA)                                     │
│  ├─ Database A                                         │
│  │   ├─ Schema s1 ── 表/视图/索引/函数/触发器          │
│  │   └─ Schema s2 ── …                                 │
│  ├─ Database B                                         │
│  │   └─ public ── …                                    │
│  └─ shared objects: roles, tablespaces, …             │
└────────────────────────────────────────────────────────┘

这样看，就能快速定位：
“我连接的是哪个 Server → 哪个 Cluster → 哪个 Database → 哪个 Schema → 哪个对象”。

server,cluster,tablespace 级别这个级别一般是基于pg_hba.conf去配置

database级别：通过命令级别操作，grant

namespace，schema级别：玩的不多,不去了解

对象级别：通过grant命令去设置

小任务

构建一个用户（你自己的名字）

比如构建一个数据库，在这个数据库下构建一个schema（数据库默认有一个public的schema）

再这个schema下构建一个表

给用户赋予select，update,insert权限赋值给用户

以超级用户（如 postgres）登录

1	psql -h 182.92.135.91 -U postgres -W

在 psql 里依次执行

-- 1) 建用户
CREATE USER yjy WITH PASSWORD 'yjy123';

-- 2) 建数据库，并把 owner 设成 yjy
CREATE DATABASE yjy_db OWNER yjy;

-- 3) 切到新建的数据库
\c yjy_db

-- 4) 建一个业务 schema（除了 public 之外）
CREATE SCHEMA yjy_schema AUTHORIZATION yjy;

-- 5) 在该 schema 下建一张示例表
CREATE TABLE yjy_schema.demo (
    id   serial PRIMARY KEY,
    info text
);

-- 6) 把 yjy_schema.demo 的常用权限一次性赋给 yjy
--    也可以写 GRANT SELECT, INSERT, UPDATE ... 但更简单的写法：
GRANT USAGE ON SCHEMA yjy_schema TO yjy;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA yjy_schema TO yjy;

-- 可选：让 yjy 今后自己新建的表也自动带这些权限
ALTER DEFAULT PRIVILEGES IN SCHEMA yjy_schema
    GRANT SELECT, INSERT, UPDATE ON TABLES TO yjy;

验证

退出超级用户会话，用新建用户登录：

1	psql -h 182.92.135.91 -U yjy -d yjy_db -W

在 yjy_db=> 提示符下：

-- 应该能成功
INSERT INTO yjy_schema.demo(info) VALUES ('hello');
SELECT * FROM yjy_schema.demo;
UPDATE yjy_schema.demo SET info='hi' WHERE id=1;

到此，任务完成。

六：图形化界面安装

我使用navicat了

直接在public下建表

CREATE TABLE hello (
    id   serial PRIMARY KEY,
    msg  text
);

INSERT INTO hello(msg) VALUES ('hi');
SELECT * FROM hello;

七、数据类型

PGSQL支持的类型特别丰富，大度哟书的类型MySQL都有对应的关系

名称	说明	对比MySQL
布尔类型	boolean，标准的布尔类型	MySQL中虽然没有对应的boolean，但是有替换的类型，数值的tinyint类型和PGSQL的boolean都是占一个字节。
整型	smallint(2字节），integer(4字节)，bigint(8字节)	跟MySQL没啥区别
浮点型	decimal,numeric (和decimal一样，精准浮点型)，real(float),double precision(double) money(货币类型)	和MySQL基本也没有啥区别，MySQL支持float,double,decimal.MySQL没有这个货币类型。
字符串类型	varchar(n)(character varying), char(n) (character),text	和MySQL基本没区别。PGSQL存储的varchar类型，可以存储一个G，MySQL好像存储64kb
日期类型	data(年月日),time(时分秒),timestamp(年月日时分秒)	和MySQL基本一致。mysql有个datetime
二进制类型	bytea-存储二进制类型	MySQL也支持，MySQL是blog
位置类型	bit（n）-定长位图，bit varying(n)	存储0或者1.MySQL也有但是用的不多。
枚举类型	enum,跟java的enum一样	MySQL也一样
集合类型	点直线线段。。。。	MySQL没有但是一般也用不到
数组类型	在类型后面，追加[] 代表存储数组	MySQL没有
JSON类型	json(存储JSON数据的文本)，JSONB（存储JSON二进制）【JSONB 是 PostgreSQL 的高性能 JSON 类型，除非你有特殊理由要保留原始文本，否则默认选 JSONB】	MySQL8.x也支持
ip类型	cidr(存储ip地址)	MySQL不支持
。。。

八、PostgreSQL基本操作

8.1 单引号和双引号

在PGSQL中，与SQL语句时，单引号用来标识实际的值。双引号用来标识一个关键字，比如表名，字段名。

1
2
3

-- 单引号写具体的值，双引号类似MySQL的``标记，用来填充关键字
-- 下面的葡萄牙会报错，因为葡萄牙不是关键字
select 1.14 '卡特尔',"卡塔尔";

8.2 数据类型转换

第一种方式：只需要在值的面前，添加上具体的数据类型即可

1 2	-- 将字符串转成位图类型 select bit '010010010100101';

第二种方式：也可以在具体值的后面，添加上：：类型，来指定

-- 数据类型
select '2011-11-11'::date;
select '0101010101010'::bit(20);
select '13'::int;

第三种方式：使用CAST函数

1 2	-- 类型转换的完整写法 select CAST(varchar '100' as int);

8.3 布尔类型

可以存储三个值：true,false,null

1 2	-- 布尔类型的约束没有那么强 true,false 大小写随意他会给你转；同时yes,on他也认识 select true,false,'yes'::boolean,boolean 'no',True,False,Null::boolean;

8.4 数值类型

8.4.1 整型

整型比较简单，主要就是三个：

smallint,int2: 2字节
integer,int ,int4：4字节
bigint,int8 : 8字节

正常没啥事就integer,如果要存主键，比如雪花算法，那就bigint,空间要节约，根据情况smallint

8.4.2 浮点型

浮点类型就2个（其实是一个）

decimal(n,m)：本质就是numeric,PGSQLh会帮你转换
numeric(n.m): PGSQL本质的浮点类型

针对浮点类型的数据，就使用numeric

8.4.3 序列

MySQL中的主键自增，是基于auto_increment 去实现，MySQL里没有序列的对象。

PGSQL和Oracle十分相似，支持序列：sequence

PGSQL可没有auto_increment.

序列的正常构建方式：

create sequence yjy.table_id_seq;
-- 查询下一个值
select nextval('yjy.table_id_seq');
-- 查询当前值
select currval('yjy.table_id_seq')

默认情况下，sequence的起始值都是0，每次nextval递增1，最大值9223372036854775807

告诉缓存，插入的数据比较多，可以指定告诉缓存，一次性计算出20个后续的值，nextval时就可以不去计算，直接去高速缓存拿值，效率会有一点点提升。

序列大多数的应用，是作表的主键自增效果。

-- 表自增
create table yjy.xxx(
	id int8 default nextval('yjy.table_id_seq'),
    name varchar(16)
);
insert into yjy.xxx (name) values ('xxx');
select * from yjy.xxx;

这一种写法没有问题但是比较麻烦

PGSQL提供了序列的数据结构，可以在声明表结果时，直接指定序列的类型即可。

smallserial
serial
bigserial

-- 表自增
create table yjy.yyy(
	id bigserial,
    name varchar(16)
);
insert into yjy.yyy (name) value ('yyy');

在drop表之后，序列不会删除，但是序列会变为不可用的状态。

因为序列在使用serial去构建时，会绑定在指定表的指定列上。

8.5 字符串类型

字符串类型用的时最多的一种，在PGSQL里，主要支持三种：

character(就是MySQL的char类型)，定长字符串。（最大可用存储1G）
character varying(varchar)
text

操作没什么说的，但是字符串的常见函数特别多

字符串的拼接一定要使用||来拼接

8.6 日期类型

在PGSQL中，核心的时间类型，就三个。

timestamp (时间戳，覆盖年月日时分秒)
data(年月日)
time（时分秒）

在PGSQL中，声明时间的方式。

之需要使用字符串的正常编写yyyy-MM-dd HH:mm:ss 就可以转换为时间类型

直接在字符串位置使用之前讲道德数据类型转换就可以了。

当前系统时间：

可以使用now作为当前系统时间（没有时区的概念）

select TIMESTAMP 'now'

-- 直接查询now 没有时区的概念
select time with tinme zone 'now' at time zone '08:00:00'

select CURRENT_TIMESTAMP;

也可以使用current_timestamp的方式获取（推荐，默认东八区）

日期类型的运算

正常对date类型做 + - 运算，默认单位就是天

也可以用interval的方式

1	select timestamp '2011-11-11 12:12:12' + interval '1day' + interval '1minute' + interval '1month';

8.7 枚举类型

枚举类型MySQL也支持，只是没什么用。

可以声明枚举类型作为表中的字段类型，这样可以无形的给表追加规范

-- 声明一个星期的枚举
create type week as enum ('Mon','Tues','Sun');

create table test(
	id bigserial,
	weekday week
	
);
insert into test (weekday) values ('Mon')

8.8 IP类型

PGSQL支持IP类型的存储，支持IPv4,IPv6这种，甚至Mac内的类型也支持

这种IP类，可以在存储IP时，帮助做校验,也支持范围查找

1	select '192.168.101.200'::cidr;

8.9 JSON&JSONB类型

JSON在MySQL8.x也做了支持，但是MySQK支持的不好，因为JSON类型做查询时，基本无法给JSON字段做索引。

PGSQL支持JSON类型以及JSONB类型

JSON和JSONB的使用基本没有区别

撇去JSON类型，本质上JSON格式就一个字符串，比如MySQL5.7不支持JSON的情况下，使用text也可以，但是字符串类型无法校验JSON的格式，其次单独的字符串没有办法只获取JSON中某个key对应的value.

JSON和JSONB的区别：

JSON类型无法构建索引，JSONB类型可以创建索引
JSON类型的数据中多余的空格会被存储下来，JSONB会自动取消多余的空格。
JSON类型甚至可以存储重复的key,以最后一个为准，JSONB不会保留多余的重复key.
JSON会保留存储时key的顺序，JSONB不会保存原有顺序。

JSON中key对应的value数据类型

JSON	PGSQL
String	text
number	numeric
boolean	boolean
null	(none)
操作JSON

上述的四种JSON存储的类型
JSON数组
JSON对象
构建表存储JSON

8.10 复合类型

复合类型就好像Java中的一个对象，Java中有一个User,User和表做了一个映射，User中有个人信息对象。可以基于复合类型对映射上个人信息

public class User{
    private Integer id;
    private Info info;
}
class Info {
    private String name;
    private Integer age;
}

-- 构建复合类型 映射上Info
create type info_type as (name varchar(32),age int);
-- 构建表 映射user
create table user(
    id serial;
    info info_type;
)
-- 添加数据
insert into tb_user (info) values (('张三',23));
insert into tb_user (info) values (('Lucy',223));
insert into tb_user (info) values (('张三',23));
insert into tb_user (info) values (('张三',23));

select * from tb_user;

8.11 数组类型

数组还是要依赖其他类型，比如在设置住址，住址可能有多个住址，看采用数组类型去修饰字符串。

PGSQL中，指定数组的方式就是[]，可以指定一维数组，也支持二维甚至更多维数组。

构建数组的方式：

drop table test;
create table test(
	id serial,
	col1 int[],
	col2 int[2],
	col3 int[][]
);

-- 构建表指定数组长度后，并不是说数组内容只有2的长度，可以插入更多数据
-- 甚至在你插入数据，如果将二维数组的结构扔到一维数组上，也可以存储。
select '{how,are,you}'::VARCHAR[];

数据类型 - 数组（比较方式）

-- 包含
select array[1,2] @> array[1]
-- 被包含
select array[1,2] <@ array[1,2,4]
-- 是否有相同元素
select array[2,4,4,45,1] && array[1];

九:表

表的构建语句，基本都会。

核心在于构建表时，要指定上一些约束。

9.1 约束

9.1.1 主键

create table test(
	id bigserial PRIMARY KEY,
	name VARCHAR(32)
);

9.1.2 非空

create table test(
	id bigserial PRIMARY KEY,
	name VARCHAR(32) not NULL
);

9.1.3 唯一

create table test(
	id bigserial PRIMARY KEY,
	name VARCHAR(32) not NULL,
	id_card VARCHAR(32) UNIQUE
);

9.1.4 检查

-- 检查约束
-- 价格的表 price,discount_price
drop table test;
create table test(
	id bigserial primary KEY,
	name VARCHAR(32) not null,
	price numeric check(price > 0),
	discount_price numeric check(discount_price > 0),
	check(price >= discount_price)
);

9.1.5 外键（了解）

9.1.6 默认值

一般公司内，要求表中除了主键和业务字段之外，必须要有5个字段

created，create_id, updated, updated_id, is_delete

1	created timestamp default current_timestamp;

9.2 触发器

触发器trigger,是由事件触发的一种存储过程

当对表进行insert,update,delete,truncate操作时，会触发表的Trigger(看触发器的创建时指定的事件)

构建两张表，学生信息表，学生分数表。

在删除学生信息的同时，自动删除学生的分数。

create table student(
	id int,
	name varchar(32)
);

create table score(
	id int,
	student_id int,
	math_score numeric,
	english_score numeric,
	chinese_score numeric
);

insert into student (id,name) values (1,'张三');
insert into student (id,name) values (2,'lisi1');

insert into 
score
(id,student_id,math_score,english_score,chinese_score) 
VALUES
(1,1,66,66,66);


insert into 
score
(id,student_id,math_score,english_score,chinese_score) 
VALUES
(2,2,77,77,77);

为了完成级联删除，需要编写pl/sql

先查看一下PGSQL支持plsql,查看PGSQL的plsql语法

-- 创建一个名为 test() 的无参函数，返回一个整数
-- $$ 可以理解为时一种特殊的单引号
CREATE OR REPLACE FUNCTION test()
RETURNS int                       -- 正确关键字是 RETURNS（有 's'）
AS $$
DECLARE
    money int := 10;             -- 声明局部变量并初始化为 10
BEGIN
    RETURN money;                -- 将变量 money 作为结果返回
END;
$$ LANGUAGE plpgsql;

在简单了解了一下plpgsql的语法后，编写一个触发器函数。

触发器函数允许使用一些特殊变量

-- 构建一个删除学生分数的触发器函数
create FUNCTION trigger_function_delete_student_score() returns trigger as $$
BEGIN
delete from score where student_id = OLD.id;
	return old;
END;
$$ LANGUAGE plpgsql;

开始构建触发器，在学生信息表删除时，执行前面声明的触发器函数

create trigger trigger_student 
after delete on student 
for each row 
execute function trigger_function_delete_student_score();

9.3 表空间

在存储数据时，数据肯定要落到磁盘上，基于构建的tablespace,指定数据存放在磁盘上的物理地址。

如果没有字节设计tablespace，PGSQL会自动指定一个位置作为默认的存储点。

1 2	-- 查询表存储的物理地址 select pg_relation_filepath('student');

这个位置是在$PG_DATA后的存放地址

1	$PG_DATA == /var/lib/pgsql/14/data/

410000 起始就是存储数据的物理文件

构建表空间，指定数据存放位置

1 2	-- 构建表空间，构建表空间需要用户权限是超级管理员，起始需要指定的目录已经存在 create tablespace tp_test location '/var/lib/pgsql/14/data/tp_data';

构建数据库，以及表，指定到这个表空间中

1	create database db_test tablespace tp_test;

其实指定表空间的存储位置后，PGSQL会在$PG_DATA目录下存储一份，同时在咱们构建tablespace时，指定的路径下也存储一份。

进一步发现，其实在PGSQL目录下，存储的是一个link,链接文件，类似一个快捷方式

9.4 视图

跟MySQL没什么区别，把一些复杂的操作封装起来，还可以隐藏一些敏感数据。

视图对于用户来说，就是一张真实的表，可以直接基于视图查询一张或者多张表的信息。

视图对于开发来说，就是一条SQL语句。

在PGSQL中，简单（单表）的视图时允许写操作的

但是强烈不推荐对视图进行写操作,虽然PGSQL默认允许（简单的视图）

写入的时候，其实修改的是表本身

create view vw_Socre as 
(select id,math_score from score);

select * from vw_score;
update vw_score set math_score = 99 where id =2;

-- 复杂视图(两张表关联) 默认不允许修改
create view vw_student_score as 
(select stu.name,score.math_score from student stu,score score where stu.id = score.student_id);

9.5 索引

9.5.1 索引的基本概念

先了解概念和使用

索引是数据库中快速查询数据的方法.

索引能提升查询效率的同时，也会带来一些问题

增加了存储空间
写操作时，花费的事件比较多

索引可以提升效率，甚至还可以给字段做一些约束

9.5.2 索引的分类

BTree索引：最常用的索引。

Hash索引：跟Mysql类似，做等值判断

GIN索引：针对字段的多个值的类型，比如数组类型

9.5.3 创建索引看效果

准备大量数据，测试索引效果

-- 测试索引效果
create table tb_index(
	id bigserial PRIMARY KEY,
	name VARCHAR(36),  -- 修正为足够容纳 UUID
	phone VARCHAR(64)[]
);

-- 添加30w条数据测试效果（300w太慢，建议先测试30w）
DO $$
DECLARE
	i int := 0;
BEGIN
	WHILE i < 300000 LOOP
		i := i + 1;
		INSERT INTO tb_index(name, phone)
		VALUES (
			md5(random()::text || clock_timestamp()::text)::uuid,
			array[random()::varchar(64)]
		);
	END LOOP;
END;
$$ LANGUAGE plpgsql;

select * from tb_index where id = 22222;
select * from tb_index where name = 'c6278y29178192192';
explain select * from tb_index where name = 'c6278y29178192192';
-- Seq Scan 代表全表扫描

这个时候我们创建一个索引

1
2
3

-- name 字段构建索引（默认是b-tree）
create index index_tb_index_name on tb_index(name);
-- Index Scan (使用索引)

包含查询

select * from tb_index where phone @> arrary['12121212121'::varchar(63)];
explain select * from tb_index where phone @> array['12121212121'::varchar(64)];
-- Seq Scan 全表扫描

-- 给phone字符串数组类型字段构建一个GIN索引
create index index_tb_index_phone_gin on tb_index using gin(phone);
-- Bitmap Index 位图扫描

9.6 物化视图

前面说过普通视图，本质就是一个SQL语句，普通的视图并不会本地磁盘存储任何物理。

每次查询视图都是执行这个SQL。效率有点低

物化视图从名字上就可以看出来，必然是要持久化一份数据的。使用套路和视图基本一致。这样一来查询物化视图，就想打关于查询一张单独的表，相比之前的普通视图，物化视图就不需要每次都查询复杂SQL，每次查询的都是真实的物理存储地址中的一份数据（表）。

物化视图因为会持久化到本地，完全脱离原来的表结构。

而且物化视图是可以单独设置索引等信息来提升物化视图的查询效率。

但是有好处也有坏处，更新时间不太好把握。如果更新频频繁，对数据库压力也不小。如果更新不频繁，会造成数据存在延迟问题，实时性就不好了。

如果要更新物化视图，可以采用触发器的形式，当原表中的数据被写后，可以通过触发器执行同步物化视图的操作，或者就基于定时任务去完成数据同步。

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_test AS
SELECT id, name, price FROM test;

-- 查询物化视图
SELECT * FROM mv_test;

-- 原表插入新数据
INSERT INTO test VALUES (4, '月饼', 5.1);

-- 此时物化视图不会自动更新
SELECT * FROM mv_test; -- 看不到“月饼”

-- 手动刷新
REFRESH MATERIALIZED VIEW mv_test;

-- 现在可以看到“月饼”了
SELECT * FROM mv_test;

物化视图不会自动同步原表数据，必须手动 REFRESH MATERIALIZED VIEW。

如果你需要实时同步，应该用普通视图（VIEW）**或**触发器+表的方案，而不是物化视图。

全量更新和增量更新

十、事务

什么是ACID

在日常操作中，对于一组相关操作，通常要求要么都成功，要么都失败。在关系型数据库中，称这一组操作为事务。为了保证整体事务的安全性，有ACID这一说：

原子性A：事务是一个最小的执行单元，一次事务中的操作要么都成功，要么都失败
一致性C：在事务完成时，所有数据必须保持在一致的状态。（事务完成后，最终结果和预期结果一致）
隔离性：一次事务操作，要么时其他事务操作前的状态，要么其他事务操作后的状态，不存在中间状态。
持久性：事务提交后，数据会落到本地磁盘，修改是永久性的。

PostgreSQL中，在事务的并发问题里，也是基于MVCC，多版本并发控制区维护数据的一致性。相比于传统的锁操作，MVCC最大的优点可以让读写互相不冲突。

当然PostgreSQL也支持表锁和行锁，可以解决写写的冲突。

PostgreSQL相比于其他数据，有一个比较大的优化，DDL也可以包含在一个事务中。比如集群中的操作，一个事务可以保证多个节点都构建出一个表，才算成功。

事务的基本使用

首先基于前面的各种操作，应该以及自动体会到了，PostgreSQL是自动提交事务。跟MySQL一样的。

1	\set AUTOCOMMIT off

三个命令：

begin
commit
rollback

10.3 保存点（了解）

比如项目中有一个大事务操作，不好控制，超时有影响，回滚会造成一切重来，成本太高。

我针对大事务，拆分成几个部分。第一部分完成后，构建一个保存点。如果后面操作失败了，需要回滚，不需要全盘回滚，回滚到之前的保存点，继续重试。

有人会发现，破坏了整体事务的原子性。

但是，只要操作合理，可以在保存点做充实，只要重试不成功，依然可以回滚；

十一、并发问题

11.1 事务的隔离级别

在不考虑隔离性的前提下，事务的并发可能会出现的问题：

脏读：读到了其他食物未提交的数据（必须避免这种情况）
不可重复读：同一事务中，多次查询同一数据，结果不一致，因为其他事务修改造成的。（一些业务中这种不可重复读不是问题）
幻读：同一事务中，多次查询同一数据，因为其他事务对数据进行了增删，导致出现了一些问题（一些业务中这种幻读不是问题）

针对这些并发问题，关系型数据库有一些事务的隔离级别，一般用4种。

READ UNCOMMITTED：读未提交（啥用没有，并且PGSQL没有，提供了只是为了完整性）
READ COMMITTED：读已提交，可以解决脏读（PGSQL默认隔离级别）
REPEATABLE READ：可重复读，可以解决脏读和不可重复读（MySQL默认是这个隔离级别，PGSQL也提供了，但是设置为可重复读，效果还是串行化）
SERIALIZABLE：串行化，啥都能解决（锁，效率慢）

PGSQL在老版本种，只有两个隔离级别，读已提交和串行化。在PGSQL中不存在脏读问题。

11.2 MVCC

首先要搞清楚，为啥要有MVCC。

如果一个数据库，频繁的进行读写操作，为了保证安全，采用锁的机制。但是如果采用锁机制，如果一些事务在写数据，另外一个事务就无法读数据，会造成读写之间相互阻塞。大多的数据库都会采用一个机制多版本并发控制MVCC来解决这个问题。

比如你要查询一行数据，但是这行数据正在被修改，事务还没提交，如果此时对这行数据加锁，会导致其他的读操作阻塞，需要等待，如果采用PostgreSQL,他的内部会针对这一行保存多个版本，如果数正在被写入，让读操作区查询之前的版本，就不会阻塞。等写操作的事务提交了，读操作才能查看到最新的数据。这机制可以确保读写操作没有冲突，这就是MVCC的主要特点。

写写操作，和MVCC没关系，哪个就是加锁！

在操作之前，先了解一下PGSQL中，每张表都会自带两个字段

xmin:给当前事务分配的数据版本。如果有其他事务做了写操作，并且提交事务了，就给xmin分配新的版本。
xmax：当前事务没有存在新版本，xmax就是0.如果有其他事务做了写操作，未提交事务，将写操作的版本放到xmax中

把 PostgreSQL 的每一行数据想像成一张「小纸条」，纸条上除了记录真正的字段值（id、name …）外，还偷偷写了两个隐藏字段：

xmin：这行诞生的「出生证号」（创建它的事务号）
xmax：这行注销的「死亡证号」（删除它的事务号，0 表示还没死）

借助这两个号码，加上当前自己事务所拿到的「快照号」（snapshot），就能判断：

我能不能看到这一行？
这一行对别人来说是不是已经死了？

用纸条的故事串一遍图片里的 6 个瞬间

时刻	事务号	纸条内容（id=1）	说明
1	Tx 100 刚开启	`xmin=100, xmax=0, name='张三'`	张三这张纸条是事务 100 刚生成的，目前没人删除。
2	Tx 100 再次看	同上	事务 100 用自己的快照看：xmin=100 ≤ 100 且 xmax=0，可见。
3	Tx 101 修改	把 name 改成 “李四”，并生成新版本	旧纸条：xmin=100, xmax=101（被 101 删除）
4	Tx 100 第三次看	旧纸条：xmin=100, xmax=101	100 的快照只认 ≤100 的出生证和未死亡的行。
5	Tx 101 提交	删除与新增都正式生效	数据库里现在只有新纸条有效。
6	Tx 100 结束后再看	新纸条：xmin=101, xmax=0	此时 Tx 100 已经结束，拿的是新的快照。发现 xmin=101 大于旧快照号 100，看不到这条新生命，于是只能看到已提交的新版本，name 变成 “李四”。

一句话总结

xmin ≤ 快照号且 xmax = 0 → 这行对我来说活着并且可见。
xmax ≤ 快照号 → 这行对我来说已经被删除。
xmin > 快照号 → 这行在我出生之后才被创建，现在我还看不到。

借助这一对隐藏字段，PostgreSQL 实现了 MVCC（多版本并发控制）：读写互不阻塞，每个事务看到的都是「属于自己的那一刻的世界」。

PostgreSQL中也提供了页锁，咨询所，但是这个不需要要关注，这个是为了锁的完整性。

12.1 表锁

表锁显而易见，就是锁住整一张表。表锁也分很多种模式。

表锁的模式很多，其中最核心的两个：

ACCESS SHARE ：共享锁（读锁），读读操作不阻塞，但是不允许写操作并行
ACCESS EXCLUSIVE：互斥锁（写锁），无论什么操作进来都阻塞

详细查看官网

ACCESS SHARE 让“大家一起读”；ACCESS EXCLUSIVE 让“谁也别动”。通过 LOCK TABLE 手动获取或在执行 SQL 时隐式获取，再用 pg_locks 一目了然地观察锁冲突即可。

12.2 行锁

PostgreSQL的行锁和MySQL的基本一模一样的，基于select for update就可以指定行锁。

MySQL中有一个概念，for update时，如果select 的查询没有命中所以可能会锁表。

PostgreSQL有个特点，一般情况，在select的查询没有命中索引时，他不一定会锁表，然后会实现行锁。

PostgreSQL 行锁就两种：

FOR UPDATE
FOR SHARE

使用示例：

-- 先开启事务
BEGIN;

-- 基于 FOR UPDATE 锁住 id = 3 的行
SELECT * FROM test WHERE id = 3 FOR UPDATE;

-- 更新该行
UPDATE test SET name = 'vi' WHERE id = 3;

-- 提交事务，锁释放
COMMIT;

其他会话若要锁同一行，会被阻塞。

十三、备份&恢复

防止数据丢失的第一道防线就是备份。数据丢失有的是硬件损坏，还有人为的误删之类的，也有BUG的原因导致误删数据。

正常备份和恢复，如果公司有DBA，一般咱们不用参与，BUT,学的java，啥都得会点

在PostgreSQL中，有三种备份方式：

SQL备份（逻辑备份）：其实就是利用数据库自带的类型dump的命令，或者时你用图形化界面执行导入导出时，底层就是基于这个dump命令实现的。备份出来一份sql文件，谁需要就复制给谁。

优点：简单，方便操作，有手就行，还挺可靠。

缺点：数据量如果比较大，这种方式巨慢。

文件下同备份（物理备份）：其实就是找到当前数据优酷，数据文件在磁盘存储的位置，将数据文件直接复制一份或者多份，存储在不同的物理机上，即使物理机爆炸一个，还有其他物理机。

优点：相比逻辑备份，恢复的速度快。

缺点：在备份数据时，可能数据还在写入，一定程度上会丢失数据。在恢复数据时，也需要注意数据库的版本和环境必须保持高度的一致。如果是线上正在运行的数据库，这种复制的方式无法在生成环境实现。

如果说要做数据的迁移，这种方式还不错滴。

归档备份：（也属于物理备份）

先了解几个概念，在PostgreSQL有多个子进程来辅助一些操作

BgWrite 进程：BgWrite是将内存中的数据写到磁盘中的一个辅助进程。当向数据库中执行写操作后，数据不会马上持久化到磁盘里。这个主要是为了提升性能。BgWriter会周期性的将内存中的数据写入到磁盘。但是这个周期时间，长了也不行，慢了也不行。
- 如果快了，IO操作频繁，效率慢。
- 如果慢了，有查询操作需要内存中的数据时，需要BgWriter现把数据从内存写道磁盘中，再提供查询操作作为返回结果，会导致查询操作效率变低。
- 考虑一个问题：事务提交了数据没有落磁盘，这时，服务器宕机了怎么办？
WalWriter进程：WAL就是write ahead log的缩写，说人话就是预写日志（redo log）.其实数据还在内存中时，其实已经写入了WAL日志一份。这样依赖，即使BgWriter进程没写入到磁盘中时，数据也不好存在丢失的问题。
- WAL能单独做备份么？单独

待完成…

十四、数据迁移、集群

待完成…

SpringBoot+postgresql+mybatis

public class User {
	private String name;
	private Integer age;

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

}

application.yml

server:
  servlet:
    context-path: /PostgreSQL
  port: 8080
  
spring:
  datasource:
    url: jdbc:postgresql://192.168.11.12:5432/1912
    username: postgres
    password: 123456
    driver-class-name: org.postgresql.Driver

mybatis:
  type-aliases-package: com.yangjiayu.bean
  mapper-locations: classpath:mapper/*.xml

@RestController
public class UserController {

	@Autowired
	private UserMapper mapper;

	@RequestMapping("/select")
	public List<User> select() {
		return mapper.getUser();
	}

	@RequestMapping("/insert")
	public int insert(User user) {
		return mapper.insertUser(user);
	}

	@RequestMapping("/update")
	public int update(User user) {
		return mapper.updateUser(user);
	}

	@RequestMapping("/delete")
	public int delete(String name) {
		return mapper.deleteUser(name);
	}
}

Mapper:

@Mapper
public interface UserMapper {

	public List<User> getUser();

	public int insertUser(User user);

	public int updateUser(User user);

	public int deleteUser(String name);

}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yangjiayu.mapper.UserMapper">
	<select id="getUser" resultType="User">
		select * from user_test
	</select>

	<insert id="insertUser" parameterType="User">
		insert into
		user_test(name,age) values(#{name},#{age})
	</insert>
	<update id="updateUser" parameterType="User">
		update user_test set
		age=#{age} where name = #{name}
	</update>
	<delete id="deleteUser" parameterType="String">
		delete from user_test
		where name=#{name}
	</delete>
</mapper>

SpringBoot+postgresql+mybatisplus

我们创建一个 product 表，包含ID、名称、价格、库存和创建时间等字段。

-- 连接到你的数据库，然后执行以下 DDL 语句
CREATE TABLE product (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, -- 使用 BIGINT 和 IDENTITY 实现自增主键，这是 PostgreSQL 推荐的方式
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    create_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP -- 创建时间，默认为当前时间
);

-- 添加一些注释，方便理解
COMMENT ON TABLE product IS '商品表';
COMMENT ON COLUMN product.id IS '主键ID (自增)';
COMMENT ON COLUMN product.name IS '商品名称';
COMMENT ON COLUMN product.price IS '商品价格';
COMMENT ON COLUMN product.stock IS '库存数量';
COMMENT ON COLUMN product.create_time IS '创建时间';

-- 为了测试，可以插入一些初始数据
INSERT INTO product (name, price, stock) VALUES
('高性能笔记本电脑', 7999.00, 50),
('机械键盘', 499.50, 200),
('4K显示器', 2599.00, 100),
('无线鼠标', 199.00, 300),
('降噪耳机', 1299.00, 80);

你可以通过 Spring Initializr 快速创建一个项目。

选择以下依赖：

Spring Web
MyBatis-Plus
PostgreSQL Driver
Lombok (强烈推荐，可以简化代码)

Maven 的 pom.xml 文件核心依赖如下：

<dependencies>
    <!-- Spring Boot Web Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- MyBatis-Plus Starter -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.3.1</version> <!-- 建议使用较新稳定版 -->
    </dependency>

    <!-- PostgreSQL JDBC Driver -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- Lombok for code simplification -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

    <!-- Spring Boot Test Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

在 src/main/resources/ 目录下创建或修改 application.yml 文件，配置数据库连接和 MyBatis-Plus。

server:
  port: 8080

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/your_database_name # 替换为你的数据库地址和名称
    username: your_username # 替换为你的数据库用户名
    password: your_password # 替换为你的数据库密码
    driver-class-name: org.postgresql.Driver

mybatis-plus:
  # 如果你的 Mapper XML 文件放在特定位置，可以指定。对于纯注解和MP自带方法，此项可不配
  mapper-locations: classpath:/mapper/*.xml
  # 配置驼峰命名转换
  configuration:
    map-underscore-to-camel-case: true
  global-config:
    db-config:
      # 配置主键策略为数据库自增
      id-type: auto

. Entity (实体类)

创建 com.example.demo.entity.Product 类。

@TableName(“product”): 将类与数据库中的 product 表对应。
@TableId(type = IdType.AUTO): 声明 id 是主键，并且类型是数据库自增。
@Data: Lombok 注解，自动生成 Getter, Setter, toString() 等方法。
@TableField(“create_time”): 将 createTime 属性映射到 create_time 数据库列（如果开启了驼峰转换，此注解可省略）。

package com.example.demo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Data
@TableName("product")
public class Product {

    @TableId(type = IdType.AUTO)
    private Long id;

    private String name;

    private BigDecimal price;

    private Integer stock;

    // 数据库是 create_time，这里是 createTime，MP 会自动做驼峰转换
    private LocalDateTime createTime;
}

创建 com.example.demo.mapper.ProductMapper 接口。

核心： 只需要继承 BaseMapper。
继承后，ProductMapper 就自动拥有了大量的 CRUD 方法，如 insert, selectById, updateById, deleteById, selectList, selectPage 等。你无需再写任何 XML 或 SQL！

package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.Product;
import org.apache.ibatis.annotations.Mapper;

@Mapper // 或者在主启动类上使用 @MapperScan
public interface ProductMapper extends BaseMapper<Product> {
    // 无需编写任何方法，即可拥有强大的CRUD功能！
    // 如果有复杂的多表查询，可以在这里自定义方法，并使用 XML 或注解编写 SQL。
}

在你的 Spring Boot 主启动类上，添加 @MapperScan 注解来扫描 Mapper 接口。

package com.example.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.demo.mapper") // 扫描 Mapper 接口所在的包
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

创建 com.example.demo.controller.ProductController 类，提供 API 接口。

这里我们将重点展示 分页和排序 的实现。

package com.example.demo.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.entity.Product;
import com.example.demo.mapper.ProductMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.util.StringUtils;

import java.util.List;

@RestController
@RequestMapping("/products")
public class ProductController {

    @Autowired
    private ProductMapper productMapper;

    // 1. 新增商品
    @PostMapping
    public Product createProduct(@RequestBody Product product) {
        productMapper.insert(product);
        return product;
    }

    // 2. 根据ID查询商品
    @GetMapping("/{id}")
    public Product getProductById(@PathVariable Long id) {
        return productMapper.selectById(id);
    }

    // 3. 查询所有商品
    @GetMapping
    public List<Product> getAllProducts() {
        return productMapper.selectList(null); // 传入 null 表示无条件查询
    }

    // 4. 更新商品
    @PutMapping("/{id}")
    public Product updateProduct(@PathVariable Long id, @RequestBody Product product) {
        product.setId(id); // 确保ID正确
        productMapper.updateById(product);
        return product;
    }
    
    // 5. 根据ID删除商品
    @DeleteMapping("/{id}")
    public String deleteProduct(@PathVariable Long id) {
        int result = productMapper.deleteById(id);
        return result > 0 ? "删除成功" : "删除失败，商品不存在";
    }

    // 6. 【重点】分页和排序查询
    @GetMapping("/page")
    public IPage<Product> getProductPage(
            @RequestParam(defaultValue = "1") long current, // 当前页码，默认为1
            @RequestParam(defaultValue = "10") long size,   // 每页数量，默认为10
            @RequestParam(required = false) String sortField, // 排序字段，如 "price", "stock"
            @RequestParam(required = false, defaultValue = "true") boolean isAsc // 是否升序，默认为是
    ) {
        // 1. 创建分页对象
        Page<Product> page = new Page<>(current, size);

        // 2. 创建查询条件构造器
        QueryWrapper<Product> queryWrapper = new QueryWrapper<>();

        // 3. 处理排序
        if (StringUtils.hasText(sortField)) {
            // isAsc 为 true 则升序，false 则降序
            queryWrapper.orderBy(true, isAsc, sortField);
        } else {
            // 默认按创建时间降序
            queryWrapper.orderByDesc("create_time");
        }

        // 4. 执行分页查询
        // selectPage 方法会自动完成分页逻辑和 COUNT 查询
        return productMapper.selectPage(page, queryWrapper);
    }
}

注意： 要使 MyBatis-Plus 的分页功能生效，你需要配置一个分页插件。在 Spring Boot 中，这通常是自动配置的，但如果遇到问题，可以手动添加一个配置类：

package com.example.demo.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 添加分页插件，并指定数据库类型为 PostgreSQL
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
        return interceptor;
    }
}

现在你可以启动 Spring Boot 应用程序了。启动后，使用 Postman 或 curl 等工具测试你的 API。

1. 新增商品
POST http://localhost:8080/products
Body (JSON):

{
    "name": "智能手表",
    "price": 899.00,
    "stock": 150
}

2. 分页查询（不带排序，默认按创建时间降序）
GET http://localhost:8080/products/page?current=1&size=3
你会得到第一页的3条数据。

3. 分页并按价格升序排序
GET http://localhost:8080/products/page?current=1&size=3&sortField=price&isAsc=true
返回的结果将是价格最低的3个商品。

4. 分页并按库存降序排序
GET http://localhost:8080/products/page?current=1&size=3&sortField=stock&isAsc=false
返回的结果将是库存最高的3个商品。

5. 查询 ID 为 2 的商品
GET http://localhost:8080/products/2

6. 删除 ID 为 1 的商品
DELETE http://localhost:8080/products/1

总结

对比你之前提供的 XML 方式，你会发现 MyBatis-Plus 的巨大优势：

代码极简：Mapper 接口继承 BaseMapper 后，无需编写任何 SQL 就能完成单表的 CRUD。
功能强大：内置了分页、逻辑删除、乐观锁等高级功能，只需简单配置即可使用。
条件构造器 (QueryWrapper)：可以非常灵活、安全地构建复杂的查询条件，避免了手动拼接 SQL 的风险和繁琐。
无缝集成：与 Spring Boot 完美集成，配置简单，开箱即用。

这个 Demo 完整地展示了如何使用 Spring Boot 和 MyBatis-Plus 操作 PostgreSQL 数据库，并实现了核心的分页和排序功能，希望能帮助你快速上手