MySQL与PostgreSQL的使用区别

发表于 更新于 本文字数： 1.4k 阅读时长 ≈ 5 分钟

以MySQL 9以及PostgreSQL 17为例，从Web开发者的角度出发，列举两个数据库在使用方面的差异，仅介绍与CRUD依赖的操作，存储过程/主从复制暂不介绍

账号管理

管理员

MySQL创建管理员账户操作如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 1. 创建mydb_admin管理员账户
-- 格式：`CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';`
CREATE USER 'mydb_admin'@'%' IDENTIFIED WITH caching_sha2_password BY 'your_password';

-- 2. 授权数据库/表访问权限
-- 格式：`GRANT PRIVILEGE ON database.table TO 'username'@'host';`
GRANT ALL PRIVILEGES ON mydb.* TO 'mydb_admin'@'%';
-- 如果不清楚有哪些权限，执行
-- SHOW PRIVILEGES;

-- 3. 立即生效
FLUSH PRIVILEGES;

-- 4. 查看账户列表核对
SELECT * FROM mysql.user;
-- SELECT * FROM mysql.user WHERE User='mydb_admin' AND Host='%';

-- 5.查看账户授权
SHOW GRANTS FOR 'mydb_admin'@'%';

PostgreSQL创建管理员账户操作如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 1. 创建mydb_admin管理员账户
CREATE ROLE mydb_admin LOGIN PASSWORD 'your_password';

-- 2. 授权数据库/表访问权限（需要多条语句）
GRANT ALL PRIVILEGES ON DATABASE mydb TO mydb_admin;
-- 如果不想让账号访问其他db
-- REVOKE CONNECT ON DATABASE otherdb1 FROM PUBLIC;

-- 所有跟public相关的授权都需要先切换到指定数据库
\c mydb;
-- 授权public
GRANT ALL PRIVILEGES ON SCHEMA public TO mydb_admin;
-- 后续所有变更自动授权（由super admin执行）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO mydb_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO mydb_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO mydb_admin;

-- 3. 删除角色
-- DROP OWNED BY mydb_admin;
-- DROP ROLE mydb_admin;

注意

MySQL通过上面配置即可对外提供访问，PostgreSQL还需要配置pg_hba.conf，此外，如果删除后重建mydb，需要重新授权

1
2
3
4
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# host  all             all             0.0.0.0/32              scram-sha-256
host    mydb mydb_admin 0.0.0.0/32 scram-sha-256
# hostssl mydb mydb_admin 0.0.0.0/32 scram-sha-256

Web读写

MySQL创建CRUD账户操作如下

1
2
3
4
5
6
7
8
9
10
11
-- 1. 创建读写账户
CREATE USER 'mydb_write'@'%' IDENTIFIED WITH caching_sha2_password BY 'your_password';

-- 授权
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'mydb_write'@'%';

-- 2. 创建只读账户
CREATE USER 'mydb_read'@'%' IDENTIFIED WITH caching_sha2_password BY 'your_password';

-- 授权
GRANT SELECT ON mydb.* TO 'mydb_read'@'%';

PostgreSQL创建CRUD账户操作如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 1. 创建读写账户
-- 创建角色
CREATE ROLE mydb_write_role;

-- 角色授权，只能连接mydb
GRANT CONNECT ON DATABASE mydb TO mydb_write_role;
-- 所有跟public相关的授权都需要先切换到指定数据库
\c mydb;
GRANT USAGE ON SCHEMA public TO mydb_write_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO mydb_write_role;
-- 后续所有变更自动授权（由super admin以及mydb_admin执行）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO mydb_write_role;

-- 创建用户并关联角色
CREATE USER mydb_write_user WITH PASSWORD 'your_password';
GRANT mydb_write_role TO mydb_write_user;

-- 2. 创建只读账户
-- 创建角色
CREATE ROLE mydb_read_role;

-- 角色授权
GRANT CONNECT ON DATABASE mydb TO mydb_read_role;
-- 所有跟public相关的授权都需要先切换到指定数据库
\c mydb;
GRANT USAGE ON SCHEMA public TO mydb_read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mydb_read_role;
-- 后续所有变更自动授权（由super admin以及mydb_admin执行）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mydb_read_role;

-- 创建用户并关联角色
CREATE USER mydb_read_user WITH PASSWORD 'your_password';
GRANT mydb_read_role TO mydb_read_user;

DDL

建库

MySQL建库操作如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. 列出所有数据库
SHOW DATABASES;

-- 2. 创建mydb数据库
CREATE DATABASE mydb;
-- 创建并设置charset
-- CREATE DATABASE mydb CHARACTER SET utf8mb4;

-- 3. 查看建库sql
SHOW CREATE DATABASE mydb;

-- 4. 查看数据库列表核对
SELECT * FROM information_schema.SCHEMATA;

-- 5. 切换到mydb
USE mydb;

PostgreSQL建库操作如下

1
2
3
4
5
6
7
8
9
10
-- 1. 列出所有数据库
\l+
-- 或者
SELECT datname FROM pg_database;

-- 2. 创建mydb数据库
CREATE DATABASE mydb;

-- 3. 切换到mydb
\c mydb

建表

MySQL建表操作如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 1. 创建数据表
-- 指定innodb、纪录备注、创建索引
CREATE TABLE order_main(
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "ID",
  `order_number` VARCHAR(30) NOT NULL COMMENT "order number",
  `user_id` BIGINT NOT NULL COMMENT "user id",
  `product_id` BIGINT NOT NULL COMMENT "product id",
  -- ...
  UNIQUE KEY(`order_number`),
  INDEX `idx_user_id` (`user_id`)
) ENGINE InnoDB DEFAULT CHARSET utf8mb4 COMMENT "order main";

-- 单独创建索引示例如下
CREATE INDEX `idx_product_id` ON `order_main`(`product_id`);

-- 2. 查看数据表列表核对
SHOW TABLES;
-- SHOW TABLES LIKE '%order_main%';

-- 3. 查看建表语句
SHOW CREATE TABLE order_main;

PostgreSQL建表操作如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 1. 创建数据表
-- 与关键字相同的字段需要使用双引号，不支持备注
CREATE TABLE order_main(
  "id" INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  "order_number" VARCHAR(30) NOT NULL,
  "user_id" BIGINT NOT NULL,
  "product_id" BIGINT NOT NULL,
  -- ...
  UNIQUE("order_number")
);

-- 非pk以及unique索引需要单独创建，名称需要区分数据表
CREATE INDEX "order_main_idx_product_id" ON "order_main"("product_id");

-- 2. 查看数据表列表核对
\dt+
-- 列出所有数据库表
-- \dt *
-- 或者
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

-- 3. 查看数据表结构，不支持查看建表sql
\d order_main

其他

命令行显示优化

MySQL如下

1
2
3
4
-- 将;改为\G，以扩展模式输出
SHOW CREATE TABLE order_main\G

-- mysql 所有输出自带命令执行耗时时长，单位：秒

PostgreSQL如下

1
2
3
4
5
-- 当内容长度超过一行自动切换为扩展模式输出
\x auto

-- 输出sql命令执行使用的时间，单位：毫秒
\timing on

导入/导出sql

MySQL如下

1
2
3
4
5
# 导入
mysql -h localhost -u mydb_admin -P 3306 -p mydb < mydb.sql

# 导出
mysqldump -h localhost -u mydb_admin -P 3306 -p mydb album > mydb.album.sql

或者使用sql导入文件

1
2
3
4
USE mydb;

-- 文件需要放在指定目录
SOURCE /tmp/mydb.album.sql

PostgreSQL如下

1
2
3
4
5
# 导入，非if not exists，有数据会报错
psql -d mydb -f mydb.sql

# 导出
pg_dump -t public.album mydb > mydb.album.sql

数据库配置

MySQL如下

1
2
3
4
5
6
7
8
-- 查看服务器相关配置
SHOW VARIABLES;

-- 查看服务器与buffer_pool相关的配置
SHOW VARIABLES LIKE '%buffer_pool%';

-- 修改max_connections
SET GLOBAL max_connections=1024;

PostgreSQL如下

1
2
3
4
5
6
7
8
9
10
11
-- 查看服务器相关配置
SHOW ALL;

-- 查看max_connections
SHOW max_connections;

-- 查看服务器以max开头的配置
SELECT name, setting FROM pg_settings WHERE name LIKE 'max_%';

-- 修改timezone
SET timezone='Asia/Hong_kong';

进程管理

MySQL如下

1
2
3
4
5
6
-- 查看当前活动过进程
SHOW PROCESSLIST;
-- SHOW FULL PROCESSLIST;

-- 结束进程
KILL _pid_;

PostgreSQL如下

1
2
3
4
5
6
7
8
9
-- 查看当前活动过进程
SELECT * FROM pg_stat_activity;

-- 结束进程
SELECT pg_cancel_backend(_pid_);
SELECT pg_terminate_backend(_pid_);

-- 重新加载配置
SELECT pg_reload_conf();