构建一个二手物品交易平台的后台数据层，SQLite是一个值得考虑的选择。它不需要独立服务器进程，数据存储在一个文件中，部署简单，适合中小规模应用。但要把这个数据库设计得既能满足业务需求又具备良好的查询性能，不能只凭感觉建几张表了事。整体需求包括：用户注册登录、发布闲置物品、按分类浏览、对感兴趣的商品发起购买或留言。围绕这些行为，至少需要用户表、分类表、商品表、订单表和留言表。
表结构设计的第一步是明确实体之间的关系。一个用户能发布多件商品，因此商品表需要外键指向用户表。一个分类下可以有多件商品，商品表也要外键指向分类表。订单关联买家、卖家和具体商品，通常还需要记录成交价格和状态。留言则关联商品和发送者。关系理顺后，建表语句才能写得严谨，而不是等到数据量变大再回头修改。
一、核心表结构与约束设计
创建表时最容易被忽略的是约束。很多开发者认为SQLite是轻量数据库，可以省略外键和检查约束，等业务逻辑层去保证数据正确。但事实是，数据库层的约束是最后一道防线，能避免程序bug写入无效数据。SQLite默认不启用外键约束，需要在每个连接执行PRAGMA foreign_keys = ON;，否则外键声明形同虚设。
下面是完整的建表脚本。用户表保存登录信息，密码字段在实际项目中应存储哈希值而非明文。分类表使用自增主键，名称要求唯一。商品表包含标题、描述、价格、成色、状态、发布者和分类，价格必须大于等于0，状态限制在可枚举的几个值内。订单表包含买家、卖家、商品、成交价和状态，留言表则关联商品和发送者。
PRAGMA foreign_keys = ON;
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
phone TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);
CREATE TABLE categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
sort_order INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
seller_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
title TEXT NOT NULL,
description TEXT,
price REAL NOT NULL CHECK (price >= 0),
condition TEXT NOT NULL CHECK (condition IN ('全新', '几乎全新', '轻微使用', '明显使用', '需要维修')),
status TEXT NOT NULL DEFAULT '在售' CHECK (status IN ('在售', '已预订', '已售出', '已下架')),
view_count INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
updated_at TEXT,
FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL,
buyer_id INTEGER NOT NULL,
seller_id INTEGER NOT NULL,
deal_price REAL NOT NULL CHECK (deal_price >= 0),
status TEXT NOT NULL DEFAULT '待确认' CHECK (status IN ('待确认', '已完成', '已取消')),
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE RESTRICT,
FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE RESTRICT,
FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE RESTRICT
);
CREATE TABLE messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL,
sender_id INTEGER NOT NULL,
content TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE
);
这里的外键策略需要根据业务权衡。比如商品表指向分类表使用ON DELETE RESTRICT，意味着分类下还有商品时不允许删除分类；指向用户表使用ON DELETE CASCADE，删除用户时自动删除他发布的商品，当然实际项目中软删除可能更合适。CHECK约束限制了价格不能为负数、商品成色和状态只能是预设枚举，这些都能将无效数据挡在数据库之外。
二、索引策略与查询性能优化
二手交易平台的常见查询包括按分类筛选商品、按卖家查看他发布的商品、按时间倒序浏览最新发布，以及买家查看自己的订单。如果这些查询频繁执行而相关列没有索引，数据量达到几万条后全表扫描会明显变慢。为商品表的外键和排序字段建立索引是性价比很高的做法。
下面给出索引创建语句。商品表的category_id和seller_id分别对应分类筛选和个人主页查询，status加上created_at的联合索引适合首页按状态筛选后按时间倒序展示。订单表则需要为buyer_id和seller_id建立索引，分别支撑买家订单列表和卖家卖出记录。
CREATE INDEX idx_items_category_id ON items(category_id); CREATE INDEX idx_items_seller_id ON items(seller_id); CREATE INDEX idx_items_status_created ON items(status, created_at DESC); CREATE INDEX idx_orders_buyer_id ON orders(buyer_id); CREATE INDEX idx_orders_seller_id ON orders(seller_id); CREATE INDEX idx_orders_item_id ON orders(item_id); CREATE INDEX idx_messages_item_id ON messages(item_id);
联合索引的列顺序不能随意。idx_items_status_created把status放在前面，是因为查询几乎总是先按状态等值过滤，再按时间排序。如果反过来把created_at放在前面，排序可以利用索引，但状态过滤还得回表扫描，效果差很多。可以用EXPLAIN QUERY PLAN查看实际执行计划，验证索引是否被使用。下面这段分页查询依赖该联合索引，能够快速定位在售商品并倒序取最新的若干条。
SELECT i.id, i.title, i.price, i.created_at, u.username FROM items i JOIN users u ON i.seller_id = u.id WHERE i.status = '在售' ORDER BY i.created_at DESC LIMIT 20 OFFSET 0;
三、下单流程中的事务与一致性
一次下单操作至少涉及两步：向订单表插入记录，同时把商品状态从在售改为已预订或已售出。如果这两步没有放在同一个事务里，很可能出现订单创建成功但商品状态未更新，或者商品被标记为已售出却没有对应订单。SQLite的事务语法和其他SQL数据库基本一致，用BEGIN TRANSACTION开启，所有操作成功后COMMIT提交，出现异常时ROLLBACK回滚。
下面是一个简化的事务示例，先检查商品是否仍处于在售状态，防止两个买家同时购买同一件商品。虽然SQLite写入是串行化的，但应用层的检查仍然必要。事务中先更新商品状态，再插入订单，任何一步失败都会整体回滚。
BEGIN TRANSACTION;
UPDATE items
SET status = '已预订', updated_at = datetime('now', 'localtime')
WHERE id = 1 AND status = '在售';
INSERT INTO orders (item_id, buyer_id, seller_id, deal_price, status)
SELECT id, 2, seller_id, price, '待确认'
FROM items
WHERE id = 1 AND status = '已预订';
COMMIT;
SQLite默认使用数据库级锁，并发写操作会排队。对于二手交易这种读多写少的场景，可以开启WAL模式提升并发读写能力，执行PRAGMA journal_mode = WAL;。WAL模式下读操作不会阻塞写操作，写操作也不会阻塞读操作，只有多个写操作之间仍需要串行。如果应用部署在单机环境，WAL模式能显著改善用户体验。
PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;
四、时间字段与状态字段的选型细节
时间字段到底存成TEXT还是INTEGER，一直没有绝对答案。TEXT方案直接存ISO 8601格式字符串，比如2025-01-01 10:30:00，可读性好，方便调试，也支持字符串范围比较。INTEGER方案存Unix时间戳，节省空间，跨时区计算简单，但查询结果需要转换。对于SQLite这种没有专门日期类型的数据库，推荐存TEXT并统一使用UTC时间，前端展示时再转换成本地时区。
状态字段也有类似选择。有些开发者习惯用TINYINT数字表示状态，比如0代表在售、1代表已预订，这样比较节省存储，但代码里满是魔法数字，后期维护困难。推荐使用TEXT配合CHECK约束，让数据库自描述。即使以后要增加状态，只要修改CHECK约束即可，可读性远高于数字。唯一需要注意的是，应用代码中不要直接拼接SQL，应使用参数化查询避免注入，同时也能正确处理字符串值。
软删除也是设计时需要考虑的问题。例如商品被卖家下架，是直接删除记录还是只更新状态为已下架？订单和留言都依赖商品记录，删除商品会导致关联数据失去上下文。因此商品表使用状态字段进行软删除是更稳妥的做法，统计和历史查询也不会丢失数据。用户表同样可以考虑增加is_active字段，而不是直接物理删除。
五、扩展设计思路与常见坑点
当二手交易平台从单一校园扩展到多校区或多城市时，商品表可能需要增加地区字段，或者单独建立地区表。分类表也可以扩展成多级分类，通过parent_id自关联实现树形结构。这些扩展并不会推翻现有设计，只需要在核心表上增加外键和索引即可，说明一套设计合理的初始结构能够伴随业务成长。
常见坑点之一是忘记开启外键约束，导致关联数据可以被随意删除或插入无效引用。另一个坑点是过早在所有列上建立索引，虽然查询快了但写入变慢，并且占用额外磁盘空间。SQLite的索引选择依赖统计信息，必要时可以执行ANALYZE;更新统计，让查询优化器做出更准确的判断。还有一点是时间默认值在不同环境下可能产生时区偏差，建议应用启动时统一设置时区或直接使用UTC时间。
备份与恢复也是实战中不能忽略的部分。SQLite数据库文件可以通过文件复制进行冷备份，但为了确保一致性，建议使用VACUUM INTO 'backup.db';命令在线备份，它会在不中断服务的情况下生成一个完整的数据库副本。线上环境还应定期执行PRAGMA integrity_check;检查数据库文件完整性，防止潜在损坏影响业务。