wsk/zhuangang

0 Fork 0

Files

T

Shikui Wang 4f12b8b2ba phase 6 part 2: per-week related-topics sections (gen_week_topics.py), week14/15/16 adjustments from BOSS research, README/CONTRIBUTING updates

2026-10-23 17:00:50 +08:00

41 KiB

Raw Permalink Blame History

id, title, type, domain, stack, week, est_hours, order

title

type

domain

stack

week

est_hours

order

plan.week02

第 2 周：数据库（PostgreSQL 为主，兼顾 MySQL）+ 注册登录与 JWT

week

plan

gin

postgresql

mysql

第 2 周：数据库（PostgreSQL 为主，兼顾 MySQL）+ 注册登录与 JWT

第一段“Go 全栈基础”的第 2 周。上周 week01-go-gin.md，下周 week03-project1.md。

本周目标

把任务清单的存储从内存换成 PostgreSQL：会设计表、写常用 SQL、看懂执行计划、用迁移管理表结构、在 Go 里写事务。
能说清 MySQL 和 PostgreSQL 的主要差异（招聘里 MySQL 出现得更多），包括索引、EXPLAIN、事务隔离级别。
给服务加上注册、登录和 JWT 鉴权。

为什么用 PostgreSQL 学、却要兼顾 MySQL：两者的常用 SQL 大部分相同；PostgreSQL 的 pgvector 扩展在第 5~6 周 RAG 项目里可以当向量库用；而招聘写 MySQL 的更多，所以每个知识点都附上 MySQL 的写法和区别，面试时能讲清差异即可。

参考资料

PostgreSQL 中文文档：http://www.postgres.cn/docs/current/
MySQL 8 参考手册：https://dev.mysql.com/doc/refman/8.0/en/
pgx：https://github.com/jackc/pgx
golang-migrate：https://github.com/golang-migrate/migrate
golang-jwt：https://github.com/golang-jwt/jwt

本周最终目录（在上周 `todo-api/backend` 基础上新增）

todo-api/backend/
├── docker-compose.dev.yml           # postgres（和可选的 mysql）
├── Makefile
├── .env.example
├── db/migrations/
│   ├── 000001_create_tasks.up.sql / .down.sql
│   ├── 000002_add_tasks_index.up.sql / .down.sql
│   └── 000003_users_and_tags.up.sql / .down.sql
├── sql/practice/                    # 练习 SQL，不参与运行
│   ├── day1_crud.sql
│   ├── day2_queries.sql
│   └── mysql_vs_pg.sql
└── internal/
    ├── config/config.go
    ├── model/user.go
    ├── store/pg_store.go            # 任务的 PostgreSQL 实现
    ├── store/pg_store_test.go       # 集成测试（没有数据库时自动跳过）
    ├── store/user_store.go          # 用户存储接口 + 内存实现
    ├── store/pg_user_store.go
    ├── auth/password.go
    ├── auth/jwt.go
    ├── auth/jwt_test.go
    ├── handler/auth_handler.go
    ├── middleware/auth.go
    └── router/router.go             # 改为接收依赖集合

第 1 天：用 Docker 跑 PostgreSQL，写基础 SQL 和表设计

学习内容

1. 用 Docker 启动数据库

说明：本地开发不直接往系统装数据库，用 Docker 起一个容器，删掉重来也不怕；数据放在具名卷里，容器删了数据还在。
案例：开发用的 Compose 文件。

# 文件：docker-compose.dev.yml
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: todo
      POSTGRES_PASSWORD: todo
      POSTGRES_DB: todo
    ports: ["5432:5432"]
    volumes: [pgdata:/var/lib/postgresql/data]
  mysql:                      # 只在做 MySQL 对比练习时启动：docker compose -f docker-compose.dev.yml up -d mysql
    image: mysql:8
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: todo
    ports: ["3306:3306"]
volumes:
  pgdata:

# Ubuntu 没装 Docker 的话：sudo apt install docker.io docker-compose-v2，然后把自己加进 docker 组
docker compose -f docker-compose.dev.yml up -d postgres
docker compose -f docker-compose.dev.yml exec postgres psql -U todo -d todo
# psql 常用：\dt 列出表  \d tasks 看表结构  \q 退出

2. 建表和约束

说明：主键、NOT NULL、UNIQUE、CHECK、外键这些约束让数据库帮你守住规则，比只在代码里校验更可靠（多个服务、手工改数据时也不会写进脏数据）。
案例：用户、任务（一对多）、标签（多对多）。

-- 文件：sql/practice/day1_crud.sql
CREATE TABLE users (
  id            BIGSERIAL PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  name          TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE practice_tasks (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,  -- 一对多
  title       TEXT NOT NULL CHECK (char_length(title) BETWEEN 1 AND 100),
  done        BOOLEAN NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE tags (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE task_tags (                                               -- 多对多的关联表
  task_id BIGINT REFERENCES practice_tasks(id) ON DELETE CASCADE,
  tag_id  BIGINT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, tag_id)
);

INSERT INTO users (email, name) VALUES ('a@example.com', '小王'), ('b@example.com', '小李');
INSERT INTO practice_tasks (user_id, title) VALUES (1, '学 SQL'), (1, '学 Gin'), (2, '写周报');
UPDATE practice_tasks SET done = true WHERE id = 1;
SELECT id, title, done FROM practice_tasks WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;
DELETE FROM practice_tasks WHERE id = 3;

常见坑：UPDATE / DELETE 忘写 WHERE 会改掉整张表；生产环境操作前先用同样的条件 SELECT 一遍。
面试常问：外键的 ON DELETE CASCADE 和 RESTRICT 各适合什么场景（订单明细跟着订单删 → CASCADE；有订单的用户不能删 → RESTRICT）。

3. MySQL 写法对照

说明：同样的表在 MySQL 里要注意：自增用 AUTO_INCREMENT；字符集用 utf8mb4（否则存不了表情）；存储引擎用 InnoDB（支持事务和外键，MySQL 8 默认就是）。
案例：

-- 文件：sql/practice/mysql_vs_pg.sql（在 MySQL 里执行）
CREATE TABLE users (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PostgreSQL: INSERT ... RETURNING id;   MySQL 没有 RETURNING：
INSERT INTO users (email, name) VALUES ('a@example.com', '小王');
SELECT LAST_INSERT_ID();

-- “存在就更新”：PostgreSQL 用 ON CONFLICT (email) DO UPDATE，MySQL 用：
INSERT INTO users (email, name) VALUES ('a@example.com', '王')
  ON DUPLICATE KEY UPDATE name = VALUES(name);

方面	PostgreSQL	MySQL 8
自增主键	`BIGSERIAL` / `GENERATED AS IDENTITY`	`AUTO_INCREMENT`
插入后取回	`RETURNING`	`LAST_INSERT_ID()`
存在就更新	`ON CONFLICT ... DO UPDATE`	`ON DUPLICATE KEY UPDATE`
时间	`TIMESTAMPTZ`（带时区）	`DATETIME`（不带时区，一般统一存 UTC）
JSON	`JSONB`，可直接建索引	`JSON`，索引要借助生成列
字符串比较	默认区分大小写	常用排序规则默认不区分大小写
默认隔离级别	读已提交	可重复读
Go 驱动占位符	`$1, $2`	`?`

要建的文件

docker-compose.dev.yml、sql/practice/day1_crud.sql、sql/practice/mysql_vs_pg.sql

运行方式

docker compose -f docker-compose.dev.yml exec -T postgres psql -U todo -d todo < sql/practice/day1_crud.sql

完成标准

不看资料能写出建表、增删改查；能说出 3 条以上 MySQL 和 PostgreSQL 的差异。

第 2 天：多表查询、索引、EXPLAIN

学习内容

1. JOIN、GROUP BY、HAVING

说明：JOIN 把多张表按关系连起来；LEFT JOIN 保留左表所有行（右边没有就是 NULL）；GROUP BY 分组统计，HAVING 对分组结果过滤。
案例：任务统计报表。

-- 文件：sql/practice/day2_queries.sql
-- 每个用户的任务数和完成数（没有任务的用户也要列出来）
SELECT u.name,
       count(t.id)                          AS total,
       count(t.id) FILTER (WHERE t.done)    AS done   -- MySQL 写法：SUM(t.done = 1)
FROM users u
LEFT JOIN practice_tasks t ON t.user_id = u.id
GROUP BY u.id, u.name
ORDER BY total DESC;

-- 带某个标签的任务（经过关联表的两次 JOIN）
SELECT t.id, t.title
FROM practice_tasks t
JOIN task_tags tt ON tt.task_id = t.id
JOIN tags g ON g.id = tt.tag_id
WHERE g.name = '学习';

-- 任务数超过 2 的用户
SELECT user_id, count(*) FROM practice_tasks GROUP BY user_id HAVING count(*) > 2;

2. 索引和执行计划

说明：索引（B+ 树 / B 树）让查询不用扫全表，但会让写入变慢、占空间，所以只给常用的查询条件和排序字段建。组合索引遵循“最左前缀”：(user_id, created_at) 能用于 WHERE user_id = ? 和 WHERE user_id = ? ORDER BY created_at，不能单独用于 WHERE created_at > ?。
案例：造 10 万条数据，对比加索引前后的执行计划。

-- 造数据（PostgreSQL 的 generate_series）
INSERT INTO practice_tasks (user_id, title, done)
SELECT 1 + (random() * 1)::int, '任务 ' || g, random() < 0.3
FROM generate_series(1, 100000) g;
ANALYZE practice_tasks;

EXPLAIN ANALYZE
SELECT id, title FROM practice_tasks WHERE user_id = 1 AND NOT done ORDER BY created_at DESC LIMIT 20;
-- 看到 Seq Scan（全表扫描）和较长的执行时间

CREATE INDEX idx_tasks_user_created ON practice_tasks (user_id, created_at DESC);

EXPLAIN ANALYZE
SELECT id, title FROM practice_tasks WHERE user_id = 1 AND NOT done ORDER BY created_at DESC LIMIT 20;
-- 变成 Index Scan，时间明显下降（具体数字以你机器上为准，记到练习文件的注释里）

MySQL 对照：EXPLAIN SELECT ... 重点看 type（ALL 是全表扫描，ref/range 用到了索引）、key（实际用的索引）、rows（估计扫描行数）、Extra（Using filesort 说明排序没走索引）。MySQL 8.0.18 起也支持 EXPLAIN ANALYZE。
面试常问：
- 索引为什么用 B+ 树（矮、范围查询友好）；
- 索引失效的常见情况：对字段做函数运算（WHERE date(created_at) = ...）、隐式类型转换、LIKE '%xx' 前缀模糊、违反最左前缀；
- MySQL InnoDB 的聚簇索引和二级索引“回表”，以及“覆盖索引”如何避免回表。

要建的文件

sql/practice/day2_queries.sql：上面的查询 + 你自己的 EXPLAIN 结果注释。
db/migrations/000002_add_tasks_index.up.sql：给正式的 tasks 表加 (done, created_at DESC) 索引（第 3 天建迁移体系后再执行）。

运行方式

在 psql 里执行；MySQL 部分在 docker compose ... exec mysql mysql -uroot -proot todo 里执行。

完成标准

能读懂执行计划里最关键的两三行，能说出 3 种索引失效的情况。

第 3 天：迁移和 Go 访问数据库（pgx）

学习内容

1. 数据库迁移

说明：表结构变更也要像代码一样进 Git、按顺序执行。每次变更一对文件：up（执行）和 down（回滚）。上线顺序一般是“先跑迁移，再发新代码”，避免“代码升级了、表还是旧的”导致接口 500。
案例：

-- 文件：db/migrations/000001_create_tasks.up.sql
CREATE TABLE tasks (
  id          BIGSERIAL PRIMARY KEY,
  title       TEXT NOT NULL CHECK (char_length(title) BETWEEN 1 AND 100),
  done        BOOLEAN NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 文件：db/migrations/000001_create_tasks.down.sql
DROP TABLE IF EXISTS tasks;

# 文件：Makefile（缩进必须是 Tab）
DATABASE_URL ?= postgres://todo:todo@localhost:5432/todo?sslmode=disable

db-up:
	docker compose -f docker-compose.dev.yml up -d postgres
migrate-up:
	migrate -path db/migrations -database "$(DATABASE_URL)" up
migrate-down:
	migrate -path db/migrations -database "$(DATABASE_URL)" down 1
migrate-new:
	migrate create -ext sql -dir db/migrations -seq $(name)

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
make db-up && make migrate-up
make migrate-down && make migrate-up   # 确认能来回执行

2. 用 pgx 实现 TaskStore

说明：pgx 是 Go 里常用的 PostgreSQL 驱动，pgxpool 是连接池。因为上周把存储层定义成了接口，这里只要新写一个实现。SQL 用 $1 占位符传参，永远不要拼接字符串（SQL 注入）。
另一种常见做法是 sqlc（写 SQL 生成 Go 代码）或 GORM（ORM）。本计划用 pgx 直接写，SQL 看得最清楚；了解另外两种的取舍即可。
案例：

// 文件：internal/store/pg_store.go
package store

import (
	"context"
	"errors"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"

	"todo-api/internal/model"
)

type PgStore struct{ pool *pgxpool.Pool }

func NewPgStore(pool *pgxpool.Pool) *PgStore { return &PgStore{pool: pool} }

const taskCols = "id, title, done, created_at, updated_at"

func (s *PgStore) List(ctx context.Context, done *bool) ([]model.Task, error) {
	rows, err := s.pool.Query(ctx,
		`SELECT `+taskCols+` FROM tasks WHERE ($1::boolean IS NULL OR done = $1) ORDER BY id`, done)
	if err != nil {
		return nil, err
	}
	return pgx.CollectRows(rows, pgx.RowToStructByPos[model.Task]) // 按列顺序填充结构体
}

func (s *PgStore) Get(ctx context.Context, id int64) (model.Task, error) {
	var t model.Task
	err := s.pool.QueryRow(ctx, `SELECT `+taskCols+` FROM tasks WHERE id = $1`, id).
		Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt, &t.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return t, ErrNotFound
	}
	return t, err
}

func (s *PgStore) Create(ctx context.Context, t model.Task) (model.Task, error) {
	err := s.pool.QueryRow(ctx,
		`INSERT INTO tasks (title, done) VALUES ($1, $2) RETURNING `+taskCols, t.Title, t.Done).
		Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt, &t.UpdatedAt)
	return t, err
}

func (s *PgStore) Update(ctx context.Context, t model.Task) (model.Task, error) {
	err := s.pool.QueryRow(ctx,
		`UPDATE tasks SET title = $2, done = $3, updated_at = now() WHERE id = $1 RETURNING `+taskCols,
		t.ID, t.Title, t.Done).
		Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt, &t.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return t, ErrNotFound
	}
	return t, err
}

func (s *PgStore) Delete(ctx context.Context, id int64) error {
	tag, err := s.pool.Exec(ctx, `DELETE FROM tasks WHERE id = $1`, id)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

常见坑：RowToStructByPos 要求结构体字段顺序和 SELECT 的列顺序一致；用 MySQL 时驱动是 github.com/go-sql-driver/mysql + 标准库 database/sql，占位符是 ?。

易错点 / 面试常问（补充）

易错点：迁移文件一旦在别的环境执行过就不要再改，要改就新建一个迁移；down 文件要能真正回滚 up 的改动。
易错点：pgx 查询后要处理 rows.Err()，并 defer rows.Close()；查单行没结果时返回的是 pgx.ErrNoRows，要转成业务的“不存在”。
易错点：SQL 一律用参数占位符（PostgreSQL 是 $1，MySQL 是 ?），不要拼接字符串，否则有 SQL 注入。
面试常问：连接池为什么必要、参数怎么设（最大连接数受数据库 max_connections 限制）；应用升级和数据库迁移谁先谁后（先做向后兼容的迁移，再发新代码）。

要建的文件

db/migrations/000001_*、000002_*、Makefile、internal/store/pg_store.go
.env.example：DATABASE_URL=postgres://todo:todo@localhost:5432/todo?sslmode=disable

运行方式

go get github.com/jackc/pgx/v5
go build ./...

完成标准

能说出“为什么先跑迁移再发代码”，能说出 SQL 注入是什么、怎么避免。

第 4 天：事务、隔离级别、幂等写入

学习内容

1. 事务

说明：事务保证一组写操作“要么全成功，要么全失败”（原子性），并发时互不干扰（隔离性）。ACID：原子性、一致性、隔离性、持久性。
案例：给任务打标签：插入标签（已存在就复用）+ 写关联表，必须在一个事务里。

-- 文件：db/migrations/000003_users_and_tags.up.sql
CREATE TABLE users (
  id            BIGSERIAL PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  name          TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE tags (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE task_tags (
  task_id BIGINT REFERENCES tasks(id) ON DELETE CASCADE,
  tag_id  BIGINT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, tag_id)
);

// 文件：internal/store/pg_tx.go
package store

import (
	"context"

	"github.com/jackc/pgx/v5"
)

// withTx 开启事务执行 fn：fn 返回错误就回滚，否则提交。
func (s *PgStore) withTx(ctx context.Context, fn func(tx pgx.Tx) error) error {
	tx, err := s.pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx) // 已经提交后再调用 Rollback 不会有任何影响
	if err := fn(tx); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

// AddTags 给任务打标签；标签不存在就创建（幂等：重复调用结果一样）。
func (s *PgStore) AddTags(ctx context.Context, taskID int64, names []string) error {
	return s.withTx(ctx, func(tx pgx.Tx) error {
		for _, name := range names {
			var tagID int64
			err := tx.QueryRow(ctx,
				`INSERT INTO tags (name) VALUES ($1)
				 ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name
				 RETURNING id`, name).Scan(&tagID)
			if err != nil {
				return err
			}
			if _, err := tx.Exec(ctx,
				`INSERT INTO task_tags (task_id, tag_id) VALUES ($1, $2) ON CONFLICT DO NOTHING`,
				taskID, tagID); err != nil {
				return err
			}
		}
		return nil
	})
}

常见坑：事务里用 s.pool 而不是 tx 执行语句，那条语句就不在事务里了。
小实验：在循环里故意 return errors.New("boom")，确认标签和关联都没写进去。

2. 隔离级别（面试高频）

说明：并发事务可能出现三类问题：脏读（读到别人没提交的数据）、不可重复读（同一事务里两次读同一行结果不同）、幻读（两次范围查询行数不同）。四个隔离级别从低到高：读未提交、读已提交、可重复读、串行化。PostgreSQL 默认读已提交，MySQL InnoDB 默认可重复读（通过 MVCC 多版本 + 间隙锁在很大程度上避免幻读）。
案例：开两个 psql 窗口亲手观察“不可重复读”。

-- 窗口 A
BEGIN;  -- 默认读已提交
SELECT title FROM tasks WHERE id = 1;          -- 看到旧标题
-- 窗口 B
UPDATE tasks SET title = '被 B 改了' WHERE id = 1;  -- 自动提交
-- 窗口 A
SELECT title FROM tasks WHERE id = 1;          -- 看到新标题：不可重复读
COMMIT;

-- 再来一次，A 改用可重复读
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT title FROM tasks WHERE id = 1;
-- B 再改一次后，A 再查：仍是事务开始时的值
COMMIT;

面试常问：MVCC 是什么（每行保留多个版本，读不加锁）；乐观锁（版本号字段 UPDATE ... WHERE version = ?）和悲观锁（SELECT ... FOR UPDATE）各适合什么场景。

要建的文件

db/migrations/000003_users_and_tags.up.sql / .down.sql、internal/store/pg_tx.go

运行方式

make migrate-up && go build ./...

完成标准

亲手复现过不可重复读；能讲清四个隔离级别和 MySQL、PostgreSQL 的默认值。

第 5 天：密码哈希和 JWT

学习内容

1. 密码只存哈希

说明：数据库泄露时，明文密码会直接暴露。bcrypt 是“故意很慢”且自带随机盐的哈希算法，暴力破解成本高；MD5、SHA256 太快，不适合存密码。
案例：

// 文件：internal/auth/password.go
package auth

import "golang.org/x/crypto/bcrypt"

func HashPassword(plain string) (string, error) {
	b, err := bcrypt.GenerateFromPassword([]byte(plain), bcrypt.DefaultCost)
	return string(b), err
}

func CheckPassword(hash, plain string) bool {
	return bcrypt.CompareHashAndPassword([]byte(hash), []byte(plain)) == nil
}

2. JWT

说明：JWT 是“头部.载荷.签名”三段 Base64 字符串。服务端用密钥签名，之后只要验签就知道是谁、是否过期，不用查会话表。注意：载荷只是编码不是加密，不能放敏感信息；签发后在过期前很难作废，所以过期时间要短（配合刷新令牌，本周不实现）。
案例：签发和解析。

// 文件：internal/auth/jwt.go
package auth

import (
	"time"

	"github.com/golang-jwt/jwt/v5"
)

type Claims struct {
	UserID int64 `json:"uid"`
	jwt.RegisteredClaims
}

type TokenManager struct {
	secret []byte
	ttl    time.Duration
}

func NewTokenManager(secret string, ttl time.Duration) *TokenManager {
	return &TokenManager{secret: []byte(secret), ttl: ttl}
}

func (m *TokenManager) Issue(userID int64) (string, error) {
	now := time.Now()
	claims := Claims{
		UserID: userID,
		RegisteredClaims: jwt.RegisteredClaims{
			IssuedAt:  jwt.NewNumericDate(now),
			ExpiresAt: jwt.NewNumericDate(now.Add(m.ttl)),
		},
	}
	return jwt.NewWithClaims(jwt.SigningMethodHS256, claims).SignedString(m.secret)
}

func (m *TokenManager) Parse(token string) (int64, error) {
	var c Claims
	_, err := jwt.ParseWithClaims(token, &c,
		func(*jwt.Token) (any, error) { return m.secret, nil },
		jwt.WithValidMethods([]string{"HS256"}), // 只接受 HS256，防止“算法替换”攻击
	)
	if err != nil {
		return 0, err
	}
	return c.UserID, nil
}

// 文件：internal/auth/jwt_test.go
package auth

import (
	"testing"
	"time"
)

func TestToken(t *testing.T) {
	m := NewTokenManager("a-very-long-secret-for-testing-only-123", time.Hour)
	tok, _ := m.Issue(42)
	if id, err := m.Parse(tok); err != nil || id != 42 {
		t.Fatalf("parse: id=%d err=%v", id, err)
	}

	expired := NewTokenManager("a-very-long-secret-for-testing-only-123", -time.Minute)
	tok2, _ := expired.Issue(1)
	if _, err := m.Parse(tok2); err == nil {
		t.Fatal("过期令牌应该解析失败")
	}

	other := NewTokenManager("another-secret-another-secret-12345678", time.Hour)
	tok3, _ := other.Issue(1)
	if _, err := m.Parse(tok3); err == nil {
		t.Fatal("别的密钥签的令牌应该解析失败")
	}
}

func TestPassword(t *testing.T) {
	h, _ := HashPassword("secret123")
	if !CheckPassword(h, "secret123") || CheckPassword(h, "wrong") {
		t.Fatal("密码校验不对")
	}
}

面试常问：JWT 和服务端 Session 的优缺点；令牌放 localStorage（怕 XSS）还是 httpOnly Cookie（要防 CSRF）。

要建的文件

internal/auth/password.go、jwt.go、jwt_test.go

运行方式

go get golang.org/x/crypto/bcrypt github.com/golang-jwt/jwt/v5
go test ./internal/auth -v

完成标准

测试通过；能说清 JWT 三段分别是什么，为什么载荷不能放密码。

第 6 天：注册、登录、鉴权中间件

学习内容

1. 用户存储和注册登录

说明：用户存储同样先定义接口；邮箱唯一冲突转成 409；登录时“邮箱不存在”和“密码错误”返回同一个错误，避免别人试探哪些邮箱注册过。
案例：

// 文件：internal/model/user.go
package model

import "time"

type User struct {
	ID           int64     `json:"id"`
	Email        string    `json:"email"`
	Name         string    `json:"name"`
	PasswordHash string    `json:"-"` // 永远不输出到 JSON
	CreatedAt    time.Time `json:"createdAt"`
}

// 文件：internal/store/user_store.go
package store

import (
	"context"
	"errors"
	"sync"
	"time"

	"todo-api/internal/model"
)

var ErrEmailTaken = errors.New("email already registered")

type UserStore interface {
	CreateUser(ctx context.Context, u model.User) (model.User, error)
	GetUserByEmail(ctx context.Context, email string) (model.User, error)
	GetUser(ctx context.Context, id int64) (model.User, error)
}

type MemoryUserStore struct {
	mu     sync.Mutex
	nextID int64
	byID   map[int64]model.User
}

func NewMemoryUserStore() *MemoryUserStore {
	return &MemoryUserStore{nextID: 1, byID: map[int64]model.User{}}
}

func (s *MemoryUserStore) CreateUser(_ context.Context, u model.User) (model.User, error) {
	s.mu.Lock()
	defer s.mu.Unlock()
	for _, x := range s.byID {
		if x.Email == u.Email {
			return model.User{}, ErrEmailTaken
		}
	}
	u.ID, u.CreatedAt = s.nextID, time.Now()
	s.nextID++
	s.byID[u.ID] = u
	return u, nil
}

func (s *MemoryUserStore) GetUserByEmail(_ context.Context, email string) (model.User, error) {
	s.mu.Lock()
	defer s.mu.Unlock()
	for _, x := range s.byID {
		if x.Email == email {
			return x, nil
		}
	}
	return model.User{}, ErrNotFound
}

func (s *MemoryUserStore) GetUser(_ context.Context, id int64) (model.User, error) {
	s.mu.Lock()
	defer s.mu.Unlock()
	u, ok := s.byID[id]
	if !ok {
		return model.User{}, ErrNotFound
	}
	return u, nil
}

// 文件：internal/store/pg_user_store.go
package store

import (
	"context"
	"errors"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"

	"todo-api/internal/model"
)

type PgUserStore struct{ pool *pgxpool.Pool }

func NewPgUserStore(pool *pgxpool.Pool) *PgUserStore { return &PgUserStore{pool: pool} }

const userCols = "id, email, name, password_hash, created_at"

func scanUser(row pgx.Row) (model.User, error) {
	var u model.User
	err := row.Scan(&u.ID, &u.Email, &u.Name, &u.PasswordHash, &u.CreatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return u, ErrNotFound
	}
	return u, err
}

func (s *PgUserStore) CreateUser(ctx context.Context, u model.User) (model.User, error) {
	created, err := scanUser(s.pool.QueryRow(ctx,
		`INSERT INTO users (email, name, password_hash) VALUES ($1, $2, $3) RETURNING `+userCols,
		u.Email, u.Name, u.PasswordHash))
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) && pgErr.Code == "23505" { // 唯一约束冲突
		return model.User{}, ErrEmailTaken
	}
	return created, err
}

func (s *PgUserStore) GetUserByEmail(ctx context.Context, email string) (model.User, error) {
	return scanUser(s.pool.QueryRow(ctx, `SELECT `+userCols+` FROM users WHERE email = $1`, email))
}

func (s *PgUserStore) GetUser(ctx context.Context, id int64) (model.User, error) {
	return scanUser(s.pool.QueryRow(ctx, `SELECT `+userCols+` FROM users WHERE id = $1`, id))
}

// 文件：internal/handler/auth_handler.go
package handler

import (
	"errors"
	"net/http"
	"net/mail"
	"strings"

	"github.com/gin-gonic/gin"

	"todo-api/internal/auth"
	"todo-api/internal/middleware"
	"todo-api/internal/model"
	"todo-api/internal/store"
)

type AuthHandler struct {
	Users  store.UserStore
	Tokens *auth.TokenManager
}

type credentials struct {
	Email    string `json:"email"`
	Name     string `json:"name"`
	Password string `json:"password"`
}

func (h *AuthHandler) Register(c *gin.Context) {
	var req credentials
	if err := c.ShouldBindJSON(&req); err != nil {
		Fail(c, http.StatusBadRequest, 40001, "请求体格式错误")
		return
	}
	req.Email = strings.ToLower(strings.TrimSpace(req.Email))
	if _, err := mail.ParseAddress(req.Email); err != nil || len(req.Password) < 8 || req.Name == "" {
		Fail(c, http.StatusBadRequest, 40002, "邮箱格式不对、名字为空或密码少于 8 位")
		return
	}
	hash, err := auth.HashPassword(req.Password)
	if err != nil {
		Fail(c, http.StatusInternalServerError, 50000, "服务器错误")
		return
	}
	u, err := h.Users.CreateUser(c.Request.Context(), model.User{Email: req.Email, Name: req.Name, PasswordHash: hash})
	if errors.Is(err, store.ErrEmailTaken) {
		Fail(c, http.StatusConflict, 40901, "邮箱已注册")
		return
	}
	if err != nil {
		Fail(c, http.StatusInternalServerError, 50000, "服务器错误")
		return
	}
	Created(c, u)
}

func (h *AuthHandler) Login(c *gin.Context) {
	var req credentials
	if err := c.ShouldBindJSON(&req); err != nil {
		Fail(c, http.StatusBadRequest, 40001, "请求体格式错误")
		return
	}
	u, err := h.Users.GetUserByEmail(c.Request.Context(), strings.ToLower(strings.TrimSpace(req.Email)))
	if err != nil || !auth.CheckPassword(u.PasswordHash, req.Password) {
		Fail(c, http.StatusUnauthorized, 40101, "邮箱或密码错误") // 两种情况同一个提示
		return
	}
	token, err := h.Tokens.Issue(u.ID)
	if err != nil {
		Fail(c, http.StatusInternalServerError, 50000, "服务器错误")
		return
	}
	OK(c, gin.H{"token": token, "user": u})
}

func (h *AuthHandler) Me(c *gin.Context) {
	u, err := h.Users.GetUser(c.Request.Context(), middleware.CurrentUserID(c))
	if err != nil {
		Fail(c, http.StatusNotFound, 40402, "用户不存在")
		return
	}
	OK(c, u)
}

2. 鉴权中间件

说明：从 Authorization: Bearer <token> 取令牌并校验，把用户 ID 放进上下文；失败返回 401 并中止。401 表示“你是谁我不知道”，403 表示“我知道你是谁，但你没权限”。
案例：

// 文件：internal/middleware/auth.go
package middleware

import (
	"net/http"
	"strings"

	"github.com/gin-gonic/gin"

	"todo-api/internal/auth"
)

const userIDKey = "userID"

func RequireAuth(tm *auth.TokenManager) gin.HandlerFunc {
	return func(c *gin.Context) {
		raw, ok := strings.CutPrefix(c.GetHeader("Authorization"), "Bearer ")
		if !ok {
			c.AbortWithStatusJSON(http.StatusUnauthorized, gin.H{"code": 40100, "message": "未登录"})
			return
		}
		uid, err := tm.Parse(raw)
		if err != nil {
			c.AbortWithStatusJSON(http.StatusUnauthorized, gin.H{"code": 40100, "message": "登录已失效"})
			return
		}
		c.Set(userIDKey, uid)
		c.Next()
	}
}

func CurrentUserID(c *gin.Context) int64 { return c.GetInt64(userIDKey) }

// 文件：internal/router/router.go
package router

import (
	"net/http"

	"github.com/gin-gonic/gin"

	"todo-api/internal/auth"
	"todo-api/internal/handler"
	"todo-api/internal/middleware"
)

type Deps struct {
	Tasks  *handler.TaskHandler
	Auth   *handler.AuthHandler
	Tokens *auth.TokenManager
}

func New(d Deps) *gin.Engine {
	r := gin.New()
	r.Use(gin.Recovery(), middleware.Logger(), middleware.CORS("http://localhost:5173"))
	api := r.Group("/api")
	api.GET("/health", func(c *gin.Context) { c.JSON(http.StatusOK, gin.H{"status": "ok"}) })
	api.POST("/auth/register", d.Auth.Register)
	api.POST("/auth/login", d.Auth.Login)

	authed := api.Group("", middleware.RequireAuth(d.Tokens))
	authed.GET("/me", d.Auth.Me)
	authed.GET("/tasks", d.Tasks.List)
	authed.GET("/tasks/:id", d.Tasks.Get)
	authed.POST("/tasks", d.Tasks.Create)
	authed.PATCH("/tasks/:id", d.Tasks.Update)
	authed.DELETE("/tasks/:id", d.Tasks.Delete)
	return r
}

易错点 / 面试常问（补充）

易错点：登录失败不要区分“用户不存在”和“密码错误”，统一返回“用户名或密码错误”，防止被枚举账号。
易错点：鉴权中间件解析令牌时要指定允许的签名算法，否则可能被“改算法”攻击；令牌过期时间不要太长。
易错点：注册接口的邮箱唯一靠数据库唯一索引保证，不要只靠“先查再插”（并发时会重复），捕获唯一约束错误后返回 409。
面试常问：401 和 403 的区别（没登录 / 登录了但没权限）；令牌被盗怎么办（短有效期 + 刷新令牌 + 服务端黑名单或版本号）。

要建的文件

上面 6 个文件；更新 api.http：注册 → 登录 → 把令牌存成变量 → 带令牌访问任务接口。

运行方式

go build ./... && go test ./...

完成标准

不带令牌访问 /api/tasks 返回 401；登录后带令牌返回 200。

第 7 天：配置切换、接口测试、集成测试、收尾

学习内容

1. 配置和依赖组装

说明：用环境变量选择存储实现（STORE=memory|postgres），本地没装数据库也能跑；JWT 密钥必须从环境变量读，长度不够就拒绝启动。
案例：

// 文件：internal/config/config.go
package config

import (
	"errors"
	"os"
	"time"
)

type Config struct {
	Port        string
	Store       string // memory 或 postgres
	DatabaseURL string
	JWTSecret   string
	JWTTTL      time.Duration
}

func Load() (Config, error) {
	c := Config{
		Port:        getenv("PORT", "8080"),
		Store:       getenv("STORE", "memory"),
		DatabaseURL: os.Getenv("DATABASE_URL"),
		JWTSecret:   os.Getenv("JWT_SECRET"),
		JWTTTL:      2 * time.Hour,
	}
	if len(c.JWTSecret) < 32 {
		return c, errors.New("JWT_SECRET 至少 32 个字符")
	}
	if c.Store == "postgres" && c.DatabaseURL == "" {
		return c, errors.New("STORE=postgres 时必须设置 DATABASE_URL")
	}
	return c, nil
}

func getenv(k, def string) string {
	if v := os.Getenv(k); v != "" {
		return v
	}
	return def
}

// 文件：main.go
package main

import (
	"context"
	"errors"
	"log/slog"
	"net/http"
	"os"
	"os/signal"
	"syscall"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"

	"todo-api/internal/auth"
	"todo-api/internal/config"
	"todo-api/internal/handler"
	"todo-api/internal/router"
	"todo-api/internal/store"
)

func main() {
	cfg, err := config.Load()
	if err != nil {
		slog.Error("config", "err", err)
		os.Exit(1)
	}

	var tasks store.TaskStore = store.NewMemoryStore()
	var users store.UserStore = store.NewMemoryUserStore()
	if cfg.Store == "postgres" {
		pool, err := pgxpool.New(context.Background(), cfg.DatabaseURL)
		if err == nil {
			err = pool.Ping(context.Background())
		}
		if err != nil {
			slog.Error("database", "err", err)
			os.Exit(1)
		}
		defer pool.Close()
		tasks, users = store.NewPgStore(pool), store.NewPgUserStore(pool)
	}

	tm := auth.NewTokenManager(cfg.JWTSecret, cfg.JWTTTL)
	r := router.New(router.Deps{
		Tasks:  &handler.TaskHandler{Store: tasks},
		Auth:   &handler.AuthHandler{Users: users, Tokens: tm},
		Tokens: tm,
	})
	srv := &http.Server{Addr: ":" + cfg.Port, Handler: r}

	go func() {
		if err := srv.ListenAndServe(); err != nil && !errors.Is(err, http.ErrServerClosed) {
			slog.Error("listen", "err", err)
			os.Exit(1)
		}
	}()
	ctx, stop := signal.NotifyContext(context.Background(), os.Interrupt, syscall.SIGTERM)
	defer stop()
	<-ctx.Done()
	shutdownCtx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	_ = srv.Shutdown(shutdownCtx)
}

2. 接口测试改为带登录

说明：路由加了鉴权，上周的测试要先拿到令牌。测试里直接用 TokenManager 签一个，比走注册登录更快。
案例：

// 文件：internal/handler/task_handler_test.go
package handler_test

import (
	"net/http"
	"net/http/httptest"
	"strings"
	"testing"
	"time"

	"github.com/gin-gonic/gin"

	"todo-api/internal/auth"
	"todo-api/internal/handler"
	"todo-api/internal/router"
	"todo-api/internal/store"
)

func setup() (*gin.Engine, string) {
	gin.SetMode(gin.TestMode)
	tm := auth.NewTokenManager("test-secret-test-secret-test-secret!!", time.Hour)
	r := router.New(router.Deps{
		Tasks:  &handler.TaskHandler{Store: store.NewMemoryStore()},
		Auth:   &handler.AuthHandler{Users: store.NewMemoryUserStore(), Tokens: tm},
		Tokens: tm,
	})
	token, _ := tm.Issue(1)
	return r, token
}

func do(r *gin.Engine, method, path, token, body string) *httptest.ResponseRecorder {
	w := httptest.NewRecorder()
	req := httptest.NewRequest(method, path, strings.NewReader(body))
	req.Header.Set("Content-Type", "application/json")
	if token != "" {
		req.Header.Set("Authorization", "Bearer "+token)
	}
	r.ServeHTTP(w, req)
	return w
}

func TestTasksRequireAuth(t *testing.T) {
	r, token := setup()
	if w := do(r, http.MethodGet, "/api/tasks", "", ""); w.Code != http.StatusUnauthorized {
		t.Fatalf("无令牌 got %d", w.Code)
	}
	if w := do(r, http.MethodPost, "/api/tasks", token, `{"title":"学 SQL"}`); w.Code != http.StatusCreated {
		t.Fatalf("创建 got %d", w.Code)
	}
	if w := do(r, http.MethodGet, "/api/tasks/999", token, ""); w.Code != http.StatusNotFound {
		t.Fatalf("不存在 got %d", w.Code)
	}
}

func TestRegisterLogin(t *testing.T) {
	r, _ := setup()
	body := `{"email":"a@example.com","name":"小王","password":"secret123"}`
	if w := do(r, http.MethodPost, "/api/auth/register", "", body); w.Code != http.StatusCreated {
		t.Fatalf("注册 got %d", w.Code)
	}
	if w := do(r, http.MethodPost, "/api/auth/register", "", body); w.Code != http.StatusConflict {
		t.Fatalf("重复注册 got %d", w.Code)
	}
	if w := do(r, http.MethodPost, "/api/auth/login", "", `{"email":"a@example.com","password":"bad"}`); w.Code != http.StatusUnauthorized {
		t.Fatalf("错误密码 got %d", w.Code)
	}
}

3. 集成测试（连真数据库）

说明：内存版测试快，但发现不了 SQL 写错；集成测试连真数据库。没设置 TEST_DATABASE_URL 时自动跳过，这样没有数据库的环境也能跑 go test。
案例：

// 文件：internal/store/pg_store_test.go
package store

import (
	"context"
	"errors"
	"os"
	"testing"

	"github.com/jackc/pgx/v5/pgxpool"

	"todo-api/internal/model"
)

func setupPg(t *testing.T) *PgStore {
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("未设置 TEST_DATABASE_URL，跳过集成测试")
	}
	pool, err := pgxpool.New(context.Background(), url)
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(pool.Close)
	if _, err := pool.Exec(context.Background(), "TRUNCATE tasks RESTART IDENTITY CASCADE"); err != nil {
		t.Fatal(err)
	}
	return NewPgStore(pool)
}

func TestPgStoreCRUD(t *testing.T) {
	s := setupPg(t)
	ctx := context.Background()
	created, err := s.Create(ctx, model.Task{Title: "集成测试"})
	if err != nil {
		t.Fatal(err)
	}
	created.Done = true
	if _, err := s.Update(ctx, created); err != nil {
		t.Fatal(err)
	}
	done := true
	list, _ := s.List(ctx, &done)
	if len(list) != 1 {
		t.Fatalf("got %d", len(list))
	}
	if err := s.AddTags(ctx, created.ID, []string{"学习", "学习"}); err != nil {
		t.Fatal(err)
	}
	if err := s.Delete(ctx, created.ID); err != nil {
		t.Fatal(err)
	}
	if _, err := s.Get(ctx, created.ID); !errors.Is(err, ErrNotFound) {
		t.Fatalf("want ErrNotFound, got %v", err)
	}
}

易错点 / 面试常问（补充）

易错点：集成测试每次开始前要清理数据（本周用 TRUNCATE ... RESTART IDENTITY CASCADE），否则测试之间互相影响、结果不稳定。
易错点：go test ./... -race 能发现数据竞争，但只在测试实际跑到的代码路径上。
面试常问：单元测试、集成测试、端到端测试的区别和比例；为什么用接口（Store）隔离存储层——测试可以换成内存实现，业务代码不用改。

要建的文件

internal/config/config.go、更新 main.go、更新 internal/handler/task_handler_test.go、internal/store/pg_store_test.go
todo-api/README.md 补充：数据库启动、迁移、切换存储、鉴权说明。
提交推送（确认 .env 没提交）。

运行方式

export JWT_SECRET=$(openssl rand -hex 32)
STORE=postgres DATABASE_URL=postgres://todo:todo@localhost:5432/todo?sslmode=disable go run .
TEST_DATABASE_URL=postgres://todo:todo@localhost:5432/todo?sslmode=disable go test ./... -race

完成标准

切换 STORE 后接口行为一致，重启后数据还在；全部测试通过；go vet 无警告。

本周相关专题

本节由 tools/gen_week_topics.py 根据专题文件的 week 字段自动生成，请勿手改；想调整就修改对应专题文件的 frontmatter 后重新生成。

AI 编程练习（每周）：本周至少一个功能用 AI 编程工具（Cursor / Claude Code / Codex 等）完成，按 AI 编程：工作流写清需求和规则，按验证与证据保存测试结果和会话记录（脱敏后），第 15 周整理成“AI 编程 + 验证方法”的应聘案例。

技术栈	课程 / 场景 / 面试题
Gin Web 框架	课程：中间件、JWT 鉴权和统一错误处理；场景：企业后台接口规范；面试题
Go 语言	课程：测试和工程化
MySQL	课程：InnoDB 索引；课程：事务、隔离级别、MVCC 和锁（行锁、间隙锁、死锁）；场景：大表深分页和历史数据；面试题
PostgreSQL	课程：表设计和 SQL；课程：索引、EXPLAIN、事务、隔离级别和 MVCC；场景：订单状态机；面试题

本周自查清单

会建表、加约束，会设计一对多和多对多
会写 JOIN、GROUP BY、HAVING，会看 PostgreSQL 和 MySQL 的执行计划
能说出索引失效的常见情况、B+ 树、回表和覆盖索引
会写迁移，理解“先迁移再发代码”
会用 pgx 写参数化查询和事务，会用 ON CONFLICT 做幂等写入
能讲清四个隔离级别、MVCC、乐观锁和悲观锁
能说出至少 5 条 MySQL 和 PostgreSQL 的差异
会用 bcrypt 存密码，会签发和校验 JWT，分得清 401 和 403
在总纲进度记录里勾掉第 2 周

41 KiB Raw Permalink Blame History Unescape Escape

第 2 周：数据库（PostgreSQL 为主，兼顾 MySQL）+ 注册登录与 JWT

本周最终目录（在上周 todo-api/backend 基础上新增）

第 1 天：用 Docker 跑 PostgreSQL，写基础 SQL 和表设计

学习内容

要建的文件

运行方式

完成标准

第 2 天：多表查询、索引、EXPLAIN

学习内容

要建的文件

运行方式

完成标准

第 3 天：迁移和 Go 访问数据库（pgx）

学习内容

要建的文件

运行方式

完成标准

第 4 天：事务、隔离级别、幂等写入

学习内容

要建的文件

运行方式

完成标准

第 5 天：密码哈希和 JWT

学习内容

要建的文件

运行方式

完成标准

第 6 天：注册、登录、鉴权中间件

学习内容

要建的文件

运行方式

完成标准

第 7 天：配置切换、接口测试、集成测试、收尾

学习内容

要建的文件

运行方式

完成标准

本周相关专题

本周自查清单

41 KiB

Raw Permalink Blame History

本周最终目录（在上周 `todo-api/backend` 基础上新增）