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
– FreedomPride, Commented Feb 10, 2017 at 2:47
Possible duplicate of mysql (5.1) > create table with name from a variable — PM 77-1
– PM 77-1, Commented Feb 10, 2017 at 3:05

Bill Karwin · Accepted Answer · 2017-02-10 02:56:03Z

1

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?

answered Feb 10, 2017 at 2:56

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FreedomPride Over a year ago

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.

Yuri G · Accepted Answer · 2017-02-10 03:01:20Z

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, 2017 at 3:01

Yuri G

1,2821 gold badge10 silver badges14 bronze badges

Collectives™ on Stack Overflow

MYSQL stored procedure create dynamic table from date

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related