使用 Node 脚本清除 mysql 关联表中无效的数据

转载自片段社区 Node 脚本清除 mysql 关联表中无效的数据

// 本脚本依赖 knex 和 mysql： yarn add knex mysql

const Knex = require('knex')

const db = Knex({
  client: 'mysql',
  connection: {
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'test'
  }
})

/**
 * 清除与 User 表关联的失效数据
 * @param {*} target_table
 */
async function clearByUser(target_table) {
  await clearTable(target_table, 'user_id', 'user', 'id')
}

/**
 * 清除关联表中无效的数据
 * @param {*} target_table 要执行清除操作的[目标表]
 * @param {*} target_field 与源表关联的[目标表字段]
 * @param {*} source_table 与目标表关联的[源表]
 * @param {*} source_field 与目标表关联的[源表字段]
 */
async function clearTable(target_table, target_field, source_table, source_field) {
  let ids = (await db
    .table(target_table)
    .select(`${target_table}.id`)
    .whereRaw(`${target_field} is not null`)
    .whereRaw(`${target_field} not in (select ${source_field} from ${source_table})`)).map(it => it.id)

  let changeCount = await db
    .table(target_table)
    .whereRaw(`${target_field} is not null`)
    .whereRaw(`${target_field} not in (select ${source_field} from ${source_table})`)
    .del()

  if (changeCount > 0) {
    console.log(`表 ${target_table} 中 ID 为 ${ids} 的数据已删除`)
  }
}

async function main() {
  await clearByUser('feedback')
  // DELETE FROM feedback_reply WHERE feedback_id NOT IN (SELECT id FROM feedback)
  await clearTable('feedback_reply', 'feedback_id', 'feedback', 'id')
  await clearByUser('notice')
}

main()