rename table to 'NOW() + old_table_name'

问题:

is it possible somehow to use the NOW() function or something similar in MYSQL rename table()?

the reason i need this is because instead of dropping old tables straight away we prefer to first rename then to old_ date-table-was-taken-ouf-of-use_ table_name so when we actualy delete it we know how long the table has been 'unavailable'

回答1:

You can create a dynamic SQL statement and execute that:

SET @tablename = 'MyTable';

SELECT @query := CONCAT('RENAME TABLE `', @tablename, '` TO `', 
    CURDATE(), @tablename, '`');

PREPARE STMT FROM @query;
EXECUTE STMT;

The curdate() function returns the current date as string in the format yyyy-MM-dd.

P.S. You can't execute multi-line statements like this from the Query Browser, but you can put them into a file (for example called commandfile.sql) and run them like:

mysql -u <user> -p<password> <dbname> < commandfile.sql