清空mysql指定库里所有表数据-自动删除所有表,有外键约束的表优先删除? 由于要清空数据库数据 ,手动非常麻
清空mysql指定库里所有表数据-自动删除所有表,有外键约束的表优先删除
? 由于要清空数据库数据 ,手动非常麻烦。网上找了一下，有一个Oracle的，参照它，在其上修改一下用于Mysql，把代码奉献如下:
?相关连接:
清空mysql指定库里所有表数据
?
自动删除所有表,有外键约束的表优先删除
还差一个SQLSERVER 版，那个弄出来了，通知俺一下，感觉这个东西还蛮有用的。
代码如下:
?package com.jtsite.DAOJDBC;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import javax.servlet.UnavailableException;import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;/** * @author Bicashy,fencer911(for Mysql Version) */public class TableOperate {static Statement stmt = null;static Connection conn = null;static Map map = new HashMap(); //用来保存已经删除了的表的集合static Map filterMap = new HashMap(); //用来保存需要过滤的表的集合static String schema ;/** * 获得数据库链接 * @return */private static Connection getConnection(){try {Class.forName( "com.mysql.jdbc.Driver").newInstance(); //String url= "jdbc:oracle:thin:@10.45.10.177:1521:highway"; String url= "jdbc:mysql://localhost:3306/laidong8v5?useUnicode=true&characterEncoding=utf-8";String user= "java"; String password= "java"; schema = user;conn = DriverManager.getConnection(url,user,password);return conn;} catch (InstantiationException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalAccessException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} return null;}/** * 获得该用户的所有表,并删除表（除了要过滤的表） */private static void findDeleteTableSQL(){if(conn!=null){try {stmt=conn.createStatement(); String sql = "show TABLES";//找到该链接用户的所有表 ResultSet rs=stmt.executeQuery(sql); while(rs.next()){String tabName = rs.getString(1);//getString("table_name");//如果map中包含了表名,说明已经删除过了//如果filterMap中包含了表名,则不删除if(!map.containsKey(tabName)&&!filterMap.containsKey(tabName)){printDeleteTableSQL(tabName);}}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/** * * 删除表，并将删除表的语句输出到控制台(记录后方便在数据库客户端执行) * @param l * @param tableName * @return */private static void printDeleteTableSQL(String tableName){String sql = "DELETE FROM "+tableName.toUpperCase();try { stmt=conn.createStatement(); System.out.println(sql+";"); stmt.execute(sql);stmt.close(); //将删除语句输出到控制台map.put(tableName,null);} catch (MySQLIntegrityConstraintViolationException e) {// TODO Auto-generated catch blockString error = e.toString(); System.out.println(error);int p1=error.indexOf("(");int p2=error.indexOf("FOREIGN KEY");error=error.substring(p1, p2);int p3=error.indexOf("CONSTRAINT");error=error.substring(p3);error=error.replaceAll("CONSTRAINT", "").replaceAll(" ", "").replaceAll("`", "");//截取错误信息得到外键约束名称String fk_constraints =error;System.out.println("fk_constraints"+fk_constraints);deleteTableNameFromFK(fk_constraints);//删除外键约束表后,就可以将本表删掉printDeleteTableSQL(tableName);}catch (Exception e) {System.out.println(e.toString());}}/** * 删除通过外键约束找到的有子记录的表 * @param fk_constraints */private static void deleteTableNameFromFK(String fk_constraints){String sql = "select table_name from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema='laidong8v5' and constraint_name='"+fk_constraints+"'";try {ResultSet rs=stmt.executeQuery(sql); while(rs.next()){String tabN = rs.getString("table_name");printDeleteTableSQL(tabN);//递归}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/** * 初始化需要过滤表的集合(该表如果是别的表的外键关联表也有可能被删除) */private static void initFilterMap(){String[] tables = {"ETC_USER","etc_dic_data","MENU"};for (int j = 0; j < tables.length; j++) {filterMap.put(tables[j].toUpperCase(),null);}}public static void main(String[] args) {getConnection();//initFilterMap();//初始化不需要删除的表//删除该用户下所有表（除了需要过滤的）,并获得删除语句findDeleteTableSQL();//删除某一个表（有外键约束的表将先删除）,并获得删除语句//printDeleteTableSQL("obu_mast");} }?
代码如下:
?package com.jtsite.DAOJDBC;
import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import javax.servlet.UnavailableException;import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;/** * @author Bicashy,fencer911(for Mysql Version) */public class TableOperate {static Statement stmt = null;static Connection conn = null;static Map map = new HashMap(); //用来保存已经删除了的表的集合static Map filterMap = new HashMap(); //用来保存需要过滤的表的集合static String schema ;/** * 获得数据库链接 * @return */private static Connection getConnection(){try {Class.forName( "com.mysql.jdbc.Driver").newInstance(); //String url= "jdbc:oracle:thin:@10.45.10.177:1521:highway"; String url= "jdbc:mysql://localhost:3306/laidong8v5?useUnicode=true&characterEncoding=utf-8";String user= "java"; String password= "java"; schema = user;conn = DriverManager.getConnection(url,user,password);return conn;} catch (InstantiationException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalAccessException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} return null;}/** * 获得该用户的所有表,并删除表（除了要过滤的表） */private static void findDeleteTableSQL(){if(conn!=null){try {stmt=conn.createStatement(); String sql = "show TABLES";//找到该链接用户的所有表 ResultSet rs=stmt.executeQuery(sql); while(rs.next()){String tabName = rs.getString(1);//getString("table_name");//如果map中包含了表名,说明已经删除过了//如果filterMap中包含了表名,则不删除if(!map.containsKey(tabName)&&!filterMap.containsKey(tabName)){printDeleteTableSQL(tabName);}}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/** * * 删除表，并将删除表的语句输出到控制台(记录后方便在数据库客户端执行) * @param l * @param tableName * @return */private static void printDeleteTableSQL(String tableName){String sql = "DELETE FROM "+tableName.toUpperCase();try { stmt=conn.createStatement(); System.out.println(sql+";"); stmt.execute(sql);stmt.close(); //将删除语句输出到控制台map.put(tableName,null);} catch (MySQLIntegrityConstraintViolationException e) {// TODO Auto-generated catch blockString error = e.toString(); System.out.println(error);int p1=error.indexOf("(");int p2=error.indexOf("FOREIGN KEY");error=error.substring(p1, p2);int p3=error.indexOf("CONSTRAINT");error=error.substring(p3);error=error.replaceAll("CONSTRAINT", "").replaceAll(" ", "").replaceAll("`", "");//截取错误信息得到外键约束名称String fk_constraints =error;System.out.println("fk_constraints"+fk_constraints);deleteTableNameFromFK(fk_constraints);//删除外键约束表后,就可以将本表删掉printDeleteTableSQL(tableName);}catch (Exception e) {System.out.println(e.toString());}}/** * 删除通过外键约束找到的有子记录的表 * @param fk_constraints */private static void deleteTableNameFromFK(String fk_constraints){String sql = "select table_name from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema='laidong8v5' and constraint_name='"+fk_constraints+"'";try {ResultSet rs=stmt.executeQuery(sql); while(rs.next()){String tabN = rs.getString("table_name");printDeleteTableSQL(tabN);//递归}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/** * 初始化需要过滤表的集合(该表如果是别的表的外键关联表也有可能被删除) */private static void initFilterMap(){String[] tables = {"ETC_USER","etc_dic_data","MENU"};for (int j = 0; j < tables.length; j++) {filterMap.put(tables[j].toUpperCase(),null);}}public static void main(String[] args) {getConnection();//initFilterMap();//初始化不需要删除的表//删除该用户下所有表（除了需要过滤的）,并获得删除语句findDeleteTableSQL();//删除某一个表（有外键约束的表将先删除）,并获得删除语句//printDeleteTableSQL("obu_mast");} }??
??