packServer/backend/scripts/dedupe-scan-records.js

65 lines
2.2 KiB
JavaScript
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

'use strict';
/**
* 运维脚本:清理重复运单号,同一个运单号只保留最后一条
*
* 规则与 `database/migrations/20261019120000-dedupe-scan-records.js` 完全一致:
* - 分组:`subject_id`(空值按 0 归组)+ `barcode`,跨主体互不影响;
* - 每组保留 `scan_time` 最新的一条,时间相同保留 `id` 最大的;
* - 其余**物理删除**(scan_records 是流水表,没有软删除)。
*
* 用途:后端的 24 小时合并窗口之外,同一运单再扫会新增一条记录。
* 需要把历史数据「只留最后一条」时随时可以再跑本脚本(幂等,可重复执行)。
*
* 用法:
* npm run records:dedupe # 先看统计
* npm run records:dedupe -- --yes # 真正删除
*/
const mysql = require('mysql2/promise');
const resolveDbConfig = require('./db-config');
const SQL_DUP_IDS = `
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY COALESCE(subject_id, 0), barcode
ORDER BY scan_time DESC, id DESC
) AS rn
FROM scan_records
) ranked
WHERE rn > 1
`;
(async () => {
const apply = process.argv.includes('--yes');
const cfg = resolveDbConfig();
console.log('数据库:%s@%s:%s', cfg.database, cfg.host, cfg.port);
const conn = await mysql.createConnection(cfg);
try {
const [[ total ]] = await conn.query('SELECT COUNT(*) AS cnt FROM scan_records');
const [ rows ] = await conn.query(SQL_DUP_IDS);
const ids = rows.map(row => Number(row.id));
console.log('扫码记录共 %d 条,其中重复(同主体同运单号的旧记录)%d 条', total.cnt, ids.length);
if (!ids.length) {
console.log('没有重复运单号,无需处理');
return;
}
if (!apply) {
console.log('预览模式:没有做任何修改。加 --yes 才会真正删除。');
return;
}
const [ result ] = await conn.query('DELETE FROM scan_records WHERE id IN (?)', [ ids ]);
console.log('已删除 %d 条,剩余 %d 条(每个运单号只保留最后一条)', result.affectedRows, total.cnt - result.affectedRows);
} finally {
await conn.end();
}
})().catch(err => {
console.error('清理失败:', err.message);
process.exit(1);
});