mysql 变量 重命名,Mysql：如果存在则重命名表

This DROP TABLE IF EXISTS works, too bad that RENAME TABLE IF EXISTS doesn't work.

Can anyone suggest a solution for this query?

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 video_top_day TO video_top_day_for_delete' at line 1

query:

RENAME TABLE IF EXISTS video_top_day TO video_top_day_for_delete

解决方案

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;