MYSQL stored procedure create dynamic table from date

Question

I'm facing a little problem to create the table name dynamically.

For example , This query (SELECT DATE_FORMAT(NOW(),'%Y%m')) returns 201702

My motive is to check whether the table exist. If it doesn't exist , it will create it automatically. Now here's my problem.

messages_`,datereturn

It seems I'm getting error for even compiling. How do we pass parameter to create the table?

Really appreciate your help.

Full Code:

   BEGIN
    SET datereturn = (SELECT DATE_FORMAT(NOW(),'%Y%m'));

    CREATE TABLE IF NOT EXISTS `messages_`,datereturn (
      `mid` bigint(17) NOT NULL,
      `uid` int(11) NOT NULL,
      `csid` int(11) NOT NULL,
      `content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
      `content_type` tinyint(4) NOT NULL,
      `datecreated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `datecreated1` bigint(13) DEFAULT NULL,
      PRIMARY KEY (`mid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    END

EDIT: it's different than the answer proposed. I'm making the table dynamic with MySQL 5.6. The above suggested thread doesn't work for my version. So, I don't know why it's a possible of duplicate.

@PM77-1, Well , i've tried that I'm getting this 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 ' datereturn) ( `mid` bigint(17) NOT NULL, `uid` int(11) NOT NULL, `csid' at line 4``` — FreedomPride, Feb 10 '17 at 02:47
Possible duplicate of [mysql (5.1) > create table with name from a variable](http://stackoverflow.com/questions/7489142/mysql-5-1-create-table-with-name-from-a-variable) — PM 77-1, Feb 10 '17 at 03:05

score 1 · Accepted Answer · answered Feb 10 '17 at 02:56

Stored procedures don't allow you to combine variables into SQL identifiers. In other words, things like table names must be set before the query is parsed.

So you need to create a dynamic SQL statement and make the stored procedure prepare the CREATE TABLE statement at runtime:

BEGIN
    SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS messages_',datereturn,' (',
      'mid bigint NOT NULL, ',
      'uid int NOT NULL, ',
      'csid int NOT NULL, ',
      'content varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, ',
      'content_type tinyint NOT NULL, ',
      'datecreated timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ',
      'datecreated1 bigint DEFAULT NULL, ',
      'PRIMARY KEY (mid)',
    ') ENGINE=InnoDB DEFAULT CHARSET=latin1');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;    
END

(Note: I have not tested this code, so if I missed a quote, I expect you know how to fix that.)

In this example, I also removed your back-ticks because they're not necessary, and I removed the length options for int types because they have no effect.

All that said, I have to question why you need a table per date. Seems like a code smell to me. Have you considered creating a table partitioned by date instead?

Bill, you're a life savior . I'll need to return this parameter to the server . So the insertion method will base on the date. Well, it's first time i'm listening abut table partitioned by date . I'm looking into it. — FreedomPride, Feb 10 '17 at 03:01

score 0 · Answer 2 · answered Feb 10 '17 at 03:01

0

SET @CrtTbl = CONCAT('CREATE TABLE', CONCAT('`messages_', datererun), '(mid bigint(17) NOT NULL, .... and so on

...

PREPARE CrtTblStmt FROM @CrtTbl;
EXECUTE CrtTblStmt;
DEALLOCATE PREPARE CrtTblStmt;

something like that

answered Feb 10 '17 at 03:01

Yuri G

1,206
1
9
13

MYSQL stored procedure create dynamic table from date

2 Answers2