MySQL: IF EXISTS ... TRUNCATE

Question

Want to truncate a table if it exists:

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mytable') TRUNCATE mytable

Error:

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 'IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mytable') ' at line 1

I tried also to add THEN after ) but the problems seems to be at IF.

Answer 1

You need the two statements below to do that:

create table if not exists <mytable>;

truncate table <mytable>;

Answer 2

So I had a similar issue, and to resolve it, I created this procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS `truncate_if_exist`$$

CREATE  PROCEDURE `truncate_if_exist`(IN tbl_name VARCHAR(150) )
  BEGIN
    IF EXISTS( SELECT 1 FROM information_schema.TABLES WHERE table_name = tbl_name AND table_schema = DATABASE()) THEN
    SET @query = CONCAT('TRUNCATE ', tbl_name);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END IF;
  END $$

DELIMITER ;

And then called it for each table that I wanted to truncate

for example:

CALL truncate_if_exist('users');
CALL truncate_if_exist('random_tmp_table');

Obviously, if the table does not exist, it will not run the TRUNCATE command.