5.4 KiB
5.4 KiB
UNIS HRMS:SQLite → MySQL 迁移指南
系统的数据层在设计时就为迁移做准备:同一套 ORM 模型 + 同一套 Alembic 迁移, 在 SQLite 与 MySQL 上都能生成正确的 DDL。迁移动作的本质是 「改一个环境变量 + 跑一次迁移 + 倒一次数据」。
0. 已做的准备工作(无需再做)
| 事项 | 做法 |
|---|---|
| 统一命名约束 | app/db/base.py 中的 NAMING_CONVENTION,MySQL 要求约束名全局唯一,SQLite 默认匿名 |
| 金额类型 | 自定义 Money(DECIMAL(14,2)),SQLite 返回 float 时自动规整为 Decimal |
| 布尔/JSON | BOOLEAN(MySQL→TINYINT(1))、JSON(MySQL 5.7+ / SQLite 3.38+ 通用) |
| 枚举 | 数据库层用 VARCHAR + Pydantic Literal 校验,避免 MySQL ENUM 变更困难 |
| 表选项 | after_parent_attach 事件为每张表补 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
| 主键 | 全部 INT AUTO_INCREMENT,MySQL 直接可用 |
| 初始迁移 | migrations/versions/*_initial_schema.py(46 张业务表) |
1. 建库建账号(MySQL 8.0 为例)
CREATE DATABASE hrms DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'hrms'@'%' IDENTIFIED BY '强密码';
GRANT ALL PRIVILEGES ON hrms.* TO 'hrms'@'%';
FLUSH PRIVILEGES;
建议
utf8mb4_0900_ai_ci(MySQL 5.7 用utf8mb4_general_ci)。 若使用utf8mb4_bin之外的大小写不敏感排序规则,sys_user.username等唯一索引 仍然安全,因为工号/用户名统一存小写。
2. 安装 MySQL 驱动
./.venv/bin/pip install -e ".[mysql]" # aiomysql + PyMySQL + cryptography
cryptography是必需的:MySQL 8 默认认证插件是caching_sha2_password, 缺少它时 PyMySQL/aiomysql 会抛出cryptography is required for sha256_password。
3. 切换连接串
编辑项目根目录的 .env:
# SQLite(开发)
# DATABASE_URL=sqlite+aiosqlite:///./data/hrms.db
# MySQL(生产)
DATABASE_URL=mysql+aiomysql://hrms:强密码@127.0.0.1:3306/hrms?charset=utf8mb4
AUTO_CREATE_SCHEMA=false
AUTO_SEED=false
说明:
- 运行时(FastAPI)使用异步驱动
aiomysql; - Alembic 使用同步驱动,
settings.sync_database_url会自动把+aiomysql改写成+pymysql,无需维护两条 DSN; - 切到 MySQL 后请把
AUTO_CREATE_SCHEMA置为false,表结构完全交给 Alembic 管理。
4. 建表
./.venv/bin/alembic upgrade head
命令都在项目根目录执行;
alembic.ini与.env在根目录,app/是后端包,data/放 SQLite 库与附件,因此不需要cd server。
想先检查将要执行的 SQL(不连库):
./.venv/bin/alembic upgrade head --sql # 打印完整建表脚本
5. 迁移存量数据
推荐用「结构由 Alembic 建,数据由脚本搬」的方式,可控且可重跑:
# 1) 保持 DATABASE_URL 指向 SQLite,导出为中间文件(默认落在 data/ 下)
./.venv/bin/python scripts/export_sqlite.py --out data/sqlite_dump.json
# 2) DATABASE_URL 指向 MySQL 后导入(表结构需先 upgrade head)
./.venv/bin/python scripts/export_sqlite.py --in data/sqlite_dump.json
脚本自己会把相对路径按项目根目录解析,可以从任何目录用绝对路径调用。
或使用 MySQL 官方工具直接搬运(表结构与数据一次完成,之后需要 stamp):
mysqlpump -u hrms -p --databases hrms > hrms.sql # 从旧库导出
mysql -u hrms -p hrms_new < hrms.sql # 导入新库
./.venv/bin/alembic stamp head # 标记已有库为最新结构
6. 校验
./.venv/bin/alembic check # 模型与库结构一致性(应为 No new upgrade operations detected)
./.venv/bin/python -m pytest -q # 全量回归(独立用 data/test_hrms.db)
./.venv/bin/python -m app.seed # 补齐权限/字典等基线数据(幂等)
重点抽查:
sal_payslip、ben_enrollment的DECIMAL小数位是否为 2;JSON列(scope、items、changes)能否正常读写;- 中文/emoji 是否无乱码(连接串必须带
charset=utf8mb4); GET /api/v1/system/logs是否仍在记录写操作。
7. 后续演进(模型变更后)
./.venv/bin/alembic revision --autogenerate -m "add field xxx"
./.venv/bin/alembic upgrade head
SQLite 下 Alembic 自动启用 render_as_batch(),因此 ALTER COLUMN
一类 SQLite 原生不支持的操作也能正常执行。
8. 已知差异与注意事项
- 时区:库内统一存 UTC naive
DATETIME(db.base.utcnow()),展示层由前端按Asia/Shanghai渲染,请勿在数据库中改存本地时间。 - 大小写:Linux MySQL
lower_case_table_names=0,表名必须保持脚本中的小写下划线。 Integer自增:如需超过 21 亿行(日志表若干年后),把对应列改为BigInteger并生成一次迁移即可。- 容器部署:用
./scripts/deploy.sh起整站时,把.env的DATABASE_URL指向 MySQL(compose 内置库用服务名mysql)并设PIP_EXTRAS=[mysql],详见根 README「生产部署」。 - 全文检索:当前模糊查询用
LIKE,MySQL 上可平滑换成MATCH ... AGAINST(已在core/pagination.py单点封装)。 - 连接池:MySQL 建议
pool_recycle=3600,可在db/session.py的非 SQLite 分支加上。