unis_hrms/docs/MySQL迁移指南.md

5.4 KiB
Raw Permalink Blame History

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. 已知差异与注意事项

  1. 时区:库内统一存 UTC naive DATETIME(db.base.utcnow()),展示层由前端按 Asia/Shanghai 渲染,请勿在数据库中改存本地时间。
  2. 大小写:Linux MySQL lower_case_table_names=0,表名必须保持脚本中的小写下划线。
  3. Integer 自增:如需超过 21 亿行(日志表若干年后),把对应列改为 BigInteger 并生成一次迁移即可。
  4. 容器部署:用 ./scripts/deploy.sh 起整站时,把 .env 的 DATABASE_URL 指向 MySQL(compose 内置库用服务名 mysql)并设 PIP_EXTRAS=[mysql],详见根 README「生产部署」。
  5. 全文检索:当前模糊查询用 LIKE,MySQL 上可平滑换成 MATCH ... AGAINST (已在 core/pagination.py 单点封装)。
  6. 连接池:MySQL 建议 pool_recycle=3600,可在 db/session.py 的非 SQLite 分支加上。