I have a db with 100 tables. I want to delete data from all tables using mysql command or in phpmyadmin
2
-
1Does this answer your question? Truncate all tables in a MySQL database in one command?Schwern– Schwern2022-11-23 04:08:18 +00:00Commented Nov 23, 2022 at 4: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– Schwern2022-11-23 18:35:34 +00:00Commented Nov 23, 2022 at 18:35
Add a comment
|
3 Answers
- 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).
Comments
Use information_schema.TABLES make dynamic query and exeute.
select concat('delete from ',TABLE_NAME,';') from information_schema.TABLES where TABLE_SCHEMA='databasename';
4 Comments
Schwern
truncate table will be significantly faster than delete.
Gowtham
yes better idea change in concat('delete from ',TABLE_NAME,';') to concat('truncate table ',TABLE_NAME,';')
Ankit Raj
I want to delete all tables data.
Ankit Raj
is there some way to delete data from all tables or at the time of import only import table structure/columns
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
1 Comment
RB Projects
It will be better to add
SET GROUP_CONCAT_MAX_LEN=32768; in case there are a lot of tables, or they are with long names. I had a scenario with a database with about 130 tables and the default MAX_LEN wasn't enough. And do not forget to return the foreign key checks SET FOREIGN_KEY_CHECKS = 1; after the end of the script.