How to Stop Accidental Full‑Table Updates in MySQL with sql_safe_updates and MyBatis‑Plus

Preventing Accidental Full‑Table Updates

When an UPDATE statement is executed without a proper WHERE clause, MySQL will modify every row in the target table, which can cause severe data loss, especially when a column such as company_id is NULL.

Using sql_safe_updates in MySQL

The sql_safe_updates system variable blocks UPDATE or DELETE statements that lack an explicit WHERE condition. When the variable is set to ON (value 1), MySQL raises an error for such statements; when set to OFF (value 0), the statements run normally.

You can enable it at the session level: SET sql_safe_updates = 1; or globally: SET GLOBAL sql_safe_updates = 1; To verify the current setting, run: SHOW VARIABLES LIKE '%sql_safe_updates%'; After enabling, an unsafe statement such as: UPDATE user SET username='javaboy'; will produce an error like:

Note that enabling sql_safe_updates may affect existing applications that rely on unconditional updates, so review and adapt code before applying it in production.

MyBatis‑Plus IllegalSQLInnerInterceptor Plugin

MyBatis‑Plus provides the IllegalSQLInnerInterceptor to intercept high‑risk SQL such as full‑table updates or deletes. Its main capabilities are:

Detect and block statements that perform full‑table updates or deletions.

Enforce index usage to avoid full‑table scans.

Prevent unauthorized bulk modifications, reducing data‑loss risk.

Perform extra checks on SQL containing NOT, OR, or sub‑queries to catch logical or performance issues.

Configuration is straightforward—define a Spring bean that adds the interceptor:

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // Add illegal‑SQL interceptor
        interceptor.addInnerInterceptor(new IllegalSQLInnerInterceptor());
        return interceptor;
    }
}

When enabled, any UPDATE or DELETE without a proper WHERE clause triggers an error. However, statements that end with a harmless condition like WHERE 1=1 may bypass the check.

IDE Support

Several IntelliJ IDEA plugins can flag risky SQL patterns during development. The author mentions a personal favorite plugin (screenshot omitted) that highlights potential full‑table operations, though it may not catch every case described earlier.

Code Review Practices

Regular code reviews are essential; many unsafe SQL statements are discovered during peer review rather than at runtime.

Additional Remedies

Beyond enabling sql_safe_updates and using the MyBatis‑Plus interceptor, developers should adopt disciplined query writing, add explicit safeguards in repository layers, and continuously monitor database logs for unexpected full‑table modifications.