I have a db with 100 tables. I want to delete data from all tables using mysql command or in phpmyadmin
Asked
Active
Viewed 47 times
0
-
1Does this answer your question? [Truncate all tables in a MySQL database in one command?](https://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command) – Schwern Nov 23 '22 at 04:08
-
If you're doing this because you want to reset the database after tests, you can instead run all the tests in a transaction and rollback at the end. – Schwern Nov 23 '22 at 18:35
3 Answers
2
- Backup your database structure (use
mysqldumpwith--no-datacommand line option). - Drop database.
- Restore database from the dump.
This method have no problems with FOREIGN KEY relations. Rather than DELETE/TRUNCATE usage where you must clear the tables content in definite order (if you'd clear master table before slave one then the deletion will fail due to referential constraint violation).
Akina
- 39,301
- 5
- 14
- 25
1
Use information_schema.TABLES make dynamic query and exeute.
select concat('delete from ',TABLE_NAME,';') from information_schema.TABLES where TABLE_SCHEMA='databasename';
Gowtham
- 61
- 4
-
3[`truncate table`](https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html) will be significantly faster than `delete`. – Schwern Nov 23 '22 at 04:09
-
yes better idea change in concat('delete from ',TABLE_NAME,';') to concat('truncate table ',TABLE_NAME,';') – Gowtham Nov 23 '22 at 04:10
-
-
is there some way to delete data from all tables or at the time of import only import table structure/columns – Ankit Raj Nov 23 '22 at 04:17
0
or try this one
SET FOREIGN_KEY_CHECKS = 0;
SET @TABLES = NULL;
SELECT GROUP_CONCAT('delete from ', table_name,';') INTO @TABLES FROM information_schema.tables
WHERE table_schema = 'databasename' and table_name in ('tbl_audit_trail','tbl_celery');
SET @TABLES= replace( @TABLES,',','');
select @TABLES;
copy the result and execute
Gowtham
- 61
- 4