mysql (5.1) > create table with name from a variable
20,475
Solution 1
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;
Solution 2
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;
Author by
Jakob Jingleheimer
Updated on August 05, 2022Comments
-
Jakob Jingleheimer almost 2 years
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 to2020
.-
Admin almost 13 yearsIdentifiers are not variables. Variables are not identifiers. This requires the use of "dynamic SQL" -- it's icky in all databases I've worked with.
-
Admin almost 13 years
-