Mysql: RENAME TABLE IF EXISTS

First create table IF NOT EXISTS. Then RENAME it, so it will always exist!

Otherwise, rename the table, and if it doesn't exist just handle the error.

It is obvious, but it works.


I've managed to execute a code that always works and generates no errors when the table doesn't exist:

SELECT Count(*)
INTO @exists
FROM information_schema.tables 
WHERE table_schema = [DATABASE_NAME]
    AND table_type = 'BASE TABLE'
    AND table_name = 'video_top_day';

SET @query = If(@exists>0,
    'RENAME TABLE video_top_day TO video_top_day_for_delete',
    'SELECT \'nothing to rename\' status');

PREPARE stmt FROM @query;

EXECUTE stmt;

When you don't want to replace [DATABASE NAME] manually you can use the following variable

SELECT DATABASE() INTO @db_name FROM DUAL;

Tags:

Mysql