mysql中清空表数据使用delete和turncate的区别

一、描述需求：

1. 在实习的过程中，有被安排过这么一个任务：就是使用定时任务实现删除一张表A的数据，然后再把另一张视图表view上的数据插入到表A当中。
   • 当时组长给了我类似这么段代码：

truncate table A;   --清空表A的数据
INSERT INTO A SELECT * FROM view; -- 把视图view的数据插入表A

• 需求分析：其实大概可以理解为：写一个方法，这个方法先执行删除表A的数据，然后再把视图view的数据查询插入到表A当中，然后再写一个定时任务，定时调用这个方法。

  • 注意点：因为同时操作了两条sql，所以应该要使用事务进行操作

• 这里主要探讨的是数据库表清空数据的问题，所以定时任务就略过了

二、需求实现

1. 当时的我就按照组长的要求写了类似下面这么一段jdbc的代码（就是数据库连接的四件套）公司一般都会有自己封装好的jdcb工具类的
   • 就是删除表结构使用了：turncate table A进行清空A表的数据

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.SQLException;  
  
public class DatabaseOperations {  
  
    // 数据库连接信息（请根据实际情况修改）  
    private static final String URL = "jdbc:mysql://localhost:3306/your_database";  
    private static final String USER = "your_username";  
    private static final String PASSWORD = "your_password";  
  
    public static void main(String[] args) {  
        Connection connection = null;  
  
        try {  
            // 加载数据库驱动（如果是JDBC 4.0及以上版本，这步可以省略）  
            // Class.forName("com.mysql.cj.jdbc.Driver");  
            
            // 建立数据库连接  
            connection = DriverManager.getConnection(URL, USER, PASSWORD);  
            
            // 关闭自动提交，开启事务  
            connection.setAutoCommit(false);  
  
            try {  
                // 删除表A的所有数据  
                String turncateSql = "turncate table A";  
                PreparedStatement deleteStmt = connection.prepareStatement(turncateSql);  
                deleteStmt.executeUpdate();  
  
                // 将视图view_B的数据插入到表A中  
                // 假设表A和视图view_B的列是一致的，如果不是，请相应修改INSERT语句  
                String insertSql = "INSERT INTO A SELECT * FROM view_B";  
                PreparedStatement insertStmt = connection.prepareStatement(insertSql);  
                insertStmt.executeUpdate();  
  
                // 提交事务  
                connection.commit();  
  
                System.out.println("数据迁移完成。");  
  
            } catch (SQLException e) {  
                // 发生异常，回滚事务  
                connection.rollback();  
                throw e; // 可以选择重新抛出异常，以便上层处理  
            }  
  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            // 关闭连接  
            if (connection != null) {  
                try {  
                    connection.close();  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
        }  
    }  
}

代码测试

1. 因为两条sql需要同成功同失败嘛，所以我们可以先把表A的数据删除了，然后故意把第一条清空表数据的sql写错，然后调用该方法：

   • 如果表A有数据的话，那说明该方法的第二条sql执行了，说明这个事务写的不正确，就需要修改代码了，因为你第1条sql出现错误之后应该到catch模块直接回滚了整个事务了，所以表A不应该有数据。
   • 如果表A依旧没有数据，这说明事务回滚了，所有sql执行无效（这才是正确的效果）。

2. 因为两条sql需要同成功同失败嘛，我们可以故意把第二条给表A插入数据的sql写错，然后调用该方法：

   • 这里注意了：正常来讲：如果表A数据没了，那说明事务没有回滚，这个事务写得有问题。，但是呢这里就是不正常了，因为有可能不是事务写得有问题题，而是第一条清空表数据的sql的问题,因为turncate table A不支持事务回滚（我当时在测的时候，一直以为是我的事务写得有问题，最后问了一下组长说用delete from A看一下，这才发现问题的原因所在）
   • 如果表A数据还存在，那说明事务回滚了，正确。

mysql中清空表数据使用delete和turncate的区别

1. mysql中清空表数据使用delete和turncate的区别在性能、事务处理、日志记录以及重置表属性等方面都有体现。
   • 1. 性能
     • TRUNCATE：通常比DELETE快得多，因为它不逐行删除数据，而是直接删除表中的数据并重新创建表。这意味着它不会触发任何DELETE触发器，也不会记录每行删除的日志。
     • DELETE：会逐行删除数据，可以触发DELETE触发器，并且会记录每行删除的日志，这会导致性能下降，特别是在处理大量数据时。
   • 2. 事务和回滚（本文最想强调的）
     • TRUNCATE：在某些数据库系统中（包括MySQL的InnoDB存储引擎），TRUNCATE是一个DDL（数据定义语言）操作，它隐式地提交事务，因此不能被回滚
     • DELETE：是一个DML操作，可以在事务中执行，并且可以被回滚。
   • 3. 日志记录
     • TRUNCATE：通常只记录少量的日志信息，因为它不逐行删除数据。在InnoDB中，TRUNCATE会重置表的自增值（如果有的话），但不会重置表的AUTO_INCREMENT计数器（直到表被重新填充）。
     • DELETE：会记录每行被删除的数据的日志，这可能导致大量的日志生成，特别是在删除大量数据时。
   • 4. 重置表属性
     • TRUNCATE：会重置任何自增的计数器（但在InnoDB中，AUTO_INCREMENT计数器可能不会立即重置，直到表被重新填充）。它还会重置表的统计信息，这可能会影响查询优化器的决策。
     • DELETE：不会重置自增计数器或表的统计信息。
   • 5. 触发器
     • TRUNCATE：通常不会触发DELETE触发器（尽管这取决于具体的数据库系统）。
     • DELETE：会触发DELETE触发器。