I have a db with 100 tables. I want to delete data from all tables using mysql command or in phpmyadmin

CodePudding user response：

Use information_schema.TABLES make dynamic query and exeute.

select concat('delete from ',TABLE_NAME,';') from information_schema.TABLES where TABLE_SCHEMA='databasename';

CodePudding user response：

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

CodePudding user response：

• Backup your database structure (use mysqldump with --no-data command 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).