记账这件事，市面上的App不少，但要么塞满广告，要么数据存在别人服务器上让人不放心。其实对于个人记账这种数据量小、读写频率低的场景，SQLite几乎是为它量身定做的：单文件数据库、零配置、跨平台，一个几MB的文件就能存下你十年的流水。这篇文章就带你从表结构设计开始，一步步用SQLite搭出一个结构清晰、查询顺手、还能持续扩展的个人记账数据库。文中的SQL在sqlite3命令行和大部分编程语言的SQLite驱动中都可以直接运行。
一、先想清楚数据模型：账本是三张表的事
很多人一上来就急着写CREATE TABLE，结果用了两个月发现分类想改、账户想加、转账不知道怎么记。设计表结构之前，先把记账的业务拆解一下：一笔交易发生时，本质上有三个信息——钱从哪来（或到哪去）、花在了什么类别上、金额和时间是多少。对应到表结构，就是账户表、分类表、交易流水表三张核心表。
账户表（accounts）记录你的每一笔“钱包”，可以是现金、某张银行卡、支付宝、微信，字段包括账户名、初始余额、账户类型。分类表（categories）采用自引用的父子结构，支持“餐饮”下面再分“早餐、午餐、外卖”这种两级分类，这样既能在粗粒度上看总支出，也能下钻看明细。交易流水表（transactions）是核心，每一条记录代表一次资金变动，通过外键关联到账户和分类，并加一个交易类型字段区分收入、支出和转账。
这里有个容易被忽视的设计点：转账不要拆成“支出一笔+收入一笔”两条记录。拆开的写法看似简单，但统计支出时会把转账也算进去，月底一看账单，钱明明只是从银行卡挪到了支付宝，支出却虚高了一大截。正确做法是用一个type字段标记transfer，并额外存一个对手账户ID，这样转账在统计时可以单独过滤。
二、建表SQL与外键约束
下面是完整的建表语句。注意SQLite默认不开启外键约束，需要手动执行PRAGMA foreign_keys = ON;，否则外键形同虚设，删掉一个分类后流水表会留下孤儿记录。
PRAGMA foreign_keys = ON;
-- 账户表
CREATE TABLE accounts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
type TEXT NOT NULL CHECK(type IN ('cash','bank','alipay','wechat','other')),
init_balance REAL NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
);
-- 分类表（自引用支持二级分类）
CREATE TABLE categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
parent_id INTEGER REFERENCES categories(id),
kind TEXT NOT NULL CHECK(kind IN ('expense','income'))
);
-- 交易流水表
CREATE TABLE transactions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT NOT NULL CHECK(type IN ('expense','income','transfer')),
amount REAL NOT NULL CHECK(amount > 0),
account_id INTEGER NOT NULL REFERENCES accounts(id),
to_account_id INTEGER REFERENCES accounts(id),
category_id INTEGER REFERENCES categories(id),
note TEXT,
trade_date TEXT NOT NULL DEFAULT (date('now','localtime')),
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
);
-- 索引：按日期和账户查询是最高频的操作
CREATE INDEX idx_txn_date ON transactions(trade_date);
CREATE INDEX idx_txn_account ON transactions(account_id);
CREATE INDEX idx_txn_category ON transactions(category_id);
关于金额字段，这里用REAL存储是个人账本可接受的简化，但如果对精度要求高，更严谨的做法是以“分”为单位存整数，彻底避开浮点误差。CHECK约束也值得保留：它能在应用层疏忽时兜底，比如金额为负数、交易类型拼写错误这类低级错误，直接在数据库层面被拦截。
索引方面，个人记账的数据量通常在万条以内，性能压力不大，但trade_date和account_id上的索引依然是值得的，因为几乎所有报表查询都带有日期范围或账户过滤条件，索引能让SQLite避免全表扫描，响应从毫秒级降到微秒级。
三、记账场景下的高频查询
表建好了，接下来看几个日常真正会用到的查询。第一个是按月汇总各类支出，这是月底复盘最常用的报表：
SELECT COALESCE(parent.name, c.name) AS 分类,
SUM(t.amount) AS 支出合计
FROM transactions t
JOIN categories c ON t.category_id = c.id
LEFT JOIN categories parent ON c.parent_id = parent.id
WHERE t.type = 'expense'
AND t.trade_date BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY COALESCE(parent.name, c.name)
ORDER BY 支出合计 DESC;
这里用COALESCE处理二级分类的归并：如果记录挂在子分类上，就汇总到父分类名下，这样月报不会出现“早餐30元、午餐25元”这种碎片化条目，而是统一显示“餐饮”一个大类。想要看明细时再去掉归并即可。
第二个实用查询是计算各账户当前余额，思路是初始余额加上收支差，再考虑转出的钱：
SELECT a.name AS 账户,
ROUND(a.init_balance
+ IFNULL(SUM(CASE WHEN t.type='income' THEN t.amount END),0)
- IFNULL(SUM(CASE WHEN t.type='expense' THEN t.amount END),0)
- IFNULL(SUM(CASE WHEN t.type='transfer' THEN t.amount END),0)
+ IFNULL(SUM(CASE WHEN t.type='transfer' AND t.to_account_id=a.id
THEN t.amount END),0)
, 2) AS 当前余额
FROM accounts a
LEFT JOIN transactions t
ON t.account_id = a.id OR t.to_account_id = a.id
GROUP BY a.id;
转账的处理逻辑是：从转出账户扣钱、向转入账户加钱。这个查询跑出来的余额可以定期和对账单核对，如果数字对不上，说明有漏记或多记，这正是记账系统最有价值的自我校验功能。
四、写入数据的工程细节
查询写得再漂亮，数据写不干净也白搭。用编程语言操作SQLite时，有几个实践值得坚持：第一，永远用预编译语句（参数化查询），既防SQL注入，又能让SQLite缓存执行计划，重复写入时明显更快；第二，批量写入要包在事务里，比如导入历史账单CSV时，几万条数据裸写可能要几十秒，包上事务后一秒内就能完成，因为事务把每次写盘合并成了一次。
import sqlite3
conn = sqlite3.connect('finance.db')
conn.execute('PRAGMA foreign_keys = ON;')
def add_expense(amount, account_id, category_id, note=''):
sql = '''INSERT INTO transactions(type, amount, account_id, category_id, note)
VALUES('expense', ?, ?, ?, ?)'''
with conn:
conn.execute(sql, (amount, account_id, category_id, note))
# 批量导入历史数据：放在一个事务里
def import_history(rows):
sql = '''INSERT INTO transactions(type, amount, account_id, category_id, trade_date)
VALUES('expense', ?, ?, ?, ?)'''
with conn:
conn.executemany(sql, rows)
Python的with conn语法正好对应一个事务的边界，出异常自动回滚，平时自动提交，语义非常清晰。另一个细节是WAL模式：执行PRAGMA journal_mode=WAL;后，读写可以并发进行，如果你的记账程序有后台同步或定时任务在写数据，同时前台在查报表，WAL能避免锁冲突。
最后建议养成定期备份的习惯，SQLite的备份不能只复制文件（写入过程中复制可能得到损坏的副本），用VACUUM INTO 'backup.db'或者.backup命令才是安全的在线备份方式。账本数据虽小，却是几年积累的记录，丢一次就再也补不回来了。