在Mybatis中不对select做任何改变，将物理删除的逻辑迁移到逻辑删除

假设现在有一个已经完成或者已经到中期的项目，但是忘记做逻辑删除了，更改的办法如下

第一步，在项目中添加如下的拦截器

import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.DataPermissionInterceptor;
import lombok.SneakyThrows;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.apache.commons.collections.CollectionUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;

import java.io.StringReader;
import java.sql.Connection;
import java.util.Collections;
import java.util.List;
import java.util.Properties;

@Component
@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class LogicDeleteInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);

        // 判断是否是select
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {
            return invocation.proceed();
        }

        BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
        String sql = boundSql.getSql();
        String finalSql = this.handleSql(sql);
        metaObject.setValue("delegate.boundSql.sql", finalSql);
        return invocation.proceed();
    }

    /**
     * 改写SQL
     * {@link DataPermissionInterceptor}
     *
     * @param originalSql 执行的SQL语句
     * @return 处理后的SQL
     */
    private String handleSql(String originalSql) throws JSQLParserException {
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        Select select = (Select) parserManager.parse(new StringReader(originalSql));
        processSelectBody(select.getSelectBody());
        return select.toString();
    }

    /**
     * 设置 where 条件 -- 使用CCJSqlParser将原SQL进行解析并改写
     *
     * @param plainSelect 查询对象
     */
    @SneakyThrows(Exception.class)
    protected void setWhere(PlainSelect plainSelect) {
        this.processPlainSelect(plainSelect);
    }

    /**
     * 生成拦截对象的代理
     *
     * @param target 目标对象
     * @return 代理对象
     */
    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    /**
     * mybatis配置的属性
     *
     * @param properties mybatis配置的属性
     */
    @Override
    public void setProperties(Properties properties) {
    }

    /**
     * 处理查询对象（初步分解）
     *
     * @param select 查询对象
     */
    private void processSelectBody(SelectBody select) throws JSQLParserException {
        // select from ...
        if (select instanceof PlainSelect) {
            processPlainSelect((PlainSelect) select);
        }
        // select from ... (union | union all | ...) select from ...
        else if (select instanceof SetOperationList setOperationList) {
            List<SelectBody> selectBodyList = setOperationList.getSelects();
            for (SelectBody s : selectBodyList) {
                processPlainSelect((PlainSelect) s);
            }
        }
    }

    /**
     * 处理查询对象（具体细化）
     *
     * @param plainSelect 查询对象
     */
    private void processPlainSelect(PlainSelect plainSelect) throws JSQLParserException {
        FromItem fromItem = plainSelect.getFromItem();

        // select 1
        if (fromItem == null) {
            return;
        }

        String mainTableLogicDelete = null;
        if (fromItem instanceof Table table) { // select from table
            Alias fromItemAlias = table.getAlias();
            String originalTableName = table.getName();
            String mainTableName = fromItemAlias == null ? originalTableName : fromItemAlias.getName();
            mainTableLogicDelete = mainTableName + ".del_flag = 0";
        } else if (fromItem instanceof SubSelect subSelect) { // select from (select ...)
            processSelectBody(subSelect.getSelectBody());
        }

        // join ...
        List<Join> joins = plainSelect.getJoins();
        if (CollectionUtils.isNotEmpty(joins)) {
            for (Join join : joins) {
                Table rightItem = (Table) join.getRightItem();
                Alias rightItemAlias = rightItem.getAlias();
                String rightItemOriginalTableName = rightItem.getName();
                String rightItemMainTableName = rightItemAlias == null ? rightItemOriginalTableName : rightItemAlias.getName();
                String subTableLogicDelete = rightItemMainTableName + ".del_flag = 0";
                Expression onExpression = join.getOnExpression();
                if (onExpression == null) {
                    join.setOnExpressions(Collections.singletonList(CCJSqlParserUtil.parseCondExpression(subTableLogicDelete)));
                } else {
                    join.setOnExpressions(Collections.singletonList(new AndExpression(onExpression, CCJSqlParserUtil.parseCondExpression(subTableLogicDelete))));
                }
            }
        }

        // where ...
        if (mainTableLogicDelete != null) {
            if (plainSelect.getWhere() == null) {
                plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(mainTableLogicDelete));
            } else {
                processWhereClauseSubSelects(plainSelect.getWhere());
                plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(mainTableLogicDelete)));
            }
        }
    }

    /**
     * 处理 where 子查询
     *
     * @param where where 条件
     */
    private void processWhereClauseSubSelects(Expression where) throws JSQLParserException {
        if (where instanceof SubSelect subSelect) {
            PlainSelect subSelectBody = (PlainSelect) subSelect.getSelectBody();
            processPlainSelect(subSelectBody);
        } else if (where instanceof BinaryExpression binaryExpression) {
            processWhereClauseSubSelects(binaryExpression.getLeftExpression());
            processWhereClauseSubSelects(binaryExpression.getRightExpression());
        } else if (where instanceof InExpression inExpression) {
            ItemsList rightItemsList = inExpression.getRightItemsList();
            if (rightItemsList instanceof SubSelect subSelect) {
                PlainSelect subSelectBody = (PlainSelect) subSelect.getSelectBody();
                processPlainSelect(subSelectBody);
            } else if (rightItemsList instanceof ExpressionList expressionList) {
                List<Expression> expressions = expressionList.getExpressions();
                for (Expression expression : expressions) {
                    processWhereClauseSubSelects(expression);
                }
            }
        } else if (where instanceof ExistsExpression existsExpression) {
            processWhereClauseSubSelects(existsExpression.getRightExpression());
        } else if (where instanceof NotExpression notExpression) {
            processWhereClauseSubSelects(notExpression.getExpression());
        } else if (where instanceof Parenthesis parenthesis) {
            processWhereClauseSubSelects(parenthesis.getExpression());
        } else if (where instanceof IsNullExpression isNullExpression) {
            processWhereClauseSubSelects(isNullExpression.getLeftExpression());
        } else if (where instanceof CaseExpression caseExpression) {
            for (Expression whenClause : caseExpression.getWhenClauses()) {
                processWhereClauseSubSelects(whenClause);
            }
            if (caseExpression.getElseExpression() != null) {
                processWhereClauseSubSelects(caseExpression.getElseExpression());
            }
        } else if (where instanceof Function function) {
            for (Expression parameter : function.getParameters().getExpressions()) {
                processWhereClauseSubSelects(parameter);
            }
        } else if (where instanceof Between between) {
            processWhereClauseSubSelects(between.getLeftExpression());
            processWhereClauseSubSelects(between.getBetweenExpressionStart());
            processWhereClauseSubSelects(between.getBetweenExpressionEnd());
        } else if (where instanceof AnyComparisonExpression anyComparisonExpression) {
            processWhereClauseSubSelects(anyComparisonExpression.getSubSelect());
        }
    }
}

然后将这个插件注册到mybatis里面

sessionFactory.setPlugins(new LogicDeleteInterceptor());

此时仅完成第一步，也就是对所有select在不手动改变的情况下，迁移到逻辑删除的逻辑上来

第二步，修改所有delete

这一步很简单，vscode的查找替换功能很强大，正则匹配也不复杂，可自行选择写python脚本或是直接编辑器替换

第三步，改表，执行如下的存储过程，为数据库内的所有表加一列

del_flag tinyint(1) not null default 0 comment '逻辑删除位'

create
    definer = root@localhost procedure alterMultiTableAddDelFlag()
BEGIN
    -- 定义表名变量
    DECLARE s_tablename VARCHAR(100);

    DECLARE cur_table_structure CURSOR
        FOR
        SELECT table_name
        FROM INFORMATION_SCHEMA.TABLES
        -- test = 数据库名称
        WHERE table_schema = '数据库名称'
          AND table_name NOT IN (SELECT t.table_name
                                 FROM (SELECT table_name, column_name
                                       FROM information_schema.columns
                                       WHERE table_name IN (SELECT table_name
                                                            FROM INFORMATION_SCHEMA.TABLES
                                                            WHERE table_schema = 'drgx-mes')) t
                                 WHERE t.column_name = 'object_name');

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s_tablename = NULL;

    OPEN cur_table_structure;

    FETCH cur_table_structure INTO s_tablename;

    WHILE (s_tablename IS NOT NULL)
        DO
            SET @MyQuery = CONCAT("alter table `", s_tablename,
                                  "` add COLUMN `del_flag` TINYINT(1) ", "COMMENT '逻辑删除位' NOT NULL DEFAULT 0");
            PREPARE msql FROM @MyQuery;

            EXECUTE msql;

            FETCH cur_table_structure INTO s_tablename;
        END WHILE;
    CLOSE cur_table_structure;


END;

完成以上三步，即可简单实现从物理删除到逻辑删除的迁移，具体情况还可具体分析

注：这样做的好处是，在已经很多张表并且业务逻辑已经在物理删除的框架内难以轻易改动的情况下，不需要往pojo里加delFlag，不需要在mapper的resultMap里加del_flag，不需要修改mapper里面任何的select语句，只需要修改mapper里的delete语句，就能完成迁移（前提是默认用户不需要接触到delFlag这个属性，大部分情况下是这样的，因为删除的操作会提供接口，delFlag这个属性一定要暴露给用户进行get和set的操作较少）