生产数据库批量更新数据的一点实践

/**
 * @description
 *
 * 1. SQL查出目标数据主键，导出CSV
 * 2. 创建单条update SQL模版
 * 3. 执行该JS文件,会生成目标SQL
 * 4. 目标机器执行SQL
 *
 * 注意：修改CHUNK_SIZE来控制逐条更新的记录量
 */
const csv = require('csv-parser');
const fs = require('fs');
const ids = [];
const CHUNK_SIZE = 10;
// 输入数据文件
const INPUT_FILE = 'quote_ids.csv';
// 目标生成SQL文件
const OUTPUT_FILE = 'repair_data.sql';
const createSQLTemplate = (idArr) => `update \`a-prod\`.quote a left join \`b-prod\`.geographic_unit b
    on a.sales_org = b.sales_org and a.sales_office = b.sales_office and a.business_unit = b.business_unit and
       a.country = b.country_iso_code
set a.geographic_unit_id=b.id
where a.id in ('${idArr.join('\',\'')}');\n\n\n`;
fs.createReadStream(INPUT_FILE)
    .pipe(csv())
    .on('data', (row) => {
        ids.push(row.id);
    })
    .on('end', () => {
        const chunkIds = ids.reduce((res, item, index) => {
            let group = Math.floor(index / CHUNK_SIZE);
            if (res[group] === undefined) {
                res[group] = [];
            }
            res[group].push(item);
            return res;
        }, []);

        fs.writeFile(OUTPUT_FILE, chunkIds.map(item => createSQLTemplate(item)).join(''), () => {
            console.log('write success');
        });
    });