how to create mysql table names based on the current month and year

Question

I create mysql event script with the aim to create mysql tables automatically every month. I want the name of the tables based on the current month and year. How do I do that?

I am trying this:

CREATE DEFINER = `root`@`localhost` EVENT `event_name` 
ON SCHEDULE EVERY 1 MONTH 
ON COMPLETION NOT PRESERVE ENABLE 
DO 
CREATE TABLE IF NOT EXISTS  `currentMonth_currentYear_tableName` (
 `column1` INT( 11 ) NOT NULL ,
 `column2` DATETIME NOT NULL ,
 `column3` DECIMAL( 10, 1 ) NOT NULL ,
  PRIMARY KEY (  `column1` )
) ENGINE = INNODB DEFAULT CHARSET = utf8

Does this work? `CREATE TABLE IF NOT EXISTS CONCAT(DATE_FORMAT(NOW(),'%m_%Y'), '_tableName') (` — hargobind, Jul 17 '14 at 00:18
@hargobind, thanks but I get error. 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 '(NOW(),`%m_%Y`),`_tableName`) ( `column1` INT( 11 ) NOT NULL , `column2` DAT' at line 4. Problem in here: CONCAT(DATE_FORMAT(NOW(),`%m_%Y`),`_tableName`) — new bie, Jul 17 '14 at 00:25
Try assigning `CONCAT(DATE_FORMAT(NOW(),'%m_%Y'), '_tableName')` to a variable and using the variable where the table name should go. — hargobind, Jul 17 '14 at 00:26

score 3 · Accepted Answer · edited Apr 13 '17 at 12:42

I haven't tested this from an Events declaration, but it seems to work in my query browser:

SET @SQL = CONCAT('CREATE TABLE IF NOT EXISTS ', DATE_FORMAT(NOW(),'%m_%Y'), '_tableName (
 `column1` INT( 11 ) NOT NULL ,
 `column2` DATETIME NOT NULL ,
 `column3` DECIMAL( 10, 1 ) NOT NULL ,
  PRIMARY KEY (  `column1` )
) ENGINE = INNODB DEFAULT CHARSET = utf8'
);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The idea for CONCAT/PREPARE/EXECUTE was taken from https://dba.stackexchange.com/a/14401

score 2 · Answer 2 · answered Jul 17 '14 at 00:28

You can't use expressions or variables as identifiers in SQL. Identifiers names must be constant at the time you prepare the query.

To make a variable table name like you describe, you'll have use format an SQL statement as as string, and then use PREPARE and EXECUTE to run it.

In other words, the expression or variable must resolve to a constant identifier name before you prepare the statement.

how to create mysql table names based on the current month and year

2 Answers2