第 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 安装与版本

数据库服务一律用随书 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 --version
mysql --version
mongosh --version
sqlite3 --version

图形客户端用 DBeaver 社区版，一个程序连 PostgreSQL、MySQL、SQLite 等各种关系库，免费；社区版不支持 MongoDB，MongoDB 用官方的 Compass（第 24 章 24.1）：

winget install DBeaver.DBeaver.Community

brew install --cask dbeaver-community

JetBrains 的 DataGrip 是收费的同类工具，已在用 JetBrains IDE 的可以选它。不想离开编辑器的，在 VS Code 里装 SQLTools（mtxr.sqltools）和对应数据库的驱动扩展，装扩展的方法见第 2 章。

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:dev@127.0.0.1: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 常用命令

下表的 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', 'tom@example.com');	字符串用单引号，双引号在 PostgreSQL 里是列名
一次插入多行	INSERT INTO orders (user_id, amount) VALUES (1, 30), (1, 20), (2, 50);	比逐行插入快得多
有则更新、无则插入	INSERT INTO users (id, name, email) VALUES (1, 'Tom', 'tom@x.com') 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 典型工作流

让程序用连接串连上随书 PostgreSQL

以 Python 为例，连接串放在 .env 里（.env 的用法见第 15 章）：

1. 起数据库：docker compose --profile db up -d。
2. 在项目根目录新建 .env，写一行 DATABASE_URL=postgresql://postgres:dev@localhost:5432/postgres：code .env。
3. 让 Git 忽略它：echo ".env" >> .gitignore。
4. 装 PostgreSQL 驱动和读 .env 的库：uv add "psycopg[binary]" python-dotenv。
5. 新建 app.py，内容见下：code app.py。
6. 运行，打印出 PostgreSQL 17.… 就是连上了：uv run python app.py。

import os

import psycopg
from 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）：

1. 在容器里导出成归档文件：docker compose exec postgres pg_dump -U postgres -Fc -f /tmp/app.dump postgres。
2. 复制到宿主机，文件名带日期：docker compose cp postgres:/tmp/app.dump ./app-2026-10-08.dump。
3. 再复制一份到另一台机器（scp 见第 6 章）：scp app-2026-10-08.dump backup-host:/backups/。
4. 恢复演练，先建一个空库：docker compose exec postgres createdb -U postgres restore_test。
5. 把归档恢复进去：docker compose exec postgres pg_restore -U postgres -d restore_test /tmp/app.dump。
6. 核对表是否齐全：docker compose exec postgres psql -U postgres -d restore_test -c "\dt"，再对关键表 SELECT count(*) 和原库比行数。
7. 演练完删掉：docker compose exec postgres dropdb -U postgres restore_test。

第 1 步加进定时任务（Linux 的 cron、Windows 的任务计划程序），每天跑一次，保留最近若干份。这里先把归档写在容器里再 docker compose cp 出来；第 21 章 21.4 用 -T 经管道直接导到宿主机，两种结果相同。

用迁移工具管理表结构

常见的迁移工具：Python 项目用 Alembic（配合 SQLAlchemy），Node 项目用 Prisma 的 prisma migrate，Java 项目用 Flyway（迁移脚本就是按版本号命名的 .sql 文件）。下面用 Alembic 在 SQLite 上走一遍，换 PostgreSQL 只改连接串：

1. 在项目里装 Alembic：uv add alembic。
2. 生成迁移目录 migrations/ 和配置文件 alembic.ini：uv run alembic init migrations。
3. 把 alembic.ini 里的 sqlalchemy.url 改成 sqlite:///app.db：code alembic.ini。
4. 新建一个迁移脚本，在 migrations/versions/ 下生成一个以版本号开头的文件，按下面的片段填 upgrade() 和 downgrade()：uv run alembic revision -m "create users"。
5. 执行到最新版本，日志里出现 Running upgrade -> c4b9a7c54c3d, create users：uv run alembic upgrade head。
6. 看库当前在哪个版本，输出 c4b9a7c54c3d (head)：uv run alembic current。
7. 发现有问题，退回上一个版本，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 配置要点

下面几项在程序或连接串里设，各库的服务端配置文件见第 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 秒连不上就报错，服务端能看到应用名 myapp
DATABASE_URL=postgresql://postgres:dev@localhost:5432/postgres?connect_timeout=5&application_name=myapp
# 连接池大小，程序读出来传给驱动
DB_POOL_SIZE=10
# 随书 MySQL，库名 app 要先建好，字符集用 utf8mb4
MYSQL_URL=mysql+pymysql://root:dev@127.0.0.1:3306/app?charset=utf8mb4
# 随书 MongoDB，没有口令
MONGODB_URI=mongodb://localhost:27017/app
# 口令 p@ss:w/rd#1 里的特殊字符按 URL 编码写
PROD_DATABASE_URL=postgresql://app:p%40ss%3Aw%2Frd%231@db.example.com:5432/app

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 深入阅读

• 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，以及按模型自动生成迁移脚本。