Delete data from all tables in MYSQL

Question

I have 100 tables, 40,000 rows in each table. I want to go into MySQL and delete all rows from all tables.

...in 1 statement, if possible?

I want to keep the database and tables.

Answer 1

Begin by accessing phpMyAdmin via cPanel or Plesk.

1. Select the database you wish to work with (from the list of databases on the left column when on the phpMyAdmin home page).
2. A list of tables will appear in the left column and in the wider right column.
3. Check the table(s) you wish to truncate (delete data only).
4. In the drop down box that initially says “With selected:” select “Empty” from this list.
5. It will ask you if you really want to truncate the table(s). Check to make sure you selected the tables you really want to truncate.
6. Click on Yes to truncate (delete data) from the table.

Answer 2

In command line...

USE dbname

SET foreign_key_checks = 0;
TRUNCATE tablename;  //do this for each table you want emptied
SET foreign_key_checks = 1;

Answer 3

The most robust way to clear all tables in a database was submitted by kostanos in How to remove all MySQL tables from the command-line without DROP database permissions?

Since the code below clears all tables in the current database you might want to select a different database before you proceed.

USE DATABASE_YOU_WANT_TO_CLEAR;

Following snippet will remove data from the tables even in presence of foreign key constraints. You might also want to double check the list of tables before actual cleanup, just in case if you forgot to select proper database.

-- save current foreign key settings and disable foreign key checks
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET GROUP_CONCAT_MAX_LEN=32768;  -- you never know how people name their tables
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
  FROM information_schema.tables
  WHERE table_schema = (SELECT DATABASE());  -- operates on the current DB
SELECT IFNULL(@tables,'dummy') INTO @tables;  -- avoid error if there are no tables

-- At this point you might want to double check the list of tables
-- to be cleared.  Run SELECT @tables; to do so.

SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

Answer 4

Use MySQL Workbench to get the Entity Relationship Diagram of your database ( in Workbench : Database -> Reverse Engineer ). Then drop the database. Then create database using Workbench ( Database -> Forward Engineer ).

Answer 5

I had to restore a backup which was missing the create statements. So I restored an older backup but I had to get rid of the data. This oneliner helped me truncate everything.

mysql -u user -ppass -NBe "SELECT 'SET foreign_key_checks = 0;' UNION SELECT CONCAT('TRUNCATE TABLE myschema.', table_name, ';') FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'myschema' AND table_type = 'BASE TABLE'" | mysql -u user -ppass

For better readability again with line breaks:

mysql -u user -ppass -NBe "SELECT 'SET foreign_key_checks = 0;' 
   UNION SELECT CONCAT('TRUNCATE TABLE myschema.', table_name, ';') 
   FROM INFORMATION_SCHEMA.tables 
   WHERE table_schema = 'myschema' AND table_type = 'BASE TABLE'"
      | mysql -u user -ppass

be sure to use the string without line breaks and replace myschema with your table schema and set the correct login credentials or host for mysql process.

Answer 6

This one works for me in MySQL 5, and it is specific to tables:

echo 'show tables' | mysql --skip-column-names -u root YOUR_DB | awk '{print "truncate table " $0 ";"}' | mysql -u root YOUR_DB

Replace YOUR_DB by the name of your database. You have to provide your password twice, so you have a chance to think it again ... ;-)

Answer 7

Why couldnt you just export the structure of the database, delete the database, then recreate it and import the structure?

Answer 8

DELIMITER $$

DROP PROCEDURE IF EXISTS `cleanAllTables`$$

CREATE DEFINER=`root`@`%` PROCEDURE `cleanAllTables`()

BEGIN

DROP Temporary TABLE IF EXISTS AllTables;

Create Temporary Table AllTables (

SELECT @curRow := @curRow + 1 AS row_number
, table_name 
FROM INFORMATION_SCHEMA.tables s
JOIN    (SELECT @curRow := 0
) r
WHERE s.table_schema = 'databasename');

set @countOfAllTables = (select count(*) from AllTables);
set @c = 1;
WHILE @c<=@countOfAllTables DO

    set @table_name = (select table_name from AllTables where row_number = @c);
    set @stmt = concat( 'Truncate Table ', @table_name);
    Prepare stmt from @stmt;
    Execute stmt;
  SET @c = @c + 1 ;
END WHILE ;

END$$

DELIMITER ;

Answer 9

This two line Script is the best... try it

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO

Answer 10

$ mysqldump --no-data -u [username] -p[password] [database] > [location]
$ mysql -u [username] -p[password]
mysql > drop database [database]; create database [database];
mysql > exit;
$ mysql -u [username] -p[password] [database] < [location]

The --no-data switch preserves only the database table information, and will ignore all table data; you merely have to reimport the generated .sql file to regain all table information.

Answer 11

Easiest method to truncate all tables while retaining schema.

mysqldump -d -uuser -ppass --add-drop-table databasename > databasename.sql

mysql -uuser -ppass databasename < databasename.sql

Not sure if it will retain stored procedures as they are not in use where I work, but I use this regularly to reset databases.

The -d switch on mysqldump means "don't dump data."

The --add-drop-table prepends a DROP TABLE statement to every CREATE TABLE in the dump.

Answer 12

I don't think so (but I've been wrong before). What I tend to do is those cases is a two-step process.

If your DBMS has a command line interface, you can use it to create a script to do the bulk of the work, something like:

db2 "select 'db2 delete from ' | tblname from sysibm.systables
    where owner = 'pax'" >p2.sh
p2.sh

The first bit simply creates a p2.sh file (or a p2.cmd file under Windows) containing a delete from statement for every table owned by pax. Then you just run that command file to do the dirty work. You may want to check it first, of course :-)

Not the one-step process you were looking for but still very simple. I'm assuming here that mysql also has a command line interface.

Update:

The MySQL version of the above looks like it should be:

echo "select 'mysql truncate table ' | table_name
              from information_schema.tables" | mysql >p2.sh
bash p2.sh

This uses the truncate command which is usually more efficient than delete from for deleting all rows. It also uses the proper MySQL system tables to get the table names.

One point though - you may want to put a where clause on that select to limit the tables to those you want deleted. The query as it stands will try to delete every table. One possibility is to limit it with specific table_schema and/or table_type values.

Answer 13

Export the SQL script, delete the database, recreate the database against the script. :)

Answer 14

This will require a stored procedure or script that loops through each table and does:

truncate table <tablename>

To get the list of tables to truncate you can do something like:

SELECT table_name 
FROM INFORMATION_SCHEMA.tables 
WHERE table_schema = 'db_name' 

Answer 15

Just a guess...try it

TRUNCATE TABLE *;

Or

TRUNCATE TABLE table1, table2, table3;

Or

DELETE FROM table1, table2, table3 WHERE 1=1;

Answer 16

It's possible, but it has side-effects you might not like:

drop database <dbname>;

It means the tables' structures are deleted, as well as the indexes, stored procedures, etc., etc.

The only other way would be to write a stored procedure which loops somehow with

truncate table <tablename>;