转载自片段社区 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()
