Question 22116 in Mysql Question posted in
The official MySQL documentation can be found here.

Delete all data from multiple tables in mysql

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

Tags:

3

Answers


  1. 0

    Use information_schema.TABLES make dynamic query and exeute.

    select concat('delete from ',TABLE_NAME,';') from information_schema.TABLES where TABLE_SCHEMA='databasename';
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
  3. 0
    • 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).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search