Mysql:如果存在，请重命名表 [英] Mysql: RENAME TABLE IF EXISTS

问题描述

此DROP TABLE IF EXISTS起作用，太糟糕了，导致RENAME TABLE IF EXISTS不起作用.

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

查询:

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;

当您不想手动替换[DATABASE NAME]时，可以使用以下变量

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

SELECT DATABASE() INTO @db_name FROM DUAL;

这篇关于Mysql:如果存在，请重命名表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！