03.1 阶段 0:环境准备与关系模型基础
所属:全栈开发面试题库 / 学习路径 / 03 MySQL 与 PostgreSQL
本页目录
一、阶段定位
本阶段是整条路径的起点,解决两个问题:把 MySQL 与 PostgreSQL 在本机跑起来,以及建立"库—表—行—列—约束"这一套关系模型的基本语言。后面所有阶段都建立在"能连上数据库、能看懂建表语句、能写一条带 WHERE 的 SELECT"之上。
阶段 0 不要求理解索引、事务、MVCC,但要求把环境装稳、把三张示例表建出来。判断自己是否卡在本阶段,用以下现象对号:
- 执行
docker ps看不到数据库容器,或mysql -uroot -p/psql -U postgres报Can't connect to MySQL server/connection refused——这是环境没通,不要急着看语法。 - 写
CREATE TABLE时搞不清INT、BIGINT、VARCHAR(50)、DATETIME、NUMERIC(10,2)的区别,建出来的订单金额用了FLOAT——这是数据类型没选对,后面金额比较会出精度问题。 - 建表时不知道主键是什么、外键起什么作用,表之间没有任何关联——这是关系模型没建立,阶段 1 写 JOIN 时会完全无从下手。
- 插入中文后查询出来是
??或乱码——这是字符集没配对,不是"数据库坏了"。
衔接关系:本阶段产出的示例库(用户表、商品表、订单表)会被 阶段 1 SQL 进阶与数据建模 直接复用,用来练习 JOIN、聚合与窗口函数;阶段 2 会在这套表上灌 100 万行数据验证索引。环境若装在系统盘、数据目录没做卷映射,后面阶段 4 练习备份恢复时会因为"数据到底存在哪"而卡住,因此本阶段统一用容器 + 命名卷。
二、阶段目标与投入
学完本页,应当能用可观察的行为证明以下能力:
| 能力目标(可验证的行为) | 验证方式 |
|---|---|
用一条 docker run 命令分别启动 MySQL 8 与 PostgreSQL 16,指定端口、root 密码、数据卷 |
docker ps 能看到两个容器;docker volume ls 能看到命名卷 |
用命令行客户端连上两个实例,执行 SELECT VERSION(); 与 SELECT version(); |
版本号分别为 8.x 与 16.x |
在两个库各建一个 shop 示例库,建出用户、商品、订单、订单明细四张表 |
\dt(psql)或 SHOW TABLES;(mysql)能看到四张表 |
| 给用户表与订单表之间加上外键约束,并演示外键约束拒绝孤儿订单 | 插入不存在的 user_id 时被报错拒绝 |
用 SELECT ... WHERE ... ORDER BY ... LIMIT 完成基本查询与分页 |
能说出 LIMIT 两个参数分别是什么意思 |
| 能解释字符集与排序规则的关系,插入中文不乱码 | SHOW VARIABLES LIKE 'character%'; 或 SHOW server_encoding; 显示 utf8mb4 / UTF8 |
建议学时:
- 全职(每天 4–6 小时):3–4 天,约 12–16 小时。装环境 2 小时、建表与插数据 4 小时、CRUD 与约束 4 小时、字符集与类型 2 小时、复盘 2 小时。
- 在职(每天 2 小时):1.5 周,约 14 小时。不要一天看完概念,要在本机把每条命令敲一遍。
学时分配:容器启动与连接 20%、实例/库/schema 层级 10%、数据类型 20%、约束与 DDL 25%、DML 基础 15%、字符集 10%。数据类型与约束是面试和线上事故的高频源头,时间要给够。
三、先修自检
| 先修项 | 合格标准 | 不合格时回到 |
|---|---|---|
| 命令行基础 | 能在终端执行 cd、ls/dir、docker --version、查看端口占用 |
01.1 操作系统 |
| Docker 可用 | docker run hello-world 能正常输出;知道什么是镜像、容器、卷 |
08.1 容器化 |
| 网络端口概念 | 知道 3306 是 MySQL 默认端口、5432 是 PostgreSQL 默认端口,能用 netstat/ss 看监听 |
01.2 计算机网络 |
| 一门编程语言基础 | 知道变量、函数、返回值,后面 ORM 与连接池阶段能接得上 | 任意后端语言路径,不在本路径补齐 |
| 文本编辑器 | 能把 SQL 脚本保存为 .sql 文件并执行 |
不强制 |
经验判断:如果你之前只用过 Navicat、DBeaver 这类图形工具而从没碰过命令行客户端,本阶段要刻意用 mysql 与 psql 命令行完成所有操作——线上服务器大多没有图形界面,排障时只能靠命令行。
四、知识地图
"掌握程度"分三档:了解、会用、能讲清原理。
| 模块 | 知识点 | 掌握程度 | 对应题库章节 |
|---|---|---|---|
| 实例与容器 | docker run 参数、端口映射、命名卷、环境变量设密码 | 会用 | 08.1 容器化 |
| 层级模型 | 实例 → database/schema → 表 → 行;MySQL 的 database 与 PostgreSQL 的 schema 差异 | 能讲清原理 | 05.1 关系型数据库通用能力 |
| 数据类型 | 整数宽度、定点/浮点、字符串、日期时间、布尔 | 会用 | 05.1 关系型数据库通用能力 |
| DDL 与约束 | CREATE TABLE、主键、外键、NOT NULL、UNIQUE、CHECK、DEFAULT | 能讲清原理 | 05.1 关系型数据库通用能力 |
| DML 基础 | INSERT、SELECT、WHERE、ORDER BY、LIMIT、UPDATE、DELETE | 会用 | 05.1 关系型数据库通用能力 |
| 字符集 | utf8mb4 / UTF8、collation、连接字符集、中文乱码根因 | 会用 | 05.1 关系型数据库通用能力 |
| 权限与安全 | root 账号、普通账号、GRANT、最小权限原则 | 了解 | 05.1 关系型数据库通用能力 |
进度管理建议:把这张表打印出来,每完成一个模块把"掌握程度"往下调一档;三档全到位再进阶段 1。更细的全量清单见 03.11 知识点全清单,术语定义见 03.12 术语表。
五、分模块精讲
5.1 用 Docker 启动两个实例
为什么需要它:在 Windows/macOS 上原生安装 MySQL 与 PostgreSQL 麻烦、版本难管理、卸载不干净;用容器可以做到"一条命令起、一条命令删",数据通过命名卷持久化。本路径所有阶段都基于这两个容器。
核心机制:
- MySQL 8 官方镜像默认端口 3306,
MYSQL_ROOT_PASSWORD环境变量初始化 root 密码;首次启动会做数据目录初始化,之后重启不再执行。 - PostgreSQL 16 官方镜像默认端口 5432,
POSTGRES_PASSWORD环境变量初始化超级用户postgres密码;默认库是postgres。 -v 卷名:/var/lib/mysql(MySQL)或-v 卷名:/var/lib/postgresql/data(PG)把数据目录挂到命名卷,容器删了数据还在。-p 13306:3306把容器 3306 映射到主机 13306,避免与本机可能已装的数据库冲突。
最小可运行示例:
# 启动 MySQL 8,主机端口 13306,root 密码 Root@123,数据卷 mysql8_data
docker run -d --name mysql8 \
-p 13306:3306 \
-e MYSQL_ROOT_PASSWORD='Root@123' \
-v mysql8_data:/var/lib/mysql \
mysql:8
# 启动 PostgreSQL 16,主机端口 15432,postgres 密码 Pg@123,数据卷 pg16_data
docker run -d --name pg16 \
-p 15432:5432 \
-e POSTGRES_PASSWORD='Pg@123' \
-v pg16_data:/var/lib/postgresql/data \
postgres:16
# 进入命令行客户端
docker exec -it mysql8 mysql -uroot -p'Root@123'
docker exec -it pg16 psql -U postgres
-- 两个库分别验证版本
SELECT VERSION(); -- MySQL 输出如 8.0.36
SELECT version(); -- PostgreSQL 输出如 PostgreSQL 16.3 ...
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
Can't connect to MySQL server on '127.0.0.1' (10061) |
容器没起来,或端口映射写成 -p 3306:3306 但本机 3306 被占用 |
docker ps -a 看状态;换主机端口如 13306 |
connection refused (psql) |
PG 容器未就绪就连接,镜像首次初始化需要几秒 | docker logs pg16 看到 database system is ready to accept connections 再连 |
| 容器删了数据全没 | 没挂卷,数据写在容器可写层 | 用命名卷 -v pg16_data:...,不要用匿名卷 |
密码含 @ 被 shell 解析 |
命令行里没加引号 | 密码用单引号包起来,或写在 .env 文件里 |
面试考法:常问"生产环境你怎么管理 MySQL 实例"。要点答到:容器化用于开发与测试,生产多用云托管 RDS 或本地包安装;关键是数据目录与日志目录要独立挂载、端口与资源限额要固定、镜像版本要锁死(不要用 latest)。
5.2 实例、库与 schema 的层级
为什么需要它:很多人混淆"实例""数据库""schema"三个概念。MySQL 里 CREATE DATABASE 之后直接建表;PostgreSQL 里一个 database 下还有一层 schema(默认 public),跨 database 不能直接 JOIN。这个差异不搞清楚,阶段 1 写查询、阶段 5 做复制时会一直别扭。
核心机制:
- 实例(instance):一个数据库进程,监听一个端口,拥有共享内存与后台线程。
- MySQL:一个实例可以挂多个 database,
USE dbname切换;database 之间物理隔离,不能直接跨库 JOIN(要在同实例下用dbname.table勉强关联)。 - PostgreSQL:实例叫集簇(cluster),一个集簇下多个 database;连接时必须指定一个 database,连接内部才有 schema(默认
public);跨 database 不能直接查询,要靠 postgres_fdw 或dblink。 - 表的全限定名:MySQL 是
库名.表名;PG 是库名.schema.表名(通常省略库名与 schema)。
最小可运行示例:
-- MySQL:建库并切换
CREATE DATABASE shop DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;
SHOW TABLES;
-- PostgreSQL:建库需要在 postgres 库下执行,再重新连接
CREATE DATABASE shop;
\c shop -- psql 内切换到 shop 库
CREATE SCHEMA IF NOT EXISTS public;
\dt -- 列出 public schema 下的表
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
PG 里 SELECT * FROM users 报 relation "users" does not exist |
表建在了别的 schema,或 search_path 不含该 schema | \dt *.users 看表在哪;SHOW search_path; |
MySQL 里 SELECT * FROM shop.orders 慢或报错 |
跨库关联优化器支持有限 | 同一实例内可勉强关联,但跨实例不行 |
删除 database 报 database is being accessed by other users(PG) |
有活动连接 | 先 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='shop'; |
面试考法:问"PostgreSQL 的 schema 和 MySQL 的 database 是一回事吗"。要点:不是。PG 的 schema 是同一 database 下的命名空间,用来组织表(如 hr.employees、sales.orders),不同 schema 可以在同一个连接里 JOIN;MySQL 的 database 更像 PG 的 database,是连接级别的隔离。
5.3 常用数据类型与选择
为什么需要它:字段类型选错是线上事故的高发来源:金额用浮点导致对账差一分、用 VARCHAR(255) 存枚举导致索引膨胀、用 DATETIME 存毫秒级时间导致排序与比较不准。本阶段要把"这个字段该用什么类型"形成肌肉记忆。
核心机制:
- 整数:
TINYINT(1B)、SMALLINT(2B)、INT(4B)、BIGINT(8B);PG 对应smallint/integer/bigint。主键优先BIGINT,预留未来扩容。 - 定点数与浮点数:金额必须用
DECIMAL(M,D)(PG 叫NUMERIC),如DECIMAL(12,2);FLOAT/DOUBLE是二进制浮点,存在精度误差,禁止用于金额。 - 字符串:短固定长度用
CHAR(n),变长用VARCHAR(n);MySQL 的VARCHAR(n)的 n 是字符数不是字节数(utf8mb4 下最多 4 字节/字符);PG 推荐直接用text,性能与varchar相同。 - 日期时间:MySQL 有
DATE、TIME、DATETIME(范围 1000–9999)、TIMESTAMP(范围 1970–2038,带时区转换);PG 有date、time、timestamp(无时区)、timestamptz(带时区)。存 UTC 时间统一用TIMESTAMP/timestamptz。 - 布尔:MySQL 没有原生 BOOLEAN,
BOOLEAN是TINYINT(1)的别名;PG 有真正的boolean(true/false)。
最小可运行示例:
-- MySQL:商品表演示类型选择
CREATE TABLE product (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
price DECIMAL(12,2) NOT NULL, -- 金额,定点
stock INT UNSIGNED NOT NULL DEFAULT 0,
status TINYINT NOT NULL DEFAULT 1, -- 1 上架 0 下架
is_deleted TINYINT(1) NOT NULL DEFAULT 0, -- 逻辑删除
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- PostgreSQL:等价写法
CREATE TABLE product (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(128) NOT NULL,
price NUMERIC(12,2) NOT NULL,
stock INTEGER NOT NULL DEFAULT 0,
status SMALLINT NOT NULL DEFAULT 1,
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
金额字段存 0.1+0.2 得到 0.30000001 |
用了 FLOAT/DOUBLE | 改 DECIMAL(12,2),历史数据用脚本刷 |
| 订单号存 VARCHAR 但按数字排序 | 字符串字典序 vs 数值序 | 订单号定长且纯数字可改 BIGINT,否则按字符串排序 |
TIMESTAMP 到 2038 年报错 |
2038 问题 | 用 DATETIME(MySQL)或 timestamptz(PG) |
MySQL 的 VARCHAR(255) 在建二级索引时超限 |
utf8mb4 下 255×4=1020 字节,联合索引可能超 3072 字节 | 缩短长度,或用 VARCHAR(191)(老版本 innodb_large_prefix 限制) |
面试考法:"金额为什么不能用 double"。要点:IEEE 754 二进制浮点无法精确表示 0.1、0.2 这类十进制小数,累计成千上万笔后对账差几分钱;DECIMAL 是十进制定点存储,精确。追问常问"BIGINT 存分是否可行"——可行,用整数存最小货币单位,省空间且精确,但要统一约定。
5.4 约束:主键、外键、NOT NULL、UNIQUE、CHECK
为什么需要它:约束是数据库最后一道防线。应用层可能漏判、并发请求可能竞态,约束能在数据库层面拒绝脏数据。本阶段要理解每种约束在"防什么"。
核心机制:
- 主键(PRIMARY KEY):唯一且非空,一张表只能一个;InnoDB 按主键聚簇存储(阶段 2 展开)。
- 外键(FOREIGN KEY):子表列必须在父表主键中存在,或为 NULL;
ON DELETE RESTRICT(默认)/CASCADE/SET NULL决定父行删除时的行为。 - NOT NULL:列不允许 NULL;这不是"默认值",而是语义约束。
- UNIQUE:列值唯一,允许多个 NULL(MySQL 与 PG 都把 NULL 视为互不相等)。
- CHECK(MySQL 8.0.16 起真正生效,之前被解析但忽略):自定义条件,如
price > 0。 - 生产实践:高并发大表常去掉外键(由应用层保证一致性),但主键、NOT NULL、UNIQUE、CHECK 要保留。
最小可运行示例:
-- 用户表与订单表,外键关联
CREATE TABLE shop_user (
id BIGINT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
phone VARCHAR(20) NOT NULL,
UNIQUE KEY uk_phone (phone)
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
amount DECIMAL(12,2) NOT NULL,
status VARCHAR(16) NOT NULL DEFAULT 'PENDING',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
REFERENCES shop_user (id) ON DELETE RESTRICT,
CONSTRAINT chk_amount CHECK (amount >= 0)
);
-- 演示外键拒绝:user_id=999 不存在
INSERT INTO orders (id, user_id, amount) VALUES (1, 999, 100.00);
-- MySQL 报错:ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
-- PG 报错:ERROR: insert or update on table "orders" violates foreign key constraint
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
删用户时报错 Cannot delete or update a parent row |
该用户有订单,外键 RESTRICT | 先删订单,或改 ON DELETE CASCADE(要想清楚) |
插入重复手机号报 Duplicate entry |
UNIQUE 约束 uk_phone | 业务层先查重,或捕获 1062 错误做幂等 |
| MySQL 5.7 加了 CHECK 但不生效 | 5.7 之前 CHECK 被解析忽略 | 升级到 8.0.16+,或用触发器 |
| 外键导致大批量删除/更新变慢 | 父表删除要逐行检查子表 | 大表批量操作先禁用外键检查(仅维护窗口),或拆批 |
面试考法:"高并发系统为什么常去掉外键"。要点:外键在每次写时都要做引用检查,大并发下是开销;分库分表后跨库外键根本无法实现;但代价是脏数据要靠应用层与定时任务兜底。追问:"那主键呢"——主键不能去,它是聚簇索引与复制位点的基础。
5.5 DML 基础:INSERT、SELECT、WHERE、ORDER BY、LIMIT
为什么需要它:这是每天都在写的语句,但"深翻页为什么慢""WHERE 与 ON 的区别""LIMIT 两个参数"这些细节要在本阶段建立。
核心机制:
- SELECT 执行逻辑(逻辑顺序):FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT。书写顺序与执行顺序不同。
- WHERE 在分组前过滤行,HAVING 在分组后过滤组。
- LIMIT 两个参数:
LIMIT offset, count(MySQL)或LIMIT count OFFSET offset(PG 同支持)。LIMIT 100000, 20会扫描前 100020 行再丢弃前 10 万,这就是深翻页慢的原因。 - 比较运算符:
=、<>/!=、>、<、BETWEEN、IN、LIKE(%任意多字符、_单字符)、IS NULL。 - 字符串大小写:MySQL 默认 collation 不敏感,PG 默认敏感,这个差异阶段 1 展开。
最小可运行示例:
-- 基础查询:按金额倒序取前 10 笔
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 'PAID' AND amount > 100
ORDER BY created_at DESC, amount DESC
LIMIT 10;
-- 深翻页的代价:offset 越大越慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 游标分页(推荐):用上一页最后一个 id 作为起点
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 第 10 页查询耗时从 5ms 涨到 2s | LIMIT offset 大,扫描并丢弃前 offset 行 | 改游标分页 WHERE id > ? |
WHERE name = 'admin' 能匹配 'Admin' |
MySQL 默认 collation 不区分大小写 | 需要区分时用 COLLATE utf8mb4_bin,或 PG 默认行为 |
WHERE col = NULL 查不到任何行 |
NULL 比较要用 IS NULL |
改 col IS NULL / col IS NOT NULL |
| UPDATE 忘加 WHERE 全表被刷 | 客户端没开事务,或没加 LIMIT | 始终在事务里改数;先 SELECT 核对条件 |
面试考法:"深翻页为什么慢,怎么优化"。要点:OFFSET 要扫描并丢弃前 N 行,N 越大越慢;优化用游标分页(记住上一页最后一个排序键)或覆盖索引延迟关联。追问:"ORDER BY 走什么索引"——排序字段与过滤字段组合出联合索引,阶段 2 展开。
5.6 字符集与排序规则
为什么需要它:中文乱码、大小写不敏感导致的唯一约束重复、ORDER BY 排序结果不符合预期,根源都是字符集与 collation 没配对。这是新手最容易误判为"数据库坏了"的问题。
核心机制:
- 字符集(character set):字符在磁盘上怎么编码,如 utf8mb4(MySQL,支持完整 UTF-8 含 emoji)、UTF8(PG 等价 utf8mb4)。
- 排序规则(collation):字符串怎么比较与排序,如
utf8mb4_0900_ai_ci(MySQL 8 默认,ai=accent insensitive、ci=case insensitive)、utf8mb4_bin(二进制,区分大小写);PG 的en_US.UTF-8、C、POSIX。 - 连接字符集:客户端与服务器之间通信的编码,MySQL 用
SET NAMES utf8mb4,PG 用client_encoding。 - MySQL 的
utf8是个历史陷阱:它最多 3 字节,存不下 emoji 与生僻字,必须用utf8mb4。
最小可运行示例:
-- MySQL:查看与设置
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SET NAMES utf8mb4;
-- 建表时指定
CREATE TABLE t (
name VARCHAR(64)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 大小写敏感查询
SELECT * FROM t WHERE name = 'Admin' COLLATE utf8mb4_bin;
-- PostgreSQL
SHOW server_encoding; -- UTF8
SHOW client_encoding; -- UTF8
SET client_encoding TO 'UTF8';
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
插入中文后查出来是 ?? |
连接字符集不是 utf8mb4 | SET NAMES utf8mb4,或连接串加 characterEncoding=utf8mb4 |
emoji 存入报 Incorrect string value |
列字符集是 utf8(3字节) | 改列/库为 utf8mb4 |
| 唯一约束挡不住 'Admin' 与 'admin' | collation 是 _ci 不区分大小写 | 用 utf8mb4_bin 或业务层加小写唯一索引 |
ORDER BY name 中文排序按拼音还是笔画 |
collation 决定 | MySQL 用 utf8mb4_zh_0900_as_cs 等;PG 用 zh_CN.UTF-8 |
面试考法:"MySQL 的 utf8 与 utf8mb4 区别"。要点:utf8 是阉割版,最多 3 字节,存不下 BMP 之外的字符(emoji、部分生僻汉字);utf8mb4 是完整 UTF-8,每字符最多 4 字节,MySQL 8 已默认。追问:"用了 utf8mb4 索引长度怎么算"——VARCHAR(255) 索引最长 255×4=1020 字节,联合索引要注意 3072 字节限制。
5.7 账号权限与安全基线
为什么需要它:root 账号被应用直接使用、密码写在代码里、任意 IP 可登录,是最常见的安全事故。本阶段建立最小权限意识。
核心机制:
- MySQL:
CREATE USER 'app'@'10.0.%' IDENTIFIED BY '...';,GRANT SELECT, INSERT, UPDATE ON shop.* TO 'app'@'10.0.%';。 - PostgreSQL:
CREATE ROLE app LOGIN PASSWORD '...';,GRANT SELECT ON ALL TABLES IN SCHEMA public TO app;。 - 生产应用账号只给业务库的 SELECT/INSERT/UPDATE/DELETE,不给 DROP、SUPER、GRANT OPTION。
- 密码用环境变量或配置中心注入,不写进代码仓库。
最小可运行示例:
-- MySQL
CREATE USER 'app'@'%' IDENTIFIED BY 'App@Strong!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'%';
FLUSH PRIVILEGES;
-- PostgreSQL
CREATE ROLE app LOGIN PASSWORD 'App@Strong!' CONNECTION LIMIT 20;
GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 应用用 root 连上后误删表 | 权限过大 | 建专用账号,最小权限 |
Access denied for user 'app'@'10.0.0.5' |
GRANT 时主机段不匹配 | 检查 user@host,MySQL 是账号+主机二元组 |
| PG 改表权限后新表还要重新授权 | 默认权限未设 | ALTER DEFAULT PRIVILEGES 批量授权 |
面试考法:"数据库权限设计原则"。要点:最小权限、按应用分离账号、禁止 root 远程登录、审计高危操作、密码定期轮换。
六、分层练习
三档练习逐档升级,上一档没做完不要跳。
6.1 复现型(照着做,目标是"跑通 + 能解释")
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 起两个容器 | 按 5.1 命令启动 MySQL 8 与 PG 16,端口分别 13306/15432 | docker ps 可见;版本查询正常 |
0.5 小时 |
| 建示例库 | 在两个库各建 shop,建 shop_user、product、orders、order_item 四张表 |
\dt / SHOW TABLES 可见四张表 |
1 小时 |
| 外键演示 | 插入不存在 user_id 的订单,观察两个库的报错 | 能抄下错误码 1452 / 23503 | 0.5 小时 |
| 中文与 emoji | 在 name 列插入中文与 emoji,查询不乱码 | 能复现 utf8(3字节)下 emoji 报错 | 0.5 小时 |
| 深翻页对比 | orders 灌 1 万行,对比 LIMIT 10000,10 与游标分页耗时 |
游标分页明显更快 | 1 小时 |
6.2 改造型(给出现成代码,改出正确行为)
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 修金额字段 | 给定 price FLOAT 改为 DECIMAL(12,2),写迁移脚本 |
历史数据金额精确到分 | 1 小时 |
| 修字符集 | 给定 DEFAULT CHARSET=utf8 改为 utf8mb4,处理已有数据 |
emoji 可存可读 | 1 小时 |
| 加约束 | 给 orders 补 CHECK(amount>0)与 UNIQUE(order_no) | 非法数据被拒 | 0.5 小时 |
| 建专用账号 | 为应用建 app 账号,只给 shop 库 DML 权限 | app 不能 CREATE TABLE | 0.5 小时 |
6.3 设计型(自己定方案)
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 迷你电商建模 | 设计用户、商品、订单、订单项、评论五张表,写出 DDL | 每张表有主键、时间戳、逻辑删除;外键关系画清 | 3 小时 |
| 类型选择说明 | 对每个字段写一句话说明为什么选这个类型 | 金额、时间、状态枚举的选择理由能口述 | 1 小时 |
| 安全基线清单 | 列一份本机开发环境的账号/端口/卷/字符集清单 | 可作为新同事入职搭建文档 | 1 小时 |
七、排障表
| 现象 | 可能原因 | 排查步骤 | 解决办法 |
|---|---|---|---|
Can't connect to MySQL server on '127.0.0.1:13306' (2003) |
容器未起、端口未映射、主机端口被占 | docker ps -a;docker logs mysql8;netstat -ano | findstr 13306 |
启动容器;换主机端口 |
connection refused (psql) |
PG 还在初始化 | docker logs pg16 等 ready to accept connections |
等待 5–10 秒再连 |
Access denied for user 'root'@'...' (1045) |
密码错、主机段不匹配 | 核对 MYSQL_ROOT_PASSWORD;SELECT user,host FROM mysql.user; |
用正确密码;或 ALTER USER |
中文显示为 ?? |
连接字符集不是 utf8mb4 | SHOW VARIABLES LIKE 'character%'; |
SET NAMES utf8mb4;连接串指定 |
插入 emoji 报 Incorrect string value: '\xF0\x9F...' |
列字符集是 utf8 不是 utf8mb4 | SHOW CREATE TABLE t; 看 CHARSET |
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4; |
| 外键插入报 1452 / 23503 | 父表无对应主键 | SELECT * FROM shop_user WHERE id=?; |
先插父表行,或检查 user_id |
relation "users" does not exist (PG) |
表在别的 schema | \dt *.users;SHOW search_path; |
SET search_path TO xxx,public; |
删库报 database is being accessed |
有活动连接 | SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='shop'; |
终止连接后再删 |
八、出口条件
全部勾选后再进入 阶段 1 SQL 进阶与数据建模。
- [ ] 能用一条
docker run命令起 MySQL 8 与 PG 16,数据目录挂命名卷,重启后数据仍在 - [ ] 能在两个库各建出 shop_user、product、orders、order_item 四张表,外键关系正确
- [ ] 能解释
BIGINT、DECIMAL(12,2)、DATETIME/timestamptz三个类型的选择理由 - [ ] 能演示外键拒绝孤儿订单,并抄下两个库的错误码
- [ ] 能解释 MySQL 的
utf8与utf8mb4区别,插入中文与 emoji 不乱码 - [ ] 能写出游标分页 SQL,说清
LIMIT offset,count深翻页为什么慢 - [ ] 能建一个最小权限的应用账号,用它连接并只做 DML
自测题(先自己想,再看要点):
- MySQL 的
utf8与utf8mb4有什么区别?为什么 MySQL 8 推荐后者?- 要点:utf8 最多 3 字节,存不下 emoji 与部分生僻字;utf8mb4 是完整 UTF-8,最多 4 字节;8.0 默认。
- 金额字段为什么不能用
DOUBLE?用什么?- 要点:二进制浮点有精度误差;用
DECIMAL(12,2)/NUMERIC,或 BIGINT 存分。
- 要点:二进制浮点有精度误差;用
- MySQL 的 database 与 PostgreSQL 的 schema 是同一层概念吗?
- 要点:不是。MySQL 的 database 连接级隔离;PG 的 schema 是同一 database 下的命名空间,可跨 schema JOIN。
- 外键约束在高并发大表上为什么常被去掉?什么约束不能去?
- 要点:外键每次写都要引用检查,且分库分表后跨库无法实现;主键、NOT NULL、UNIQUE、CHECK 不能去。
LIMIT 100000, 20为什么慢?怎么改?- 要点:扫描并丢弃前 10 万行;改游标分页
WHERE id > ? ORDER BY id LIMIT 20。
- 要点:扫描并丢弃前 10 万行;改游标分页
- 插入中文变
??,从哪几个环节排查?- 要点:server 字符集、database/table 字符集、连接字符集(
SET NAMES)、客户端编码;用SHOW VARIABLES LIKE 'character%'逐项核对。
- 要点:server 字符集、database/table 字符集、连接字符集(
- PG 里
SELECT * FROM users报relation does not exist,但表确实建过,可能是什么原因?- 要点:表建在了非 public schema,或
search_path不含该 schema;用\dt *.users定位。
- 要点:表建在了非 public schema,或