当前位置：首页 > news >正文

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

news 2025/5/10 14:50:06

一、描述需求：

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

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

需求分析：其实大概可以理解为：写一个方法，这个方法先执行删除表A的数据，然后再把视图view的数据查询插入到表A当中，然后再写一个定时任务，定时调用这个方法。
- 注意点：因为同时操作了两条sql，所以应该要使用事务进行操作
这里主要探讨的是数据库表清空数据的问题，所以定时任务就略过了

二、需求实现

当时的我就按照组长的要求写了类似下面这么一段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();  }  }  }  }  
}

代码测试

因为两条sql需要同成功同失败嘛，所以我们可以先把表A的数据删除了，然后故意把第一条清空表数据的sql写错，然后调用该方法：
- 如果表A有数据的话，那说明该方法的第二条sql执行了，说明这个事务写的不正确，就需要修改代码了，因为你第1条sql出现错误之后应该到catch模块直接回滚了整个事务了，所以表A不应该有数据。
- 如果表A依旧没有数据，这说明事务回滚了，所有sql执行无效（这才是正确的效果）。
因为两条sql需要同成功同失败嘛，我们可以故意把第二条给表A插入数据的sql写错，然后调用该方法：
- 这里注意了：正常来讲：如果表A数据没了，那说明事务没有回滚，这个事务写得有问题。，但是呢这里就是不正常了，因为有可能不是事务写得有问题题，而是第一条清空表数据的sql的问题,因为turncate table A不支持事务回滚（我当时在测的时候，一直以为是我的事务写得有问题，最后问了一下组长说用delete from A看一下，这才发现问题的原因所在）
- 如果表A数据还存在，那说明事务回滚了，正确。

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

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