基于Node.js与MySQL数据库的用户最后登录时间校验及不活跃账户自动删除实现方案咨询

Hey there! Let's walk through implementing both of these features with Node.js and MySQL—they're totally manageable once you break them down.

First off, you'll need to make sure your users table tracks the last login time. If you don't already have this field, add it first:

ALTER TABLE users ADD COLUMN last_login DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

(Note: The ON UPDATE CURRENT_TIMESTAMP will automatically update this field whenever the user logs in successfully—super handy!)

Next, in your Node.js code, when you need to check if a user is active, you can query this field and compare it against your "inactive" threshold (say, 30 days). Here's an example using the mysql2 library:

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

async function isUserActive(userId, inactiveThresholdDays = 30) {
  const connection = await mysql.createConnection({
    host: 'your-host',
    user: 'your-user',
    password: 'your-password',
    database: 'your-db'
  });

  try {
    const [rows] = await connection.execute(
      'SELECT last_login FROM users WHERE id = ?',
      [userId]
    );

    if (rows.length === 0) {
      throw new Error('User not found');
    }

    const lastLogin = new Date(rows[0].last_login);
    const thresholdDate = new Date();
    thresholdDate.setDate(thresholdDate.getDate() - inactiveThresholdDays);

    // Return true if last login is within the threshold, false otherwise
    return lastLogin >= thresholdDate;
  } finally {
    await connection.end();
  }
}

// Usage example
isUserActive(123)
  .then(isActive => {
    if (isActive) {
      console.log('User is active!');
    } else {
      console.log('User is inactive—time to prompt them or take action!');
    }
  })
  .catch(err => console.error(err));

If you're using an ORM like Sequelize, the logic is similar—you'd query the user and do the date comparison in your Node.js code, or even write a raw SQL condition in your query.

You've got two solid options here: a Node.js scheduled task, or a MySQL event scheduler. Let's cover both.

Option 1: Node.js Scheduled Task (using node-schedule) #

This is great if you want to keep the logic in your application layer. First, install the package:

npm install node-schedule

Then set up a job that runs periodically (say, every midnight) to delete users who haven't logged in in 90 days:

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

// Define the cleanup job
const cleanupInactiveUsers = schedule.scheduleJob('0 0 * * *', async () => {
  console.log('Starting inactive user cleanup...');
  const connection = await mysql.createConnection({
    host: 'your-host',
    user: 'your-user',
    password: 'your-password',
    database: 'your-db'
  });

  try {
    const [result] = await connection.execute(
      'DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 90 DAY)'
    );
    console.log(`Deleted ${result.affectedRows} inactive users`);
  } catch (err) {
    console.error('Cleanup failed:', err);
  } finally {
    await connection.end();
  }
});

console.log('Inactive user cleanup job scheduled (runs daily at midnight)');

Option 2: MySQL Event Scheduler #

If you prefer to handle this directly in the database (no dependency on your Node.js app running), use MySQL's built-in event scheduler. First, make sure it's enabled:

SET GLOBAL event_scheduler = ON;

Then create an event that runs daily to delete inactive users:

CREATE EVENT delete_inactive_users
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
  DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 90 DAY);

(Note: You might need the EVENT privilege on your database user to create this. Also, check your MySQL config to ensure the event scheduler stays enabled after a restart.)

Pro Tips #

• Always test deletion logic with a SELECT first before running DELETE—you don't want to accidentally remove active users!
• Consider adding a "soft delete" instead of hard deletion first (e.g., add a deleted_at column) if you might need to recover accounts later.
• Make sure your server/database timezone is consistent to avoid date comparison bugs.

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