CODEWITHSUNDEEP
mysqlsqlbashscripting
Xofo
Xofo

Reputation: 1278

Truncating tables between two dates (using MySQL and bash script)

I am trying to write a database truncate script that will truncate the tables in the database between two dates. This is a maintenance script that is run in a test setup to quickly clean things out.

The script is written in bash and calls MySQL. As previously stated this is a for testing purposes to cycle through things quickly.

I am getting an error. I suspect it may be the single quotes/tics in surrounding the dates. Any guidance would be appreciated.

mysql -umaintainer -pfoo -Nse 'show tables' TestDatabase | while read table; do mysql -umaintainer -pfoo -Nse "SET FOREIGN_KEY_CHECKS = 0 ; truncate table $table where date BETWEEN '2015-01-01 00:00:00' AND '2015-03-31 23:59:59'" TestDatabase; done

Error is:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where date BETWEEN '2015-01-01 00:00:00' AND '2015-03-31 23:59:59'' at line 1

Upvotes: 0

Views: 1144

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

truncate table does not take a where clause. It is all or nothing. That's why it is fast.

You can do:

delete t from $table t
    where date BETWEEN '2015-01-01 00:00:00' AND '2015-03-31 23:59:59';

If you need a fast way to delete large quantities of old rows, then you should think about partitioning. The database drops partitions much more quickly than it deletes the same rows.

Upvotes: 2

Related Questions