How to TRUNCATE table if the table doesn't exists

Question

I want to run a few TRUNCATE statements. But in between some of the database doesn't exists. But SQL stops the rest of the queries.

Is there any way to ignore the error and continue to execute the rest of the statements.

TRUNCATE _aaaaa_tmp;
TRUNCATE _bbbbb_tmp;
TRUNCATE _ccccc_tmp;

Error Code: 1146. Table '_bbbbb_tmp' doesn't exist

Answer 1

It depends on how you are executing those queries:

• If you are loading an SQL file, execute then with --force, and errors will be ignored (all errors! So if you want to catch other possible issues, that is going to be a problem)
• If run from an application, you can execute each command individually and only ignore on truncate for the specific table not found error
• With some sql logic, you can check for table existence on information_schema.tables, although you may need to create a procedure just for that (raw command line doesn't allow for programming logic directly).

I think implementing that outside of SQL would be the preferred method (there is no "good" dynamic SQL execution on MySQL (other than tricks with prepared statements). E.g. a bash one liner (no error or escaping handling):

mysql -BN -e "SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)
              FROM information_schema.tables 
              WHERE table_schema in ('schemas', 'to', 'process') 
              AND table_name like '\_%\_tmp'" 
| while read table; do mysql -e "TRUNCATE TABLE $table"; done