MySQL按条件删除重复记录需求（Node.js定时执行场景）

嘿，针对你在Node.js里每秒执行命令删除指定商品重复竞价记录的需求，我来给你梳理下优化方案和具体实现~

一、优化你的SQL删除语句 #

你的原SQL思路是对的，但用子查询在WHERE条件里，当数据量大的时候性能可能不太理想。可以改成JOIN的方式，先找出每个商品下重复竞价组里的最新记录（max(id)），再删除同组里的其他记录，效率会更高：

DELETE t1 
FROM bid_account t1
JOIN (
    SELECT auction_id, bidding_price, MAX(id) AS max_id
    FROM bid_account
    WHERE auction_id = 1335
    GROUP BY auction_id, bidding_price
    HAVING COUNT(*) > 1
) t2 ON t1.auction_id = t2.auction_id 
    AND t1.bidding_price = t2.bidding_price 
    AND t1.id != t2.max_id
WHERE t1.auction_id = 1335;

这个语句的优势：

• 只针对有重复记录的竞价组处理（HAVING COUNT(*) > 1），减少不必要的数据扫描
• JOIN的写法更适合数据库优化器生成高效执行计划
• 明确保留每个竞价组里id最大的那条记录（也就是最新插入的那条），如果要保留最早的记录，把MAX(id)改成MIN(id)即可

二、Node.js中实现每秒定时执行的任务 #

这里给你两种可行方案，都能避免异步任务重叠的问题：

方案1：使用node-schedule（灵活的定时任务库） #

先安装依赖：

npm install node-schedule mysql2

然后编写代码：

const schedule = require('node-schedule');
const mysql = require('mysql2/promise');

// 替换成你的实际数据库配置
const dbConfig = {
  host: 'localhost',
  user: 'root',
  password: 'your-db-password',
  database: 'your-db-name'
};

// 封装删除重复记录的异步函数
async function removeDuplicateBids(targetAuctionId = 1335) {
  let connection;
  try {
    connection = await mysql.createConnection(dbConfig);
    
    // 参数化SQL，避免SQL注入风险
    const deleteSql = `
      DELETE t1 
      FROM bid_account t1
      JOIN (
          SELECT auction_id, bidding_price, MAX(id) AS max_id
          FROM bid_account
          WHERE auction_id = ?
          GROUP BY auction_id, bidding_price
          HAVING COUNT(*) > 1
      ) t2 ON t1.auction_id = t2.auction_id 
          AND t1.bidding_price = t2.bidding_price 
          AND t1.id != t2.max_id
      WHERE t1.auction_id = ?;
    `;
    
    const [result] = await connection.execute(deleteSql, [targetAuctionId, targetAuctionId]);
    console.log(`[${new Date().toLocaleString()}] 商品ID ${targetAuctionId}：成功删除 ${result.affectedRows} 条重复记录`);
  } catch (error) {
    console.error(`[${new Date().toLocaleString()}] 删除重复记录失败：`, error);
  } finally {
    // 确保数据库连接关闭
    if (connection) await connection.end();
  }
}

// 每秒执行一次任务（node-schedule表达式 * * * * * * 代表每秒触发）
const scheduledJob = schedule.scheduleJob('* * * * * *', async () => {
  await removeDuplicateBids(1335);
});

console.log('定时删除重复记录任务已启动，每秒执行一次');

方案2：使用原生setInterval（无需额外依赖） #

如果不想安装第三方库，用原生setInterval也可以，但要加锁防止前一次任务未完成就触发下一次：

const mysql = require('mysql2/promise');

const dbConfig = {
  host: 'localhost',
  user: 'root',
  password: 'your-db-password',
  database: 'your-db-name'
};

let isTaskRunning = false;

async function removeDuplicateBids(targetAuctionId = 1335) {
  let connection;
  try {
    connection = await mysql.createConnection(dbConfig);
    const deleteSql = `
      DELETE t1 
      FROM bid_account t1
      JOIN (
          SELECT auction_id, bidding_price, MAX(id) AS max_id
          FROM bid_account
          WHERE auction_id = ?
          GROUP BY auction_id, bidding_price
          HAVING COUNT(*) > 1
      ) t2 ON t1.auction_id = t2.auction_id 
          AND t1.bidding_price = t2.bidding_price 
          AND t1.id != t2.max_id
      WHERE t1.auction_id = ?;
    `;
    const [result] = await connection.execute(deleteSql, [targetAuctionId, targetAuctionId]);
    console.log(`[${new Date().toLocaleString()}] 商品ID ${targetAuctionId}：成功删除 ${result.affectedRows} 条重复记录`);
  } catch (error) {
    console.error(`[${new Date().toLocaleString()}] 删除重复记录失败：`, error);
  } finally {
    if (connection) await connection.end();
    isTaskRunning = false;
  }
}

// 每秒触发一次，仅当前任务完成后才执行下一次
setInterval(async () => {
  if (isTaskRunning) return;
  isTaskRunning = true;
  await removeDuplicateBids(1335);
}, 1000);

console.log('定时删除重复记录任务已启动，每秒执行一次');

三、关键注意事项 #

• 性能优化：如果bid_account表数据量很大，每秒执行DELETE可能给数据库带来压力。建议根据业务调整执行频率（比如改成每分钟一次），或者改成触发式去重（在新竞价记录插入后再执行，而非定时轮询）。
• 索引优化：给auction_id和bidding_price建立联合索引，能大幅提升SQL执行效率：

  CREATE INDEX idx_auction_bidding ON bid_account(auction_id, bidding_price);
  

• 安全防护：一定要用参数化查询（代码里的?占位符），不要直接把变量拼到SQL字符串里，避免SQL注入风险。

内容的提问来源于stack exchange，提问作者Clebson