16

I'm trying to create a table with a name based on the current year and month(2011-09), but MySQL doesn't seem to like this.

SET @yyyy_mm=Year(NOW())+'-'+Month(NOW());
CREATE TABLE `survey`.`@yyyy_mm` LIKE `survey`.`interim`;
SHOW TABLES IN `survey`;

+-----------+
| interim   |
+-----------+
| @yyyy_mm  |
+-----------+

If I do CREATE TABLE; without the ticks around @yyyy_mm, I get a generic syntax error.

@yyyy_mm resolves to 2020.

Jakob Jingleheimer
  • 30,952
  • 27
  • 76
  • 126
  • Identifiers are not variables. Variables are not identifiers. This requires the use of "dynamic SQL" -- it's icky in all databases I've worked with. –  Sep 20 '11 at 17:25
  • See http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure and http://stackoverflow.com/questions/929244/mysql-create-table-with-dynamic-database-name and http://stackoverflow.com/questions/5530755/mysql-variables-storing-database-name –  Sep 20 '11 at 17:27

2 Answers2

35

You should be able to do something like this:

SET @yyyy_mm=DATE_FORMAT(now(),'%Y-%m');
SET @c = CONCAT('CREATE TABLE `survey`.`',@yyyy_mm, '` LIKE `survey`.`interim`');
PREPARE stmt from @c;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
nos
  • 223,662
  • 58
  • 417
  • 506
8
set @yyyy_mm=concat(year(now()),'-',month(now()));
set @str = concat('create table survery.`', @yyyy_mm,'` like survey.interim;');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98