Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to force truncate all tables(which are all innodb) in a database in MySQL?

Tags:

mysql

innodb

I think I get foreign key constraint error when I try to truncate innodb tables. I was not having problems with this when using MyISAM.

Is there an easy way to force truncate all tables? Or should I just make a script to drop the database, create new one and then create the tables from scratch?

like image 642
developarvin Avatar asked Apr 06 '11 09:04

developarvin


People also ask

How delete all tables from MySQL database?

Connect to the target database. Select all tables from the left sidebar. Right-click and choose delete, or simply hit delete button. Press Cmd + S to commit changes to the server.


2 Answers

About the FK constraints, you could disable them with next statements -

SET FOREIGN_KEY_CHECKS = 0;
...DML statements
SET FOREIGN_KEY_CHECKS = 1; -- enable checking
like image 59
Devart Avatar answered Sep 19 '22 13:09

Devart


If you have foreign key problems during your operation you can:

ALTER TABLE tablename DISABLE KEYS

then do your business, and afterwards re-enable keys with:

ALTER TABLE tablename ENABLE KEYS

This techinique is used in MySQL dumps.

like image 22
vbence Avatar answered Sep 18 '22 13:09

vbence