第 20 章 · 数据库通用
一句话：各种数据库都要用到的那部分：连接串、客户端、通用 SQL、事务、索引与执行计划、备份恢复、迁移工具。 用它还是用别的：本章写的是共性，换哪个库都用得上；某个库自己的命令和配置看各章：PostgreSQL 第 21 章，MySQL 第 22 章，SQLite 第 23 章，MongoDB 第 24 章；只做缓存的数据用 Redis（第 25 章）。 核对版本：SQLite 3.45、psql 16.2、Alembic 1.20、DBeaver 26.2，随书 compose 里的 PostgreSQL 17、MySQL 8.4、MongoDB 8，2026-10
20.1 安装与版本
Section titled “20.1 安装与版本”数据库服务一律用随书 compose 起，不在本机装。在随书的 compose/ 目录下启动 PostgreSQL、MySQL、MongoDB 三个库，Compose 的用法详见第 13 章：
docker compose --profile db up -d客户端可以不装，直接用容器里自带的，如 docker compose exec postgres psql -U postgres。要在本机命令行里连库，再装对应的客户端：
| 客户端 | Windows | macOS | Ubuntu |
|---|---|---|---|
psql（PostgreSQL） |
winget install PostgreSQL.PostgreSQL.17 -i |
brew install libpq |
sudo apt install postgresql-client |
mysql（MySQL） |
winget install Oracle.MySQL -i |
brew install mysql-client |
sudo apt install mysql-client |
mongosh（MongoDB） |
winget install MongoDB.Shell |
brew install mongosh |
npm install -g mongosh |
sqlite3（SQLite） |
winget install SQLite.SQLite |
系统自带 | sudo apt install sqlite3 |
Windows 上 PostgreSQL 和 MySQL 没有单独的客户端包，-i 打开安装向导，组件里只勾命令行工具（PostgreSQL 叫 Command Line Tools），不装服务器。macOS 的 libpq、mysql-client 不会自动进 PATH，按 brew info libpq 提示的那一行把 bin 目录加进 PATH。Ubuntu 默认源里没有 mongosh，用 npm 装最省事。
查版本：
psql --versionmysql --versionmongosh --versionsqlite3 --version图形客户端用 DBeaver 社区版，一个程序连 PostgreSQL、MySQL、SQLite 等各种关系库，免费；社区版不支持 MongoDB，MongoDB 用官方的 Compass（第 24 章 24.1）：
winget install DBeaver.DBeaver.Communitybrew install --cask dbeaver-communityJetBrains 的 DataGrip 是收费的同类工具，已在用 JetBrains IDE 的可以选它。不想离开编辑器的，在 VS Code 里装 SQLTools（mtxr.sqltools）和对应数据库的驱动扩展，装扩展的方法见第 2 章。
20.2 五分钟概念
Section titled “20.2 五分钟概念”- 连接串（Connection String）：把“连哪台机器、哪个端口、用谁的身份、进哪个库”写成一行，常见的是 URL 形式
postgresql://user:pass@host:port/db。程序、命令行客户端都认它，放在环境变量DATABASE_URL里，不写进代码。 - 数据库、模式、表：一个数据库服务里有多个数据库（Database），彼此隔离；库里再按模式（Schema）分组，PostgreSQL 默认模式叫
public，MySQL 里模式和数据库是同一个东西；表（Table）放在模式里。SQLite 一个文件就是一个库。 - 事务（Transaction）与 ACID：一组改动要么全部生效、要么全部不生效，转账扣款和入账不会只做一半。ACID 是原子性、一致性、隔离性、持久性四个英文词的首字母，提交（COMMIT）之后断电也不丢。
- 隔离级别（Isolation Level）：几个事务同时跑时，一个事务能不能看到别的事务在它执行中途提交的改动、同一条查询读两次结果是否一致。PostgreSQL 默认读已提交（Read Committed），MySQL 默认可重复读（Repeatable Read），同一段代码在两个库上读到的结果可能不同。
- 索引（Index）：按某一列排好序的目录，查
WHERE email = ?时直接定位，不用一行行翻。没有索引的大表每次查询都全表扫描；索引也有代价，每次写入都要顺带更新它。 - 迁移（Migration）：把表结构的每次变化写成一个带编号的脚本，放进仓库，用工具按顺序执行。开发库、测试库、生产库都跑同一组脚本，表结构就一致，出错还能退回上一步。
随书 compose 里各库的连接串（宿主机上的程序用）：
| 数据库 | 连接串 | 说明 |
|---|---|---|
| PostgreSQL | postgresql://postgres:dev@localhost:5432/postgres |
有的库写 postgres://，含义相同 |
| MySQL | mysql://root:[email protected]:3306/mysql |
Python 的 SQLAlchemy 写 mysql+pymysql://…，指明驱动；主机写 127.0.0.1，有的驱动把 localhost 当成本机套接字，见第 22 章 22.1 |
| MongoDB | mongodb://localhost:27017/test |
没有口令，所以没有 user:pass@ |
| SQLite | sqlite:///app.db |
SQLAlchemy 的写法，三个斜杠后是相对路径；多数驱动直接传文件路径 |
| Java（JDBC） | jdbc:postgresql://localhost:5432/postgres |
JDBC 的格式，用户和口令另外传 |
程序在 compose 里的另一个容器中时，主机名换成服务名，如 postgres:5432，见第 13 章 13.2。
下图左边是程序的路径：程序读到连接串，交给驱动，驱动通过 TCP 连到数据库服务的端口；psql 用的是同一个连接串。图形客户端填的主机、端口、用户、库也是这几样，只是拆成了表单的几个格子。所以哪条路连不上，先核对的都是同四样东西：
20.3 常用命令
Section titled “20.3 常用命令”下表的 SQL 在 SQLite 里都能直接运行（sqlite3 app.db 进入后逐条输入，每条以分号结尾），PostgreSQL、MySQL 上写法不同的在说明里标出。示例用两张表：users（用户）和 orders（订单）。
| 任务 | 命令 | 说明 |
|---|---|---|
| 建表与改表 | ||
| 建表 | CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT); |
自增主键：PostgreSQL 写 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY，MySQL 写 id BIGINT AUTO_INCREMENT PRIMARY KEY |
| 建带外键的表 | CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id), amount NUMERIC); |
SQLite 要先执行 PRAGMA foreign_keys = ON; 才检查外键 |
| 加一列 | ALTER TABLE users ADD COLUMN age INTEGER; |
已有的行这一列是 NULL |
| 删表 | DROP TABLE IF EXISTS orders; |
表和数据一起删，不能撤销 |
| 增删改 | ||
| 插入一行 | INSERT INTO users (name, email) VALUES ('Tom', '[email protected]'); |
字符串用单引号，双引号在 PostgreSQL 里是列名 |
| 一次插入多行 | INSERT INTO orders (user_id, amount) VALUES (1, 30), (1, 20), (2, 50); |
比逐行插入快得多 |
| 有则更新、无则插入 | INSERT INTO users (id, name, email) VALUES (1, 'Tom', '[email protected]') ON CONFLICT (id) DO UPDATE SET email = excluded.email; |
PostgreSQL 同；MySQL 写 INSERT … AS new ON DUPLICATE KEY UPDATE email = new.email |
| 更新 | UPDATE users SET age = 20 WHERE id = 1; |
不带 WHERE 会改全表 |
| 删除 | DELETE FROM orders WHERE amount < 15; |
不带 WHERE 会删全表，先用同样的条件 SELECT 看一眼 |
| 查询 | ||
| 按条件查 | SELECT name FROM users WHERE age >= 18 AND email LIKE '%@x.com'; |
% 匹配任意多个字符 |
| 排序取前 N 条 | SELECT * FROM orders ORDER BY amount DESC LIMIT 10; |
DESC 从大到小 |
| 分页 | SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 20; |
第 3 页；不写 ORDER BY 时每页内容不固定 |
| 去重 | SELECT DISTINCT user_id FROM orders; |
有下过单的用户 ID |
| 查空值 | SELECT name FROM users WHERE email IS NULL; |
写 = NULL 永远查不到 |
| 联表与分组 | ||
| 内连接 | SELECT u.name, o.amount FROM users u JOIN orders o ON o.user_id = u.id; |
只列出有订单的用户 |
| 左连接 | SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON o.user_id = u.id; |
没订单的用户也列出，amount 为 NULL |
| 分组统计 | SELECT user_id, COUNT(*), SUM(amount) FROM orders GROUP BY user_id; |
每个用户的订单数和总额 |
| 分组后过滤 | SELECT user_id, SUM(amount) FROM orders GROUP BY user_id HAVING SUM(amount) > 45; |
分组前过滤用 WHERE，分组后用 HAVING |
| 子查询 | SELECT name FROM users WHERE id IN (SELECT user_id FROM orders); |
下过单的用户名 |
| 索引与执行计划 | ||
| 看执行计划 | EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 1; |
SQLite 写法，输出 SCAN orders 即全表扫描；PostgreSQL、MySQL 写 EXPLAIN SELECT … |
| 建索引 | CREATE INDEX idx_orders_user ON orders (user_id); |
再看执行计划变成 SEARCH orders USING INDEX idx_orders_user (user_id=?) |
| 建唯一索引 | CREATE UNIQUE INDEX idx_users_email ON users (email); |
之后插入重复邮箱报 UNIQUE constraint failed: users.email |
| 删索引 | DROP INDEX idx_orders_user; |
MySQL 写 DROP INDEX idx_orders_user ON orders; |
| 实际执行并看耗时 | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
PostgreSQL、MySQL 写法，SQLite 没有；会真的执行，别对 DELETE 用 |
| 事务 | ||
| 开始事务 | BEGIN; |
MySQL 也可以写 START TRANSACTION; |
| 提交 | COMMIT; |
改动生效，别的连接才看得到 |
| 回滚 | ROLLBACK; |
从 BEGIN 起的改动全部撤销 |
| 设保存点 | SAVEPOINT s1; |
事务里打一个点 |
| 回到保存点 | ROLLBACK TO s1; |
只撤销保存点之后的改动，事务还在，最后仍要 COMMIT |
| 其他 | ||
| 当前时间 | SELECT CURRENT_TIMESTAMP; |
SQLite 返回 UTC 时间，比北京时间少 8 小时 |
各客户端做同一件事的写法。psql、sqlite3 以 \ 或 . 开头的命令不用分号；mysql 里 SQL 语句要分号，source、exit 不用：
| 任务 | psql | mysql | sqlite3 | mongosh |
|---|---|---|---|---|
| 列出数据库 | \l |
SHOW DATABASES; |
.databases |
show dbs |
| 切换数据库 | \c 库名 |
USE 库名; |
.open 文件名 |
use 库名 |
| 列出表 | \dt |
SHOW TABLES; |
.tables |
show collections |
| 看表结构 | \d users |
DESCRIBE users; |
.schema users |
没有固定结构，db.users.findOne() 看一条 |
| 执行 SQL 文件 | \i init.sql |
source init.sql |
.read init.sql |
load("init.js") |
| 退出 | \q |
exit |
.quit |
exit |
20.4 典型工作流
Section titled “20.4 典型工作流”让程序用连接串连上随书 PostgreSQL
Section titled “让程序用连接串连上随书 PostgreSQL”以 Python 为例，连接串放在 .env 里（.env 的用法见第 15 章）：
- 起数据库：
docker compose --profile db up -d。 - 在项目根目录新建
.env，写一行DATABASE_URL=postgresql://postgres:dev@localhost:5432/postgres：code .env。 - 让 Git 忽略它：
echo ".env" >> .gitignore。 - 装 PostgreSQL 驱动和读
.env的库：uv add "psycopg[binary]" python-dotenv。 - 新建
app.py，内容见下：code app.py。 - 运行，打印出
PostgreSQL 17.…就是连上了：uv run python app.py。
import os
import psycopgfrom dotenv import load_dotenv
load_dotenv() # 把 .env 里的变量读进环境with psycopg.connect(os.environ["DATABASE_URL"]) as conn: print(conn.execute("SELECT version()").fetchone()[0])Node 的写法相同：npm install pg，new pg.Client({ connectionString: process.env.DATABASE_URL })，用 node --env-file=.env app.js 启动。
备份分两种。逻辑备份（Logical Backup）把数据导出成 SQL 语句或工具专用的归档文件，能跨版本、能只恢复一张表，数据大了导出和导入都慢；物理备份（Physical Backup）直接复制数据目录的文件，快，但要求恢复时数据库版本一致，一般交给云数据库或专门的工具。不管哪种，都要做到三点：定期自动做，复制一份到另一台机器或对象存储（异地），定期真的恢复一次（恢复演练）。下面以 PostgreSQL 的逻辑备份为例，各库的命令见第 21 章（pg_dump）、第 22 章（mysqldump）、第 23 章（.backup）、第 24 章（mongodump）：
- 在容器里导出成归档文件：
docker compose exec postgres pg_dump -U postgres -Fc -f /tmp/app.dump postgres。 - 复制到宿主机，文件名带日期：
docker compose cp postgres:/tmp/app.dump ./app-2026-10-08.dump。 - 再复制一份到另一台机器（
scp见第 6 章）：scp app-2026-10-08.dump backup-host:/backups/。 - 恢复演练，先建一个空库：
docker compose exec postgres createdb -U postgres restore_test。 - 把归档恢复进去：
docker compose exec postgres pg_restore -U postgres -d restore_test /tmp/app.dump。 - 核对表是否齐全：
docker compose exec postgres psql -U postgres -d restore_test -c "\dt"，再对关键表SELECT count(*)和原库比行数。 - 演练完删掉：
docker compose exec postgres dropdb -U postgres restore_test。
第 1 步加进定时任务（Linux 的 cron、Windows 的任务计划程序），每天跑一次，保留最近若干份。这里先把归档写在容器里再 docker compose cp 出来；第 21 章 21.4 用 -T 经管道直接导到宿主机，两种结果相同。
用迁移工具管理表结构
Section titled “用迁移工具管理表结构”常见的迁移工具：Python 项目用 Alembic（配合 SQLAlchemy），Node 项目用 Prisma 的 prisma migrate，Java 项目用 Flyway（迁移脚本就是按版本号命名的 .sql 文件）。下面用 Alembic 在 SQLite 上走一遍，换 PostgreSQL 只改连接串：
- 在项目里装 Alembic：
uv add alembic。 - 生成迁移目录
migrations/和配置文件alembic.ini：uv run alembic init migrations。 - 把
alembic.ini里的sqlalchemy.url改成sqlite:///app.db：code alembic.ini。 - 新建一个迁移脚本，在
migrations/versions/下生成一个以版本号开头的文件，按下面的片段填upgrade()和downgrade()：uv run alembic revision -m "create users"。 - 执行到最新版本，日志里出现
Running upgrade -> c4b9a7c54c3d, create users：uv run alembic upgrade head。 - 看库当前在哪个版本，输出
c4b9a7c54c3d (head)：uv run alembic current。 - 发现有问题，退回上一个版本，
users表被删掉：uv run alembic downgrade -1。
def upgrade() -> None: op.create_table( "users", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String(50), nullable=False), )
def downgrade() -> None: op.drop_table("users")版本号每次随机生成，你看到的和这里不同。Alembic 在库里建一张 alembic_version 表记录当前版本，所以同一个脚本不会执行两次。迁移脚本和代码一起提交，部署时先跑 alembic upgrade head 再启动新代码。
20.5 配置要点
Section titled “20.5 配置要点”下面几项在程序或连接串里设，各库的服务端配置文件见第 21、22 章：
| 配置项 | 在哪设 | 建议 |
|---|---|---|
| 连接池大小 | 驱动或 ORM 的参数，如 SQLAlchemy 的 pool_size |
每个进程 5 到 20；进程数乘以池大小要小于库的最大连接数 |
| 最大连接数 | 服务端 max_connections |
PostgreSQL 默认 100，MySQL 默认 151，超出报 too many connections |
| 连接超时 | 连接串参数，PostgreSQL 写 connect_timeout=5 |
主机不通时 5 秒就报错，不会卡很久 |
| 语句超时 | PostgreSQL statement_timeout；MySQL max_execution_time（单位毫秒，只管 SELECT） |
给 Web 请求设几秒（如 PostgreSQL '5s'、MySQL 5000），防止一条慢查询占住连接 |
| 时区 | PostgreSQL timezone，MySQL time_zone |
库里存 UTC，显示时再换成本地时区；PostgreSQL 用 timestamptz 类型 |
| 字符集 | MySQL 连接串加 charset=utf8mb4 |
MySQL 的 utf8 存不了表情符号，一律用 utf8mb4，见第 22 章 |
| 应用名 | PostgreSQL 连接串参数 application_name=myapp |
在服务端看连接列表时，一眼看出是哪个程序的连接 |
一个 .env 里的连接串片段：
# 随书 PostgreSQL：5 秒连不上就报错，服务端能看到应用名 myappDATABASE_URL=postgresql://postgres:dev@localhost:5432/postgres?connect_timeout=5&application_name=myapp# 连接池大小，程序读出来传给驱动DB_POOL_SIZE=10# 随书 MySQL，库名 app 要先建好，字符集用 utf8mb4# 随书 MongoDB，没有口令MONGODB_URI=mongodb://localhost:27017/app# 口令 p@ss:w/rd#1 里的特殊字符按 URL 编码写PROD_DATABASE_URL=postgresql://app:p%40ss%3Aw%2Frd%[email protected]:5432/app20.6 注意事项
Section titled “20.6 注意事项”connection to server at "localhost" (::1), port 5432 failed: Connection refused。 原因：数据库没起或端口不对；程序在容器里时localhost指容器自己。处理：docker compose ps看服务和端口；容器里把主机改成服务名postgres。- 口令里有
@，报could not translate host name "ss@localhost" to address。 原因：连接串按@、:、/、#切分，口令被切断。处理：把口令 URL 编码，如@写%40；Python 里用urllib.parse.quote(口令, safe="")生成。 - 高峰时报
sorry, too many clients already（MySQL 是Too many connections）。 原因：每个请求新开连接不关，或进程数乘池大小超过max_connections。处理：用连接池并按 20.5 算好池大小。 - 程序写了数据，别的连接查不到，程序退出后数据没了；SQLite 里另一个连接写入报
database is locked。 原因：开了事务没提交。处理：写完COMMIT，或用with块让驱动自动提交。 - 查询很慢，
EXPLAIN里是Seq Scan on orders（SQLite 是SCAN orders）。 原因：WHERE或JOIN用到的列没有索引，全表扫描。处理：给这一列建索引，再看执行计划变成Index Scan或SEARCH … USING INDEX。 - 存进去的时间和页面上的差 8 小时。 原因：库存 UTC、显示没换时区，或两边时区设置不一致；SQLite 的
CURRENT_TIMESTAMP就是 UTC。处理：一律存 UTC，PostgreSQL 用timestamptz，显示时再换成本地时区。 - 上线后报
column "age" does not exist，开发库上明明有这一列。 原因：有人在开发库上手工改了表，生产库没改。处理：表结构只通过迁移脚本改，部署时先alembic upgrade head。 - 要恢复时
pg_restore报input file appears to be a text format dump. Please use psql.，或备份文件是空的。 原因：备份格式和恢复命令不配，或备份任务早已失败而没演练过所以没发现。处理：按 20.4 定期恢复到临时库核对行数；SQL 文本备份用psql -f恢复，见 21.6。
20.7 深入阅读
Section titled “20.7 深入阅读”- PostgreSQL 教程：官方入门教程，从建库建表、联表查询、分组讲到事务、视图和外键。
- SQLite 支持的 SQL：SQLite 每条语句的语法图，含 EXPLAIN QUERY PLAN、UPSERT 和 PRAGMA。
- Use The Index, Luke：讲索引怎么工作、怎么读执行计划，例子覆盖 PostgreSQL、MySQL、Oracle、SQL Server。
- DBeaver 文档：新建连接、驱动下载、SSH 隧道连库、数据导入导出。
- Alembic 教程：从 alembic init 到 upgrade、downgrade，以及按模型自动生成迁移脚本。